Skip to content

3 Steps to Allow Remote Root to Access MySQL Database

MySQL does not allow remote root to access database by default. It is because MySQL considers username and host as a whole for authentication and authorization. For example, root@127.0.0.1 is different from root@localhost, you can set different privileges or password on the two accounts.

In this case, we would like to make root can access the database remotely from any client in LAN (192.168.0.0/24). Let's try to issue the following command under mysql prompt.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY PASSWORD 'clear_text_password' WITH GRANT OPTION;
ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

Since MySQL does not accept clear text password, this does not work.

Let's try it again with the hashed function password().

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' IDENTIFIED BY PASSWORD password('clear_text_password') WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password('clear_text_password') WITH GRANT OPTION' at line 1

The error is belongs to syntax error. The above syntax does not allow functions. It should be divided into several steps.

OK, let's take a few steps to finish.

Create Remote Root

For remote root access, we need to create a root account with an IP range.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.0.%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

Note that, the wild card that is used for LAN in a string is '%', not '*'.

Set Password

To enable accessibility, we need to set a password for the new account.

mysql> SET PASSWORD FOR 'root'@'192.168.0.%' = PASSWORD('clear_text_password');
Query OK, 0 rows affected (0.03 sec)

Reload Privileges

To make the changes effective immediately, we need to clear current privileges.

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

Another way to reload the privileges is using mysqladmin under OS prompt.

[root@localhost ~]# mysqladmin -u root -p flush-privileges

Leave a Reply

Your email address will not be published. Required fields are marked *