MySQL Group Replication-现有普通复制集群提升为Group Replication

￡神魔★判官ぃ 2022-07-15 14:27 135阅读 0赞

上一篇测试了各种场景下，向Group Replication中添加新节点。那么，如果原来有一套master-slave集群，需要怎么把它改造成Group Replication集群呢？

Group Replication是基于GTID来复制的，那么第一步就是需要将普通复制改造成GTID复制。有了GTID那么将Master-Slave结构改造成Group Replication就很方便了。

### 搭建普通复制环境 ###

#### 解压安装包 ####

[root@mser02 local]# tar -zxvf mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64.tar.gz
    
    [root@mser02 local]# mv mysql-5.7.15-labs-gr090-linux-glibc2.5-x86_64 mysql5.7
    
    [root@mser02 local]# cd mysql5.7/

#### 初始化数据 ####

\[server01\]

./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data_1

\[server02\]

./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data_2

\[server03\]

./bin/mysqld --no-defaults --user=mysql --initialize --explicit_defaults_for_timestamp --basedir=/usr/local/mysql5.7/ --datadir=/usr/local/mysql5.7/data_3

#### 编辑配置文件 ####

\[server1\]

my\_1.cnf

[mysqld]
    server-id = 1
    basedir=/usr/local/mysql5.7/
    datadir=/usr/local/mysql5.7/data_1
    user=mysql
    explicit_defaults_for_timestamp
    socket=mysql1.sock
    port = 3306
    
    # binlog
    log-bin=mysql-bin
    binlog-format = ROW
    log-slave-updates = ON
    master-info-repository = TABLE
    relay-log-info-repository = TABLE
    binlog-checksum = NONE
    slave-parallel-workers = 0

\[server2\]

my\_2.cnf

[mysqld]
    server-id = 2
    basedir=/usr/local/mysql5.7/
    datadir=/usr/local/mysql5.7/data_2
    user=mysql
    explicit_defaults_for_timestamp
    socket=mysql2.sock
    port = 3307
    
    # binlog
    log-bin=mysql-bin
    binlog-format = ROW
    log-slave-updates = ON
    master-info-repository = TABLE
    relay-log-info-repository = TABLE
    binlog-checksum = NONE
    slave-parallel-workers = 0

\[server3\]

my\_3.cnf

[mysqld]
    server-id = 3
    basedir=/usr/local/mysql5.7/
    datadir=/usr/local/mysql5.7/data_3
    user=mysql
    explicit_defaults_for_timestamp
    socket=mysql3.sock
    port = 3308
    
    # binlog
    log-bin=mysql-bin
    binlog-format = ROW
    log-slave-updates = ON
    master-info-repository = TABLE
    relay-log-info-repository = TABLE
    binlog-checksum = NONE
    slave-parallel-workers = 0

mysql> CREATE USER rpl_user@'%';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
    Query OK, 0 rows affected, 1 warning (0.00 sec)

#### 启动mysql ####

\[server01\]

./bin/mysqld --defaults-file=/usr/local/mysql5.7/my_1.cnf

\[server02\]

./bin/mysqld --defaults-file=/usr/local/mysql5.7/my_2.cnf

\[server03\]

./bin/mysqld --defaults-file=/usr/local/mysql5.7/my_3.cnf

#### 修改初始密码 ####

\[server01\]

./bin/mysqladmin -u root -h 127.0.0.1 -P 3306 -p password "111111"

\[server02\]

./bin/mysqladmin -u root -h 127.0.0.1 -P 3307 -p password "111111"

\[server03\]

./bin/mysqladmin -u root -h 127.0.0.1 -P 3308 -p password "111111"

#### 组建集群 ####

首先在\[server01\]中进行如下配置：

CREATE USER rpl_user@'%';
    GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
    FLUSH PRIVILEGES;

再在其他两个节点执行如下命令

mysql> change master to master_host='127.0.0.1',master_port=3306,master_user='rpl_user',master_password='rpl_pass',master_log_file='mysql-bin.000001',master_log_pos=386;
    Query OK, 0 rows affected, 2 warnings (0.14 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 127.0.0.1
                      Master_User: rpl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 859
                   Relay_Log_File: mser01-relay-bin.000002
                    Relay_Log_Pos: 785
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

这样普通Master-Slave结构就完成了

#### 进行测试 ####

Master节点上执行

mysql> create database bobo;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> use bobo;
    Database changed
    mysql> create table test1 (id int primary key,name varchar(20));
    Query OK, 0 rows affected (0.19 sec)
    
    mysql> insert into test1(id,name) values (1,'aaaa');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> insert into test1(id,name) values (2,'bbbb');
    Query OK, 1 row affected (0.05 sec)
    
    mysql> insert into test1(id,name) values (3,'cccc');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> insert into test1(id,name) values (4,'dddd');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> select * from test1; +----+------+
    | id | name | +----+------+
    |  1 | aaaa |
    |  2 | bbbb |
    |  3 | cccc |
    | 4 | dddd | +----+------+
    4 rows in set (0.00 sec)

Slave节点执行

mysql> use bobo;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> select * from test1; +----+------+
    | id | name | +----+------+
    |  1 | aaaa |
    |  2 | bbbb |
    |  3 | cccc |
    | 4 | dddd | +----+------+
    4 rows in set (0.00 sec)

### 转换成GTID复制 ###

MySQL 5.7.6以后的版本可以在线进行GTID的切换，但是要保证所有组中成员gtid\_mode 为off状态。

实现步骤：

所有的Server执行

set @@global.enforce_gtid_consistency = warn;

> 特别注意： 这一步是关建的一步使用不能出现警告。

所有的server上执行：

set @@global.enforce_gtid_consistency = on;

所有的Server上执行（不关心最先最后，但要执行完）：

set @@global.gtid_mode = off_permissive;

执行：

set @@global.gtid_mode=on_permissive;

实质在这一步骤生的日志都是带GTID的日志了，这个步骤号称是不关心任何节点，但从实管理上推荐在slave上先执行，然后再去master上执行。

确认传统的binlog复制完毕,该值为0

show status like ‘ongoing_anonymous_transaction_count’;

需要所有的节点都确认为0.

所有节点进行判断

show status like ‘ongoing_anonymous_transaction_count’;

为零  
所有的节点也可以执行一下: flush logs; 用于切换一下日志。

所有的节点启用gtid\_mode

set @@global.gtid_mode=on

把gtid\_mode = on相关配置写入配置文件

gtid_mode=on
    
    enforce_gtid_consistency=on

启用Gtid的自动查找节点复制：

stop slave;
    
    change master to master_auto_position=1;
    
    start slave;

这样普通的Master-Slave就在线的切换成了基于GTID复制了。

> 注：能够进行在线的GTID的切换，得益于mysql5.7.6以后的版本中，gtid\_mode出现了两个新的状态OFF\_PERMISSIVE、ON\_PERMISSIVE，以及enforce\_gtid\_consistency中新增Warn状态
> 
> enforce\_gtid\_consistency
> 
> Warn ：当检测到不支持GTID的语句和事务，返回警告，并在日志中记录
> 
> gtid\_mode
> 
>  *  OFF\_PERMISSIVE: New transactions are anonymous. Replicated transactions can be eitheranonymous or GTID transactions.
>  *  ON\_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
>  *  OFF\_PERMISSIVE ：不产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
>  *  ON\_PERMISSIVE ：产生GTID，Slave即接受不带GTID的事务，也接受带GTID的事务
> 
> 有了不支持GTID事务的检查功能，也有了即支持GTID复制也支持普通复制的过度状态，这样就可以在线平滑的将Master-Slave普通复制切换成GTID复制。  
> 此步骤参考：  
> [http://wubx.net/mysql-5-7-class-repl-online-2-gtid-repl/][http_wubx.net_mysql-5-7-class-repl-online-2-gtid-repl]

### 开启Group Replication ###

修改成GTID模式之后，接下来切换成Group Replication就方便了。

#### 安装Group Replicaiton插件 ####

在全部server上执行

#### 配置参数 ####

\[server1\]

mysql> set @@global.transaction_write_set_extraction = XXHASH64
    mysql> set @@global.group_replication_start_on_boot = OFF
    mysql> set @@global.group_replication_bootstrap_group = OFF 
    mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
    mysql> set @@global.group_replication_local_address = '127.0.0.1:6606'
    mysql> set @@global.group_replication_group_seeds = '127.0.0.1:6607,127.0.0.1:6608'

\[server2\]

mysql> set @@global.transaction_write_set_extraction = XXHASH64
    mysql> set @@global.group_replication_start_on_boot = OFF
    mysql> set @@global.group_replication_bootstrap_group = OFF 
    mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
    mysql> set @@global.group_replication_local_address = '127.0.0.1:6607'
    mysql> set @@global.group_replication_group_seeds = '127.0.0.1:6606,127.0.0.1:6608'

\[server3\]

mysql> set @@global.transaction_write_set_extraction = XXHASH64
    mysql> set @@global.group_replication_start_on_boot = OFF
    mysql> set @@global.group_replication_bootstrap_group = OFF 
    mysql> set @@global.group_replication_group_name = 0c6d3e5f-90e2-11e6-802e-842b2b5909d6
    mysql> set @@global.group_replication_local_address = '127.0.0.1:6608'
    mysql> set @@global.group_replication_group_seeds = '127.0.0.1:6607,127.0.0.1:6606'

> ==**特别注意**==：如果之前没有配置transaction\_write\_set\_extraction=XXHASH64，这里修改之后之前创建的数据库是没有办法执行插入操作的。所有如果想在线完成Group Replication的改造需要保证之前已经设置了transaction\_write\_set\_extraction=XXHASH64。

#### 构建集群 ####

\[server1\]

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' FOR CHANNEL 'group_replication_recovery';

> 注：在普通Master-Slave中已经配置过复制用户了，所以在构建Group Replicaiton时不需要再重新创建了。

开启group\_replication

SET GLOBAL group_replication_bootstrap_group=ON;
    START GROUP_REPLICATION;
    SET GLOBAL group_replication_bootstrap_group=OFF;

其他节点加入group\_replication集群

stop slave;
    START GROUP_REPLICATION;

在查看节点信息

这样普通Master-Slave集群改造成Group Replication集群就完成了。

[http_wubx.net_mysql-5-7-class-repl-online-2-gtid-repl]: http://note.youdao.com/