Giving a Root User Remote Access to a mySQL database

I happen to be within a firewalled environment and I wanted to simply login as the root user for a mysql server from a development machine. Well, security purists would scoff at the idea, but I needed it (running some scripts as root from the dev machine).

The issue is mysql out-of-the-box is configured to explicitly dis-allow root from log in from anything other than the local machine.

Enabling remote access for any user is easy. Most of us have gone through this as part of mysql setup :

Step 1: On the mySQL server:

sudo vi /etc/my.cnf

Look for the line that says

bind-address=YOUR-SERVER-IP

Change that to:

bind-address=YOUR-SERVER-IP

Step 2: Restart mysql service

sudo /etc/init.d/mysql restart

All’s well. As long as individual users have been given permissions to a database through something like:

GRANT ALL ON *.* TO foo@bar IDENTIFIED BY 'PASSWORD';

Now what if I want the same for root? We need to update the allowed permissions for root on the server side with the following simple steps.

mysql -uroot -p

To Check what’s the current state of user,host login permissions we’ll lookup the default mysql table elegantly named: mysql.


mysql> use mysql;
mysql> select host, user from user;
+—————+——————+
| host | user |
+—————+——————+
| myhostname | root |
| localhost | root |
| % | myusername |
| myhostname | myusername |
+—————+——————+
3 rows in set (0.00 sec)

It seems that a non-root user ‘myusername’ was indeed allowed to login from anywhere. This is not the case for root. Now I’ll update the ‘myhostname’ entry to use the wildcard ‘%’, and then issue the command to reload the privilege tables.

mysql>update user set host=’%’ where user=’root’ and host=’myhostname’;
mysql>flush privileges;

Now, here’s the catch: It’s important to update the entry where host=’myhostname’. Inserting a fresh row will not work. Now you should be able to login from a remote machine (say a development box) to the mysql server as root. Any perl scripts will work fine. If this is not enabled on the remote machine, mysql command-line or any scripts for instance will look for ‘root’@'the-development-machine’ instead of ‘root’@'myhostname’. Thereby confusing the heck out of you.

$mysql -u root -h myhostname -p

This simple fix gave me a headache for sometime. So, this is a note to self.

Leave a Reply