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