How to install Percona XtraDB Cluster

For this tutorial, we will be installing:

• Percona-XetraDB-cluster 5.7
• Haproxy version 1.6.3
• sysbench version 1.0.12

Prerequisites

To complete this tutorial, you'll need the following:

• Four(4) Debian 9(Stretch) servers

This lab has been tested also on Ubuntu 14.04 and Xenial 16.04.

Server Information

Servername	IP adress	Node Role
db1	10.192.16.58	First db server
db2	10.192.16.59	Second db server
db3	10.192.16.60	Third db server
web1	10.192.16.61	Test client

STEP 1- Install Debian 9 on all 4 servers

Make sure that you install Debian 9 on all servers and all servers are up to date by runnimg the commands below after the install.

sudo apt-get update

sudo apt-get -y upgrade 

Note: If you are not running a DNS server in your environment, make sure you update you /etc/hosts file on each node.

127.0.0.1     localhost
10.192.16.58  db1
10.192.16.59  db2  
10.192.16.60  db3
10.192.16.61  web1
# The following lines are desirable for IPv6 capable hosts
::1     localhost ip6-localhost ip6-loopback
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters

STEP 2- Install Percona_XtraDB-Cluster on db1,db2 and db3

• Login as root
• Use "vim" or "nano" to create a new file "percona.sh".
• Copy the script below and paste it into the new file.

Percona install script:

#!/bin/bash
#update all nodes
sudo apt-get update
sudo apt-get -y upgrade
#Install Percon-Xtradb-cluster
sudo apt-get -y install wget
cd /tmp/
wget https://repo.percona.com/apt/percona-release_0.1-5.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-5.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get -y install percona-xtradb-cluster-57
#Stop mysqld on all nodes
sudo systemctl stop mysql

• Save the file and close it
• Make the file executable

chmod +x percona.sh

• Run the script

./percona.sh

During the install, you will be asked to enter the "mysql root password". Enter a password

Use the same password for all 3 nodes

• After the install complete, check mysql is not running on all node.The script should stop mysql, but it is always good to check.

systemctl status mysql
Active: inactive (dead)

if mysql is running stop it with:

systemctl stop mysql

STEP 3- Configue Percona_XtraDB-Cluster

Now is time to configure the servers.It is best to start on the first node (db1) open the file /etc/mysql/my.cnf The file will look like this

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/

At the end of the file add the following lines

[mysqld]
server_id=1
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.58
wsrep_node_name=db1
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

Once done save the file

db1 configuration file

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
[mysqld]
server_id=1
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.58
wsrep_node_name=db1
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

db2 configuration file

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
[mysqld]
server_id=2
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.59
wsrep_node_name=db2
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

db3 configuration file

#
# The Percona XtraDB Cluster 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/
[mysqld]
server_id=3
datadir=/var/lib/mysql
user=mysql
# Path to Galera library
wsrep_provider=/usr/lib/libgalera_smm.so
# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.192.16.58,10.192.16.59,10.192.16.60
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
# This InnoDB autoincrement locking mode is a requirement for Galera
innodb_autoinc_lock_mode=2
# Node  address and name
wsrep_node_address=10.192.16.60
wsrep_node_name=db3
# SST method
wsrep_sst_method=xtrabackup-v2
# Cluster name
wsrep_cluster_name=ppnet_cluster
# Authentication for SST method
wsrep_sst_auth="sstuser:password"

After editing /etc/mysql/my.cnf, it is time to bootstrap the first node.

STEP 4- Bootstrap the first node (db1)

Use the command below to bootstrap the first node

/etc/init.d/mysql bootstrap-pxc

if everything goes well you will get the message below

[ ok ] Bootstrapping Percona XtraDB Cluster database server: mysqld ..

• Login to mysql

mysql -p 

• Enter the passwword used during installation
• Type

show status like "wsrep_cluster_size";

You should see for the cluster size value

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

so far everything it going well. Let us start the second node.