Mysql--percona-xtradb-cluster using Xtrabackup to add nodes

Although PXC supports adding cluster nodes online, it has not yet addressed the Wsrep_sst_method=xtrabackup or wsrep_sst_method=mysqldump times error. Therefore, try to manually complete the Xtrabackup backup and restore and add the nodes to the cluster.

Scene:

There are three clean CentOS 6 servers, IP configured as: 192.168.166.169,192.168.166.170,192.168.166.171, ready to build three nodes of Percona XtraDB Cluster, Currently 192.168.166.169,192.168.166.170 is joined to the cluster and is ready to add 192.168.166.171 to the clusters.

The wsrep_node_name of 192.168.166.169 is Pxc1,

The wsrep_node_name of 192.168.166.170 is Pxc2,

The wsrep_node_name of 192.168.166.171 is Pxc3,

============================================

First back up on server 192.168.166.170, use galera-info to get the cluster serial number at the time of backup:

/usr/bin/innobackupex--defaults-file="/etc/my.cnf" --host="localhost" --port=3306 --user="Sstuser" --password="S3cret" --socket="/var/lib/mysql/mysql.sock" --galera-Info"/pxc_backup/"

Copy the backup to the 192.168.166.171, switch to the backup directory, and perform the recovery:

Innobackupex--apply-log./

After the recovery is complete, you can see a xtrabackup_galera_info that holds the cluster serial number at the time of the backup

The Xtrabackup_galera_info data in this demo is:

5f3c567e-033f-11e6-a67c-67e2991c9c84:13

Copy files Grastate.dat to 192.168.166.171 from 192.168.166.169 or 192.168.166.170

Under Default RPM installation, the Grastate.dat path is:/var/lib/mysql

The value in the Grastate.dat file is then modified on 192.168.166.171 to Xtrabackup_galera_info data:

2.1 uuid:    5f3c567e-033f-11e6-a67c-67e2991c9c84seqno:   -10

Modify the file MySQL Data directory permissions

Chown-r mysql:mysql/var/lib/mysql

Edit MySQL configuration file/etc/my.cnf

Then execute the following command on 192.168.166.169 and 192.168.166.170 to see the minimum number of serial numbers saved on each node:

Global ' Wsrep_local_cached_downto ';

Assuming that the minimum sequence number on the 192.168.166.170 is lower than the xtrabackup_galera_info data, it proves that the 192.168.166.170 can be used for incremental synchronization, while 192.168.166.170 's Wsrep_node_ Name is Pxc2, so the script that launches the PXC3 node is:

Service MySQL start--wsrep_sst_donor=pxc2

Startup success:

 is lock file (/var/lock/subsys/mysql) existsstarting MySQL (Percona XtraDB Cluster) .... SUCCESS!

Log in to MySQL View status:

' wsrep% ';

Insert test data to verify that cluster synchronization is working correctly.

============================================

Catch the bus, leave, happy weekend

Mysql--percona-xtradb-cluster using Xtrabackup to add nodes