MySQL -- Percona-XtraDB-Cluster uses xtrabackup to add nodes,

MySQL -- Percona-XtraDB-Cluster uses xtrabackup to add nodes,

Although PXC supports online addition of cluster nodes, wsrep_sst_method = xtrabackup or wsrep_sst_method = mysqldump has not been resolved. Therefore, we try to manually back up and restore xtrabackup and add it to the cluster node.

Scenario:

There are three clean CentOS 6 servers with IP addresses configured as 192.168.166.169, 192.168.166.170, 192.168.166.171. We are preparing to build a three-node Percona XtraDB Cluster. Currently, 192.168.166.169 and 192.168.166.170 have been added to the Cluster, add 192.168.166.171 to the cluster.

Wsrep_node_name of 192.168.166.169 is pxc1,

Wsrep_node_name of 192.168.166.170 is pxc2,

Wsrep_node_name of 192.168.166.171 is pxc3,

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

First, back up the instance on 192.168.166.170 and use galera-info to obtain the cluster serial number during the 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 file to 192.168.166.171, switch to the backup directory, and perform Restoration:

innobackupex --apply-log ./

After the recovery is complete, you can see an xtrabackup_0000a_info file that stores the cluster serial number during Backup.

In this demo, The xtrabackup_galera_info data is:

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

 

Copy the grastate. dat file from 192.168.166.169 or 192.168.166.170 to 192.168.166.171.

By default, the grastate. dat path is/var/lib/mysql

Then, on 192.168.166.171, modify the value in the grastate. dat file to xtrabackup_galera_info:

# GALERA saved stateversion: 2.1uuid:    5f3c567e-033f-11e6-a67c-67e2991c9c84seqno:   -1safe_to_bootstrap: 0

Modify the permission of the mysql data directory of a file

chown -R mysql:mysql /var/lib/mysql

Edit the mysql configuration file/etc/my. cnf

 

Run the following commands on 192.168.166.169 and 192.168.166.170 respectively to view the minimum serial number saved on each node:

show global status like 'wsrep_local_cached_downto';

Assuming that the minimum serial number on 192.168.166.170 is lower than the xtrabackup_galera_info data, it is proved that incremental synchronization can be performed using 192.168.166.170, and wsrep_node_name of 192.168.166.170 is pxc2. Therefore, the script for starting pxc3 nodes is:

service mysql start --wsrep_sst_donor=pxc2

Started successfully:

ERROR! MySQL (Percona XtraDB Cluster) is not running, but lock file (/var/lock/subsys/mysql) existsStarting MySQL (Percona XtraDB Cluster).... SUCCESS!

Log on to MySQL to view the status:

show status like 'wsrep%';

Insert test data to verify that the cluster synchronization is normal.

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

Catch the shuttle bus and leave, happy weekend