MySQL Replication with ProxySQL on WHM/cPanel Servers: Part One

WHM and cPanel are no doubt the most popular hosting control panel for Linux-based environments. It supports a number of database backends – MySQL, MariaDB and PostgreSQL as the application datastore. WHM only supports standalone database setups and you can either have it deployed locally (default configuration) or remotely, by integrating with an external database server. The latter would be better if you want to have better load distribution, as WHM/cPanel handles a number of processes and applications like HTTP(S), FTP, DNS, MySQL and such.

In this blog post, we are going to show you how to integrate an external MySQL replication setup into WHM seamlessly, to improve the database availability and offload the WHM/cPanel hosting server. Hosting providers who run MySQL locally on the WHM server would know how demanding MySQL is in terms of resource utilization (depending on the number of accounts it hosts and the server specs).

MySQL Replication on WHM/cPanel

By default, WHM natively supports both MariaDB and MySQL as a standalone setup. You can attach an external MySQL server into WHM, but it will act as a standalone host. Plus, the cPanel users have to know the IP address of the MySQL server and manually specify the external host in their web application if this feature is enabled.

In this blog post, we are going to use ProxySQL UNIX socket file to trick WHM/cPanel in connecting to the external MySQL server via UNIX socket file. This way, you get the feel of running MySQL locally so users can use “localhost” with port 3306 as their MySQL database host.

The following diagram illustrates the final architecture:

We are having a new WHM server, installed with WHM/cPanel 126. Then, we have another three servers – one for ClusterControl and two for master-slave replication. ProxySQL will be installed on the WHM server itself.

Deploying MySQL Replication

At the time of this writing, we are using WHM 80.0 (build 18) which only supports up to MySQL 5.7 and MariaDB 10.3. In this case, we are going to use MySQL 5.7 from Oracle. We assume you have already installed ClusterControl on the ClusterControl server.

Firstly, setup passwordless SSH from ClusterControl server to MySQL replication servers. On ClusterControl server, do:

$ ssh-copy-id 167.172.78.39
$ ssh-copy-id 188.166.217.78

Make sure you can run the following command on ClusterControl without password prompt in between:

$ ssh 167.172.78.39 "sudo ls -al /root"
$ ssh 188.166.217.78 "sudo ls -al /root"

Then go to ClusterControl, click the button Deploy a cluster, you will see the following screenshot and choose Create a database cluster:

Choose MySQL Replication as database engine, Vendor from Oracle, and version as well.

You can click continue and fill in the Cluster Name and tagging; after that, you can continue.
Enter the required information for the SSH user that we configured earlier.

Configure the ports, and data directory, and define the root password for the database. You can also toggle ON / OFF for SSL Encryption and Semi-Sync Replication.

The next page, you will need to add the target node server. Fill IP Address of the Primary Node, and Secondary Node of the database.

The last step of Deployment is reviewing the configuration before we deploy the MySQL Replication 8.0.

Click Finish to start the deployment. The deployment process should take 15 to 20 minutes.

Deploying ProxySQL on WHM/cPanel

Since we want ProxySQL to take over the default MySQL port 3306, we have to firstly modify the existing MySQL server installed by WHM to listen to other port and other socket file. In /etc/my.cnf, modify the following lines (add them if do not exist):

socket=/var/lib/mysql/mysql2.sock
port=3307
bind-address=127.0.0.1

Then, restart MySQL server on cPanel server:

$ systemctl restart mysqld

At this point, the local MySQL server should be listening on port 3307, bind to localhost only (we close it down from external access to be more secure). Now we can proceed to deploy ProxySQL on the WHM host, 157.245.148.231 via ClusterControl.

First, setup passwordless SSH from ClusterControl node to the WHM server that we want to install ProxySQL:

(clustercontrol)$ ssh-copy-id [email protected]

Make sure you can run the following command on ClusterControl without password prompt in between:

(clustercontrol)$ ssh 157.245.148.231 "sudo ls -al /root"

Then, go to ClusterControl -> choose the MySQL Replication. There is an Action Menu in the Cluster level as shown below:

Click the Load Balancer, and you will see the option either want to Create a balancer or Import balancer. Choose Create a balancer, and select ProxySQL.

ClusterControl supports various Load Balancers such as ProxySQL, HAProxy, and MaxScale. Also, you can configure KeepAlived for High Availability of the load balancers. Fill in the IP Address of the node we want to deploy ProxySQL.

The server address is the WHM server, 157.245.148.231. The listening port is 3306 on the WHM server, taking over the local MySQL which is already running on port 3307. Further down, we specify the ProxySQL admin and monitoring users’ password. Then include both MySQL servers into the load balancing set and then choose “No” in the Implicit Transactions section. Click Deploy ProxySQL to start the deployment.

Our ProxySQL is now installed and configured with two host groups for MySQL Replication. One for the writer group (hostgroup 10), where all connections will be forwarded to the master and the reader group (hostgroup 20) for all read-only workloads which will be balanced to both MySQL servers.

Fill in all necessary details as highlighted by the arrows above in the diagram.

The next step is to grant MySQL root user and import it into ProxySQL. Occasionally, WHM somehow connects to the database via TCP connection, bypassing the UNIX socket file. In this case, we have to allow MySQL root access from both root@localhost and [email protected] (the IP address of WHM server) in our replication cluster.

Thus, running the following statement on the master server (167.172.78.39) is necessary:

(master)$ mysql -uroot -p
mysql> GRANT ALL PRIVILEGES ON *.* TO whm_cpanel_usr@'157.245.148.231' IDENTIFIED BY 'M6sdk1y3PPk@2' WITH GRANT OPTION;

Then, import ‘root’@’localhost’ user from our MySQL server into ProxySQL user by going to ClusterControl -> Nodes. There is node menu, pick the ProxySQL node -> Node Details. You will be presented with the following dialog:

Go to the Users tab, and Import the Users.

Tick on the whm_cpanel_usr@’%’ checkbox and choose hostgroup 10 as the default hostgroup for the user.

We can then verify if ProxySQL is running correctly on the WHM/cPanel server by using the following command:

$ netstat -tulpn | grep -i proxysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 17306/proxysql
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 17306/proxysql

Port 3306 is what ProxySQL should be listening to accept all MySQL connections. Port 6032 is the ProxySQL admin port, where we will connect to configure and monitor ProxySQL components like users, hostgroups, servers and variables.

At this point, if you go to ClusterControl -> Topology, you should see the following topology:

Configuring MySQL UNIX Socket

In Linux environment, if you define MySQL host as “localhost”, the client/application will try to connect via the UNIX socket file, which by default is located at /var/lib/mysql/mysql.sock on the cPanel server. Using the socket file is the most recommended way to access MySQL server, because it has less overhead as compared to TCP connections. A socket file doesn’t actually contain data, it transports it. It is like a local pipe the server and the clients on the same machine can use to connect and exchange requests and data.

Having said that, if your application connects via “localhost” and port 3306 as the database host and port, it will connect via socket file. If you use “127.0.0.1” and port 3306, most likely the application will connect to the database via TCP. This behaviour is well explained in the MySQL documentation. In simple words, use socket file (or “localhost”) for local communication and use TCP if the application is connecting remotely.

In cPanel, the MySQL socket file is monitored by cpservd process and would be linked to another socket file if we configured a different path than the default one. For example, suppose we configured a non-default MySQL socket file as we configured in the previous section:

$ cat /etc/my.cnf | grep socket
socket=/var/lib/mysql/mysql2.sock

cPanel via cpservd process would correct this by creating a symlink to the default socket path:

(whm)$ ls -al /var/lib/mysql/mysql.sock
lrwxrwxrwx. 1 root root 34 Jul  4 12:25 /var/lib/mysql/mysql.sock -> ../../../var/lib/mysql/mysql2.sock

To avoid cpservd to automatically re-correct this (cPanel has a term for this behaviour called “automagically”), we have to disable MySQL monitoring by going to WHM -> Service Manager (we are not going to use the local MySQL anyway) and uncheck “Monitor” checkbox for MySQL as shown in the screenshot below:

Save the changes in WHM. It’s now safe to remove the default socket file and create a symlink to ProxySQL socket file with the following command:

(whm)$ ln -s /tmp/proxysql.sock /var/lib/mysql/mysql.sock

Verify the socket MySQL socket file is now redirected to ProxySQL socket file:

(whm)$ ls -al /var/lib/mysql/mysql.sock
lrwxrwxrwx. 1 root root 18 Jul  3 12:47 /var/lib/mysql/mysql.sock -> /tmp/proxysql.sock

We also need to change the default login credentials inside /root/.my.cnf as follows:

(whm)$ cat ~/.my.cnf
[client]
#password="T<y4ar&cgjIu"
user=whm_cpanel_usr
password='M6sdk1y3PPk@2'
socket=/var/lib/mysql/mysql.sock

A bit of explanation – The first line that we commented out is the MySQL root password generated by cPanel for the local MySQL server. We are not going to use that, therefore the ‘#’ is at the beginning of the line. Then, we added the MySQL root password for our MySQL replication setup and UNIX socket path, which is now symlink to ProxySQL socket file.

At this point, on the WHM server you should be able to access our MySQL replication cluster as root user by simply typing “mysql”, for example:

(whm)$ mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 488605
Server version: 8.0.41 MySQL Community Server - GPL

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Notice the server version is 8.0.41 (ProxySQL). If you can connect as above, we can configure the integration part as described in the next section.

WHM/cPanel Integration

WHM supports a number of database server, namely MySQL 8.0, 8.4, MariaDB 10.11 and MariaDB 11.4. Since WHM is now only seeing the ProxySQL and it is detected as version 5.5.30 (as stated above), WHM will complain about unsupported MySQL version. You can go to WHM -> SQL Services -> Manage MySQL Profiles and click on Validate button. You should get a red toaster notification on the top-right corner telling about this error.

Therefore, we have to change the MySQL version in ProxySQL to the same version as our MySQL replication cluster. You can get this information by running the following statement on the master server:

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.41 |
+-----------+

Then, login to the ProxySQL admin console to change the mysql-server_version variable:

(whm)$ mysql -uproxysql-admin -p -h157.245.148.231 -P6032

Use the SET statement as below:

mysql> SET mysql-server_version = '8.0.41';

Then load the variable into runtime and save it into disk to make it persistent:

mysql> LOAD MYSQL VARIABLES TO RUNTIME;
mysql> SAVE MYSQL VARIABLES TO DISK;

Finally verify the version that ProxySQL will represent:

mysql> SHOW VARIABLES LIKE 'mysql-server_version';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| mysql-server_version | 8.0.41 |
+----------------------+--------+

If you try again to connect to MySQL by running the “mysql” command, you should now see “Server version: 8.0.41 (ProxySQL)” in the terminal.

Now we can update the MySQL root password under WHM -> SQL Services -> Manage MySQL Profiles. Edit the localhost profile by changing the Password field at the bottom with the MySQL root password of our replication cluster. Click on the Save button once done. We can then click on “Validate” to verify if WHM can access our MySQL replication cluster via ProxySQL service correctly. You should get the following green toaster at the top right corner:

If you get the green toaster notification, we can proceed to integrate ProxySQL via cPanel hook.

ProxySQL Integration via cPanel Hook

ProxySQL as the middle-man between WHM and MySQL replication needs to have a username and password for every MySQL user that will be passing through it. With the current architecture, if one creates a user via the control panel (WHM via account creation or cPanel via MySQL Database wizard), WHM will automatically create the user directly in our MySQL replication cluster using root@localhost (which has been imported into ProxySQL beforehand). However, the same database user would be not added into ProxySQL mysql_users table automatically.

From the end-user perspective, this would not work because all localhost connections at this point should be passed through ProxySQL. We need a way to integrate cPanel with ProxySQL, whereby for any MySQL user related operations performed by WHM and cPanel, ProxySQL must be notified and do the necessary actions to add/remove/update its internal mysql_users table.

The best way to automate and integrate these components is by using the cPanel standardized hook system. Standardized hooks trigger applications when cPanel & WHM performs an action. Use this system to execute custom code (hook action code) to customize how cPanel & WHM functions in specific scenarios (hookable events).

Firstly, create a Perl module file called ProxysqlHook.pm under /usr/local/cpanel directory:

$ touch /usr/local/cpanel/ProxysqlHook.pm

Then, copy and paste the lines from here. For more info, check out the Github repository at ProxySQL cPanel Hook.

Configure the ProxySQL admin interface from line 16 until 19:

my $proxysql_admin_host = '157.245.148.231';
my $proxysql_admin_port = '6032';
my $proxysql_admin_user = 'proxysql-admin';
my $proxysql_admin_pass = 'mys3cr3t';

Now that the hook is in place, we need to register it with the cPanel hook system:

(whm)$ /usr/local/cpanel/bin/manage_hooks add module ProxysqlHook
info [manage_hooks] **** Reading ProxySQL information: Host: 157.245.148.231, Port: 6032, User: proxysql-admin *****
Added hook for Whostmgr::Accounts::Create to hooks registry
Added hook for Whostmgr::Accounts::Remove to hooks registry
Added hook for Cpanel::UAPI::Mysql::create_user to hooks registry
Added hook for Cpanel::Api2::MySQLFE::createdbuser to hooks registry
Added hook for Cpanel::UAPI::Mysql::delete_user to hooks registry
Added hook for Cpanel::Api2::MySQLFE::deletedbuser to hooks registry
Added hook for Cpanel::UAPI::Mysql::set_privileges_on_database to hooks registry
Added hook for Cpanel::Api2::MySQLFE::setdbuserprivileges to hooks registry
Added hook for Cpanel::UAPI::Mysql::rename_user to hooks registry
Added hook for Cpanel::UAPI::Mysql::set_password to hooks registry

From the output above, this module hooks into a number of cPanel and WHM events:

• Whostmgr::Accounts::Create – WHM -> Account Functions -> Create a New Account
• Whostmgr::Accounts::Remove – WHM -> Account Functions -> Terminate an Account
• Cpanel::UAPI::Mysql::create_user – cPanel -> Databases -> MySQL Databases -> Add New User 
• Cpanel::Api2::MySQLFE::createdbuser – cPanel -> Databases -> MySQL Databases -> Add New User (requires for Softaculous integration).
• Cpanel::UAPI::Mysql::delete_user – cPanel -> Databases -> MySQL Databases -> Delete User
• Cpanel::Api2::MySQLFE::deletedbuser – cPanel -> Databases -> MySQL Databases -> Add New User (requires for Softaculous integration).
• Cpanel::UAPI::Mysql::set_privileges_on_database – cPanel -> Databases -> MySQL Databases -> Add User To Database
• Cpanel::Api2::MySQLFE::setdbuserprivileges – cPanel -> Databases -> MySQL Databases -> Add User To Database (requires for Softaculous integration).
• Cpanel::UAPI::Mysql::rename_user – cPanel -> Databases -> MySQL Databases -> Rename User
• Cpanel::UAPI::Mysql::set_password – cPanel -> Databases -> MySQL Databases -> Change Password

If the event above is triggered, the module will execute the necessary actions to sync up the mysql_users table in ProxySQL. It performs the operations via ProxySQL admin interface running on port 6032 on the WHM server. Thus, it’s vital to specify the correct user credentials for ProxySQL admin user to make sure all users will be synced with ProxySQL correctly.

Take note that this module, ProxysqlHook.pm has never been tested in the real hosting environment (with many accounts and many third-party plugins) and obviously does not cover all MySQL related events within cPanel. We have tested it with Softaculous free edition and it worked greatly via cPanel API2 hooks. Some further modification might be required to embrace full automation.

That’s it for now. In the next part, we will look into the post-deployment operations and what we could gain with our highly available MySQL server solution for our hosting servers if compared to standard standalone MySQL setup.