Percona is a high performance alternative for MySQL. It uses a modified InnoDB engine called XtraDB, and uses Galera’s clustering technology to offer better performance and uptime than MySQL. Today, let’s take a look at how you can setup a Percona cluster, and migrate your databases from MySQL.

Setting up Percona XtraDB cluster

Percona XtraDB Cluster (PXC) uses multi-master replication technology, where each node in the cluster can execute both read and write queries. To make this possible, special care must be given to adjust security settings, and setup data syncing.

To show you by example, let’s assume we’re setting up a 3-node PXC system with IPs 172.17.9.36, 172.17.9.37, and 172.17.9.38. Here’s the overview of the setup steps.

Open communication ports in firewall (iptables)

Percona uses the ports 3306, 4444, 4567, 4568 to receive connections and communicate with the other members in the cluster. These ports need to be open for all cluster members and application servers.

To allow connections to port 4444 of 172.17.9.36 from 172.17.9.37, the following command should be used in 172.17.9.36.

# iptables -A INPUT -p tcp --source 172.17.9.37 --dport 4444 -j ACCEPT

Repeat the same steps for other ports and IPs in all the nodes. Keep in mind that port 3306 should be open from the web application servers and load balancers (more on this, later).

Disable SELinux

Percona cluster members cannot communicate with others if SELinux is enabled (in RedHat compatible servers). So, before we go ahead with the installation, it needs to be disabled.

# echo 0 > /selinux/enforce

Pro Tip – In Ubuntu systems the equivalent of SELinux is AppArmor. You don’t have to completely disable AppArmor in Ubuntu, and instead you can just disable the MySQL profile using the command:

ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

Install PXC packages

Steps for RedHat compatible systems

In case MySQL is already installed, you’ll need to remove it first in all servers:

# yum remove mysql-server

Now, you’ll need to set up the Percona repository and a supporting package called Socat. Once it’s done, the Percona XtraDB binaries can be installed.

# yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
# yum install http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
# yum install socat Percona-XtraDB-Cluster-56

Note : In CentOS 5 servers, ” Percona-Server-shared-compat-51″ should be installed before “Percona-XtraDB-Cluster-56” is installed.

Steps for Ubuntu compatible systems

If MySQL is already installed it should be removed first:

# apt-get remove mysql-server-5.5

Now, add the Percona repository key to apt.

# apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Then, add the Percona repository to Ubuntu sources located at /etc/apt/sources.list.

deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

Install the PXC packages with:

# apt-get update
# apt-get install percona-xtradb-cluster-55

Bootstrap one node

In a multi-master cluster such as PXC, servers should be started only one after another. This is to make sure that each server is fully in sync with the live servers before being ready to accept database queries.

Let’s say we choose server 172.17.9.36 as the first node. Let’s call this “node 1”. Edit /etc/my.cnf in this server, and input the following entries. See comments to know the purpose of each entry.

 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 user=mysql
 # Specifying the path to galera library for replication
 wsrep_provider=/usr/lib/libgalera_smm.so
 # Cluster initialization configuration for initial startup – mention IP addresses of nodes in the cluster
 wsrep_cluster_address=gcomm://172.17.9.36,172.17.9.37,172.17.9.38
 # Mention IP address of self
 wsrep_node_address=172.17.9.36
 # Method to sync the states between the nodes In the cluster
 wsrep_sst_method=rsync
 # Specify the storage engine and log format
 binlog_format=ROW
 default_storage_engine=InnoDB
 innodb_autoinc_lock_mode=2
 # SST authentication
 wsrep_sst_auth="sstuser:yourPass"

Now node 1 is ready to be started up as “primary”. For this, use the command:

# service mysql bootstrap-pxc

After the server comes online, you can check the cluster status with the command:

mysql> show global status like '%wsrep_cluster%';
 +------------------------------+----------------------------------------------------+
 | Variable_name | Value |
 +------------------------------+----------------------------------------------------+
 | wsrep_cluster_conf_id | 11|
 | wsrep_cluster_size | 1 |
 | wsrep_cluster_state_uuid | fd28bf16-e205-11e5-b1f7-1fcad8d42d44 |
 | wsrep_cluster_status | Primary |
+------------------------------+----------------------------------------------------+
4 rows in set (0.07 sec)

This says that the cluster is now online with ONE node, and is ready to accept connections (denoted as Primary).

Enable State Snapshot Transfer (SST)

At this point, the “cluster” is online. Now, for the other nodes to join the cluster, the current cluster member should be able to send it’s database state to others. For that, a user needs to be created and connection should be allowed from outside.

In our example, the user created is “sstuser” and the password is “yourPass”. To add this user, use the commands:

mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'yourPass';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;

Sync the other nodes with the first node

To bring the other nodes online, copy the /etc/my.cnf from “node 1” to the other servers and “wsrep_node_address” setting should be updated to their corresponding IP addresses.

Then the servers should be started ONE AT A TIME with the command:

# service mysql start

Once all nodes are started, the cluster would be online. At this point, executing the command below on any node should show you the cluster strength. In our example, it’s 3.

mysql> show global status like '%wsrep_cluster%';
 +------------------------------+----------------------------------------------------+
 | Variable_name | Value |
 +------------------------------+----------------------------------------------------+
 | wsrep_cluster_conf_id | 11|
 | wsrep_cluster_size | 3 |
 | wsrep_cluster_state_uuid | fd28bf16-e205-11e5-b1f7-1fcad8d42d44 |
 | wsrep_cluster_status | Primary |
+------------------------------+----------------------------------------------------+
4 rows in set (0.07 sec)

Now the cluster is ready to receive connections.

[ Setting up the cluster is only half the job. To get the performance benefits, you need to spread the database load on all the nodes. Click here to know how to setup Percona Load Balancing. ]

Migrating databases from MySQL

MyISAM is the default engine for MySQL databases, but PXC doesn’t have reliable support for MyISAM. So, it is recommended to change the database engine to InnoDB before attempting a migration. If you have an open source app like WordPress, the change can be made without any trouble. If you have a custom application, you’ll need to consult your developer first.

So, the steps to migrate is:

1. Change database engine to InnoDB – This can be done using the ALTER command of MySQL.
2. Take a logical dump of database  – Use “mysqldump” to take a full SQL dump of the database. If you have a different version of MySQL from what you have in the cluster, you’ll need to upgrade the source database first, to avoid conflicts.
3. Restore the database dump in any node – Since each node is a “primary”, the dump can be restored on any node, and the database will get propagated in all nodes.

[ Want to avoid downtime due to database server failures? Here’s how to setup Percona high availability. ]

Summary

Percona XtraDB Cluster is a popular high performance alternative to MySQL clusters. Here we’ve gone through how a Percona Cluster can be setup, and how MySQL databases can be migrated into the cluster.

Bobcares helps business websites of all sizes achieve world-class performance and uptime, using tried and tested website architectures. If you’d like to know how to make your website more reliable, we’d be happy to talk to you.