PXC集群添加新节点
本篇文章是在二进制包方式安装 PXC 集群 这篇博客的基础上操作的。
PXC 集群中已经存在 db-node1/2/3 这 3 个节点，我们把 db-node4 节点作为新节点并添加到 PXC 集群中。
PXC 添加新节点的方式有两种：一是使用 SST（State Snapshot Transfer）全量传输方式，二是使用 IST（Incremental State Transfer ）利用 MySQL 复制和备份的特性增量传输数据方式添加新节点。我们选择使用 IST 方式添加新节点。
1.环境信息介绍
版本信息：
| 名称 | 版本 |
|---|---|
| 系统 | CentOS Linux release 7.2.1511 (Core) |
| Percona-XtraDB-Cluster | 5.7.21 |
主机信息：
| 主机名 | IP |
|---|---|
| db-node1 | 192.168.199.230 |
| db-node2 | 192.168.199.231 |
| db-node3 | 192.168.199.131 |
| db-node4 | 192.168.199.212 |
2.安装依赖
yum install -y perl-DBD-MySQL perl-IO-Socket-SSL socat nc percona-xtrabackup-24.x86_64
3.下载 PXC
# wget -P /usr/local/ https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.21-29.26/binary/tarball/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gz
解压并创建软链接：
# tar zxf Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101.tar.gz -C /opt/mysql/
# ln -sv /opt/mysql/Percona-XtraDB-Cluster-5.7.21-rel20-29.26.1.Linux.x86_64.ssl101 /usr/local/mysql
4.创建数据库相关目录
# mkdir -pv /data/mysql/mysql3306/{data,logs,tmp}
修改权限：
# chown -R mysql.mysql /usr/local/mysql/
# chown -R mysql.mysql /data
5.创建配置文件
可以从其他节点拷贝过来一份配置文件，记得修改 server-id 参数，里面不要包含 PXC 的配置信息
# cat > /etc/my.cnf << EOF
###### base ######
#my.cnf
[client]
port = 3306
socket = /tmp/mysql3306.sock
[mysql]
prompt="\\u@\\h:\\p [\\d]> "
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
tmpdir = /data/mysql/mysql3306/tmp
port = 3306
socket = /tmp/mysql3306.sock
event_scheduler = 0
#wsrep_debug=on
#wsrep_provider='debug=on'
#timeout
interactive_timeout = 300
wait_timeout = 300
#character set
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
lower_case_table_names =1
###### GTID ######
gtid-mode = on
enforce-gtid-consistency=1
###### symi replication ######
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000 # 1 second
#rpl_semi_sync_slave_enabled=1
####### slow log ######
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
####### binlog ######
binlog_format = row
server-id = 2123306
log-bin = /data/mysql/mysql3306/logs/mysql-bin
max_binlog_size = 256M
sync_binlog = 0
expire_logs_days = 10
#procedure
log_bin_trust_function_creators=1
####### relay log ######
skip_slave_start = 1
max_relay_log_size = 128M
relay_log_purge = 1
relay_log_recovery = 1
relay-log=relay-bin
relay-log-index = relay-bin.index
log_slave_updates = ON
#slave-skip-errors=1032,1053,1062
#skip-grant-tables
####### buffers & cache ######
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 128K
join_buffer_size = 128K
thread_cache_size = 200
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 100M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
6.初始化数据库
# cd /usr/local/mysql
# ./bin/mysqld --defaults-file=/etc/my.cnf --initialize
二、创建新旧节点主从关系
我们从 PXC 集群中先选择一个节点做 master，这里选择 db-node3 节点做为 master。
1.创建复制账号
在 db-node3 上创建复制账号
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'unixfbi';
2.备份数据
在 db-node3 上执行
# innobackupex --defaults-file=/etc/my.cnf --user=root --password=unixfbi /data/
3.拷贝备份文件
拷贝到 db-node4 上
# scp -r /data/2018-03-26_17-51-52 root@192.168.199.212:/data/
4.恢复备份
在 db-node4 上执行
# innobackupex --apply-log /data/2018-03-26_17-51-52/
# innobackupex --copy-back /data/2018-03-26_17-51-52/
# chown -R mysql.mysql /data/mysql/mysql3306/data/
启动数据库：
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &
5.创建主从关系
root@localhost:mysql3306.sock [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.199.131',
MASTER_USER='repl',
MASTER_PASSWORD='unixfbi',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
6.查看复制状态
root@localhost:mysql3306.sock [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 740
Relay_Log_File: relay-bin.000004
Relay_Log_Pos: 913
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 740
Relay_Log_Space: 1114
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1313306
Master_UUID: 72b23ebf-2b53-11e8-9413-2c27d72e9d73
Master_Info_File: /data/mysql/mysql3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 959932a2-d46a-ee17-6277-717d01a0abd6:12-13
Executed_Gtid_Set: 959932a2-d46a-ee17-6277-717d01a0abd6:1-13
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
可以看出复制是成功的。
三、开始添加新节点到 PXC 集群
1.停止复制并确认 binlog 位置
root@localhost:mysql3306.sock [(none)]> stop slave;
root@localhost:mysql3306.sock [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.199.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 740
Relay_Log_File: relay-bin.000004
Relay_Log_Pos: 913
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 740
Relay_Log_Space: 1114
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1313306
Master_UUID: 72b23ebf-2b53-11e8-9413-2c27d72e9d73
Master_Info_File: /data/mysql/mysql3306/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 959932a2-d46a-ee17-6277-717d01a0abd6:12-13
Executed_Gtid_Set: 959932a2-d46a-ee17-6277-717d01a0abd6:1-13
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
根据以上状态信息，可以知道已经执行到的日志坐标是：
Relay_Master_Log_File: mysql-bin.000007
Exec_Master_Log_Pos: 740
2.停止 Slave 实例
# pkill mysql
# ps aux|grep mysql
3.查找 Xid 信息
我们可以在 db-node3 节点上根据同步的日志文件位置查找 Xid，这个 xid 就是新节点在 gcache 中需要从什么位置开始同步数据：
# mysqlbinlog -v /data/mysql/mysql3306/logs/mysql-bin.000007 |grep 740|grep Xid
#180326 18:35:08 server id 1313306 end_log_pos 740 CRC32 0x6c6fc95e Xid = 13
可以看出，在主库 db-node3 上位置 740 对应的 Xid 为 13。
在 db-node3 节点上，查看这个 Xid 是否有效
root@localhost [(none)]>show status like '%wsrep_local_cached_downto%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| wsrep_local_cached_downto | 10 |
+---------------------------+-------+
1 row in set (0.00 sec)
发现是从 10 开始，说明 Xid=13 是有效的。
4.拷贝 grastate.dat 文件
把 db-node3 上的 grastate.dat 文件拷贝到 db-node4 节点上
我们先查看一下这个文件的内容：
# cat /data/mysql/mysql3306/data/grastate.dat
# GALERA saved state
version: 2.1
uuid: 6a66cd5d-2b95-11e8-9d88-8e82fe5f5429
seqno: -1
safe_to_bootstrap: 0
seqno: -1 表示这个节点已经在集群中。
把这个文件复制到 db-node4 节点的 datadir 目录下，记得修改文件用户属主：
# scp grastate.dat root@192.168.199.212:/data/mysql/mysql3306/data/
在 db-node4 上修改属主：
# chown mysql.mysql /data/mysql/mysql3306/data/grastate.dat
5.修改 grastate.dat 文件
修改新节点 db-node4 上/data/mysql/mysql3306/data/grastate.dat 文件中的 seqno 为我们前面找到的 Xid 值 13
6.新节点配置 PXC 参数
在新节点 db-node4 上/etc/my.cnf 文件中[mysqld]段下添加如下配置参数
# PXC
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
wsrep_cluster_name=pxc-cluster
wsrep_cluster_address=gcomm://192.168.199.230,192.168.199.231,192.168.199.131,192.168.199.212 #所有节点 IP
wsrep_node_name=pxc4
wsrep_node_address=192.168.199.212
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:unixfbi
pxc_strict_mode=ENFORCING
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_cluster_address 参数把新旧节点 IP 都配置上。
7.启动新节点
启动 db-node4 节点数据库实例
# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf &
8.查看新节点 PXC 状态
root@localhost:mysql3306.sock [(none)]> show status like 'wsrep%';
+----------------------------------+-------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------------------------------------------------+
| wsrep_local_state_uuid | 6a66cd5d-2b95-11e8-9d88-8e82fe5f5429 |
| wsrep_protocol_version | 8 |
| wsrep_last_applied | 14 |
| wsrep_last_committed | 14 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 3 |
| wsrep_received_bytes | 364 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 0 |
| wsrep_flow_control_paused_ns | 0 |