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

WHM and cPanel is 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 80.0 (build 18). 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 192.168.0.31
$ ssh-copy-id 192.168.0.32

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

$ ssh 192.168.0.31 "sudo ls -al /root"
$ ssh 192.168.0.32 "sudo ls -al /root"

Then go to ClusterControl -> Deploy -> MySQL Replication and enter the required information. On the second step, choose Oracle as the vendor and 5.7 as the database version:

Then, specify the IP address of the master and slave:

Pay attention to the green tick right before the IP address. It means ClusterControl is able to connect to the server and is ready for the next step. Click Deploy 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, 192.168.0.16 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 192.168.0.16 "sudo ls -al /root"

Then, go to ClusterControl -> Manage -> Load Balancers -> ProxySQL -> Deploy ProxySQL and specify the required information:

Fill in all necessary details as highlighted by the arrows above in the diagram. The server address is the WHM server, 192.168.0.16. 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.

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 (192.168.0.31) is necessary:

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

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

Tick on the root@localhost checkbox and click Next. In the User Settings page, 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

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 239
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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 5.5.30 (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 5.7, MariaDB 10.2 and MariaDB 10.3. 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  |
+------------+
| 5.7.26-log |
+------------+

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

(whm)$ mysql -uproxysql-admin -p -h192.168.0.16 -P6032

Use the SET statement as below:

mysql> SET mysql-server_version = '5.7.26';

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 | 5.7.26 |
+----------------------+--------+

If you try again to connect to the MySQL by running "mysql" command, you should now get "Server version: 5.7.26 (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 = '192.168.0.16';
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: 192.168.0.16, 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.