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.

1. Create a remote root.
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 '*'.
2. Set the password.
mysql> SET PASSWORD FOR 'root'@'192.168.0.%' = PASSWORD('clear_text_password');
Query OK, 0 rows affected (0.03 sec)

3. Reload the privileges to make the changes effective immediately.
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