4

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

Stefan Lasiewski
  • 23,667
  • 41
  • 132
  • 186
Tom Collins
  • 143
  • 1
  • 1
  • 4

3 Answers3

10

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

Zoredache
  • 130,897
  • 41
  • 276
  • 420
  • 2
    See: http://superuser.com/questions/479138/trying-to-set-up-ssh-tunneling-to-mysql-server-for-mysql-query-browser/479434#479434 – Zoredache Mar 13 '13 at 18:20
8

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 take effect properly with:

flush privileges
duenni
  • 2,959
  • 1
  • 23
  • 38
Kai Bojens
  • 158
  • 3
  • 5
    While this will work, permitting everything `%` or `%comcast.net` seems like a bad idea. – Zoredache Mar 13 '13 at 18:56
  • 3
    +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 05: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 08: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
5

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.

Giacomo1968
  • 3,542
  • 27
  • 38