前言：最近出去玩了一段时间，博客很久没有更新了，感觉有点荒废了，接下来会给大家带来一系列的MGR相关的文章，欢迎大家阅读指点。

一、安装MySQL

标准化安装MySQL，这里就不多说了，可以参考本人之前的文章，注意一下参数文件这里有些不一样了。

  1. #my.cnf
  2. [client]
  3. port            = 3306
  4. socket          = /tmp/mysql3306.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/mysql3306/data
  17. port = 3306
  18. socket = /tmp/mysql3306.sock
  19. event_scheduler = 0
  21. tmpdir=/data/mgr/mysql3306/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/mysql3306/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=1013306
  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.102:23306"
  68. loose-group_replication_group_seeds= "172.28.128.101:23306,172.28.128.102:23306,172.28.128.103:23306"
  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

这里尤其需要注意的是group replication部分的参数

  • server_id=1013306

    设置每个节点的serverid

  • gtid_mode=ON

    开启gtid模式

  • enforce_gtid_consistency=ON

    强制gtid复制一致性

  • master_info_repository=TABLE

    复制信息保存在innodb表里

  • relay_log_info_repository=TABLE

  • binlog_checksum=NONE

    mgr不支持binlog_checksum

  • log_slave_updates=ON

    开启slave复制记录binlog

  • binlog_format=ROW

    binlog模式设置为row

  • loose-group_replication_group_name=”3db33b36-0e51-409f-a61d-c99756e90155”

    初始mgr group name

  • loose-group_replication_start_on_boot=off

    实例启动时开启group replication

  • loose-group_replication_local_address= “172.28.128.102:23306”

    本地group replication地址

  • loose-group_replication_group_seeds= “172.28.128.101:23306,172.28.128.102:23306,172.28.128.103:23306”

    设置group_seeds

  • loose-group_replication_bootstrap_group= off

    只在第一个节点设置，集群启动后需要将该参数关闭

  • loose-group_replication_single_primary_mode=off #开启多主模式

二、初始化第一个节点

  1. /usr/local/mysql/bin/mysqld --defaults-file=/data/mgr/mysql3306/my3306.cnf --user=mysql &
  1. set sql_log_bin=0;
  2. alter user user() identified by 'xuclxucl';
  3. create user 'repl'@'%' identified by 'repl4slave';
  4. grant replication slave on *.* to 'repl'@'%';
  5. set sql_log_bin=1;
  6. change master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';
  7. install plugin group_replication soname 'group_replication.so';
  8. set global group_replication_bootstrap_group=on;
  9. start group_replication;

这里要注意的是group replication的channel name是固定的

查看集群状态

  1. root@localhost [performance_schema]>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. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  7. 1 row in set (0.00 sec)

可以看到节点1已经正常

三、初始化节点2、节点3

节点2、节点3初始化步骤跟节点1初始化步骤唯一的区别就是不需要把group_replication_bootstrap_group设置为on

  1. #第二个节点初始化
  2. set sql_log_bin=0;
  3. alter user user() identified by 'xuclxucl';
  4. create user 'repl'@'%' identified by 'repl4slave';
  5. grant replication slave on *.* to 'repl'@'%';
  6. set sql_log_bin=1;
  7. change master to master_user='repl',master_password='repl4slave' for channel 'group_replication_recovery';
  8. install plugin group_replication soname 'group_replication.so';
  9. start group_replication;
  10. select * from p_s.replication_group_members;

初始化后查看集群状态

  1. root@localhost [performance_schema]>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 | c516f6d9-64af-11e8-9952-080027de0e0e | zst2        |        3306 | ONLINE       |
  7. | group_replication_applier | c8bbdc5f-64af-11e8-b469-080027de0e0e | zst3        |        3306 | ONLINE       |
  8. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  9. 3 rows in set (0.00 sec)

可以看到节点2节点3成功加入到集群当中
我们看下节点3的日志

  1. 2018-05-31T09:25:20.056511Z 4 [Note] Plugin group_replication reported: 'Group communication SSL configuration: group_replication_ssl_mode: "DISABLED"'
  2. 2018-05-31T09:25:20.056546Z 4 [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-05-31T09:25:20.056546Z 4 [Note] Plugin group_replication reported: '[GCS] SSL was not enabled'
  4. 2018-05-31T09:25:20.056546Z 4 [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:23306"; group_replication_group_seeds: "172.28.128.101:23306,172.28.128.102:23306,172.28.128.103:23306"; group_replication_bootstrap_group: false; group_replication_poll_spin_loops: 0; group_replication_compression_threshold: 1000000; group_replication_ip_whitelist: "AUTOMATIC"'
  5. 2018-05-31T09:25:20.056546Z 4 [Note] Plugin group_replication reported: 'Member configuration: member_id: 1013306; member_uuid: "c8bbdc5f-64af-11e8-b469-080027de0e0e"; single-primary mode: "false"; group_replication_auto_increment_increment: 7; '
  6. 2018-05-31T09:25:20.060811Z 6 [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-05-31T09:25:20.068823Z 9 [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-05-31T09:25:20.069006Z 4 [Note] Plugin group_replication reported: 'Group Replication applier module successfully initialized!'
  9. 2018-05-31T09:25:20.069016Z 4 [Note] Plugin group_replication reported: 'auto_increment_increment is set to 7'
  10. 2018-05-31T09:25:20.069018Z 4 [Note] Plugin group_replication reported: 'auto_increment_offset is set to 1013306'
  11. 2018-05-31T09:25:20.069077Z 0 [Note] Plugin group_replication reported: 'state 0 action xa_init'
  12. 2018-05-31T09:25:20.094279Z 0 [Note] Plugin group_replication reported: 'Successfully bound to 0.0.0.0:23306 (socket=63).'
  13. 2018-05-31T09:25:20.094279Z 0 [Note] Plugin group_replication reported: 'Successfully set listen backlog to 32 (socket=63)!'
  14. 2018-05-31T09:25:20.094279Z 0 [Note] Plugin group_replication reported: 'Successfully unblocked socket (socket=63)!'
  15. 2018-05-31T09:25:20.094291Z 0 [Note] Plugin group_replication reported: 'Ready to accept incoming connections on 0.0.0.0:23306 (socket=63)!'
  16. 2018-05-31T09:25:20.094336Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23306'
  17. 2018-05-31T09:25:20.094432Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23306 fd 64'
  18. 2018-05-31T09:25:20.094537Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23306'
  19. 2018-05-31T09:25:20.094558Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23306 fd 66'
  20. 2018-05-31T09:25:20.094759Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23306'
  21. 2018-05-31T09:25:20.094784Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23306 fd 61'
  22. 2018-05-31T09:25:20.094841Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23306'
  23. 2018-05-31T09:25:20.094858Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23306 fd 69'
  24. 2018-05-31T09:25:20.094912Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23306'
  25. 2018-05-31T09:25:20.094929Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23306 fd 71'
  26. 2018-05-31T09:25:20.094981Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.103 23306'
  27. 2018-05-31T09:25:20.094998Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.103 23306 fd 73'
  28. 2018-05-31T09:25:20.095053Z 0 [Note] Plugin group_replication reported: 'connecting to 172.28.128.101 23306'
  29. 2018-05-31T09:25:20.096464Z 0 [Note] Plugin group_replication reported: 'client connected to 172.28.128.101 23306 fd 75'
  30. 2018-05-31T09:25:21.102554Z 0 [Note] Plugin group_replication reported: 'state 4337 action xa_snapshot'
  31. 2018-05-31T09:25:21.102792Z 0 [Note] Plugin group_replication reported: 'new state x_recover'
  32. 2018-05-31T09:25:21.102797Z 0 [Note] Plugin group_replication reported: 'state 4357 action xa_complete'
  33. 2018-05-31T09:25:21.102861Z 0 [Note] Plugin group_replication reported: 'new state x_run'
  34. 2018-05-31T09:25:22.110292Z 12 [Note] Plugin group_replication reported: 'Establishing group recovery connection with a possible donor. Attempt 1/10'
  35. 2018-05-31T09:25:22.110978Z 0 [Note] Plugin group_replication reported: 'Group membership changed to zst1:3306, zst2:3306, zst3:3306 on view 15277565398765121:5.'
  36. 2018-05-31T09:25:22.117168Z 12 [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='zst2', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
  37. 2018-05-31T09:25:22.128087Z 12 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor c516f6d9-64af-11e8-9952-080027de0e0e at zst2 port: 3306.'
  38. 2018-05-31T09:25:22.128251Z 13 [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-05-31T09:25:22.128631Z 14 [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-05-31T09:25:22.129166Z 13 [Note] Slave I/O thread for channel 'group_replication_recovery': connected to master 'repl@zst2:3306',replication started in log 'FIRST' at position 4
  41. 2018-05-31T09:25:22.146993Z 12 [Note] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.'
  42. 2018-05-31T09:25:22.147900Z 13 [Note] Slave I/O thread killed while reading event for channel 'group_replication_recovery'
  43. 2018-05-31T09:25:22.147912Z 13 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000004', position 1406
  44. 2018-05-31T09:25:22.153890Z 12 [Note] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='zst2', 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-05-31T09:25:22.161932Z 0 [Note] Plugin group_replication reported: 'This server was declared online within the replication group'

四、集群测试

node1：

  1. root@localhost [performance_schema]>create database xucl;
  2. Query OK, 1 row affected (0.01 sec)
  4. root@localhost [performance_schema]>use xucl;
  5. Database changed
  6. root@localhost [xucl]>create table t(id int primary key);
  7. Query OK, 0 rows affected (0.01 sec)
  9. root@localhost [xucl]>insert into t values(1);
  10. Query OK, 1 row affected (0.01 sec)
  12. root@localhost [xucl]>insert into t values(2);
  13. Query OK, 1 row affected (0.00 sec)

node2:

  1. root@localhost [performance_schema]>select * from xucl.t;
  2. +----+
  3. | id |
  4. +----+
  5. |  1 |
  6. |  2 |
  7. +----+
  8. 2 rows in set (0.00 sec)

node3:

  1. root@localhost [performance_schema]>select * from xucl.t;
  2. +----+
  3. | id |
  4. +----+
  5. |  1 |
  6. |  2 |
  7. +----+
  8. 2 rows in set (0.00 sec)

看到复制没有问题，本次MGR搭建已完成

五、错误处理

在初始化节点2的时候出现了，节点2状态一直处于recovering的状态，因为我的集群是新的集群，一直处于recovering的状态肯定是不正常的，在节点1看error日志看不出什么异常，但是节点2的error日志就给出了一些提示

  1. root@localhost [performance_schema]>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 | c516f6d9-64af-11e8-9952-080027de0e0e | zst2        |        3306 | RECOVERING   |
  7. +---------------------------+--------------------------------------+-------------+-------------+--------------+
  8. 2 rows in set (0.00 sec)
  1. 2018-05-31T09:08:20.681617Z 0 [Note] Plugin group_replication reported: 'Group membership changed to zst1:3306, zst2:3306 on view 15277565398765121:2.'
  2. 2018-05-31T09:08:20.686706Z 13 [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=''.
  3. 2018-05-31T09:08:20.695737Z 13 [Note] Plugin group_replication reported: 'Establishing connection to a group replication recovery donor 948335e8-64ad-11e8-86e7-080027de0e0e at zst1 port: 3306.'
  4. 2018-05-31T09:08:20.695874Z 14 [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.
  5. 2018-05-31T09:08:20.696181Z 15 [Note] Slave SQL thread for channel 'group_replication_recovery' initialized, starting replication in log 'FIRST' at position 0, relay log './zst2-relay-bin-group_replication_recovery.000001' position: 4
  6. 2018-05-31T09:08:20.709183Z 14 [ERROR] Slave I/O for channel 'group_replication_recovery': error connecting to master 'repl@zst1:3306' - retry-time: 60  retries: 1, Error_code: 2005
  7. 2018-05-31T09:08:20.709205Z 14 [Note] Slave I/O thread for channel 'group_replication_recovery' killed while connecting to master
  8. 2018-05-31T09:08:20.709214Z 14 [Note] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'FIRST', position 4
  9. 2018-05-31T09:08:20.709375Z 13 [ERROR] Plugin group_replication reported: 'There was an error when connecting to the donor server. Please check that group_replication_recovery channel credentials and all MEMBER_HOST column values of performance_schema.replication_group_members table are correct and DNS resolvable.'
  10. 2018-05-31T09:08:20.709383Z 13 [ERROR] Plugin group_replication reported: 'For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.'

看错误提示猜测是因为本地host没有配置的问题，本地host配置后重启group replication，复制正常了。

  1. 127.0.0.1       zst2    zst2
  2. 127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
  3. ::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
  4. 172.28.128.101  zst1
  5. 172.28.128.102  zst2
  6. 172.28.128.103  zst3