Percona XtraDB Cluster Installation Guide

Percona XtraDB Cluster (PXC) cluster is a general multi-master synchronous replication plug-in based on Galera and transactional application, which is mainly used to solve the strong consistency problem, so that the data of each node is kept in real-time synchronization and multi-node simultaneous reading and writing is realized. It is one of the well-known cluster optimization schemes in MySQL relational database to improve the reliability of database and to realize the separation of read and write.

1. Install the PXC package

Here is the official latest package Percona XtraDB Cluster 5.7.22-29.26, the corresponding operating system is Oracle Linux 7.5. The list of hosts is shown in the following table:

Install with Yum on each node, as follows:

[[email protected] ~]# yum -y install Percona-XtraDB-Cluster-57[[email protected] ~]# yum -y install Percona-XtraDB-Cluster-57[[email protected] ~]# yum -y install Percona-XtraDB-Cluster-57

After the installation is complete, start each node's MySQL service separately, change the [email protected] password, and then close the MySQL service after the change is complete. Take MYDB03 as an example, as follows:

[[email protected] ~]# grep ‘temporary password‘ /var/log/mysqld.log[[email protected] ~]# mysql -uroot -pmysql> alter user [email protected] identified by “[email protected]”;mysql> flush privileges;

2. Edit the PXC configuration file

Edit the/etc/percona-xtradb-cluster.conf.d/mysqld.cnf file, modify or add the following:

#mydb03对应1，mydb04对应2，mydb05对应3server-id=1     expire_logs_days=1log_timestamps=systemcollation-server=utf8_general_cicharacter-set-server = utf8

Edit the/etc/percona-xtradb-cluster.conf.d/wsrep.cnf file and modify the following:

[mysqld]wsrep_provider=/usr/lib64/galera3/libgalera_smm.so#加入各个节点的IPwsrep_cluster_address=gcomm://192.168.120.129,192.168.120.130,192.168.120.131binlog_format=ROWdefault_storage_engine=InnoDBwsrep_slave_threads= 8wsrep_log_conflictsinnodb_autoinc_lock_mode=2#节点主机名，其他节点需要更改wsrep_node_name=mydb03#节点IP，其他节点需要更改wsrep_node_address=192.168.120.129#集群名称wsrep_cluster_name=pxc-clusterpxc_strict_mode=ENFORCINGwsrep_sst_method=xtrabackup-v2#sstuser用户的密码任意设置wsrep_sst_auth="sstuser:passw0rd"

3. Start PXC Cluster service

First, start the PXC Cluster service on the MYDB03, and the other nodes will be added in the next step.

[[email protected] ~]# systemctl start [email protected][[email protected] ~]# mysql -uroot -pmysql> show status like ‘wsrep%‘;

Create the Sstuser user defined in the configuration file (only the first node that is started, and the other nodes are self-synchronizing when they start).

mysql> CREATE USER ‘sstuser‘@‘localhost‘ IDENTIFIED BY ‘passw0rd‘;mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO ‘sstuser‘@‘localhost‘;mysql> FLUSH PRIVILEGES;

Join other nodes to the cluster, as long as you start the MySQL service.

[[email protected] ~]# systemctl start mysqld[[email protected] ~]# systemctl start mysqld

Cluster Status verification:

mysql> show status like ‘%wsrep_clust%‘;

4, PXC Synchronous test

Any node creates a table or database and queries the other node for validation:

[[email protected] ~]# mysql -uroot -pmysql> create database xdb;[[email protected] ~]# mysql -uroot -pmysql> show databases;

Percona XtraDB Cluster Installation Guide