Percona XtraDB Cluster cluster Environment establishment and validation guide

Percona XtraDB Cluster cluster Environment establishment and validation guide
Percona XtraDB Cluster is a clustering scheme for MySQL database. and compatible with the MySQL server Community version, Percona Server, and MariaDB.
First, install Percona XtraDB on Ubuntu Cluster
Lab Environment:
Suppose you have 3 computer devices that have an Ubuntu system installed and will be used as 3 nodes:
Node Host IP
Node1 Pxc1 172.16.24.209
Node2 Pxc2 172.16.24.208
Node3 Pxc3 172.16.24.207

Pre-conditions:
(1) Ensure that the port is not blocked by a firewall or is occupied by another process:
3306
4444
4567
4568
(2) Uninstalling AppArmor
sudo apt-get remove AppArmor

Installation steps:
Execute the following commands on each device:
wget https://repo.percona.com/apt/percona-release_0.1-4.$ (LSB_RELEASE-SC) _all.deb
sudo dpkg-i percona-release_0.1-4.$ (LSB_RELEASE-SC) _all.deb
sudo apt update
sudo apt install percona-xtradb-cluster-full-57
Passord:frank

At this point, Percona-xtradb-cluster has been installed and logged in to MySQL.
Mysql-u root-p
(Enter the password "Frank").
Add user
mysql> CREATE USER ' sstuser ' @ ' localhost ' identified by ' passw0rd ';
Mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT on *. * to ' sstuser ' @ ' localhost ';
mysql> FLUSH privileges;
Mysql> quit
sudo service MySQL stop

Second, the Configuration node
1. Initializing the cluster
Use the 1th device as the 1th cluster node. Add the following configuration in/ETC/MYSQL/MY.CNF:
###############################################################
[Mysqld]
Wsrep_provider=/usr/lib/libgalera_smm.so
Wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://
Wsrep_node_name=pxc1
wsrep_node_address=172.16.24.209
Wsrep_sst_method=xtrabackup-v2
Wsrep_sst_auth=sstuser:passw0rd
Pxc_strict_mode=enforcing
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
###############################################################
After the modification is complete, execute:
Sudo/etc/init.d/mysql BOOTSTRAP-PXC
The database will start in bootstrap mode. At this point, the cluster initialization has been completed. Log in to MySQL and execute the following command to view the initialization results:
Mysql> Show status like ' wsrep% ';
+----------------------------------+--------------------------------------+
| variable_name | Value |
+----------------------------------+--------------------------------------+
| Wsrep_local_state_uuid | 0d718de1-a19c-11e7-81e3-127c64915155 |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 4 |
...
| wsrep_local_state_comment | synced |
...
| wsrep_cluster_conf_id | 1 |
| Wsrep_cluster_size | 1 |
| Wsrep_cluster_state_uuid | 0d718de1-a19c-11e7-81e3-127c64915155 |
| Wsrep_cluster_status | Primary |
| wsrep_connected | On |
| Wsrep_local_bf_aborts | 0 |
| Wsrep_local_index | 0 |
| Wsrep_provider_name | Galera |
| Wsrep_provider_vendor | Codership Oy <[email protected]> |
| wsrep_provider_version | 3.22 (r8678538) |
| Wsrep_ready | On |
+----------------------------------+--------------------------------------+
All in Set (0.00 sec)

2. Adding nodes
Add a 2nd device, modify its configuration file/etc/mysql/my.cnf, and add the following statement:
###############################################################
[Mysqld]
Wsrep_provider=/usr/lib/libgalera_smm.so
Wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://172.16.24.209,172.16.24.208,172.16.24.207
Wsrep_node_name=pxc2
wsrep_node_address=172.16.24.208
Wsrep_sst_method=xtrabackup-v2
Wsrep_sst_auth=sstuser:passw0rd
Pxc_strict_mode=enforcing
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
###############################################################
Exit the restart. After restarting, also log in to MySQL, execute "show status like ' wsrep% ';" To view the added results.


Add a 3rd device, modify its configuration file/etc/mysql/my.cnf, and add the following statement:
###############################################################
[Mysqld]
Wsrep_provider=/usr/lib/libgalera_smm.so
Wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://172.16.24.209,172.16.24.208,172.16.24.207
Wsrep_node_name=pxc3
wsrep_node_address=172.16.24.207
Wsrep_sst_method=xtrabackup-v2
Wsrep_sst_auth=sstuser:passw0rd
Pxc_strict_mode=enforcing
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
###############################################################
Exit the restart. After restarting, also log in to MySQL, execute "show status like ' wsrep% ';" To view the added results.

Make the first device work in normal mode. To modify its configuration file/etc/mysql/my.cnf, add the following statement:
###############################################################
[Mysqld]
Wsrep_provider=/usr/lib/libgalera_smm.so
Wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://172.16.24.209,172.16.24.208,172.16.24.207
Wsrep_node_name=pxc1
wsrep_node_address=172.16.24.209
Wsrep_sst_method=xtrabackup-v2
Wsrep_sst_auth=sstuser:passw0rd
Pxc_strict_mode=enforcing
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
###############################################################
Reboot.

Third, verify the Write Set copy function (Write-set Replication)
1. Create a new database on the second node:
mysql> CREATE DATABASE testDB1;
Query OK, 1 row Affected (0.00 sec)

2. Create a table on the third node:
Mysql> Use TestDB1
Database changed
Mysql> CREATE TABLE Example (node_id INT PRIMARY KEY, Node_name VARCHAR (30));
Query OK, 0 rows affected (0.01 sec)

3. Insert records on the first node:
Mysql> INSERT into Testdb1.example VALUES (1, ' percona1 ');
Query OK, 1 row affected (0.01 sec)


4. Retrieve rows from this table on the second node:
Mysql> SELECT * from Testdb1.example;
+---------+-----------+
| node_id | Node_name |
+---------+-----------+
| 1 | Percona1 |
+---------+-----------+
1 row in Set (0.00 sec)


Reference Documentation:
Percona-xtradb-cluster-5.7.18-29.20.pdf

This article from the "write poetry with C + +" blog, declined reprint!

Percona XtraDB Cluster cluster environment setup and validation guide