cPanel and Remote MySQL connectivity issues

by | May 31, 2022 | blog

cPanel and Remote MySQL connectivity issues

The Remote MySQL section of a cPanel account allows you to add hosts (as FQDNs or IP address) for remote access to your databases.

At the MySQL level, what this operation does when you add a hostname is to create users for your databases which have the hostname which you just supplied.

The MySQL code for creating a user has the following format:

CREATE USER 'myuser'@'123.123.123.123' IDENTIFIED BY 'mypass';

When you added a host via the GUI interface in cPanel, each of your current database users will have another user created (with the same permissions) which allows them to connect from that host.

For example, if I have a mySQL user called ‘carbonlogic’, and I add the IP address: 155.165.155.165 to the remote MySQL list, cPanel will create another user ‘carbonlogic@155.165.155.165’. This will allow you to connect the ‘carbonlogic’ user from the host 155.165.155.165.

You can see the changes which take effect by logging on at the command line and logging on to mySQL as root:

mysql -u root

(you should be able to log in without -p, since it will be stored in your my.cnf file)

if you perform this command:

 SELECT user,host FROM mysql.user

Before, and then after you have added your new host to the Remote MySQL list, you will see something like this:

mysql> SELECT user,host FROM mysql.user;
+-------------+----------------+
| user        | host           |
+-------------+----------------+
| carbonlogic | localhost      |
+-------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT user,host FROM mysql.user;
+------------------+----------------+
| user             | host           |
+------------------+----------------+
| carbonlogic      | localhost      |
| carbonlogic      | 155.165.155.165|
+------------------+----------------+

A very important point to remember, is that as of cPanel version 102.0 (build 17), these changes are applied to existing users, once, at the time of the addition of the hostname.

What this means is that the Remote MySQL feature does not function like a typical whitelist. If you add new users in the future, after adding all your users, you will need to re-apply the changes by deleting the hostname, and then re-add it.

You can find the cPanel docs for Remote MySQL below:

https://docs.cpanel.net/cpanel/databases/remote-mysql/