Percona PXC Installation and Setup

Setup Percona PXC

Introduction

Percona XtraDB Cluster (PXC) is a Multi-Master parallel replication designed to be a high availability (HA) and high scalability solution for MySQL clustering. PXC is packaged with Galera, which actually gives it the multimaster cluster nature with synchronous replication.

Unlike traditional Master-slave replication which tends to break replication if you write to a slave, PXC allows you to write to any node which is part of the cluster while  guaranteeing high system up-time and no data loss.

In our database Infrastructure, most of our service critical databases run on PXC. We still have a few non-critical databases that still Master-slave replication and also the DRBD/Heartbeat solution for HA. We are slowly migrating all our databases to PXC and should be done by the end of this year 2016.

We also take other things into consideration to provide even higher HA. For instance, we ensure that the nodes in the PXC do not reside on the same rack in the data center. This prevents losing the whole cluster incase of power switch on the rack going off. We also ensure that the master nodes in our PXC - at least for service critical databases, have slaves that replicate from them. The reason for adding traditional master-slave to our PXC to ensure that if for some reason our entire PXC went down due to a bug for example (it has happened twice before and it was not pretty), we can continue to provide our music/tv services (though no writes) to our customers while we work on bringing our PXC back up again. We also have dedicated backup servers that are not used for any service, but setup specifically for data restoration incase the data in PXC got terribly corrupted.

Uninstall existing MySQL server

If you already have an old copy of Percona or MySQL or MariaDB, and want to remove it and install a fresh copy, you should perform the following commands:

   sudo apt-get update
    sudo apt-get purge -y mysql*
    sudo apt-get purge -y percona*
    sudo apt-get autoremove -y
    sudo rm -rf /etc/mysql
    sudo rm -rf /var/lib/mysql
    sudo rm -rf /etc/rc*.d/*mysql
    sudo rm -rf /var/lib/update-rc.d/mysql

Percona Installation

The following must be performed on all the nodes that will be part of the cluster. It is recommended to always use odd numbers of nodes in a cluster to avoid a split brain situation.

Create System Account
    sudo groupadd -g 3306 mysql
    sudo useradd -g 3306 -u 3306 -r mysql
 
Download Public key
   sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8507EFA5

Setup Percona Repository
    sudo vim /etc/apt/sources.list   ##Open file and add the following two lines at the end of the file
 
    deb http://repo.percona.com/apt trusty main
    deb-src http://repo.percona.com/apt trusty main

Update System
    sudo apt-get update
 
Installation
    sudo apt-get install percona-xtradb-cluster-server-5.6

Install Percona Toolkit
    sudo apt-get install percona-toolkit
 
 
Update my.cnf and setup Galera wsrep
    # Galera
    wsrep_cluster_address = gcomm://node_one_ip,node_two_ip,voter_ip
    wsrep_cluster_name = cluster_name
    wsrep_provider = /usr/lib/libgalera_smm.so
    wsrep_provider_options = "gcache.size=4G"
    wsrep_slave_threads = 32
    wsrep_sst_auth = "username:password"
    wsrep_sst_method = xtrabackup-v2
    #
    # XXX You *MUST* change!
    server-id =local_ip

Establish basic table  
    mysql_install_db
 
First, start MySQL on first node by bootstrapping:
    sudo /etc/init.d/mysql bootstrap-pxc

Then on the second node, start MySQL normally
    sudo service mysql start

And finally on the third node if using a three node cluster, also start mysql normally
    sudo service mysql start

After starting all the nodes sequentially, check to make sure that the cluster is properly configured and working by typing the following command:

    mysql> show status like 'wsrep%';


And make sure that 'wsrep_incoming_addresses' displays IPs of all three nodes configured in the cluster, that the 'wsrep_cluster_size'  = 3, and also 'wsrep_local_state_comment' = synced.

Setup a Galera Arbitrator (If using even nodes + 1)

Installing Arbitrator
    sudo apt-get install software-properties-common
    sudo apt-key adv --keyserver keyserver.ubuntu.com --recv BC19DDBA

Create a repository file and add it in the source list
    sudo touch /etc/apt/sources.list.d/galera.list
    ## in /etc/apt/sources.list file, add the line below at the bottom
    deb http://releases.galeracluster.com/ubuntu trusty main
 
Finally, install Arbitrator  
    sudo apt-get update
    sudo apt-get install galera-3 galera-arbitrator-3
 
Update the lines below in Arbitrator file (/etc/default/garb) appropriately
 
    GALERA_NODES="node_one_ip:4567"
    GALERA_GROUP="cluster_name"
    # GALERA_OPTIONS=""
    LOG_FILE="/var/log/db-test-db-image-voter.log"
 
The IP should be one of the clusters in the PXC cluster.

*NOTE: If garb does not start, maybe create the file beforehand as show below:
    sudo touch /var/log/db-test-db-image-voter.log
    sudo chmod 666 /var/log/db-test-db-image-voter.log
 
Start Arbitrator
    sudo service garb start
 
REFERENCES
https://www.globo.tech/learning-center/how-to-setup-a-galera-arbitrator-on-ubuntu-14-04-lts/