Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Percona XtraDB Cluster is a active/active database clustering solution for MySQL. It ensures high availability, prevents downtime and data loss, and provides linear scalability for a growing environment
Features of Percona XtraDB Cluster include:
- Synchronous replication
- Multi-master replication
- True parallel replication
- Automatic node provisioning
- Data consistency
- PXC Strict Mode
- Configuration script for ProxySQL
- Automatic configuration of SSL encryption
- Optimized Performance
I will give you the steps to install it step by step:
1. Fetch the package for configuring Percona software repository:
1 | wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb |
2. Install the downloaded package with dpkg:
1 2 | dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb ls /etc/apt/sources.list.d/percona-release.list |
3. Update the local cache:
1 | apt-get update |
4. Install the Percona XtraDB Cluster server package and qpress package:
1 | apt-get install percona-xtradb-cluster-full-57 qpress |
5. Login to MySQL and make some users with some permissions because we will use it later
1 2 3 4 5 6 | mysql -u root -p grant all privileges on *.* to 'sstuser'@'%' identified by 'pass' with grant option; GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%'; grant all privileges on *.* to 'user'@'%' identified by 'pass' with grant option; FLUSH PRIVILEGES; exit |
6. Stop the mysql service in order to start again and take the changes:
1 | service mysql stop |
7. Pinning the Packages in order not to make upgrade later
1 2 3 4 5 | vim /etc/apt/preferences.d/00percona.pref Package: * Pin: release o=Percona Development Team Pin-Priority: 1001 |
8. Now Configure Cluster on the first & Second and The third nodes
1 | vim /etc/mysql/percona-xtradb-cluster.conf.d/wsrep.cnf |
##Node1
1 2 3 4 5 6 7 8 9 10 11 | wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_name=dbcluster wsrep_cluster_address=gcomm://192.168.1.2,192.168.1.3,192.168.1.4 wsrep_node_name=dbnode1 wsrep_node_address=192.168.1.2 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:pass pxc_strict_mode=ENFORCING binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 |
##Node2
1 2 3 4 5 6 7 8 9 10 11 | wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_name=dbcluster wsrep_cluster_address=gcomm://192.168.1.2,192.168.1.3,192.168.1.4 wsrep_node_name=dbnode2 wsrep_node_address=192.168.1.3 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:pass pxc_strict_mode=ENFORCING binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 |
##Node3
1 2 3 4 5 6 7 8 9 10 11 | wsrep_provider=/usr/lib/libgalera_smm.so wsrep_cluster_name=dbcluster wsrep_cluster_address=gcomm://192.168.1.2,192.168.1.3,192.168.1.4 wsrep_node_name=dbnode3 wsrep_node_address=192.168.1.4 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:pass pxc_strict_mode=ENFORCING binlog_format=ROW default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 |
8. Start the first node as the primary node
1 | /etc/init.d/mysql bootstrap-pxc |
9. Login to MySQL and run below query to check status of replication
1 2 3 | mysql -u root -p show status like 'wsrep%'; |
10. Check these variables to be sure the node now is ready
1 2 3 4 5 | wsrep_local_state_comment Synced wsrep_cluster_status Primary wsrep_cluster_size 1 // means how many nodes inside the cluster wsrep_connected ON wsrep_ready ON |
11. Start the second & the third nodes and check the variables again
1 2 3 4 5 6 7 8 | /etc/init.d/mysql start show status like 'wsrep%'; wsrep_local_state_comment Synced wsrep_cluster_status Primary wsrep_cluster_size 3 // means other nodes now added to the cluster. wsrep_connected ON wsrep_ready ON |
Now it’s finished and your Percona XtraDB Cluster is ready to use without any downtime and with high availability for three nodes.
Beside XtraDB Cluster you have to make load balancing for these three nodes, the best recommended solution from my side Percona MySQL Proxy which I will explain in another article soon.
If you have any question about my article please don’t hesitate to write a comment here and I will happily reply.
Thanks for reading my article.
helpful article
waiting for the next one
Thanks Mr. Ahmad for your support