Titus IT world

Mysql DB is great, it’s free and has good performance. Also has good combination with Apache and PHP.

But we need to use multiple DB servers for High availability. And the each db clusters need to be synced and keep the same data for each server while it delivers or updates records.

Percona XtraDB could be great solution for it. I would like to present how to install percona XtraDB on 3 servers.

We need to have 3 DB servers with Ubuntu 18.04. The original documentation can be found on Percona website.

Open Ports

I set it up with 3 servers, with these IPs.

Percona1 - 10.0.21.1
Percona2 - 10.0.21.2
Percona3 - 10.0.21.3

Before starting, some of server ports need to be opened for communicate each other.

Open 3306, 4444, 4567, 4568 ports for each servers. And open the ports using below commands.

iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 3306 -j ACCEPT
iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 4444 -j ACCEPT
iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 4567 -j ACCEPT
iptables -A INPUT -i eth0 -p tcp -m iprange --src-range 10.0.21.1-10.0.21.3 --dport 4568 -j ACCEPT

If you are using AWS, you will need to open the ports as well on security groups. Just like this.

Remove apparmor or Mysql

I strongly recommend do not install mysql before installing Percona DB. It will conflict and may not working properly.
Also remove apparmor before installing.

sudo apt-get remove apparmor

Install Percona XtraDB package

Using below command, install package. During installation, you will need to set up root password.

wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb 
sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtradb-cluster-full-57

Prepare same for all 3 servers. If you are using cloud service, like AWS, you may want to take snapshot and duplicate the Percona servers.

Login into all servers and stop mysql service.

sudo service mysql stop

Config mysql on master

I have prepared these 3 nodes.
Percona1 – 10.0.21.1
Percona2 – 10.0.21.2
Percona3 – 10.0.21.3

And config mysql on master server(10.0.21.1). All servers will be set up as master, but at the beginning, we need to designate one server for master and others could be synced into it.

vi /etc/mysql/my.cnf

[mysqld]

wsrep_provider=/usr/lib/libgalera_smm.so

wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://10.0.21.1,10.0.21.2,10.0.21.3

wsrep_node_name=pxc1
wsrep_node_address=10.0.21.1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:sstuser_password

pxc_strict_mode=ENFORCING

binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-xtradb-cluster.conf.d/

Set all node with same configuration but you will need to change wsrep_cluster_address for all your nodes and configure below line.

wsrep_node_name=pxc1
wsrep_node_address=10.0.21.1

Also set your sstuser and password for this line:

wsrep_sst_auth=sstuser:sstuser_password

Create SST user

Login to each nodes and login to mysql. Then create SST users.

CREATE USER 'sstuser'@'%' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'%';

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'sstuser_password';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

Open port from terminal

Double check if port is opened and it communicate each other.

iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 3306 --source 10.0.21.0/24 --jump ACCEPT
iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 4567 --source 10.0.21.0/24 --jump ACCEPT
iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 4568 --source 10.0.21.0/24 --jump ACCEPT
iptables --append INPUT --in-interface eth0 --protocol tcp --match tcp --dport 4444 --source 10.0.21.0/24 --jump ACCEPT

Run first node

For the first node, this need to be executed.

/etc/init.d/mysql bootstrap-pxc

After that check status with mysql command below.

show status like 'wsrep%';

wsrep_local_state_comment should show as synced and wsrep_cluster_size will be 1

Run all other nodes

Run 2nd nodes using mysql command and check the status

/etc/init.d/mysql start

show status like 'wsrep%';

wsrep_cluster_size should be 2 and wsrep_local_state_comment should show as Synced

Run all other nodes with same way. wsrep_cluster_size should be same node number as all node servers at the end.