|
前言：如果我們有一個(gè)運(yùn)行了很久的MGR線上環(huán)境，現(xiàn)在要求新增加一個(gè)節(jié)點(diǎn)，該如何添加呢？ 區(qū)別于PXC的SST和IST，MGR是根據(jù)GTID來(lái)識(shí)別哪些事務(wù)已經(jīng)在本節(jié)點(diǎn)執(zhí)行過(guò)了，通過(guò)binlog來(lái)追平集群的事務(wù)，但是如果其他節(jié)點(diǎn)的binlog要是已經(jīng)刪除了怎么辦呢？同數(shù)據(jù)庫(kù)備份恢復(fù)一樣，我們通過(guò)兩種方式來(lái)添加新節(jié)點(diǎn) 一、mysqldump初始化新節(jié)點(diǎn) 備份恢復(fù)數(shù)據(jù)庫(kù) 安裝組復(fù)制插件 開(kāi)啟組復(fù)制
初始化新節(jié)點(diǎn)mysql初始化可參考之前的mysql標(biāo)準(zhǔn)化安裝，這里不過(guò)多敘述。 貼下配置文件： #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
備份數(shù)據(jù)庫(kù)/usr/local/mysql/bin/mysqldump -uroot -p -S /tmp/mysql3306.sock -A -B --master-data=2 --single-transaction >/tmp/all.sql
恢復(fù)數(shù)據(jù)庫(kù)登錄新節(jié)點(diǎn)后直接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)
安裝組復(fù)制插件change master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';install plugin group_replication soname 'group_replication.so';
開(kāi)啟組復(fù)制start group_replication;
查看組復(fù)制成員狀態(tài)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)
看到新加入的成員狀態(tài)也為ONLINE狀態(tài)了 查看日志狀態(tài)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的方式基本一致，唯一的不同就是備份恢復(fù)的方式不同。 備份innobackupex --defaults-file=/data/mgr/mysql3306/my3306.cnf --user=root --password=xuclxucl -S /tmp/mysql3306.sock /tmp/
恢復(fù)[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/
啟動(dòng)數(shù)據(jù)庫(kù)設(shè)置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)
啟動(dòng)group replicationchange master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';start group_replication;
查看成員狀態(tài)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)
|
