Howto: Remote Root Access to MySql

Posted by on January 15th, 2007

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.

This entry was posted on Monday, January 15th, 2007 at 11:24 am and is filed under *Nix, How To. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

«
»
 

25 Responses to “Howto: Remote Root Access to MySql”

  1. emon Says:
    December 11th, 2007 at 4:38 am

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

  2. Vladimir Says:
    January 4th, 2008 at 3:40 am

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

  3. Matt Says:
    April 3rd, 2008 at 4:32 pm

    Thanks, this helped.

  4. Adam Says:
    May 17th, 2008 at 12:34 pm

    Fantastic!!!

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

    Works on Ubuntu 8.04…

    Adam.

  5. Felipe Says:
    August 5th, 2008 at 8:29 am

    This should be in every mysql faq

  6. RyanR> Says:
    August 20th, 2008 at 1:52 pm

    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

  7. Alex Says:
    September 5th, 2008 at 1:13 am

    Thanks!!!

  8. Sebaherre Says:
    October 8th, 2008 at 12:18 pm

    Thanks for the help, the information is accurate

  9. Thom Says:
    January 15th, 2009 at 1:13 pm

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

    Thanks
    Thom

  10. Alagar Says:
    February 3rd, 2009 at 9:32 am

    Thansk for the help. good job

  11. Shawn Says:
    July 7th, 2009 at 8:10 am

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

  12. benrobb Says:
    July 21st, 2009 at 2:22 pm

    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.

  13. Kurt Plaatjes Says:
    February 3rd, 2010 at 1:04 am

    thanks! very helpful

  14. Access MySQL Remotely in Ubuntu @ Robot Minds of Robot Slaves Says:
    September 4th, 2010 at 12:27 pm

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

  15. sadotmd Says:
    September 16th, 2010 at 5:41 am

    Thanks. Works for me too

  16. hamid Says:
    September 27th, 2010 at 7:44 am

    Thanks

  17. Neal Says:
    September 28th, 2010 at 12:29 pm

    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.

  18. crimso Says:
    October 12th, 2010 at 5:08 am

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

  19. wannabe Says:
    March 23rd, 2011 at 8:23 am

    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

  20. roadworrier Says:
    May 12th, 2011 at 8:46 am

    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.

  21. tastesnow Says:
    July 10th, 2011 at 3:11 am

    highly appreciated. I resolved my problem.

  22. Brendan Says:
    August 3rd, 2011 at 6:42 pm

    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′);

  23. Pomodoro technique Says:
    August 6th, 2011 at 6:51 am

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

  24. James Says:
    October 31st, 2011 at 12:27 pm

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

  25. evanism Says:
    November 16th, 2011 at 5:22 am

    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!

Leave a Reply