?
Solved

Open MySQL server for remote  access

Posted on 2014-08-08
8
Medium Priority
?
293 Views
Last Modified: 2014-08-12
Hi,

I have a server running Windows Web Server 2007. On this I have IIS 7 and am running MySql.

This is running fine over localhost. However, I want to hook up remotely from my machine while developing to the server.

As its working over localhost, I assume i just need to open up port 3307 on the firewall. Now I'm worried about security with this? could anyone please advise the safest way to do this and if poss please provide some instructions so I can ensure that its protected?

Thanks in advance.

Matt
0
Comment
Question by:flynny
  • 4
  • 4
8 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40248712
You probably need to adjust permissions as well, but for the firewall question ...

Does your development machine (or rather its network) have a fixed public address?  If so, open a hole in the firewall for only that IP.
0
 

Author Comment

by:flynny
ID: 40248799
hmm.

Yes and no. The work address has a fixed IP so I could fix it for that. However I was also hoping to work fro home but this doesnt have a fixed IP.

Server side from windows firewall I just tried opening the 3306 port for now doing the following;

1. New Rule.
2. Select Port
3. Select TCP specific port 3306
4. Allow the connection
5. Rule applies Domain/Private/Public all selected.
6. Named the rule.

I then loaded MySQL workbench locally to test the connection.

Setup new connection -> Standard TCP/IP
Server: IPaddress of server por 3306.
user: root and password

I am assuming it is connecting but I get the error;

'Access denied for user'
'root'@'remote.seperateurl.co.uk' (Using password:YES)

now I dont understand the @ 'remote.seperateurl.co.uk' as the domain doesnt relate to the server? can I set this?
0
 

Author Comment

by:flynny
ID: 40248824
ok I think I am getting somewhere.

following this article;

http://serverfault.com/questions/230012/mysql-access-denied-for-user-rootx-x-x-x

I can see localhost is the only value in the list of the hosts table. This is great and will be fine for my work location, as I can add the IP. How can I fix this issue for the public ip issue working from home?
0
When ransomware hits your clients, what do you do?

MSPs: Endpoint security isn’t enough to prevent ransomware.
As the impact and severity of crypto ransomware attacks has grown, Webroot fought back, not just by building a next-gen endpoint solution capable of preventing ransomware attacks but also by being a thought leader.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40248931
http://www.rackspace.com/knowledge_center/article/mysql-connect-to-your-database-remotely

This way of doing it creates a user@specified_IP with access.  I would consider adding
dev_user@work_ip
and
dev_user@home_ip

with the same password.
0
 

Author Comment

by:flynny
ID: 40248959
Ok thanks.

Any ideas on how i can handle the home issue with no static ip??

I assume as long as I don't renew the connection I can just added the current ip?
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 40248969
Consider using a dynamic DNS provider.


http://dev.mysql.com/doc/refman/5.1/en/account-names.html

create user 'home_user'@'my_name.dynamicdns.com' identified by 'my_cool_password';
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40248971
Using a domain name does suggest a slower connection b/c DNS lookups will be involved.  But for development rather than production, I doubt it will matter.
0
 

Author Closing Comment

by:flynny
ID: 40255718
brilliant thank you for your help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article offers some helpful and general tips for safe browsing and online shopping. It offers simple and manageable procedures that help to ensure the safety of one's personal information and the security of any devices.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question