SEARCH

How-To Geek

MySql: Give Root User Logon Permission From Any Host

Note that this is Not very secure, and should only be used for a local development box where you don’t feel like setting up individual permissions, but still need to connect from other machines.

To configure this feature, you’ll need to update the mysql user table to allow access from any remote host, using the % wildcard.

Open the command-line mysql client on the server using the root account.

mysql -uroot

Then you will want to run the following two commands, to see what the root user host is set to already:

use mysql;
select host, user from user;

Here’s an example of the output on my database, which is pretty much the default settings. Note that ubuntuserv is the hostname of my server.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> select host,user from user;
+—————+——————+
| host | user |
+—————+——————+
| ubuntuserv | root |
| localhost | debian-sys-maint |
| localhost | root |
+—————+——————+
3 rows in set (0.00 sec)

Now I’ll update the ubuntuserv host to use the wildcard, and then issue the command to reload the privilege tables. If you are running this command, substitute the hostname of your box for ubuntuserv.

update user set host=’%’ where user=’root’ and host=’ubuntuserv’;
flush privileges;

That’s all there is to it. Now I was able to connect to that server from any other machine on my network, using the root account.

Again, note this isn’t very secure, and you should at least make sure that you’ve set a root password.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 02/9/07

Comments (5)

  1. daniel

    I’m not exactly sure *why* you would want to do this. IP based authentication is an extremely useful security feature, disabling it – especially for the root user – is probably a bad idea.

    That said, there really isn’t anything wrong with allowing a limited user account access from every IP. :-)

  2. The Geek

    daniel,

    That’s why I noted that it isn’t very secure a number of times =)

  3. Juan

    I appreciated this. It has been a while since I have had to build a project from scratch. With this reference, I can now get on with development. Later, I will absolutely want to lock down for my customer. Then I hope to find another reference solution just as easily.

    Actually implementing IP based authentication may not be the best answer for me due to the environment I will be in, but I will be investigating it. Simple is the essence of effectiveness for me.

    And I do not plan on giving out the root password. Isn’t that a measure of security?

  4. Andrew

    thanks a lot for this guide which helped me more for the configuration of remote access of mysql.
    I wasted 3 day for this problem, which can’t risolve -.-

  5. Kidjo

    The only guide that solved this for me. The other guides involved changing database and users, I just wanted a wild card source host as this article has done

    Thank you, thank you, thank you

Enter Your Email Here to Get Access for Free:

Go check your email!