CPANEL高可用集群

I have successfully installed and integrate MySQL Cluster with HAproxy and Keepalived to provide scalable MySQL service with cPanel server run on CentOS 6.3 64bit. As you guys know that cPanel has a function called “Setup Remote MySQL server” which we can use to remotely access and control MySQL server from cPanel.

This will bring a big advantage, because the cPanel server load will be reduced tremendously due to mysqld service and resource will be serve from a cluster of servers. Following picture shows my architecture:

cpanel_cluster

I will be using following variables:

OS: CentOS 6.3 64bit
WHM/cPanel version: 11.34.0 (build 11)
MySQL root password: MhGGs4wYs

The Tricks

  • We will need to use same MySQL root password in all servers including the cPanel server
  • cPanel server’s SSH key need to be installed in all database servers to allow passwordless SSH login
  • All servers must have same /etc/hosts value
  • At least 4 servers for MySQL Cluster ( 2 SQL/Management/LB and 2 Data nodes)
  • mysql1 (active) and mysql2 (passive) will share a virtual IP which run on Keepalived
  • mysql1 and mysql2 will be the load balancer as well, which redirect MySQL traffic from cPanel server to mysql1 and mysql2
  • MySQL Cluster only support ndbcluster storage engine. Databases will be created in ndbcluster by default
  • For mysql1 and mysql2, MySQL will serve using port 3307 because 3306 will be used by HAProxy for load balancing

All Servers

1. In this post, I am going to turn off firewall and SELINUX for all servers:

2. Install ntp using yum to make sure all servers’ time is in sync:

Prepare the cPanel Server

1. Lets start declaring all hosts naming in /etc/hosts:

2. Copy /etc/hosts file to other servers:

3. Setup SSH key. This will allow passwordless SSH between cPanel server and MySQL servers:

Just press ‘Enter’ for all prompts.

4. Copy the SSH key to other servers:

5. Setup MySQL root password in WHM. Login to WHM > SQL Services > MySQL Root Password. Enter the MySQL root password and click “Change Password”.

6. Add additional host in WHM > SQL Services > Additional MySQL Access Hostsand add required host to be allowed to access the MySQL cluster as below:

add_host

 

 

Data Nodes (mysql-data1 and mysql-data2)

1. Download and install MySQL storage package from this page:

2. Create a mysql configuration file at /etc/my.cnf and add following line. This configuration will tell the storage to communicate with mysql1 and mysql2 as the management nodes:

 

SQL Nodes (mysql1 and mysql2)

1. Install required package using yum:

2. Download all required packages for Keepalived, HAProxy and MySQL Cluster package from this site (management, tools, shared, client, server):

3. Extract and compile Keepalived:

4. Extract and compile HAProxy:

5. Install mysql packages with following order (management > tools > shared > client > server):

6. Create new directory for MySQL cluster. We also need to create cluster configuration file config.ini underneath it:

And add following line:

7. Create the mysql configuration file at /etc/my.cnf and add following line:

Starting the Cluster

1. Start mysql cluster management service:

For mysql1:

For mysql2:

2. Start the mysql cluster storage service in both data nodes (mysql-data1 & mysql-data2):

3. Start the mysql service (mysql1 & mysql2):

4. Login to mysql console and run following command  (mysql1 & mysql2):

5. Check the output of table db and user in mysql database and make sure it should appear as below:

6. Check the management status in mysql1. You should see output similar to below:

7. Change MySQL root password to follow the MySQL root password in cPanel server (mysql1):

8. Add MySQL root password into root environment so we do not need to specify password to access mysql console (mysql1 & mysql2):

And add following line:

9. Add haproxy user without password to be used by HAProxy to check the availability of real server (mysql1):

10. Add root user from any host so cPanel servers can access and control the MySQL cluster (mysql1):

11. The last step, we need to allow GRANT privileges to root@’%’ by running following command in mysql console (mysql1):

 

Configuring Virtual IP and Load Balancer (mysql1 & mysql2)

1. Configure HAProxy by creating a configuration /etc/haproxy.cfg:

And add following line:

2. Next we need to configure virtual IP. Open /etc/sysctl.conf and add following line to allow non-local IP to bind:

And run following command to apply the changes:

3. Create Keepalived configuration file at /etc/keepalived.conf and add following line:

For mysql1:

For mysql2:

4. Start HAProxy:

5. Start Keepalived:

6. Add following line into /etc/rc.local to make sure Keepalived and HAProxy start on boot:

And add following line:

7. Check the virtual IP should be up in mysql1:

8. Verify in mysql2 whether Keepalived is running in backup mode:

9. Check that HAProxy is run on port 3306 and mysqld is run on port 3307:

 

Setup Remote MySQL Server in cPanel Server

1. Go to WHM > SQL Services > Setup Remote MySQL server and enter following details. Make sure the Remote server address is the virtual IP address setup in Keepalived in mysql1:

2. Wait for a while and you will see following output:

remote_success

3. Now MySQL Cluster is integrated within WHM/cPanel. You may verify this by accessing into PHPmyAdmin in WHM at WHM > SQL Services > PHPmyAdminand you should see that you are connected into the MySQL Cluster as screenshot below:

my_cluster

Testing

We can test our MySQL high availability architecture by turning off the power completely for mysql1 or mysql2 and mysql-data1 or mysql-data2 in the same time. You will notice that the MySQL service will still available in cPanel point-of-view.

Here is my PHPmyAdmin for my test blog running on WordPress. You can notice that the database created is under ndbcluster engine:

blog_cluster

I never test this architecture in any production server yet and I cannot assure that all WHM/cPanel SQL functionalities are working as expected. Following features in cPanel has been tried and working well:

  • PHPMyAdmin
  • cPanel MySQL features (MySQL Database and MySQL Database Wizard)

 

 

. . . . . . . .

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注