Can't connect to an external MySQL server from cPanel/WHM PhpMyAdmin

[Baptiste B]

Hello,
We have a VPS with cPanel / WHM. Recently, we have outsourced our database to take the load off the VPS.
We would like to use cPanel's PhpMyAdmin to manage the external database.



For this, we followed the following tutorial to add an additional MySQL server to the configuration of PhpMyAdmin: https://support.cpanel.net/hc/en-us/articles/4402637529623-How-to-add-a-second-server-to-phpMyAdmin

Here is more details:

sudo nano  /usr/local/cpanel/base/3rdparty/phpMyAdmin/config.inc.php

And we added the following code at the end of the file:

$i++;
$cfg['Servers'][$i]['verbose'] = 'Cloud Database';
$cfg['Servers'][$i]['auth_type'] = 'cpanel';
$cfg['Servers'][$i]['host'] = 'xxxxx.xxxxx.ovh.net';
$cfg['Servers'][$i]['port'] = 'xxxxx';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;
$cfg['Servers'][$i]['DisableIS'] = false;

When we access PhpMyAdmin from cPanel, we may well select the desired server (see Capture.PNG), but many errors appear.
From cPanel > PhpMyAdmin ("Cloud Database" selected):


From WHM > PhpMyAdmin ("Cloud Database" selected):


Note that on the screenshots above, I have hidden the IP of our server with "XXX.XXX.XXX.XXX" and the name of our cPanel account with "cpanel_account_username".



Indeed, we do not know where to put the login credentials to the external MySQL database for cPanel PhpMyAdmin.

Note that from cPanel or WHM, we are able to connect to the external MySQL database by executing the following command:

mysql --host = "xxxxx.xxxxx.ovh.net" --user = "xxxxx" --port = "xxxxx" -p

and entering the password.

However, despite a lot of searches and tests, we do not know how to "give" these login credentials to cPanel PhpMyAdmin.

Is it possible?! If so, does a cPanel configuration file allow us this? Or what manipulation can we do to make this work?

Thank you in advance for your help!

 

[cPanelAnthony]

Hello! It looks like you might need to configure the remote MySQL access. Does this article help?

 

[Baptiste B]

Hello,
Thank you for your quick reply.

The article you linked to us explains how to allow other servers to access a MySQL database that is on the VPS.

What we are trying to do is connect to a MySQL database external to the VPS with the VPS 'PhpMyAdmin.
It is an OVH managed MySQL database (no cPanel on it): Cloud Databases

 

[cPanelAnthony]

Thanks for the confirmation. I will see if I can find anything regarding this.

 

[bilalkhan01]

To prevent potential security risks to MySQL server, by default remote MySQL connections are disabled in cPanel servers. Remote MySQL connections can be allowed either through Web Host Manager (WHM) or through cPanel (for specific databases created under that cPanel account). Since we do not allow remote MySQL connections in cPanel shared hosting, this guide will be only applicable to our VPS and dedicated server customers. Before you check out the following methods, make sure that port 3306 is opened in your server firewall.

Enable Remote MySQL from WHM
Login to WHM.

Locate SQL Services section and click on the Additional MySQL Access Hosts link.

MySQL Access Host

At next screen, enter IP address or host(s) that you wish to grant remote MySQL access to and click the Save button. If you want to activate these settings for all cPanel accounts, click on Click Here link at bottom. Now, remote connection has been activated in WHM. Each cPanel account that wants to use remote connection need to activate it as follows.
Enable Remote MySQL from cPanel
Login to cPanel. Please refer to how to login to cPanel from WHM for more details.

Locate the Databases section. Under Databases section locate Remote MySQL option and and click on it.

Remote MySQL

In the next screen, enter the hostname or IP address that you want to grant remote MySQL access to and click the Save button. If your IP address changes regularly, you can use wildcard by using the % symbol. This will add all IP addresses in that range. For example; if you want to list IP addresses range from 192.168.3.1 to 192.168.3.255, you can add an IP address as 192.168.3.%.
Sometimes, after enabing MySQL remote connections, it could not get accessed remotely because it might bound on localhost (127.0.0.1). To fix this, you'll need to do following changes in MySQL configuration file (my.conf).

Login to MySQL server (where MySQL server is installed) and edit the my.cnf file using a text editor such as vi. Usually my.cnf file is located at /etc/mysql/my.cnf or at /etc/my.cnf.

# vi /etc/my.cnf
Once a file is opened, locate line that contains skip-networking and make sure that it is commented as follows.

# skip-networking
Now locate the line containing bind-address and comment it out as follows.

# bind-address = 127.0.0.1
Save and Close the file and restart the MySQL server.

# /etc/init.d/mysql restart // Debian & Ubuntu Linux

# /etc/init.d/mysqld restart // CentOS & Fedora Linux