专栏首页DBA随笔MGR搭建以及性能测试

MGR搭建以及性能测试

MGR初探
MGR初探

昨天的文章中简单介绍了MySQL的组复制的概念,今天搭建了一套单机多实例单主节点的MGR环境来进行性能测试。

本地测试环境

192.168.10.104 24081 master

192.168.10.104 24082 slave

192.168.10.104 24083 slave

测试环境部署

mysql版本:5.7.25

包含实例初始化、mysql服务启动等,该部分不再赘述。

本地软件安装目录:

/usr/local/mysql-5.7.25-linux-glibc2.12-x86_64

本地data目录:

/data/data_mgr/s1

my.cnf文件

下面以其中的192.168.10.104为例,默认服务端口24081, 上半部分是mysql相关的常规参数,下班部分是MGR相关的参数,包含group中成员的信息,端口24901为MGR通信端口,

[mysqld]
# server configuration
datadir=/data/data_mgr/s1
basedir=/usr/local/mysql-5.7.-linux-glibc2.-x86_64
port=
socket=/data/data_mgr/s1/s1.sock
server_id=
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

binlog_checksum=NONE
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="1bb1b861-f776-11e6-be42-782bcb377193"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "127.0.0.1:24901"
loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group= off

MGR部署

Master操作:

#首先创建复制用户,并授予replication slave权限
SET SQL_LOG_BIN=;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=;

#创建一个复制通道channel
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass'
        FOR CHANNEL 'group_replication_recovery';

#安装group_replication的插件plugin
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

# 设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置
SET GLOBAL group_replication_bootstrap_group=ON;

#开启group_replication
start group_replication;
SET GLOBAL group_replication_bootstrap_group=OFF;

#查看MGR的状态
select *from performance_schema.replication_group_members;

Slave操作

#首先创建复制用户,并授予replication slave权限
SET SQL_LOG_BIN=;
CREATE USER rpl_user@'%';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=;

#创建一个复制通道channel
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass'
        FOR CHANNEL 'group_replication_recovery';

#安装group_replication的插件plugin
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

# 这里不再需要开启group_replication_bootstrap_group,由于复制组已经被创建了,只需要将第二个节点添加进去即可
set global group_replication_allow_local_disjoint_gtids_join=on;

#开启group_replication
start group_replication;
SET GLOBAL group_replication_bootstrap_group=OFF;

#查看MGR的状态
select *from performance_schema.replication_group_members;

特别需要注意的是,Master配置中,需要将参数

group_replication_bootstrap_group设置为on,

设置group_replication_bootstrap_group为ON是为了标示以后加入集群的服务器以这台服务器为基准,以后加入的就不需要设置,而Slave中需要将

group_replication_allow_local_disjoint_gtids_join设置为on,允许当前服务器加入该组,即使该组中没有事务。如果不添加这个参数,日志中将会给出下面的提示:

Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

这一点是Master和Slave搭建时候的重要区别。

性能测试

1.查看MGR的状态

mysql--root@localhost:(none) 18:09:53>>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST              | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| group_replication_applier | fff1c--e9-be96-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | cdb2b--e9-e5-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | d8dcd--e9-a3ed-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 rows in set (.08 sec)

2.切换测试

切换之前,可以看到24801是主节点

mysql--root@localhost:(none) 18:24:55>>select variable_name,member_id,member_host,member_port from performance_schema.global_status a,performance_schema.replication_group_members b where a.variable_value=b.member_id;
+----------------------------------+--------------------------------------+--------------------------+-------------+
| variable_name                    | member_id                            | member_host              | member_port |
+----------------------------------+--------------------------------------+--------------------------+-------------+
| group_replication_primary_member | 896fff1c-3400-11e9-be96-0050568fc57a | tk-dba-mysql-stat-- |       24801 |
+----------------------------------+--------------------------------------+--------------------------+-------------+
 row in set (. sec)

停止24801的group_replication,可以看到它的状态已经变成了offline

 mysql--root@localhost:(none) 18:25:06>>stop group_replication;
 Query OK,  rows affected (9.35 sec)
 mysql--root@localhost:(none) 18:30:09>>select * from performance_schema.replication_group_members;
 +---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST              | MEMBER_PORT | MEMBER_STATE |
 +---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 | group_replication_applier | fff1c--e9-be96-8fc57a | tk-dba-mysql-stat-10-104 |        | OFFLINE      |
 +---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 row in set (. sec)

在从节点上查看当前group的状态:

mysql--root@localhost:(none) 18:23:17>>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST              | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| group_replication_applier | cdb2b--e9-e5-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | d8dcd--e9-a3ed-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 rows in set (. sec)

查看当前group的主节点,我们可以看到此时主节点发生了变化,24802升级成为主节点:

mysql--root@localhost:(none) 18:30:29>>select variable_name,member_id,member_host,member_port from performance_schema.global_status a,performance_schema.replication_group_members b where a.variable_value=b.member_id;
+----------------------------------+--------------------------------------+--------------------------+-------------+
| variable_name                    | member_id                            | member_host              | member_port |
+----------------------------------+--------------------------------------+--------------------------+-------------+
| group_replication_primary_member | 906cdb2b-3400-11e9-90e5-0050568fc57a | tk-dba-mysql-stat-- |       24802 |
+----------------------------------+--------------------------------------+--------------------------+-------------+
 row in set (. sec)

恢复24801,重新加入到group_replication中,此时查看group_replication的状态

mysql--root@localhost:(none) 18:39:37>>start group_replication;
Query OK,  rows affected (2.99 sec)
mysql--root@localhost:(none) 18:40:25>>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST              | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| group_replication_applier | fff1c--e9-be96-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | cdb2b--e9-e5-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | d8dcd--e9-a3ed-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 rows in set (. sec)

结论:当MGR中的主节点宕机时,会重新选择新的master,当旧的master恢复加入后,新master不会发生改变。

节点选举情况

上面的切换测试中,当我们停掉24801的时候,系统默认为我们选择了24802这个节点作为新的master,如果我们此时想要断开24802,重新选举24803来作为新的master,该怎么做呢?这就要牵扯到group成员的权重问题了,我们可以通过设置某个节点的权重来指定我们想要选举的新master,如下:

节点
mysql--root@localhost:(none) 18:47:24>>set global group_replication_member_weight=;
Query OK,  rows affected (. sec)
节点
mysql--root@localhost:(none) 18:35:18>>set global group_replication_member_weight=;
Query OK,  rows affected (. sec)
节点
mysql--root@localhost:(none) 18:24:46>>set global group_replication_member_weight=;
Query OK,  rows affected (. sec)

现在的权重是:24801--45 24802--40 24803--50

当我们此时断开24802,我们可以看到如下结果:

mysql--root@localhost:(none) 19:00:40>>select variable_name,member_id,member_host,member_port from performance_schema.global_status a,performance_schema.replication_group_members b where a.variable_value=b.member_id;
+----------------------------------+--------------------------------------+--------------------------+-------------+
| variable_name                    | member_id                            | member_host              | member_port |
+----------------------------------+--------------------------------------+--------------------------+-------------+
| group_replication_primary_member | 994d8dcd-3400-11e9-a3ed-0050568fc57a | tk-dba-mysql-stat-- |       24803 |
+----------------------------------+--------------------------------------+--------------------------+-------------+
 row in set (. sec)


从节点上面看:
mysql--root@localhost:(none) 19:02:19>>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST              | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| group_replication_applier | fff1c--e9-be96-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | d8dcd--e9-a3ed-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 rows in set (. sec)

可以发现24803已经被选举为新的主节点。

自增列测试

实例中的auto_increment_increment跟auto_increment_offset描述的是自增步长的配置情况。auto_increment_increment,在GROUP中范围在1-9(因为一个GROUP最多只能有9个组成员),GROUP中安装的时候,默认为7;

mysql--root@localhost:(none) 19:38:55>>show variables like '%auto_incr%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| auto_increment_increment                   | 7     |
| auto_increment_offset                      |       |
| group_replication_auto_increment_increment | 7     |
+--------------------------------------------+-------+
 rows in set (. sec)

创建测试表,然后插入一部分测试数据:

mysql--root@localhost:mgr 19:43:44>>show create table tb1\G
*************************** . row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int() NOT NULL AUTO_INCREMENT,
  `name` varchar() DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 row in set (. sec)

mysql--root@localhost:mgr 19:43:52>>insert into tb1(name) select @@server_id;
Query OK,  row affected (. sec)
Records:   Duplicates:   Warnings: 

mysql--root@localhost:mgr 19:43:56>>insert into tb1(name) select @@server_id;
Query OK,  row affected (. sec)
Records:   Duplicates:   Warnings: 

mysql--root@localhost:mgr 19:43:57>>insert into tb1(name) select @@server_id;
Query OK,  row affected (. sec)
Records:   Duplicates:   Warnings: 

mysql--root@localhost:mgr 19:43:58>>select * from tb1;
+----+-------+
| id | name  |
+----+-------+
|   | 24803 |
| 14 |  |
|  | 24803 |
+----+-------+
 rows in set (. sec)

更改上面的自增长步长变量,注意修改的时候,需要在停止复制状态下修改,否则会报错:

mysql--root@localhost:mgr 19:56:17>>show variables like '%auto_incr%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| auto_increment_increment                   | 3     |
| auto_increment_offset                      |      |
| group_replication_auto_increment_increment | 3     |
+--------------------------------------------+-------+
 rows in set (. sec)

mysql--root@localhost:mgr 19:56:47>>truncate table tb1;
Query OK,  rows affected (. sec)

mysql--root@localhost:mgr 19:57:12>>insert into tb1(name) select @@server_id;
Query OK,  row affected (. sec)
Records:   Duplicates:   Warnings: 

mysql--root@localhost:mgr 19:57:15>>insert into tb1(name) select @@server_id;
Query OK,  row affected (. sec)
Records:   Duplicates:   Warnings: 

mysql--root@localhost:mgr 19:57:16>>insert into tb1(name) select @@server_id;
Query OK,  row affected (. sec)
Records:   Duplicates:   Warnings: 

mysql--root@localhost:mgr 19:57:16>>select * from tb1;
+----+-------+
| id | name  |
+----+-------+
|   | 24803 |
|  4 |  |
|   | 24803 |
+----+-------+
 rows in set (. sec)

可以发现,现在已经按照步长是3开始自增长了。

节点的接入

启动另外一个服务,端口为24804,目录结构和group当中的节点保持一致,配置文件名称为s4.cnf,需要注意的是s4中需要在loose-group_replication_local_address和loose-group_replication_group_seeds参数处添加该节点的信息。s4的配置文件如下:

[mysqld]
# server configuration
datadir=/data/data_mgr/s4
basedir=/usr/local/mysql-5.7.-linux-glibc2.-x86_64

port=
socket=/data/data_mgr/s4/s4.sock
server_id=
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=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="1bb1b861-f776-11e6-be42-782bcb377193"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "127.0.0.1:24904"
loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903,127.0.0.1:24904"
loose-group_replication_bootstrap_group= off

先对数据库服务进行初始化,后续启动服务,安装插件,设置相关参数:

#先对数据库服务进行初始化:
/usr/local/mysql-5.7.25-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/data/data_mgr/s4/s4.cnf --basedir=/usr/local/mysql-5.7.25-linux-glibc2.12-x86_64 --datadir=/data/data_mgr/s4 --initialize-insecure &

#然后启动数据库服务:
/bin/sh /usr/local/mysql-5.7.25-linux-glibc2.12-x86_64/bin/mysqld_safe --defaults-file=/data/data_mgr/s4/s4.cnf

#安装必要的插件:
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

#设置相关的group_
set global group_replication_allow_local_disjoint_gtids_join=on;

该实例启动好了之后,需要在group的其他三个节点上面设置全局变量group_replication_group_seeds的值,使得group中所有的成员参数如下:

mysql--root@localhost:mgr 19:57:19>>select @@group_replication_group_seeds;
+-----------------------------------------------------------------+
| @@group_replication_group_seeds                                 |
+-----------------------------------------------------------------+
| 127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903,127.0.0.1:24904 |
+-----------------------------------------------------------------+
 row in set (. sec)

然后开启group_replication即可:

mysql--root@localhost:(none) 18:59:37>>start group_replication;
Query OK,  rows affected,  warning (3.24 sec)

mysql--root@localhost:(none) 19:49:47>>select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST              | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
| group_replication_applier | c58505e--e9-a6b6-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | fff1c--e9-be96-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | cdb2b--e9-e5-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
| group_replication_applier | d8dcd--e9-a3ed-8fc57a | tk-dba-mysql-stat-10-104 |        | ONLINE       |
+---------------------------+--------------------------------------+--------------------------+-------------+--------------+
 rows in set (. sec)

删除节点只需要stop group_replication即可。

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447),作者:AsiaYe

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-02-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL删除了记录发现不生效?可能是这个原因

    上周五进行了一个大表删除的操作,在删除的过程中,出现了一点小问题,白白花费了两个小时,我这里记录了一下大概的过程,废话不多说了,直接看过程吧。

    AsiaYe
  • 线上MySQL部署的一个问题

    01 问题起因 目前在线上安装MySQL现在都是通过平台化操作的,平台化的后台操作逻辑也是将安装的脚本直接运行。昨天在安装的过程中总是出现错误,错误的提示信息...

    AsiaYe
  • MySQL索引的一些小细

    忙活了一天,晚上抽空做了几个关于索引和主键的小测试,记录一下,希望对大家有帮助。(所有测试都是在MySQL5.7的环境下测试的)

    AsiaYe
  • Ubuntu系统,搭建mysql+sphinx环建

    本文介绍下如何在ubuntu系统中,利用apt工具,搭建mysql+sphinx的全文检索环境。

    王亚昌
  • 腾讯云服务器 拼起来

    链接: https://cloud.tencent.com/act/group/amd/detail?group=57636

    用户3013987
  • 分分钟搭建MySQL Group Replication测试环境(r11笔记第82天)

    最近看了下MySQL 5.7中的闪亮特性Group Replication,也花了不少做了些测试,发现有些方面的表现确实不赖。当然要模拟这么一套环境还是需...

    jeanron100
  • mysql查找出现出现两次的项半路遇到迪威贵宾会I8Io883oo

    mysql> select a, count(*) from x group by a;

    用户5782834
  • 气象人开发的高级科学绘图库Proplot!

    Proplot对matplotlib进行了高度的封装,是一个高级绘图工具,其功能相当强大!而且融和了cartopy、basemap、xarray和pandas。...

    气象学家
  • 一行代码自动调参,支持模型压缩指定大小,Facebook升级FastText

    FastText 是 Facebook 开源的一款自然语言处理机器学习框架。通过这套工具,用户可以快速完成诸如文本分类等的相关任务,而且不需要设计模型架构。近日...

    机器之心
  • java原生实现屏幕设备遍历和屏幕采集(捕获)等功能

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。 ...

    eguid

扫码关注云+社区

领取腾讯云代金券