|
前言：如果我们有一个运行了很久的MGR线上环境，现在要求新增加一个节点，该如何添加呢？ 区别于PXC的SST和IST，MGR是根据GTID来识别哪些事务已经在本节点执行过了，通过binlog来追平集群的事务，但是如果其他节点的binlog要是已经删除了怎么办呢？同数据库备份恢复一样，我们通过两种方式来添加新节点 一、mysqldump初始化新节点 备份恢复数据库 安装组复制插件 开启组复制
初始化新节点mysql初始化可参考之前的mysql标准化安装，这里不过多叙述。 贴下配置文件： #my.cnf[client]port = 3307socket = /tmp/mysql3307.sock[mysql]prompt='\\u@\\h:\\p [\\d]>#pager='less -i -n -S'#tee=/home/mysql/query.logno-auto-rehash[mysqld]#miscuser = mysqlbasedir = /usr/local/mysqldatadir = /data/mgr/mysql3307/dataport = 3307socket = /tmp/mysql3307.sockevent_scheduler = 0tmpdir=/data/mgr/mysql3307/tmp#timeoutinteractive_timeout = 300wait_timeout = 300#character setcharacter-set-server = utf8open_files_limit = 65535max_connections = 100max_connect_errors = 100000#explicit_defaults_for_timestamp#logslog-output=fileslow_query_log = 1slow_query_log_file = slow.loglog-error = error.loglog_error_verbosity=3pid-file = mysql.pidlong_query_time = 1#log-slow-admin-statements = 1#log-queries-not-using-indexes = 1log-slow-slave-statements = 1#binlogbinlog_format = rowlog-bin = /data/mgr/mysql3307/logs/mysql-binbinlog_cache_size = 1Mmax_binlog_size = 200Mmax_binlog_cache_size = 2Gsync_binlog = 0expire_logs_days = 10#group replicationserver_id=1013307gtid_mode=ONenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLEbinlog_checksum=NONElog_slave_updates=ONbinlog_format=ROWtransaction_write_set_extraction=XXHASH64loose-group_replication_group_name='3db33b36-0e51-409f-a61d-c99756e90155'loose-group_replication_start_on_boot=offloose-group_replication_local_address= '172.28.128.103:23307'loose-group_replication_group_seeds= '172.28.128.101:23306,172.28.128.102:23306,172.28.128.103:23306,172.28.128.103:23307'loose-group_replication_bootstrap_group= offloose-group_replication_single_primary_mode=offloose-group_replication_enforce_update_everywhere_checks=on#relay logskip_slave_start = 1max_relay_log_size = 500Mrelay_log_purge = 1relay_log_recovery = 1#slave-skip-errors=1032,1053,1062#buffers & cachetable_open_cache = 2048table_definition_cache = 2048table_open_cache = 2048max_heap_table_size = 96Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 256query_cache_size = 0query_cache_type = 0query_cache_limit = 256Kquery_cache_min_res_unit = 512thread_stack = 192Ktmp_table_size = 96Mkey_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 32M#myisammyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1#innodbinnodb_buffer_pool_size = 100Minnodb_buffer_pool_instances = 1innodb_data_file_path = ibdata1:100M:autoextendinnodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 64Minnodb_log_file_size = 256Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_file_per_table = 1innodb_rollback_on_timeoutinnodb_status_file = 1innodb_io_capacity = 2000transaction_isolation = READ-COMMITTEDinnodb_flush_method = O_DIRECT
备份数据库/usr/local/mysql/bin/mysqldump -uroot -p -S /tmp/mysql3306.sock -A -B --master-data=2 --single-transaction >/tmp/all.sql
恢复数据库登录新节点后直接source /tmp/all.sql
查看master status mysql> show master status;+------------------+----------+--------------+------------------+--------------------------------------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------+| mysql-bin.000002 | 506 | | | 3db33b36-0e51-409f-a61d-c99756e90155:1-9:1000004-1000012:2000004-2000007 |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------+1 row in set (0,00 sec)
安装组复制插件change master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';install plugin group_replication soname 'group_replication.so';
开启组复制start group_replication;
查看组复制成员状态mysql> select * from replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 948335e8-64ad-11e8-86e7-080027de0e0e | zst1 | 3306 | ONLINE || group_replication_applier | a301269a-6617-11e8-bdce-080027de0e0e | zst3 | 3307 | ONLINE || group_replication_applier | c516f6d9-64af-11e8-9952-080027de0e0e | zst2 | 3306 | ONLINE || group_replication_applier | c8bbdc5f-64af-11e8-b469-080027de0e0e | zst3 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+4 rows in set (0,00 sec)
看到新加入的成员状态也为ONLINE状态了 查看日志状态2018-06-04T01:15:48.613456Z 3 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: 'DISABLED''2018-06-04T01:15:48.613549Z 3 [Note] Plugin group_replication reported: '[GCS] Added automatically IP ranges 10.0.2.15/24,127.0.0.1/8,172.28.128.103/24 to the whitelist'2018-06-04T01:15:48.613633Z 3 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'2018-06-04T01:15:48.613644Z 3 [Note] Plugin group_replication reported: 'Initialized group communication with configuration: group_replication_group_name: '3db33b36-0e51-409f-a61d-c99756e90155'; group_replication_local_address: '172.28.128.103:23307'; group_replication_group_seeds: '172.28.128.101:23306,172.28.128.102:23306,172.28.128.103:23306,172.28.128.103:23307'; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: 'AUTOMATIC''2018-06-04T01:15:48.613665Z 3 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1013307; member_uuid: 'a301269a-6617-11e8-bdce-080027de0e0e'; single-primary mode: 'false'; group_replication_auto_increment_increment: 7; '2018-06-04T01:15:48.619470Z 5 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.2018-06-04T01:15:48.627194Z 8 [Note] Slave SQL thread for channel 'group_replication_applier' initialized, starting replication in log 'FIRST' at position 0, relay log './zst3-relay-bin-group_replication_applier.000001' position: 42018-06-04T01:15:48.627374Z 3 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'2018-06-04T01:15:48.627384Z 3 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'2018-06-04T01:15:48.627386Z 3 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1013307'2018-06-04T01:15:48.627448Z 0 [Note] Plugin group_replication reported: 'state 0 action xa_init'2018-06-04T01:15:48.646816Z 0 [Note] Plugin group_replication reported: 'Successfully bound to 0.0.0.0:23307 (socket=56).'2018-06-04T01:15:48.646910Z 0 [Note] Plugin group_replication reported: 'Successfully set listen backlog to 32 (socket=56)!'2018-06-04T01:15:48.646955Z 0 [Note] Plugin group_replication reported: 'Successfully unblocked socket (socket=56)!'2018-06-04T01:15:48.647046Z 0 [Note] Plugin group_replication reported: 'Ready to accept incoming connections on 0.0.0.0:23307 (socket=56)!'2018-06-04T01:15:48.647451Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'2018-06-04T01:15:48.647916Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 57'2018-06-04T01:15:48.648117Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'2018-06-04T01:15:48.648145Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 59'2018-06-04T01:15:48.648204Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'2018-06-04T01:15:48.648223Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 61'2018-06-04T01:15:48.648266Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'2018-06-04T01:15:48.648347Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 63'2018-06-04T01:15:48.648453Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'2018-06-04T01:15:48.648486Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 65'2018-06-04T01:15:48.648597Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'2018-06-04T01:15:48.648696Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 67'2018-06-04T01:15:48.649017Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.101 23306'2018-06-04T01:15:48.649203Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.101 23306 fd 54'2018-06-04T01:15:49.653473Z 0 [Note] Plugin group_replication reported: 'state 4337 action xa_snapshot'2018-06-04T01:15:49.653660Z 0 [Note] Plugin group_replication reported: 'new state x_recover'2018-06-04T01:15:49.653664Z 0 [Note] Plugin group_replication reported: 'state 4357 action xa_complete'2018-06-04T01:15:49.653726Z 0 [Note] Plugin group_replication reported: 'new state x_run'2018-06-04T01:15:50.660480Z 11 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'2018-06-04T01:15:50.660530Z 0 [Note] Plugin group_replication reported: 'Group membership changed to zst1:3306, zst3:3307, zst2:3306, zst3:3306 on view 15277565398765121:6.'2018-06-04T01:15:50.672628Z 11 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='zst1', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.2018-06-04T01:15:50.679705Z 11 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 948335e8-64ad-11e8-86e7-080027de0e0e at zst1 port: 3306.'2018-06-04T01:15:50.679832Z 12 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.2018-06-04T01:15:50.680198Z 13 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './zst3-relay-bin-group_replication_recovery.000001' position: 42018-06-04T01:15:50.693479Z 12 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@zst1:3306',replication started in log 'FIRST' at position 42018-06-04T01:15:50.704190Z 11 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'2018-06-04T01:15:50.704220Z 12 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'2018-06-04T01:15:50.704220Z 12 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000002', position 51712018-06-04T01:15:50.707972Z 11 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='zst1', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.2018-06-04T01:15:50.715974Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'2018-06-04T01:21:26.572208Z 3 [Note] Aborted connection 3 to db: 'performance_schema' user: 'root' host: 'localhost' (Got timeout reading communication packets)
二、Xtrabackupxtrabackup的方式与mysqldump的方式基本一致，唯一的不同就是备份恢复的方式不同。 备份innobackupex --defaults-file=/data/mgr/mysql3306/my3306.cnf --user=root --password=xuclxucl -S /tmp/mysql3306.sock /tmp/
恢复[root@zst3 tmp]# innobackupex --defaults-file=/data/mgr/mysql3307/my3307.cnf --apply-log /tmp/2018-06-04_03-57-26/
[root@zst3 tmp]# innobackupex --defaults-file=/data/mgr/mysql3307/my3307.cnf --copy-back /tmp/2018-06-04_03-57-26/
启动数据库设置gtid_purgedmysql> set global gtid_purged='3db33b36-0e51-409f-a61d-c99756e90155:1-9:1000004-1000012:2000004-2000007';Query OK, 0 rows affected (0,00 sec)
mysql> show master status;+------------------+----------+--------------+------------------+--------------------------------------------------------------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------+| mysql-bin.000001 | 150 | | | 3db33b36-0e51-409f-a61d-c99756e90155:1-9:1000004-1000012:2000004-2000007 |+------------------+----------+--------------+------------------+--------------------------------------------------------------------------+1 row in set (0,00 sec)
启动group replicationchange master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';start group_replication;
查看成员状态mysql> select * from replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+--------------+| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |+---------------------------+--------------------------------------+-------------+-------------+--------------+| group_replication_applier | 59f7ff4d-679c-11e8-9be4-080027de0e0e | zst3 | 3307 | ONLINE || group_replication_applier | 948335e8-64ad-11e8-86e7-080027de0e0e | zst1 | 3306 | ONLINE || group_replication_applier | c516f6d9-64af-11e8-9952-080027de0e0e | zst2 | 3306 | ONLINE || group_replication_applier | c8bbdc5f-64af-11e8-b469-080027de0e0e | zst3 | 3306 | ONLINE |+---------------------------+--------------------------------------+-------------+-------------+--------------+4 rows in set (0,00 sec)
|
