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.
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:
- Change database engine to InnoDB – This can be done using the ALTER command of MySQL.
- 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.
- 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.
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.
Get a FREE consultation
Do you face frequent server performance issues?
Never again lose customers to poor page speed! Let us help you.
We keep your servers optimized, secured and updated at all times. Our engineers monitor your applications 24/7 and fix issues before it can affect your customers.
Talk to our application infrastructure specialist today to know how to keep your service top notch!
TALK TO AN EXPERT NOW!
0 Comments