mysql mgr集群部署

Mysql Mgr(MySQL Group Replication)集群部署

环境:

OS:Centos 7

Mysql版本:社区版 5.7.22

 

1.安装步骤

1.1 环境部署

节点角色	ip
节点1	192.168.1.134
节点2	192.168.1.135
节点3	192.168.1.136

1.2 下载安装介质

可以到mysql官网下载,我这里下载的版本是:5.7.27

1.3 节点1(192.168.1.134)安装

1.3.1   解压安装

[root@localhost soft]# tar -xvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz

转移到安装目录

[root@localhost soft]# mv mysql-5.7.27-linux-glibc2.12-x86_64 /opt/mysql_mgr

  

1.3.2   创建相应的目录

  

[root@localhost mysql_mgr]# cd /opt/mysql_mgr

[root@localhost mysql_mgr]# mkdir data      ##数据文件目录            

[root@localhost mysql_mgr]# mkdir conf      ## 配置文件目录

[root@localhost mysql_mgr]# mkdir -p mysqllog/relaylog ##主从环境relaylog

[root@localhost mysql_mgr]# mkdir -p mysqllog/logfile  ##错误日志文件

[root@localhost mysql_mgr]# mkdir -p mysqllog/binlog   ##binlog文件

 修改目录权限给到mysql用户(若系统没有该用户的话,可以创建)

 [root@localhost mysql_mgr]# cd /opt

[root@localhost opt]# chown -R mysql:mysql ./mysql_mgr

1.3.3   创建配置文件

[mysqld]

port=3306

basedir=/opt/mysql_mgr

datadir=/opt/mysql_mgr/data

character-set-server=utf8mb4

max_connections = 1500

show_compatibility_56=on

server_id=1

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=/opt/mysql_mgr/mysqllog/binlog/binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_index=/opt/mysql_mgr/mysqllog/relaylog/slave-relay-bin.index

relay_log=/opt/mysql_mgr/mysqllog/relaylog/relaylog-binlog

##集群部分

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address= "192.168.1.134:33061"

loose-group_replication_group_seeds= "192.168.1.134:33061,192.168.1.135:33061,192.168.1.136:33061"

loose-group_replication_bootstrap_group=OFF

report_host=192.168.1.134

report_port=3306

1.3.4   初始化数据库

[mysql@localhost bin]$ cd /opt/mysql_mgr/bin

[mysql@localhost bin]$ ./mysqld --initialize-insecure --basedir=/opt/mysql_mgr --datadir=/opt/mysql_mgr/data --user=mysql

2019-09-03T03:00:25.190551Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-09-03T03:00:26.960483Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-09-03T03:00:27.273154Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-09-03T03:00:27.411863Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fb59af57-cdf6-11e9-bf83-525400c8dc1f.

2019-09-03T03:00:27.435468Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-09-03T03:00:27.436258Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

1.3.5   启动数据库

./mysqld_safe --defaults-file=/opt/mysql_mgr/conf/my.cnf --user=mysql &

  

1.3.6   登陆

这里密码为空，不用输入密码，为了安全起见安装完成后自行修改密码

[mysql@localhost bin]$ ./mysql -h localhost -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 2

Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases

    -> ;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.01 sec)

mysql> select version();

+------------+

| version()  |

+------------+

| 5.7.27-log |

+------------+

1 row in set (0.00 sec)

1.4 节点2(192.168.1.135)安装

安装部署跟节点1一致,只是配置文件需要适当修改

1.4.1   创建配置文件

拷贝一个节点1的过来修改即可,修改红色部分

[mysqld]

port=3306

basedir=/opt/mysql_mgr

datadir=/opt/mysql_mgr/data

character-set-server=utf8mb4

max_connections = 1500

show_compatibility_56=on

server_id=135

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=/opt/mysql_mgr/mysqllog/binlog/binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_index=/opt/mysql_mgr/mysqllog/relaylog/slave-relay-bin.index

relay_log=/opt/mysql_mgr/mysqllog/relaylog/relaylog-binlog

##集群部分

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address= "192.168.1.135:33061"

loose-group_replication_group_seeds= "192.168.1.134:33061,192.168.1.135:33061,192.168.1.136:33061"

loose-group_replication_bootstrap_group=OFF

report_host=192.168.1.135

report_port=3306

1.4.2   启动数据库

./mysqld_safe --defaults-file=/opt/mysql_mgr/conf/my.cnf --user=mysql &

1.5 节点3(192.168.1.136)安装

1.5.1   创建配置文件

[mysqld]

port=3306

basedir=/opt/mysql_mgr

datadir=/opt/mysql_mgr/data

character-set-server=utf8mb4

max_connections = 1500

show_compatibility_56=on

server_id=136

gtid_mode=ON

enforce_gtid_consistency=ON

binlog_checksum=NONE

log_bin=/opt/mysql_mgr/mysqllog/binlog/binlog

log_slave_updates=ON

binlog_format=ROW

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_index=/opt/mysql_mgr/mysqllog/relaylog/slave-relay-bin.index

relay_log=/opt/mysql_mgr/mysqllog/relaylog/relaylog-binlog

##集群部分

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=OFF

loose-group_replication_local_address= "192.168.1.136:33061"

loose-group_replication_group_seeds= "192.168.1.134:33061,192.168.1.135:33061,192.168.1.136:33061"

loose-group_replication_bootstrap_group=OFF

report_host=192.168.1.136

report_port=3306

1.5.2     启动数据库

./mysqld_safe --defaults-file=/opt/mysql_mgr/conf/my.cnf --user=mysql &

1.6 单组模式配置集群

1.6.1 安装MGR插件(所有节点执行)

每个节点上都要执行

[mysql@localhost bin]$ ./mysql -h localhost -uroot

mysql> install plugin group_replication soname 'group_replication.so';

Query OK, 0 rows affected (0.04 sec)

1.6.2 设置复制账号(所有节点执行)

  

#设置复制账号

mysql> SET SQL_LOG_BIN=0;

mysql> CREATE USER repl@'%' IDENTIFIED BY 'repl';

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';

mysql> FLUSH PRIVILEGES;

mysql> SET SQL_LOG_BIN=1;

mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

1.6.3 启动MGR单主模式(节点上192.168.1.134执行)

 # 启动MGR，在主库(192.168.1.134)上执行

mysql> set global group_replication_bootstrap_group=on;

mysql> start group_replication;

mysql> set global group_replication_bootstrap_group=off;

查看mgr组信息

mysql> SELECT * FROM performance_schema.replication_group_members;

#其他节点加入MGR,在从库(192.168.1.135,192.168.1.136)上执行

mysql> start group_replication;

在节点1上查看mgr组信息

mysql> SELECT * FROM performance_schema.replication_group_members;

查看那个是主节点:

  

mysql> select * from performance_schema.global_status where variable_name='group_replication_primary_member';

  

可以看出目前192.168.1.134是主节点

  

1.6.4   验证

1.6.4.1 主库写入重库查询

在主库上(192.168.1.134)创建数据库并创建表写入测试数据

create table tb_test

(id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',

name varchar(64),

PRIMARY KEY (id)

);

insert into tb_test values(1,'name1');

insert into tb_test values(2,'name2');

insert into tb_test values(3,'name3');

insert into tb_test values(4,'name4');

insert into tb_test values(5,'name5');

insert into tb_test values(6,'name6');

insert into tb_test values(7,'name7');

insert into tb_test values(8,'name8');

insert into tb_test values(9,'name9');

insert into tb_test values(10,'name10');

分别从另外2个从库查询数据

192.168.1.135从库查询

mysql> select * from tb_test;

+----+--------+

| id | name   |

+----+--------+

|  1 | name1  |

|  2 | name2  |

|  3 | name3  |

|  4 | name4  |

|  5 | name5  |

|  6 | name6  |

|  7 | name7  |

|  8 | name8  |

|  9 | name9  |

| 10 | name10 |

+----+--------+

10 rows in set (0.00 sec)

192.168.1.136从库查询

mysql> select * from tb_test;

+----+--------+

| id | name   |

+----+--------+

|  1 | name1  |

|  2 | name2  |

|  3 | name3  |

|  4 | name4  |

|  5 | name5  |

|  6 | name6  |

|  7 | name7  |

|  8 | name8  |

|  9 | name9  |

| 10 | name10 |

+----+--------+

10 rows in set (0.00 sec)

1.6.4.2 尝试从库写入

192.168.1.135从库尝试写入

mysql> insert into tb_test values(11,'name11');

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

192.168.1.136从库尝试写入

mysql> insert into tb_test values(11,'name11');

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

1.6.4.3 关闭主库

关闭主库节点192.168.1.134

[mysql@localhost bin]$ ./mysqladmin -h localhost -uroot shutdown

登陆其中一个从库检查mgr组信息

SELECT * FROM performance_schema.replication_group_members;

  

可以发现当前的节点只有2个了，下面看下目前那个是主库

select * from performance_schema.global_status where variable_name='group_replication_primary_member';

  

可以发现192.168.1.136已经成为主节点,尝试在该节点上写入数据

mysql> insert into tb_test values(11,'name11');

Query OK, 1 row affected (0.09 sec)

写入成功

1.6.4.4 启动之前关闭的主库

192.168.1.134上操作

[mysql@localhost bin]$ ./mysqld_safe --defaults-file=/opt/mysql_mgr/conf/my.cnf --user=mysql &

SELECT * FROM performance_schema.replication_group_members;

select * from performance_schema.global_status where variable_name='group_replication_primary_member';

可以看到当前该节点没有加入到mgr,这个时候需要执行如下命令

mysql> start group_replication;

启动后成为从库

  

可以看到启动后该节点不会为主库,而是成为从库

1.6.4.5 所有的节点都关闭然后启动

三个节点执行关闭命令

./mysqladmin -h localhost -uroot shutdown 

然后启动

./mysqld_safe --defaults-file=/opt/mysql_mgr/conf/my.cnf --user=mysql &

启动后发现目前是没有mgr组信息的

  

需要执行如下命令启动mgr

 主节点执行(要想那个成为主节点就执行如下命令):

set global group_replication_bootstrap_group=on;

start group_replication;

set global group_replication_bootstrap_group=off;

  其他从节点执行:

start group_replication;

1.7 单主切换到多主模式

# 停止组复制(所有节点执行)：

mysql> stop group_replication;

mysql> set global group_replication_single_primary_mode=off;

mysql> set global group_replication_enforce_update_everywhere_checks=on;

# 随便选择某个节点执行

mysql> set global group_replication_bootstrap_group=on;

mysql> start group_replication;

mysql> set global group_replication_bootstrap_group=off;

# 其他节点执行

mysql> start group_replication;

  

查看节点信息

 可以发现group_replication_primary_member为空,说明该模式为多主模式

尝试每个节点写入数据:

192.168.1.134节点写入:

insert into tb_test values(12,'name12');

192.168.1.135节点写入:

insert into tb_test values(13,'name13');

192.168.1.136节点写入:

insert into tb_test values(14,'name14');

 说明当前每个节点都是主节点同时可以写入

  

1.8 多主切换回到单主模式

# 所有节点执行

mysql> stop group_replication;

mysql> set global group_replication_enforce_update_everywhere_checks=OFF;

mysql> set global group_replication_single_primary_mode=ON;

#主节点执行

set global group_replication_bootstrap_group=on;

start group_replication;

set global group_replication_bootstrap_group=off;

##其他节点

start group_replication;

   

查看当前主节点

1.9 遇到的错误

1.9.1   表必须有主键

mysql> insert into tb_test values(1,'name1');

ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

附上文档: