详解MySQL PXC构建新节点只需IST传输的方法

下面小编就为大家带来一篇MySQL PXC构建一个新节点只需IST传输的方法(推荐)。小编觉的挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧

需求场景：原有的pxc环境数据量已经比较大，新买的服务器要加入此集群中，如何让其用IST的方式传输，而不是SST.

PXC传输数据有两种方式：

IST：Incremental State Transfer 增量同步
SST：State Snapshot Transfer 全量同步

IST 发生的条件：This is done using caching mechanism on nodes （即只发生在之前集群里的节点数据还有在缓存中）

判断方法：

mysql> show global status like 'wsrep_local_cached_downto';
+---------------------------+-------+
| Variable_name       | Value |
+---------------------------+-------+
| wsrep_local_cached_downto | 1   |
+---------------------------+-------+
1 row in set (0.00 sec)

此值小于新开节点的seqno：

[root@zejin241 I6000]# cat grastate.dat 
# GALERA saved state
version: 2.1
uuid:  6c86dc17-246f-11e6-9955-ae4d7e89eed2
seqno:  4
cert_index:

所以我们的思路即通过备份一个集群中节点的数据，恢复到新节点上，并伪造一个grastate.dat，来记录备份数据的seqno,这样当新节点启动时就会自动用IST的方法来进行，而不是SST（注意gcache.size应该有足够的容量来保证在备份及恢复这段时间内新产生的数据都被缓存到）

galera.cache: This file is used as a main writeset store. It's implemented as a permanent ring-buffer file
that is preallocated on disk when the node is initialized. File size can be controlled with the variable
gcache.size. If this value is bigger, more writesets are cached and chances are better that the rejoining node will get IST instead of SST.
Filename can be changed with the gcache.name variable.

如下为这次的操作步骤：

node240：192.168.1.240 --已经存在的集群节点
node241：192.168.1.241 --将要加入的集群节点

step1：启动新的集群中的第一个节点

/usr/local/pxc_56/bin/mysqld_safe --defaults-file=/home/mysql/pxc6000.cnf --ledir=/usr/local/pxc_56/bin/ --wsrep-new-cluster &

配置文件如下：

[root@zejin240 I6000]
# cat /home/mysql/pxc6000.cnf
[client]
port      = 6000socket     = /home/mysql/I6000/mysql.sockdefault-character-set=utf8
[mysqld]basedir = /usr/local/pxc_56datadir = /home/mysql/I6000pid-file    = /home/mysql/I6000/mysql.
pidcharacter-set-server=utf8init_connect = 'SET NAMES utf8'log-bin=/home/mysql/I6000/log_binserver-id = 9816000innodb_buffer_pool_size = 
100Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_data_home_dir = /home/mysql/I6000innodb_file_per_table=1skip-name-resolveport      = 
6000socket     = /home/mysql/I6000/mysql.sockuser=mysqllog_error=/home/mysql/I6000/mysql_error.log
#pxcuser=mysqllog_error=error.logbinlog_format=ROW#6000 for mysqld port, 6020 for sst port, 6031 for ist port, 6030 for cluster communication 
portwsrep_cluster_address='gcomm://192.168.1.240:6030,192.168.1.241:6030,192.168.1.242:6030'
wsrep_provider=/usr/local/pxc_56/lib/libgalera_smm.sowsrep_sst_receive_address=192.168.1.240:6020wsrep_node_incoming_address=192.168.1.240:6000
wsrep_node_address=192.168.1.240:6000wsrep_provider_options = "gmcast.listen_addr=tcp://192.168.1.240:6030;ist.recv_addr=192.168.1.240:6031;
"wsrep_slave_threads=2wsrep_cluster_name=pxc_zejinwsrep_sst_method=xtrabackup-v2wsrep_node_name=node6000_240innodb_autoinc_lock_mode=2wsrep_sst_auth="sstuser:123"
log-slave-updates[mysql]no-auto-rehashdefault-character-set=utf8

mysql> use zejin;
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | chen |
| 2 | li  |
| 3 | zhang |
+----+-------+
3 rows in set (0.01 sec)mysql> update mysql.user set password=password('123');mysql> flush privileges;
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File      | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| log_bin.000003 |   2772 |       |         |          |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

step2：用innobackupex备份node240数据：

[root@zejin240 mysql]# innobackupex --user=root --password=123 --port=6000 --host=127.0.0.1 
--defaults-file=/home/mysql/pxc6000.cnf /home/mysql/InnoDB Backup Utility v1.5.1-xtrabackup; 
Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.This software is published 
underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help 
resources:percona.com/xb/p160528 09:39:07 innobackupex: Executing a version check against the server...160528 09:39:07 
innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/home/mysql/pxc6000.cnf;
mysql_read_default_group=xtrabackup;host=127.0.0.1;
port=6000' as 'root' (using password: YES).160528 09:39:07 innobackupex: Connected to MySQL server160528 09:39:07 
innobackupex: Done.160528 09:39:07 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;
mysql_read_default_file=/home/mysql/pxc6000.cnf;
mysql_read_default_group=xtrabackup;host=127.0.0.1;port=6000' as 'root' (using password: YES).160528 09:39:07 
innobackupex: Connected to MySQL server160528 09:39:07 innobackupex: Starting the backup operationIMPORTANT: 
Please check that the backup run completes successfully.      
At the end of a successful backup run innobackupex      
prints "completed OK!".innobackupex: Using server version 5.6.26-74.0-25.12-loginnobackupex: 
Created backup directory /home/mysql/2016-05-28_09-39-07160528 09:39:07 innobackupex: Starting ibbackup with command: 
xtrabackup --defaults-file="/home/mysql/pxc6000.cnf" --defaults-group="mysqld" --backup 
--suspend-at-end --target-dir=/home/mysql/2016-05-28_09-39-07 --innodb_log_file_size="50331648"
 --tmpdir=/tmp --extra-lsndir='/tmp'innobackupex: Waiting for ibbackup (pid=30280) to suspendinnobackupex: 
 Suspend file '/home/mysql/2016-05-28_09-39-07/xtrabackup_suspended_2'xtrabackup 
 version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )xtrabackup: uses 
 posix_fadvise().xtrabackup: cd to /home/mysql/I6000xtrabackup: open files limit requested 0, set to 1024xtrabackup: 
 using the following InnoDB configuration:xtrabackup:  innodb_data_home_dir = /home/mysql/I6000xtrabackup:  
 innodb_data_file_path = ibdata1:10M:autoextendxtrabackup:  innodb_log_group_home_dir = ./xtrabackup:  
 innodb_log_files_in_group = 2xtrabackup:  innodb_log_file_size = 50331648>> log scanned up to (1637591)xtrabackup: 
 Generating a list of tablespaces[01] Copying /home/mysql/I6000/ibdata1 to /home/mysql/2016-05-28_09-39-07/ibdata1>> 
 log scanned up to (1637591)[01]    ...done[01] Copying ./zejin/t1.ibd to /home/mysql/2016-05-28_09-39-07/zejin/t1.ibd[01]   
  ...done[01] Copying ./mysql/innodb_table_stats.ibd to /home/mysql/2016-05-28_09-39-07/mysql/innodb_table_stats.ibd[01]   
   ...done[01] Copying ./mysql/slave_master_info.ibd to /home/mysql/2016-05-28_09-39-07/mysql/slave_master_info.ibd[01]   
   ...done[01] Copying ./mysql/slave_relay_log_info.ibd to /home/mysql/2016-05-28_09-39-07/mysql/slave_relay_log_info.ibd[01]    
   ...done[01] Copying ./mysql/slave_worker_info.ibd to /home/mysql/2016-05-28_