How to setup Percona XtraDB Mysql Cluster

Percona XtraDB Cluster is a database clustering solution for MySQL. It ensures high availability, prevents downtime and data loss, and provides linear scalability for a growing environment. Some of the key feature of percona XtraDB cluster is:-

• Synchronous Replication, Data is written to all nodes simultaneously, or not written at all in case of a failure even on a single node.
• Multi-master Replication, Any node can trigger a data update and it will update data to all node automatically.
• True parallel Replication, Multiple threads on slave capable to performing replication on row level
• Automatic Node Provisioning, You simply add a node and it automatically syncs.
• Data consistency, No more unsynchronized nodes.
• Optimized Performance, Percona XtraDB Cluster performance is optimized to scale with a growing production workload

Percona XtraDB Cluster is a fully open-source high-availability solution for MySQL. It integrates Percona Server and Percona XtraBackup with the Galera library to enable synchronous multi-master replication. The cluster consists of nodes and regular mysql server installed, where each node contains the same set of data synchronized across nodes and each node capable of doing read and write operation to database cluster which get automatic sync over the nodes, for sync across the node percona use Galera Library.

Some benefits using Percona XtraDB Cluster

• In percona XtraDB cluster when you query, It execute locally in Node, no need establish remote connection.
• No central management. You can lose any node at any point of time, and the cluster will continue to function without any data loss.
• Good for a production environment where scaling is needed.

Lets setup Percona Xtradb cluster on ubuntu virtual machine with 2 Nodes, to setup we need to open a few ports which allow communication between all the nodes.

• Regular MySQL port (default is 3306)
• Port for group communication (default is 4567)
• Port for State Snapshot Transfer (default is 4444)
• Port for Incremental State Transfer (default is port for group communication + 1 or 4568)

Assumptions

Role	Hostname	IP Address	Operating System
master-node1	percona01	10.11.23.221	Ubuntu
master-node2	percona02	10.11.23.222	Ubuntu

On Node 1

First, change the hostname

hostname percona01

Add Percona Repository

wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb

Install Percona-XtraDB-Cluster

apt-get update
apt-get install -y percona-xtradb-cluster-57
systemctl stop mysql

Configure Replication Settings

cat >>/etc/mysql/my.cnf
[mysqld]
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=mysqlcluster
wsrep_cluster_address=gcomm://
wsrep_node_name=percona01
wsrep_node_address=10.11.23.221
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=demouser:demopassword
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2   

Initialize mysql server

systemctl start mysql

Create Replication User

mysql -uroot -p -e "create user demouser@localhost identified by 'demopassword'"
mysql -uroot -p -e "grant reload, replication client, process, lock tables on *.* to demouser@localhost"
mysql -uroot -p -e "flush privileges"   

Update Replication configuration

sed -i 's/^wsrep_cluster_address=.*/wsrep_cluster_address=gcomm:\/\/10.11.23.221,10.11.23.222/' /etc/mysql/my.cnf

On Node 2

First, change the hostname

hostname percona02

Add Percona Repository

wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb
dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb

Install Percona-XtraDB-Cluster

apt-get update
apt-get install -y percona-xtradb-cluster-57
systemctl stop mysql  

Configure Replication Settings

cat >>/etc/mysql/my.cnf
[mysqld]
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name=mysqlcluster
wsrep_cluster_address=gcomm://10.11.23.221,10.11.23.222
wsrep_node_name=percona02
wsrep_node_address=10.11.23.222
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=demouser:demopassword
pxc_strict_mode=ENFORCING
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2          

Initialize mysql server

systemctl start mysql

Repeat the same above step for adding new node to the cluster and update mysql configuration file (my.cnf).

https://github.com/percona

https://www.percona.com/blog/