绕开SST通过IST方式添加Node到Percona XtraDB Cluster


参考资料:
https://github.com/percona/xtradb-cluster-tutorial/blob/master/instructions/Avoiding%20SST.rst#bad-configuration

服务器:
idc1-server1, idc1-server2, idc1-server3

1. 安装Percona XtraDB Cluster,在 idc1-server1,idc1-server2,idc1-server3 上
$ sudo yum install http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
$ sudo yum install http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ sudo yum install Percona-Server-shared-compat
$ sudo yum install Percona-XtraDB-Cluster-server Percona-XtraDB-Cluster-client
$ sudo mkdir -p /opt/mysql/{data,tmp,run,binlogs,log}
$ sudo chown mysql:mysql /opt/mysql/{data,tmp,run,binlogs,log}
$ sudo -i
# su - mysql
$ mysql_install_db --user=mysql --datadir=/opt/mysql/data/
$ exit
# exit

2. 配置 my.cnf,在 idc1-server1 上
[heydevops@idc1-server1 ~]$ sudo vim /etc/my.cnf

 
[mysqld]
# basic settings
datadir = /opt/mysql/data
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
pid-file = /opt/mysql/run/mysqld.pid

# innodb settings
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /opt/mysql/binlogs/bin-log-mysqld
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data

# xtradb cluster settings
binlog_format = ROW
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://10.100.1.3,10.100.1.4,10.100.1.5
wsrep_node_address = 10.100.1.3
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_sst_method = xtrabackup
wsrep_sst_auth = sst:secret
wsrep_provider_options = "gcache.size=8G;"
wsrep_sst_receive_address = 10.100.1.3
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2

# server id
server-id=123

# other settings
[mysqld_safe]
log-error = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306
socket = /opt/mysql/run/mysqld.sock
default-character-set = utf8


3. 配置 my.cnf,在 idc1-server2 上
[heydevops@idc1-server2 ~]$ sudo vim /etc/my.cnf

 
[mysqld]
# basic settings
datadir = /opt/mysql/data
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
pid-file = /opt/mysql/run/mysqld.pid

# innodb settings
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /opt/mysql/binlogs/bin-log-mysqld
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data

# xtradb cluster settings
binlog_format = ROW
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://10.100.1.3,10.100.1.4,10.100.1.5
wsrep_node_address = 10.100.1.4
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_sst_method = xtrabackup
wsrep_sst_auth = sst:secret
wsrep_provider_options ="gcache.size=8G;"
wsrep_sst_receive_address = 10.100.1.4
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2

# server id
server-id=124

# other settings
[mysqld_safe]
log-error = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306
socket = /opt/mysql/run/mysqld.sock
default-character-set = utf8

4. 配置 my.cnf,在 idc1-server3 上
[heydevops@idc1-server3 ~]$ sudo vim /etc/my.cnf

 
[mysqld]
# basic settings
datadir = /opt/mysql/data
tmpdir = /opt/mysql/tmp
socket = /opt/mysql/run/mysqld.sock
pid-file = /opt/mysql/run/mysqld.pid

# innodb settings
default-storage-engine = INNODB
innodb_file_per_table = 1
log-bin = /opt/mysql/binlogs/bin-log-mysqld
log-bin-index = /opt/mysql/binlogs/bin-log-mysqld.index
innodb_data_home_dir = /opt/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/data

# xtradb cluster settings
binlog_format = ROW
wsrep_cluster_name = mycluster
wsrep_cluster_address = gcomm://10.100.1.3,10.100.1.4,10.100.1.5
wsrep_node_address = 10.100.1.5
wsrep_provider = /usr/lib64/libgalera_smm.so
wsrep_sst_method = xtrabackup
wsrep_sst_auth = sst:secret
wsrep_provider_options = "gcache.size=8G;"
wsrep_sst_receive_address = 10.100.1.5
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2

# server id
server-id=125

# other settings
[mysqld_safe]
log-error = /opt/mysql/log/mysqld.log
pid-file = /opt/mysql/run/mysqld.pid
open-files-limit = 8192

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306
socket = /opt/mysql/run/mysqld.sock
default-character-set = utf8

5. 启动服务,在 idc1-server1,idc1-server2 上
[heydevops@idc1-server1 ~]$ sudo /etc/init.d/mysql bootstrap-pxc
[heydevops@idc1-server1 ~]$ mysql -uroot

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' IDENTIFIED BY 'secret';
mysql> GRANT ALL ON *.* to 'root'@'localhost' IDENTIFIED BY 'heylinux'
mysql> GRANT ALL ON *.* to 'root'@'127.0.0.1' IDENTIFIED BY 'heylinux'

[heydevops@idc1-server2 ~]$ sudo /etc/init.d/mysql start
[heydevops@idc1-server2 ~]$ mysql -uroot

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sst'@'localhost' IDENTIFIED BY 'secret';
mysql> GRANT ALL ON *.* to 'root'@'127.0.0.1' IDENTIFIED BY 'heylinux'
mysql> GRANT ALL ON *.* to 'root'@'127.0.0.1' IDENTIFIED BY 'heylinux'

6. 导入一些数据到Cluster中

7. 绕开SST通过IST方式添加idc1-server3到Cluster中
7.1 使用innobackupex备份当前数据库
[heydevops@idc1-server1 ~]$ sudo mkdir /opt/mysql/backups
[heydevops@idc1-server1 ~]$ sudo chown mysql:mysql /opt/mysql/backups
[heydevops@idc1-server1 ~]$ sudo -i
[heydevops@idc1-server1 ~]# /usr/bin/innobackupex /opt/mysql/backups/ --user=root --password=heylinux --galera-info
[heydevops@idc1-server1 ~]# /usr/bin/innobackupex --apply-log /opt/mysql/backups/2014-01-10_10-53-44/

[heydevops@idc1-server1 ~]# cd /opt/mysql/backups/2014-01-10_10-53-44/
[heydevops@idc1-server1 2014-01-10_10-53-44]# cat xtrabackup_galera_info
b0b66f5c-6254-11e3-b7c8-d2d41d32ec17:433411

[heydevops@idc1-server1 ~]# cd /opt/mysql/data
[heydevops@idc1-server1 data]# tar czf 2014-01-10_10-53-44.tgz 2014-01-10_10-53-44/
[heydevops@idc1-server1 data]# scp 2014-01-10_10-53-44.tgz idc1-server3:/home/heydevops/

7.2 再导入一些新数据到Cluster中

7.3 在idc1-server3上导入备份
[heydevops@idc1-server3 ~]$ cd /opt/mysql/data/
[heydevops@idc1-server3 data]$ sudo rm -rf *
[heydevops@idc1-server3 data]$ sudo mkdir /data
[heydevops@idc1-server3 data]$ cd /data
[heydevops@idc1-server3 data]$ sudo tar xzf /home/heydevops/2014-01-10_10-53-44.tgz
[heydevops@idc1-server3 data]$ sudo innobackupex --copy-back --defaults-file=/etc/my.cnf --no-timestamp /data/2014-01-10_10-53-44/
[heydevops@idc1-server3 data]$ cd /opt/mysql/data/
[heydevops@idc1-server3 data]$ sudo cat xtrabackup_galera_info
b0b66f5c-6254-11e3-b7c8-d2d41d32ec17:433411

7.4 创建grastate.dat文件
[heydevops@idc1-server3 data]$ sudo vim grastate.dat
# GALERA saved state
version: 2.1
uuid: b0b66f5c-6254-11e3-b7c8-d2d41d32ec17
seqno: 433411
cert_index:
[heydevops@idc1-server3 data]$ sudo chown mysql:mysql -R *

7.5 启动服务,应该在很短的时间内就可以完成同步
[heydevops@idc1-server3 ~]$ sudo /etc/init.d/mysql start
Starting MySQL (Percona XtraDB Cluster)....SST in progress, setting sleep higher
. [ OK ]

7.6 检查进程,可以看到它正在从指定的Position开始做同步
[heydevops@idc1-server3 ~]$ ps aux | grep mysql | grep wsrep_start_position
mysql 4367 0.2 2.0 595132 34548 pts/0 Sl+ 12:25 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/opt/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/opt/mysql/log/mysqld.log --open-files-limit=8192 --pid-file=/opt/mysql/run/mysqld.pid --socket=/opt/mysql/run/mysqld.sock --port=3306 --wsrep_start_position=b0b66f5c-6254-11e3-b7c8-d2d41d32ec17:433411

7.7 查看日志,可以看到它实际上并没有做SST,而是通过IST同步了增量的部分
[heydevops@idc1-server3 ~]$ tailf /opt/mysql/log/mysqld.log

140113 13:01:51 Percona XtraDB (http://www.percona.com) 5.5.34-rel32.0 started; log sequence number 1687074217
140113 13:01:51 [Note] Event Scheduler: Loaded 0 events
140113 13:01:51 [Note] WSREP: Signalling provider to continue.
140113 13:01:51 [Note] WSREP: SST received: b0b66f5c-6254-11e3-b7c8-d2d41d32ec17:433411
140113 13:01:51 [Note] WSREP: Receiving IST: 207 writesets, seqnos 433411-433618
140113 13:01:51 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.34-55-log'  socket: '/opt/mysql/run/mysqld.sock'  port: 3306  Percona XtraDB Cluster (GPL), wsrep_25.9.r3928
140113 13:01:52 [Note] WSREP: 1 (idc1-server1): State transfer to 0 (idc1-server3) complete.
140113 13:01:52 [Note] WSREP: IST received: b0b66f5c-6254-11e3-b7c8-d2d41d32ec17:433618
140113 13:01:52 [Note] WSREP: 0 (idc1-server3): State transfer from 1 (idc1-server1) complete.
140113 13:01:52 [Note] WSREP: Shifting JOINER -> JOINED (TO: 433618)
140113 13:01:52 [Note] WSREP: Member 1 (idc1-server1) synced with group.
140113 13:01:52 [Note] WSREP: Member 0 (idc1-server3) synced with group.
140113 13:01:52 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 433618)
140113 13:01:52 [Note] WSREP: Synchronized with group, ready for connections
140113 13:01:52 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

, , , ,

  1. #1 by holyzhou on 2014/01/14 - 15:50

    又更新了 好勤快啊 彩总。先留名 ,晚上仔细拜读下

    • #2 by mcsrainbow on 2014/01/14 - 17:04

      实践下呗,也体验下。

(will not be published)
*