Not able to access MySQL/MariaDB as root from a remote database client

Symptoms

The database server has been configured to allow remote access, but you get an access denied message when attempting to access MySQL/MariaDB via a remote database client.

Description

By default, the database server doesn't accept remote connections for the root user. You must add a new database user for root at the remote address and grant privileges to that user.

Workaround

1. Access the server's command line as the 'root' user via SSH or "Terminal" in WHM.
2. Access the MySQL/MariaDB command line interface.

   mysql

3. Create the new user.

   create user 'root'@'203.0.113.2' identified by 'password';

   Please note that "203.0.113.2" and "password" must be replaced with the remote IP address and root password, respectively.
4. Grant database privileges to the new user.

     grant all privileges on *.* to 'root'@'203.0.113.2' with grant option;
   

   Please note that "203.0.113.2" must be replaced with the remote IP address.
5. Flush the privileges.

   flush privileges;

6. Exit the interface.

   exit

Additional resources

How to configure MySQL / MariaDB to listen publicly so that remote connections will work