Jan
15
2007
*Nix // How To

Howto: Remote Root Access to MySql

Author An article by benrobb    Comments 25 Comments

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.

Tags:

Related Posts

25 Comments + Add Comment

  • emon December 11, 2007 at 4:38 am

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

  • Vladimir January 4, 2008 at 3:40 am

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

  • Matt April 3, 2008 at 4:32 pm

    Thanks, this helped.

  • Adam May 17, 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.

  • Felipe August 5, 2008 at 8:29 am

    This should be in every mysql faq

  • RyanR> August 20, 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

  • Alex September 5, 2008 at 1:13 am

    Thanks!!!

  • Sebaherre October 8, 2008 at 12:18 pm

    Thanks for the help, the information is accurate

  • Thom January 15, 2009 at 1:13 pm

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

    Thanks
    Thom

  • Alagar February 3, 2009 at 9:32 am

    Thansk for the help. good job

  • Shawn July 7, 2009 at 8:10 am

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

  • benrobb July 21, 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.

  • Kurt Plaatjes February 3, 2010 at 1:04 am

    thanks! very helpful

  • Access MySQL Remotely in Ubuntu @ Robot Minds of Robot Slaves September 4, 2010 at 12:27 pm

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

  • sadotmd September 16, 2010 at 5:41 am

    Thanks. Works for me too

  • hamid September 27, 2010 at 7:44 am

    Thanks

  • Neal September 28, 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.

  • crimso October 12, 2010 at 5:08 am

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

  • wannabe March 23, 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

  • roadworrier May 12, 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.

  • tastesnow July 10, 2011 at 3:11 am

    highly appreciated. I resolved my problem.

  • Brendan August 3, 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′);

  • Pomodoro technique August 6, 2011 at 6:51 am

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

  • James October 31, 2011 at 12:27 pm

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

  • evanism November 16, 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 comment