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"
* 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

STEP 4- Bootstrap the first node (db1)
STEP 5- Start mysql on the second and third node
STEP 6- Install the test client node (web1)