How to setup Percona XtraDB Mysql Cluster

Introduction

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

Some benefits using Percona XtraDB Cluster

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.

Percona Xtradb Cluster Diagram

Setup Percona Xtradb cluster on ubuntu

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).

Reference

https://github.com/percona

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

Our team is happy to answer your sales questions. Fill out the form and we’ll be in touch as soon as possible.

+1-800-712-8823

2901 E Baltimore St, Baltimore, MD 21224