Wednesday, September 26, 2012

MySql: Give Root User Logon Permission From Any Host

I have already updated about this in my previous blog but that was from UI, In this tutorial You can do the same task from MySQL client itself.

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

mysql> use mysql;
Database changed

mysql> select host,user from user;
| host      | user |
| localhost        | root |
| | root |
| ::1       | root |
| localhost |      |
4 rows in set (0.07 sec)

Now I’ll update the localhost 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 localhost.

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

Now You are able to connect to mysql from any other machine using the root account.