Percona XtraDB Cluster官方文档(https://www.percona.com/doc/percona-xtradb-cluster/5.7/intro.html)有提到PXC的缺点是添加新节点时必须从现有的一个节点复制的数据集，如果是100GB，它将复制100GB;也就是说默认新添加的节点会发生SST(State Snapshot Transfer)的全量同步;那有没有避免全量同步的解决方案呢？答案是通过创建一个slave的方法来避免！
| Node | Host | IP |
|---|---|---|
| Node 4 | node59 | 172.16.60.59 |
1.为新节点安装Percona XtraDB Cluster(略)
- 创建相关目录
shell]# mkdir -p /data/mysql/mysql_3306/{bin_logs,data,error_logs,general_logs,relay_logs,slow_logs,tmp,undo_logs}
shell]# chown mysql.mysql /data/mysql -R
shell]# mkdir /data/backup
- 在node103上创建备份账号
root@localhost:mysql_3306.sock [(none)]> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret'; root@localhost:mysql_3306.sock [(none)]> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; root@localhost:mysql_3306.sock [(none)]> flush privileges;
- 在node103上执行全量备份
shell]# mkdir /data/backup shell]# innobackupex --socket=/tmp/mysql_3306.sock --user=bkpuser --password=s3cret --no-timestamp /data/backup/3306-$(date +%Y%m%d-%H%M)-full 170613 12:56:50 completed OK!
- 把全量备份和/etc/my.cnf拷贝到新节点
shell]# scp -P 20755 -r /data/backup/3306-20170613-1256-full/ 172.16.60.59:/data/backup/ shell]# scp -P 20755 /etc/my.cnf 172.16.60.59:/etc/
- 新节点上修改/etc/my.cnf
server_id=330659
wsrep_cluster_address=gcomm://172.16.123.101,172.16.123.102,172.16.123.103,172.16.60.59 #cluster节点ip
wsrep_node_address=172.16.69.59 #当前节点ip
shell]# innobackupex --apply-log /data/backup/3306-20170613-1256-full/ shell]# innobackupex --copy-back /data/backup/3306-20170613-1256-full/
- 新到恢复出来的binlog和position
shell]# cat /data/mysql/mysql_3306/data/xtrabackup_binlog_pos_innodb mysql-bin.000011 1694584
- 在备份的节点即node103上找到对应的Xid
shell]# mysqlbinlog -v /data/mysql/mysql_3306/bin_logs/mysql-bin.000011 | grep 1694584 #170613 12:56:48 server id 3306101 end_log_pos 1694584 CRC32 0x7e35d9cc Xid = 6242 # at 1694584
- 把node103上的grastate.dat拷贝到新节点，并对新节点的grastate.dat的seqno修改为Xid的值
shell]# scp -P 20755 /data/mysql/mysql_3306/data/grastate.dat 172.16.60.59:/data/mysql/mysql_3306/data/ shell]# vim /data/mysql/mysql_3306/data/grastate.dat # GALERA saved state version: 2.1 uuid: e200fd5f-4f69-11e7-9453-fffe2e4fc356 seqno: 6242 safe_to_bootstrap: 0
- 新节点上, 先把pxc的配置注释掉启动mysql, 然后关闭mysql再把刚才的注释取消,再启动mysql，最后在error.log可以看到
2017-06-13T05:30:02.576482Z 2 [Note] WSREP: IST receiver addr using tcp://172.16.60.59:4568
2017-06-13T05:30:02.576847Z 2 [Note] WSREP: Prepared IST receiver, listening at: tcp://172.16.60.59:4568
017-06-13T05:30:02.576898Z 2 [Note] WSREP: State gap can be likely serviced using IST. SST request though present would be void.
2017-06-13T05:30:03.167630Z WSREP_SST: [INFO] xtrabackup_ist received from donor: Running IST
2017-06-13T05:30:04.337287Z 2 [Note] WSREP: Receiving IST: 1301 writesets, seqnos 6242-7543
2017-06-13T05:32:02.544927Z 2 [Note] WSREP: IST received: e200fd5f-4f69-11e7-9453-fffe2e4fc356:7543
如果全量备份时间比较久之前的，可以恢复全量+增量,启动mysql(注意要注释掉pxc的配置),change master to 其他一个节点,等同步完成后stop slave,找到show slave status输出中的Relay_Master_Log_File和Exec_Master_Log_Pos，在对应的主库binlog找到Xid，然后再伪造grastate.dat，取消pxc的注释后重新启动mysql，最后执行reset slave all清除主从复制信息