Centos6.8 下部署Mysql组复制（MySQL Group Replication）之多主模式（5.7新特性）

我就是我 2022-06-10 10:17 165阅读 0赞

MySQL Group Replication（简称MGR）是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。MySQL组复制提供了高可用、高扩展、高可靠的MySQL集群服务。

## 1.关于MGR介绍 ##

1.1提供的特性:

多写，写冲突检测
    良好的扩展能力，可动态增删节点，组成员自动管理
    组内高可用
    确保组内数据最终一致性【重要】(通过分布式协议和分布式recovery机制保证)

1.2组复制的两种模式

在单主模式下，组复制具有自动选主功能，每次只有一个 server成员接受更新。
    在多主模式下,所有的 server 成员都可以同时接受更新.

1.3组复制的限制

仅支持InnoDB表，并且每张表一定要有一个主键，用于做write set的冲突检测；
    必须打开GTID特性，二进制日志格式必须设置为ROW，用于选主与write set
    COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景
    目前一个MGR集群最多支持9个节点
    不支持外键于save point特性，无法做全局间的约束检测与部分部分回滚
    二进制日志不支持binlog event checksum

1.4MySQL组复制协议  
![组复制原理][SouthEast]  
上图描述了MySQL组复制协议。

## 2.安装部署 ##

2.1 环境介绍  
三台服务器（centos6.8）安装mysql5.7.19版本，关于mysql安装参考我的另一个文章。  
服务器分别为：  
192.168.1.208  
192.168.1.211  
192.168.1.212

<table> 
 <thead> 
  <tr> 
   <th>服务器</th> 
   <th align="center">端口号</th> 
   <th align="right">数据及日志目录</th> 
   <th align="right">Group_replication 通讯端口</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>192.168.1.208</td> 
   <td align="center">3306</td> 
   <td align="right">/usr/local/mysql/mysql/{data,logs,tmp}</td> 
   <td align="right">33061</td> 
  </tr> 
  <tr> 
   <td>192.168.1.211</td> 
   <td align="center">3306</td> 
   <td align="right">/usr/local/mysql/mysql/{data,logs,tmp}</td> 
   <td align="right">33061</td> 
  </tr> 
  <tr> 
   <td>192.168.1.212</td> 
   <td align="center">3306</td> 
   <td align="right">/usr/local/mysql/mysql/{data,logs,tmp}</td> 
   <td align="right">33061</td> 
  </tr> 
 </tbody> 
</table>

--------------------

①.分别在三台服务器添加host

> vi /etc/hosts  
> 192.168.1.208 mgr208  
> 192.168.1.212 mgr212  
> 192.168.1.211 mgr211

2.2更改mysql配置文件my.cnf

[client]
    port = 3306
    socket = /usr/local/mysql/tmp/mysql.sock
    
    [mysqld]
    port = 3306
    socket = /usr/local/mysql/tmp/mysql.sock
    back_log = 80
    basedir = /usr/local/mysql
    tmpdir = /tmp
    datadir = /usr/local/mysql/data
    pid-file=/usr/local/mysql/mysqld.pid 
    #-------------------gobal variables------------#
    gtid_mode = ON
    enforce_gtid_consistency = ON
    master_info_repository = TABLE
    relay_log_info_repository = TABLE
    binlog_checksum = NONE
    log_slave_updates = ON
    log-bin = /usr/local/mysql/log/mysql-bin
    transaction_write_set_extraction = XXHASH64  #以便在server收集写集合的同时将其记录到二进制日志。写集合基于每行的主键，并且是行更改后的唯一标识此标识将用于检测冲突。
    loose-group_replication_group_name = 'de9be252-2b91-11e6-b8f4-00212889f856' #组的名字可以随便起,但不能用主机的GTID
    loose-group_replication_start_on_boot = off  #为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
    loose-group_replication_bootstrap_group = off 
    loose-group_replication_local_address = '192.168.1.212:33061' #sql服务器IP
    loose-group_replication_group_seeds ='192.168.1.212:33061,192.168.1.208:33061,192.168.1.211:33061'
    loose-group_replication_single_primary_mode = off  #关闭单主模式的参数（本例测试时多主模式，所以关闭该项）
    loose-group_replication_enforce_update_everywhere_checks = on #开启多主模式的参数
    max_connect_errors = 20000
    max_connections = 2000
    wait_timeout = 3600
    interactive_timeout = 3600
    net_read_timeout = 3600
    net_write_timeout = 3600
    table_open_cache = 1024
    table_definition_cache = 1024
    thread_cache_size = 512
    open_files_limit = 10000
    character-set-server = utf8
    collation-server = utf8_bin
    skip_external_locking
    performance_schema = 1
    user = mysql
    myisam_recover_options = DEFAULT
    skip-name-resolve
    local_infile = 0
    lower_case_table_names = 0
    
    #--------------------innoDB------------#
    innodb_buffer_pool_size = 2000M
    innodb_data_file_path = ibdata1:200M:autoextend
    innodb_flush_log_at_trx_commit = 1
    innodb_io_capacity = 600
    innodb_lock_wait_timeout = 120
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 200M
    innodb_log_files_in_group = 3
    innodb_max_dirty_pages_pct = 85
    innodb_read_io_threads = 8
    innodb_write_io_threads = 8
    innodb_support_xa = 1
    innodb_thread_concurrency = 32
    innodb_file_per_table
    innodb_rollback_on_timeout
    
    #------------session variables-------#
    join_buffer_size = 8M
    key_buffer_size = 256M
    bulk_insert_buffer_size = 8M
    max_heap_table_size = 96M
    tmp_table_size = 96M
    read_buffer_size = 8M
    sort_buffer_size = 2M
    max_allowed_packet = 64M
    read_rnd_buffer_size = 32M
    
    #------------MySQL Log----------------#
    log-bin = my3306-bin
    binlog_format = row  #组复制依赖基于行的复制格式
    sync_binlog = 1
    expire_logs_days = 15
    max_binlog_cache_size = 128M
    max_binlog_size = 500M
    binlog_cache_size = 64k
    slow_query_log
    log-slow-admin-statements
    log_warnings = 1
    long_query_time = 0.25
    
    #---------------replicate--------------#
    relay-log-index = relay3306.index
    relay-log = relay3306
    server-id = 91 #每个db的id唯一
    init_slave = 'set sql_mode=STRICT_ALL_TABLES'
    log-slave-updates
    [myisamchk]
    key_buffer = 512M
    sort_buffer_size = 512M
    read_buffer = 8M
    write_buffer = 8M
    #skip-grant-tables
    
    [mysqlhotcopy]
    interactive-timeout
    [mysqld_safe]
    open-files-limit = 8192
    log-error = /usr/local/mysql/log/mysqld_error.log

2.3注意事项  
启动mysql,如果启动失败，查看日志可能是/usr/local/mysql权限问题，也可以其他问题。  
mysql的basedir 必须是/usr/local/mysql 这个mysql官方说basedir如果不在这个目录的话，会有问题。

④.安装组复制插件

mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';

2.4启动Group\_replication第一个节点(192.168.1.208)

mysql > SET GLOBAL group_replication_bootstrap_group=ON; //只在第一个节点使用
    
     mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY                                    'Goodluck123456@';     
    
     mysql > CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Goodluck123456@' FOR CHANNEL 'group_replication_recovery';
    
     mysql> SET GLOBAL  group_replication_ip_whitelist="127.0.0.1/32,192.168.0.0/16,192.168.1.0/24";
    
     mysql > START GROUP_REPLICATION;
    
     mysql> SELECT * FROM performance_schema.replication_group_members;

![这里写图片描述][SouthEast 1]

2.5配置 group\_replication\_recovery 通道的恢复凭据(192.168.1.211,192.168.1.212)

mysql > SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON;
      mysql > CHANGE MASTER TO MASTER_USER='repl',MASTER_PASSWORD='Goodluck123456@' FOR  CHANNEL 'group_replication_recovery';
    
      mysql > SET GLOBAL  group_replication_ip_whitelist="127.0.0.1/32,192.168.0.0/16,192.168.1.0/24";
    
      mysql > START GROUP_REPLICATION;
    
      mysql > SELECT * FROM performance_schema.replication_group_members;

![这里写图片描述][SouthEast 1]

2.6 数据同步测试

在192.168.1.212上执行sql

mysql> CREATE TABLE `test1` (`id`  int(11) NOT NULL AUTO_INCREMENT ,`name`  varchar(20)  CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,PRIMARY KEY (`id`));
    Query OK, 0 rows affected (0.13 sec)

mysql> show tables;  
\+—————–+  
| Tables\_in\_test1 |  
\+—————–+  
| test1 |  
\+—————–+  
1 row in set (0.00 sec)

在其他两台服务器上查询  
mysql> show tables;  
\+—————–+  
| Tables\_in\_test1 |  
\+—————–+  
| test1 |  
\+—————–+  
1 row in set (0.00 sec)

# 参考 #

[Group Replication][]

[SouthEast]: /images/20220610/818c7dd7781b4543bf25836fb0ce0c37.png
[SouthEast 1]: /images/20220610/635d4f550754499591e27f7967d56040.png
[Group Replication]: https://dev.mysql.com/doc/refman/5.7/en/group-replication.html