Skip to content

3 Steps To Allow Remote Root To Access MySQL Database

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

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