前言：如果我们有一个运行了很久的MGR线上环境，现在要求新增加一个节点，该如何添加呢？
区别于PXC的SST和IST，MGR是根据GTID来识别哪些事务已经在本节点执行过了，通过binlog来追平集群的事务，但是如果其他节点的binlog要是已经删除了怎么办呢？同数据库备份恢复一样，我们通过两种方式来添加新节点

一、mysqldump

  • 初始化新节点

  • 备份恢复数据库

  • 安装组复制插件

  • 开启组复制

初始化新节点

mysql初始化可参考之前的mysql标准化安装，这里不过多叙述。
贴下配置文件：

  1. #my.cnf
  2. [client]
  3. port            = 3307
  4. socket          = /tmp/mysql3307.sock
  6. [mysql]
  7. prompt="\\u@\\h:\\p  [\\d]>
  8. #pager="less -i -n -S"
  9. #tee=/home/mysql/query.log
  10. no-auto-rehash
  12. [mysqld]
  13. #misc
  14. user = mysql
  15. basedir = /usr/local/mysql
  16. datadir = /data/mgr/mysql3307/data
  17. port = 3307
  18. socket = /tmp/mysql3307.sock
  19. event_scheduler = 0
  21. tmpdir=/data/mgr/mysql3307/tmp
  22. #timeout
  23. interactive_timeout = 300
  24. wait_timeout = 300
  26. #character set
  27. character-set-server = utf8
  29. open_files_limit = 65535
  30. max_connections = 100
  31. max_connect_errors = 100000
  32. #
  33. explicit_defaults_for_timestamp
  34. #logs
  35. log-output=file
  36. slow_query_log = 1
  37. slow_query_log_file = slow.log
  38. log-error = error.log
  39. log_error_verbosity=3
  40. pid-file = mysql.pid
  41. long_query_time = 1
  42. #log-slow-admin-statements = 1
  43. #log-queries-not-using-indexes = 1
  44. log-slow-slave-statements = 1
  46. #binlog
  47. binlog_format = row
  48. log-bin = /data/mgr/mysql3307/logs/mysql-bin
  49. binlog_cache_size = 1M
  50. max_binlog_size = 200M
  51. max_binlog_cache_size = 2G
  52. sync_binlog = 0
  53. expire_logs_days = 10
  54. #group replication
  55. server_id=1013307
  56. gtid_mode=ON
  57. enforce_gtid_consistency=ON
  58. master_info_repository=TABLE
  59. relay_log_info_repository=TABLE
  60. binlog_checksum=NONE
  61. log_slave_updates=ON
  62. binlog_format=ROW
  64. transaction_write_set_extraction=XXHASH64
  65. loose-group_replication_group_name="3db33b36-0e51-409f-a61d-c99756e90155"
  66. loose-group_replication_start_on_boot=off
  67. loose-group_replication_local_address= "172.28.128.103:23307"
  68. 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"
  69. loose-group_replication_bootstrap_group= off
  71. loose-group_replication_single_primary_mode=off
  72. loose-group_replication_enforce_update_everywhere_checks=on
  74. #relay log
  75. skip_slave_start = 1
  76. max_relay_log_size = 500M
  77. relay_log_purge = 1
  78. relay_log_recovery = 1
  79. #slave-skip-errors=1032,1053,1062
  81. #buffers & cache
  82. table_open_cache = 2048
  83. table_definition_cache = 2048
  84. table_open_cache = 2048
  85. max_heap_table_size = 96M
  86. sort_buffer_size = 2M
  87. join_buffer_size = 2M
  88. thread_cache_size = 256
  89. query_cache_size = 0
  90. query_cache_type = 0
  91. query_cache_limit = 256K
  92. query_cache_min_res_unit = 512
  93. thread_stack = 192K
  94. tmp_table_size = 96M
  95. key_buffer_size = 8M
  96. read_buffer_size = 2M
  97. read_rnd_buffer_size = 16M
  98. bulk_insert_buffer_size = 32M
  100. #myisam
  101. myisam_sort_buffer_size = 128M
  102. myisam_max_sort_file_size = 10G
  103. myisam_repair_threads = 1
  105. #innodb
  106. innodb_buffer_pool_size = 100M
  107. innodb_buffer_pool_instances = 1
  108. innodb_data_file_path = ibdata1:100M:autoextend
  109. innodb_flush_log_at_trx_commit = 2
  110. innodb_log_buffer_size = 64M
  111. innodb_log_file_size = 256M
  112. innodb_log_files_in_group = 3
  113. innodb_max_dirty_pages_pct = 90
  114. innodb_file_per_table = 1
  115. innodb_rollback_on_timeout
  116. innodb_status_file = 1
  117. innodb_io_capacity = 2000
  118. transaction_isolation = READ-COMMITTED
  119. innodb_flush_method = O_DIRECT

备份数据库

  1. /usr/local/mysql/bin/mysqldump -uroot -p -S /tmp/mysql3306.sock -A -B --master-data=2 --single-transaction >/tmp/all.sql

恢复数据库

  1. 登录新节点后直接
  2. source /tmp/all.sql

查看master status

  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+--------------------------------------------------------------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                        |
  4. +------------------+----------+--------------+------------------+--------------------------------------------------------------------------+
  5. | mysql-bin.000002 |      506 |              |                  | 3db33b36-0e51-409f-a61d-c99756e90155:1-9:1000004-1000012:2000004-2000007 |
  6. +------------------+----------+--------------+------------------+--------------------------------------------------------------------------+
  7. 1 row in set (0,00 sec)

安装组复制插件

  1. change master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';
  2. install plugin group_replication soname 'group_replication.so';

开启组复制

  1. start group_replication;

查看组复制成员状态

  1. mysql> select * from replication_group_members;
  2. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  3. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
  4. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  5. | group_replication_applier | 948335e8-64ad-11e8-86e7-080027de0e0e | zst1        |        3306 | ONLINE       |
  6. | group_replication_applier | a301269a-6617-11e8-bdce-080027de0e0e | zst3        |        3307 | ONLINE       |
  7. | group_replication_applier | c516f6d9-64af-11e8-9952-080027de0e0e | zst2        |        3306 | ONLINE       |
  8. | group_replication_applier | c8bbdc5f-64af-11e8-b469-080027de0e0e | zst3        |        3306 | ONLINE       |
  9. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  10. 4 rows in set (0,00 sec)

看到新加入的成员状态也为ONLINE状态了

查看日志状态

  1. 2018-06-04T01:15:48.613456Z 3 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
  2. 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'
  3. 2018-06-04T01:15:48.613633Z 3 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
  4. 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"'
  5. 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; '
  6. 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='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
  7. 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: 4
  8. 2018-06-04T01:15:48.627374Z 3 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
  9. 2018-06-04T01:15:48.627384Z 3 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
  10. 2018-06-04T01:15:48.627386Z 3 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1013307'
  11. 2018-06-04T01:15:48.627448Z 0 [Note] Plugin group_replication reported: 'state 0 action xa_init'
  12. 2018-06-04T01:15:48.646816Z 0 [Note] Plugin group_replication reported: 'Successfully bound to 0.0.0.0:23307 (socket=56).'
  13. 2018-06-04T01:15:48.646910Z 0 [Note] Plugin group_replication reported: 'Successfully set listen backlog to 32 (socket=56)!'
  14. 2018-06-04T01:15:48.646955Z 0 [Note] Plugin group_replication reported: 'Successfully unblocked socket (socket=56)!'
  15. 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)!'
  16. 2018-06-04T01:15:48.647451Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'
  17. 2018-06-04T01:15:48.647916Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 57'
  18. 2018-06-04T01:15:48.648117Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'
  19. 2018-06-04T01:15:48.648145Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 59'
  20. 2018-06-04T01:15:48.648204Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'
  21. 2018-06-04T01:15:48.648223Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 61'
  22. 2018-06-04T01:15:48.648266Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'
  23. 2018-06-04T01:15:48.648347Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 63'
  24. 2018-06-04T01:15:48.648453Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'
  25. 2018-06-04T01:15:48.648486Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 65'
  26. 2018-06-04T01:15:48.648597Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23307'
  27. 2018-06-04T01:15:48.648696Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23307 fd 67'
  28. 2018-06-04T01:15:48.649017Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.101 23306'
  29. 2018-06-04T01:15:48.649203Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.101 23306 fd 54'
  30. 2018-06-04T01:15:49.653473Z 0 [Note] Plugin group_replication reported: 'state 4337 action xa_snapshot'
  31. 2018-06-04T01:15:49.653660Z 0 [Note] Plugin group_replication reported: 'new state x_recover'
  32. 2018-06-04T01:15:49.653664Z 0 [Note] Plugin group_replication reported: 'state 4357 action xa_complete'
  33. 2018-06-04T01:15:49.653726Z 0 [Note] Plugin group_replication reported: 'new state x_run'
  34. 2018-06-04T01:15:50.660480Z 11 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
  35. 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.'
  36. 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=''.
  37. 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.'
  38. 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.
  39. 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: 4
  40. 2018-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 4
  41. 2018-06-04T01:15:50.704190Z 11 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
  42. 2018-06-04T01:15:50.704220Z 12 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
  43. 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 5171
  44. 2018-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='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
  45. 2018-06-04T01:15:50.715974Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'
  46. 2018-06-04T01:21:26.572208Z 3 [Note] Aborted connection 3 to db: 'performance_schema' user: 'root' host: 'localhost' (Got timeout reading communication packets)

二、Xtrabackup

xtrabackup的方式与mysqldump的方式基本一致，唯一的不同就是备份恢复的方式不同。

备份

  1. innobackupex --defaults-file=/data/mgr/mysql3306/my3306.cnf --user=root --password=xuclxucl -S /tmp/mysql3306.sock  /tmp/

恢复

  1. [root@zst3 tmp]# innobackupex --defaults-file=/data/mgr/mysql3307/my3307.cnf --apply-log /tmp/2018-06-04_03-57-26/
  1. [root@zst3 tmp]# innobackupex --defaults-file=/data/mgr/mysql3307/my3307.cnf --copy-back /tmp/2018-06-04_03-57-26/

启动数据库设置gtid_purged

  1. mysql> set global gtid_purged='3db33b36-0e51-409f-a61d-c99756e90155:1-9:1000004-1000012:2000004-2000007';
  2. Query OK, 0 rows affected (0,00 sec)
  1. mysql> show master status;
  2. +------------------+----------+--------------+------------------+--------------------------------------------------------------------------+
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                        |
  4. +------------------+----------+--------------+------------------+--------------------------------------------------------------------------+
  5. | mysql-bin.000001 |      150 |              |                  | 3db33b36-0e51-409f-a61d-c99756e90155:1-9:1000004-1000012:2000004-2000007 |
  6. +------------------+----------+--------------+------------------+--------------------------------------------------------------------------+
  7. 1 row in set (0,00 sec)

启动group replication

  1. change master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';
  2. start group_replication;

查看成员状态

  1. mysql>  select * from replication_group_members;
  2. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  3. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
  4. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  5. | group_replication_applier | 59f7ff4d-679c-11e8-9be4-080027de0e0e | zst3        |        3307 | ONLINE       |
  6. | group_replication_applier | 948335e8-64ad-11e8-86e7-080027de0e0e | zst1        |        3306 | ONLINE       |
  7. | group_replication_applier | c516f6d9-64af-11e8-9952-080027de0e0e | zst2        |        3306 | ONLINE       |
  8. | group_replication_applier | c8bbdc5f-64af-11e8-b469-080027de0e0e | zst3        |        3306 | ONLINE       |
  9. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  10. 4 rows in set (0,00 sec)