Jan
15
2007

Howto: Remote Root Access to MySql

Very quickly, another thing that I typically like to do on my server boxes is allow root access to my Mysql database from remote computers. I don’t forward the port through my router and I use a very secure password (doesn’t everyone?). I don’t want to create a security risk, I just want to connect to the database from other computers around my network – particularly from my laptop.

Again (like most of my instructions) these instructions are for Ubuntu – currently Edgy Eft.

sudo apt-get install mysql-server

Ubuntu installs Mysql at /etc/mysql/ by default. Now we need to set a root password.

mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST"
> = PASSWORD('new_password');

Now while we’re still here, we’ll create a new HOST for root and allow root to login from anywhere.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
> IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit

Thanks to Thom for the Flush Privileges comment.  I think it depends on other settings in your MySQL setup, since I didn’t have to, but just in case, I’ve added it here.  We’re almost done now. We just have to tell Mysql to allow remote logins.

sudo vi /etc/mysql/my.cnf

Out-of-the-box, MySQL only allows connections from the localhost identified by the IP Address of 127.0.0.1.  We need to remove that restriction, so find the line that says

bind-address = 127.0.0.1

and comment it out. That’s all there is to it! Now get your favorite MySql client and start developing.

Related Posts

47 Comments + Add Comment

  • Thanks a lot for sharing. it takes less then 1 min but learn whatever i was looking for.

  • Thank you. You help me to resolve my trouble in a seconds.

  • Thanks, this helped.

  • Fantastic!!!

    Accidently set ‘password’ first time, without realising that’s where I should put my own password.

    Works on Ubuntu 8.04…

    Adam.

  • This should be in every mysql faq

  • What is the syntax for commenting out a line in a my.conf file?

    Further, my.cnf did not exist on my system yet. I installed 5.0.67 from source. It lists the –data-dir as /usr/local/mysql/var. Could it go here? If it is empty, will adding that line and commenting it out do anything?

    Thanks

  • Thanks!!!

  • Thanks for the help, the information is accurate

  • Don’t you need to run FLUSH PRIVILEGES; after the GRANT ALL?

    Thanks
    Thom

  • Thansk for the help. good job

  • Seriously, do not forget the FLUSH PRIVILEGES…I had done all the other steps several times to no avail…

  • The syntax for commenting is simply to add a # in front of the line, so it would say:

    #bind-address = 127.0.0.1

    If your my.conf file is not in that location you’ll have to find it because there are all sorts of settings in there that tell MySQL how to run. If the line doesn’t exist in your my.conf file when you find it, then you can skip this step. Adding a commented out line will not change anything.

  • thanks! very helpful

  • [...] found the tip originally over here. no comments yet.« CakePHP Console Cron Job Working on Godaddy Shared Hosting [...]

  • Thanks. Works for me too

  • Thanks

  • Nice post. Just a comment, that I *think* it would be pretty important not to simply comment out this line:

    bind-address = 127.0.0.1

    But to change the IP with the machine’s IP address, which is probably often an internal IP address.

  • thanx for this! got my workbench working in a snap after reading this!

  • hi, i still get this error after doin all the tutorial says, please help me:
    1130 – Host ‘mypc.local’ is not allowed to connectto this MySQL server

  • ah, thanks. I didn’t want to do all of the things other tutorials told me about, all I wanted to do was know how to grant all privileges on *.* to ‘root’@’%’ so I could log in remotely. Perfect. Thanks.

  • highly appreciated. I resolved my problem.

  • I followed your instructions and initially still got the “access denied” error. When you make your root user at the MYSQL server, that user’s password must be encrypted by the MYSQL server in order to function. Otherwise, logging in from another host with the exact password will throw an error.

    SET PASSWORD FOR ‘root’@’xx.xx.xx.xx’ = PASSWORD(‘abc123′);

  • Is there a method to perform this from phpmyadmin? Thank you.

  • Thank you, I tried numerous other ways and this is by far the quickest (and easiest).

  • that last compontnent of #bind-address = 127.0.0.1 did it for me. I have been hacking away for hours and was screwed by that one line!

  • I used the my-huge.cnf from the mysql installation package and copied it to /etc/my.cnf, but inside my.cnf does not have bind-address = 127.0.0.1. I restarted the server using mysqld_safe command and it still does not give me remote access for root.

    I am trying to use aquadata to access from my computer (192.168.0.1) to MySQL Server 192.168.0.2, but the aquadata keeps giving access denied, using password yes.

    Can anyone help?
    THank you

  • Sorry my bad… I forgot to replace the ‘password’ with my password. Problem solved guys. Sorry for the commotion.

  • OMG thank you!!! 4 hours later but yay!!!

  • i tried to install mysql-server following error in syslog

    mysqld: 120808 6:37:57 [Note] /usr/sbin/mysqld: Normal shutdown
    mysqld:
    mysqld: 120808 6:37:57 [Note] Event Scheduler: Purging the queue. 0 events
    mysqld: 120808 6:37:57 InnoDB: Starting shutdown…
    mysqld: 120808 6:37:59 InnoDB: Shutdown completed; log sequence number 0 44233
    mysqld: 120808 6:37:59 [Note] /usr/sbin/mysqld: Shutdown complete
    mysqld:
    mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
    mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
    mysqld: 120808 6:38:00 [Note] Plugin ‘FEDERATED’ is disabled.
    mysqld: 120808 6:38:00 InnoDB: Initializing buffer pool, size = 8.0M
    mysqld: 120808 6:38:00 InnoDB: Completed initialization of buffer pool
    mysqld: 120808 6:38:01 InnoDB: Started; log sequence number 0 44233
    mysqld: 120808 6:38:01 [ERROR] Can’t create IP socket: Permission denied
    mysqld: 120808 6:38:01 [ERROR] Aborting
    mysqld:
    mysqld: 120808 6:38:01 InnoDB: Starting shutdown…
    mysqld: 120808 6:38:06 InnoDB: Shutdown completed; log sequence number 0 44233
    mysqld: 120808 6:38:06 [Note] /usr/sbin/mysqld: Shutdown complete
    mysqld:
    mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
    /etc/init.d/mysql[3831]: 0 processes alive and ‘/usr/bin/mysqladmin –defaults-file=/etc/mysql/debian.cnf ping’ resulted in
    /etc/init.d/mysql[3831]: #007/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
    /etc/init.d/mysql[3831]: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’
    /etc/init.d/mysql[3831]: Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!
    /etc/init.d/mysql[3831]:

    i have found problems Can’t create IP socket: Permission denied,what should i do solve this problem.

  • $ service mysql restart

  • $ sudo service mysql restart

  • thank you it took me 1 min to solve what took me an entire day until i found you, didnt knew how to add acces to mysql so i can acces the database from anywere i want thank you

  • I thought something was wrong with my iptables, but then I somehow reached your post and it helped.

  • It works great, thanks for sharing

  • Thanks a lot for this. Quick and to-the-point…

  • Thanks for this – saved my sanity. :-)

  • great tut, keep going.

  • Great tutorial, however I still can’t connect – I get an “Access denied for user ‘root’@’10.0.0.65′ (using password: YES)” error whenever I try to connect. I know I’m using the correct password because if I SSH in, I can log in using the MySQL shell using the same credentials. It’s on the local network, so there wouldn’t be any router port opening issues. Help?

  • Excelente aporte compañero. Saludos desde Colombia

  • Thanks for sharing. I resolved my problem.

  • FLUSH PRIVILEGES is used when you modify the user tables directly.

    Using the command (grant, etc) does not require this.

    However if you are logged in as the user, you have to logout and log in to benefit from the new privileges. Or even be restricted if privileges are removed.

    The FLUSH does not help with this either.

    G.

  • Bind-Address does not make it so that you can only connect from that machine.

    Bind-Address means what address is the MySQL service listening on. The reason that 127.0.0.1 restricts remote connections is that the localhost is only available… locally.

    So if your server has multiple IP’s, and you want MySQL to only respond on one of those interfaces, you would use that as your bind-address. You will be able to connect from any computer, but only to that IP.

  • Thanks for the tip. However I had to explicitly name the host ip address (my remote PC) like this:
    GRANT ALL ON *.* TO ‘root’@’192.168.0.25′ IDENTIFIED BY ‘password';

  • mysql> SET PASSWORD FOR ‘ROOT’@’LOCALHOST”

    This line ends with a double-quote instead of a single-quote… typo?

  • Keep coming back to this guide – thanks!

  • I feel it’s my responsibility as a DBA to tell you NEVER to grant % on root. This is a terrible mistake and you will surely pay when your database gets brute forced into and taken over. If you work in the cloud this problem is 100x worse. Hackers spin up instances and search for open 3306 ports and then brute it using root. First without a password and then passwords. Here is an example from my AWS log. This goes on for DAYS at a time.

    2014-05-20 08:45:29 30172 [Warning] Access denied for user ‘root’@’172.31.22.125′ (using password: NO)
    2014-05-20 08:45:30 30172 [Warning] Access denied for user ‘root’@’172.31.22.125′ (using password: YES)
    2014-05-20 08:45:33 30172 [Warning] Access denied for user ‘root’@’172.31.22.125′ (using password: YES)
    2014-05-20 08:45:34 30172 [Warning] Access denied for user ‘root’@’172.31.22.125′ (using password: YES)

  • Yeah, this was written several years ago and was intended for casual developers doing stuff on their own. Certainly this creates a dozen security issues, and this approach should not be used in commercial applications or anything you’re planning on hosting in a public space. My thought was that if you’re doing commercial work, you shouldn’t be randomly Googling for instructions on how to do this anyway.

Leave a comment