PXC集群用IST添加新节点

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(略)

  1. 创建相关目录
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
  1. 在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;
  1. 在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!
  1. 把全量备份和/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/
  1. 新节点上修改/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/
  1. 新到恢复出来的binlog和position
shell]# cat /data/mysql/mysql_3306/data/xtrabackup_binlog_pos_innodb
mysql-bin.000011    1694584
  1. 在备份的节点即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
  1. 把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
  1. 新节点上, 先把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清除主从复制信息

暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇