Learn more about using MySQL on cPanel with Galera Cluster. Our cPanel Support team is here to help you with your questions and concerns.

MySQL on cPanel with Galera Cluster

Galera Cluster is a synchronous multi-master database cluster designed to deliver high availability for MySQL, MariaDB, and Percona XtraDB databases. This solution ensures scalability and fault tolerance, making it a preferred choice for mission-critical database systems.

Here are some of the key features of Galera Cluster:

• Enables read and write operations on any node in the cluster with real-time replication.

  

• Guarantees no data loss by eliminating slave lag.
• Ensures all nodes maintain the same data state without divergence.
• Enhances performance and supports various workloads.
• Simplifies adding new nodes without manual backups.
• Ensures transactional table support.
• Requires minimal or no changes to database interactions.
• Removes the need to segregate queries.

All About MySQL on cPanel with Galera Cluster

Integrating Galera Cluster with cPanel involves careful planning and configuration to achieve a high-availability MySQL setup. The process includes:

1. Set up a remote server with proper settings, including disabling `skip-name-resolve` and ensuring all MySQL tables use InnoDB.
2. Deploy Keepalived and two HAProxy instances to manage failover scenarios and virtual IP addresses.
3. Ensure network infrastructure supports multicast or unicast and configure MySQL users to align with cluster limitations.

With these configurations, Galera Cluster ensures continuous service availability and robust failover mechanisms.

Prerequisites for Galera Cluster Setup

• Correct `/etc/hosts` entries on all servers.
• Avoid MyISAM tables to maintain stability.
• Support for multicast or unicast for seamless failover.
• Also, ensure remote MySQL servers resolve local hostnames correctly.
• Furthermore, MySQL, Perl, and SSH connectivity must be configured.

Meeting these conditions set up the groundwork for a stable and scalable high-availability setup.

Deploying Galera Cluster for MySQL

1. Generate a deployment package using the Galera Configurator, using parameters such as vendor (Percona XtraDB Cluster) and OS (RHEL6/CentOS6).
2. Also, three Galera servers and one ClusterControl server were set up.
3. Now, download and extract the package as seen here:

   wget <deployment-package-url>
tar -xzf <package-file>.tar.gz

4. Then, edit the `cmon.cnf.controller` file to remove repeated node IP addresses.
5. Next, deploy cluster:

   cd ~/s9s-galera-percona-3.5.0/mysql/scripts/install/
bash ./deploy.sh 2>&1 | tee cc.log

6. Then, head to the ClusterControl UI at `https:///clustercontrol`.

Running Galera on three nodes with an additional Galera Arbitrator ensures optimal performance and redundancy.

Deploying HAProxy and Keepalived

1. Use ClusterControl to deploy HAProxy on designated MySQL nodes.
2. Access HAProxy statistics to verify active database nodes.
3. Then, deploy Keepalived on HAProxy instances to manage virtual IP addresses.
4. Ensure seamless IP transitions between active nodes during failover.

These tools enhance load balancing and availability, ensuring a resilient MySQL environment.

Integrating Galera Cluster with cPanel

1. First, create a `.my.cnf` file for root users on all nodes.
2. Then, configure WHM’s Setup Remote MySQL Server feature with the virtual IP address of the Galera Cluster.
3. Next, disable the local MySQL service in WHM and confirm connections via PHPMyAdmin.
4. Ensure new databases reflect the remote MySQL server’s virtual IP address.

Adapting cPanel for Galera Cluster

1. Replace `ENGINE=MyISAM` with `ENGINE=InnoDB` in installer scripts:

   sed -i 's|ENGINE=MyISAM|ENGINE=InnoDB|g' $(grep -lir "engine=MyISAM" *)
2. Also, verify storage engine settings after upgrades to cPanel or third-party tools.

By ensuring compatibility, cPanel can fully leverage the advantages of the Galera Cluster for high availability.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

Galera Cluster is a powerful solution for ensuring high availability and redundancy in MySQL-based environments. We can create a robust and scalable database hosting solution by integrating it with tools like cPanel, HAProxy, and Keepalived.

In brief, our Support Experts demonstrated how to set up MySQL on cPanel with Galera Cluster.