Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've tried searching for an answer, but can't quite find it.

I need to remotely access a MySQL instance through the local MySQL Workbench. The port is turned on (I believe). I can Telnet into it.

All the examples I see say something along the lines of

GRANT ALL PRIVILEGES ON *.* TO USER-NAME@IP IDENTIFIED BY "PASSWORD";

The trouble with this is that I'm remoting in from a dynamic IP. The error I get is

Access denied for user 'root'@'c-67-166-150-41.hsd1.ca.comcast.net' (using password: YES)

How do I set it up to allow me in? I do have root access to the MySQL Workbench on the remote machine if I remote control in.

Thanks

share|improve this question
    
I think you've answered your own question. –  Michael Hampton Mar 13 '13 at 18:14
    
No, I didn't. My IP address is dynamic. I can't count on "c-67-166-150-41.hsd1.ca.comcast.net" staying the same. –  Tom Collins Mar 13 '13 at 18:16

3 Answers 3

up vote 7 down vote accepted

You should try to use the "%" for the host part. Something like

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

(Taken from MySQL Documentation)

Make sure that the changes talk affect with:

flush privileges
share|improve this answer
4  
While this will work, permitting everything % or %comcast.net seems like a bad idea. –  Zoredache Mar 13 '13 at 18:56
2  
+1 for for Zoredcache: this is what VPNs are for. But additionally a DB should NEVER be exposed on the internet even with strong auth. –  symcbean Mar 13 '13 at 23:45
    
Yes, it does seem like bad practice, but it's a quick and easy solution right now. Currently, I'm only in development. Once it goes into production, I'll remove the access. I also used @Zoredache's suggestion of using %comcast.net. –  Tom Collins Mar 14 '13 at 5:14
2  
IMO, a putty tunnel is a far easier quick an easy solution. Since you wouldn't have needed to change anything. But it is up to you. –  Zoredache Mar 14 '13 at 8:06
    
Any experienced professional will tell you this solution is incomplete and you are unwise to pursue this solution without at least SSH tunnels. If you do this, please make sure that the user's password is strong, and make sure that your MySQL server has been updated to address all security vulnerabilities. Bots are scanning the internet for services like open MySQL servers constantly. Since your MySQL port has already been open for a few days, chances are that some botnets have already found it and will attempt exploits and brute-force attacks against your server. –  Stefan Lasiewski Mar 14 '13 at 18:16

You would be far better off by using a tunnel of some sort to access the system. Putty/SSH provides this feature quite nicely.

share|improve this answer
2  
See: superuser.com/questions/479138/… –  Zoredache Mar 13 '13 at 18:20

I would not recommend opening up MySQL to anyone other than localhost. But you can remote in if you use a client that handles SSH tunneling. Which MySQL Workbench doesn't seem to do.

The benefits of SSH tunneling is that you can keep the server safe by not exposing MySQL to the world. You can setup SSH tunneling manually if you wish, but many DB clients have this capability baked in.

On the Mac, Sequel Pro works quite well.

It gives you the option to choose a connection via SSH. Once in that config you enter the following:

  • Name: [whatever name for the connection you want]
  • MySQL Host: 127.0.0.1 [which is localhost to the machine]
  • Username: [db username]
  • Password: [db password]
  • Database: [database name or blank to show all you have access to]
  • Port: 3306 [standard MySQL port]

Now here comes the SSH stuff. Which is 100% the same info you enter when entering an SSH session:

  • SSH Host: [hostname or IP address of the server]
  • SSH User: [the SSH username]
  • SSH Password: [the SSH password]
  • SSH Port: [leave blank of the setup is standard]

And that said if you do want to have a pure IP address based login to MySQL, then you should open up the server to access remote clients but also have a firewall on the front-end that restricts access on a per-IP basis to MySQL.

share|improve this answer
    
Why would MyQL Workbench need to? Just fire up an ssh tunnel and pont MySQL workbench to the ip address/port where it's listening. –  symcbean Mar 13 '13 at 23:47
    
Not everyone knows or understands how SSH tunneling works. So something like Sequel Pro makes it easier for a novice user. –  JakeGould Mar 14 '13 at 0:10
1  
MySQL Workbench has built-in support for SSH tunneling. –  Chris S Mar 14 '13 at 18:07
    
Oh. Well, never mind then. –  JakeGould Mar 14 '13 at 18:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.