Howto: Remote Root Access to MySql

Update: This post has garnered a lot of attention.  So I’d like to clarify up front: this article is not about hacking into other databases.  This is about configuring a server you own so that you can access it from a remote machine on the same network.  It is completely insecure and should never be used for production deployments.

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

= 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'@'%'

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  We need to remove that restriction, so find the line that says

bind-address =

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

  • emon

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

  • Vladimir

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

  • http://a Matt

    Thanks, this helped.

  • Adam


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

    Works on Ubuntu 8.04…


  • Felipe

    This should be in every mysql faq

  • RyanR>

    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?


  • http://hostell Alex


  • Sebaherre

    Thanks for the help, the information is accurate

  • Thom

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


  • Alagar

    Thansk for the help. good job

  • Shawn

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

  • benrobb

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

    #bind-address =

    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

    thanks! very helpful

  • Pingback: Access MySQL Remotely in Ubuntu @ Robot Minds of Robot Slaves()

  • sadotmd

    Thanks. Works for me too

  • hamid


  • Neal

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

    bind-address =

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

  • crimso

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

  • wannabe

    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

    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

    highly appreciated. I resolved my problem.

  • Brendan

    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

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

  • James

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

  • evanism

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

  • Cin Lung

    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 = 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 ( to MySQL Server, but the aquadata keeps giving access denied, using password yes.

    Can anyone help?
    THank you

  • Cin Lung

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

  • Gina

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

  • venki

    i tried to install mysql-server following error in syslog

    mysqld: 120808 6:37:57 [Note] /usr/sbin/mysqld: Normal shutdown
    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_safe: mysqld from pid file /var/run/mysqld/ 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: 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_safe: mysqld from pid file /var/run/mysqld/ 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!

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

  • Dhawal Parkar

    $ service mysql restart

  • Dhawal Parkar

    $ sudo service mysql restart

  • adi

    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

  • Vishalgiri

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

  • Jose Tapia

    It works great, thanks for sharing

  • Owais

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

  • Michael Horne

    Thanks for this – saved my sanity. :-)

  • eli

    great tut, keep going.

  • Max Swisher

    Great tutorial, however I still can’t connect – I get an “Access denied for user ‘root’@’′ (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?

  • Carlos Gallego

    Excelente aporte compañero. Saludos desde Colombia

  • Alim

    Thanks for sharing. I resolved my problem.

  • Gerard ONeill

    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.


  • Gerard ONeill

    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 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.

  • Erik Verheul

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

  • John Franklin


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

  • Charlie

    Keep coming back to this guide – thanks!

  • marc castrovinci

    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’@’′ (using password: NO)
    2014-05-20 08:45:30 30172 [Warning] Access denied for user ‘root’@’′ (using password: YES)
    2014-05-20 08:45:33 30172 [Warning] Access denied for user ‘root’@’′ (using password: YES)
    2014-05-20 08:45:34 30172 [Warning] Access denied for user ‘root’@’′ (using password: YES)

  • benrobb

    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.

  • Baba Dole

    For anyone that comes across this issue, if you’re on *nix systems you could use “iptables -i INPUT -s -j DROP ” to stop requests from that IP address, they will spin another one up but it’s it’s an inconvenience and can be expensive.