Installing Percona XtraDB 5.7 Cluster on Centos 7


Installing Percona XtraDB 5.7 Cluster on Centos 7

In this tutorial, we will show you how we configured Percona XtraDB Cluster 5.7 on Centos 7 server. This install will be taking advantage of the Percona repositories for Yum.

For this build we will require 3 Centos 7 systems. In our case this was done using 3 virtual machines running on a local Lan, as we planned this install to take advantage of a Load Balancer like HA Proxy.

We used the following IPs for the database servers:

DB1 – 10.10.10.211

DB2 – 10.10.10.212

DB3 – 10.10.10.213

Initial Configuration: Firewalld and SELinux

First of all we SSH into all 3 servers and configure a few options. This includes disabling SELinux and configuring the necessary firewalld rules to allow the database to operate.

Disable SELinux permanently:

nano /etc/selinux/config

Enter the following to set SELINUX to disabled:

SELINUX=disabled

Once complete we reboot the server for this to take place:

reboot

The next step will be to configure the firewall for the database service. We need to open the relevant ports to allow access. On our minimal install of Centos 7 we had firewalld installed so we used the following commands:

systemctl start firewalld
firewall-cmd --zone=public --add-service=mysql --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --zone=public --add-port=4567/tcp --permanent
firewall-cmd --zone=public --add-port=4568/tcp --permanent
firewall-cmd --zone=public --add-port=4444/tcp --permanent
firewall-cmd --zone=public --add-port=4567/udp --permanent
firewall-cmd --reload

Once we have configured our SELinux and Firewall we’re ready to start with the install of Percona XtraDB Cluster 5.7, which is a clustered version of MySQL 5.7.

Installing Percona XtraDB Cluster 5.7

NOTE: These commands need to be run on all 3 nodes.

First of all we need to install the Percona yum repository:

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

As we don’t want to use MySQL 8 at this time (Due to some scripts not being compatible we used the original build), we need to limit Percona to the original release only:

percona-release enable-only original release
percona-release enable tools release

We can now install the Percona XtraDB Cluster packages:

yum install Percona-XtraDB-Cluster-57

Now we start the Percona XtraDB Cluster server:

service mysql start

Copy the automatically generated temporary password for the superuser account:

grep 'temporary password' /var/log/mysqld.log

Change the password for the superuser account and log out. For example:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'rootPass';
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

Stop the mysql service:

service mysql stop

At this stage we now have 3 servers all able to run MySQL and with a root password set. We are now going to build the clustered nodes. To do this we need to configure them in stages.

Configuring Node #1

On the first server which in our example is percona1 you edit the my.conf:

nano /etc/my.cnf

There are two lines in this config that you need to change to match your IP addresses – wsrep_cluster_address & wsrep_node_address. Also you should remove everything from the original my.cnf file and fully replace it with:

[mysqld]
datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.10.10.211,10.10.10.212,10.10.10.213

# 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 1 address
wsrep_node_address=10.10.10.211

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

Start the first node with the following command:

systemctl start mysql@bootstrap.service

After the first node has been started, cluster status can be checked with the following command:

mysql -u root -p

To test the service is running:

SHOW STATUS LIKE 'wsrep_local_state_comment';

Check the nodes in the cluster, which at this stage should be 1:

show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+
1 row in set (0.00 sec)

These commands can also be run again at the end to confirm everything is correct.

To perform State Snapshot Transfer using XtraBackup, set up a new user with proper privileges:

mysql@percona1> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret';
mysql@percona1> GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';

Configuring Node #2

Now we configure the second node:

nano /etc/my.cnf

Now enter the same configuration as Node #1, except with the IP address we used for our second node.

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.10.10.211,10.10.10.212,10.10.10.213

# 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 2 address
wsrep_node_address=10.10.10.212

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
datadir=/var/lib/mysql
user=mysql

Then we start MySQL which now can be started normally:

systemctl start mysql

Configuring Node #3

Finally we setup the 3rd server.

nano /etc/my.cnf

Again, we’re configuring this in the same way as the previous two nodes, but with the IP address of Node #3:

[mysqld]
datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://10.10.10.211,10.10.10.212,10.10.10.213

# 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 2 address
wsrep_node_address=10.10.10.213

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

and again start MySQL

systemctl start mysql

If all the clusters start correctly you should be able to confirm this on any of the nodes by:

mysql> SHOW STATUS LIKE 'wsrep_local_state_comment';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| wsrep_local_state_comment | Synced |
+---------------------------+--------+
mysql> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
1 row in set (0.00 sec)

The next time you reboot the first server you don’t need to bootstrap the mysql any longer.

Testing the Percona setup

To test replication, lets create a new database on second node, create a table for that database on the third node, and then add some records to the table on the first node.

1. Create a new database on the second node:

mysql@percona2> CREATE DATABASE percona;
Query OK, 1 row affected (0.01 sec)

2. Create a table on the third node:

mysql@percona3> USE percona;
Database changed
mysql@percona3> CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));
Query OK, 0 rows affected (0.05 sec)

3. Insert records on the first node:

mysql@percona1> INSERT INTO percona.example VALUES (1, 'percona1');
Query OK, 1 row affected (0.02 sec)

4. Retrieve all the rows from that table on the second node:

mysql@percona2> SELECT * FROM percona.example;
+---------+-----------+
| node_id | node_name |
+---------+-----------+
|       1 | percona1  |
+---------+-----------+
1 row in set (0.00 sec)

This simple procedure should ensure that all nodes in the cluster are synchronized and working as intended.


I hope you find this guide helpful. A lot of thanks to the Percona documentation and a few other resources to get this working.

One thing to consider next is how you can use this in production. I am going to test both HA Proxy and ProxySQL and will provide a post on this shortly. So why do we need HA Proxy or ProxySQL? Well currently you have 3 different IP’s you can connect to with MySQL and we want a single IP which can allocate the load between the 3 servers (or more).

Leave a Reply

Your email address will not be published. Required fields are marked *