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, [email protected] 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 ( 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 *