MySQL组复制技术实现与数据库性能测试工具-mysql 复制数据库

【51CTO.com原创稿件】MySQL 当前存在的三种复制模式有：异步模式、半同步模式和组复制模式。本次主要调研 MySQL 组复制技术实现，与数据库性能测试工具使用。

测试环境

本文档是在 99Cloud Lab OpenStack 平台虚机上面测试，仅供参考。

系统： CentOS 7.3

虚机： 2 核 4G

版本： MySQL 5.7

技术架构

MySQL Group Replication（简称 MGR）是官方推出的高可用解决方案，原生复制技术，基于插件的方式工作。其中 single primary mode 单主模式只有一个读写，其余都是只读。

multi primary mode多主模式全部可读写

不管组复制单主还是多主的故障切换都无法让应用无感知，需要自主实现，包含以下特性：

复制管理操作更为自动化。
通过 Paxos 协议提供数据库集群节点数据强一致性保证。
多主模式所有节点都可读写操作。
解决网络分区导致的脑裂问题，提升复制数据的可靠性。

一些不足

01、官方引言

Quite obviously, regardless the mode Group Replication is deployed, it does not handle client-side fail-over. That must be handled by the application itself, connector or a middleware framework such as a proxy or router.

意思就是 MGR 内部没有提供一种机制来实现主节点故障切换对应用的无感知。应用的这种故障无感知需要借助外力实现。

02、组复制的局限

所有涉及的数据都必须发生在 InnoDB 存储引擎的表内。

所有的表必须有明确的主键定义。
网络地址只支持 IPv4。
需要低延迟，高带宽的网络。
目前集群限制最多允许 9 个节点。
必须启用 binlog。
binlog 格式必须是 row 格式。
必须打开 gtid 模式。
复制相关信息必须使用表存储。
事务写集合（Transaction write set extraction）必须打开。
log slave updates 必须打开。
binlog 的 checksum 目前不支持。
由于事务写集合的干扰，无法使用 savepoint。
serializable 隔离级别目前不支持。
对同一个对象，在集群中不同的实例上，并行地执行 DDL（哪怕是相互冲突的DDL）是可行的，但会导致数据一致性等方面的错误，目前阶段不支持在多节点同时执行同一对象的 DDL。
外键的级联约束操作目前的实现并不完全支持，不推荐使用。

实验搭建

01、实验环境

主机名	主机地址	组复制端口
mysql01	172.16.200.4	33061
mysql02	172.16.200.6	33062
mysql03	172.16.200.9	33063

02、安装软件

数据库主配文件定义

datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock 
report_host = 172.16.200.4 
max_connections = 4096 
# Disabling symbolic-links is recommended to prevent assorted security risks 
symbolic-links=0 
log-error=/var/log/mysqld.log 
pid-file=/var/run/mysqld/mysqld.pid 
server_id=1 
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 
# Group Replication configuration 
transaction_write_set_extraction=XXHASH64 
loose-group_replication_group_name="09f09aca-2ee4-44f7-b4bb-7d5fe32b52a4"  
loose-group_replication_start_on_boot=off 
loose-group_replication_local_address= "172.16.200.4:33061" 
loose-group_replication_group_seeds= "172.16.200.9:33063,172.16.200.4:33061,172.16.200.6:33062" 
loose-group_replication_bootstrap_group= off 
#loose-group_replication_single_primary_mode=FALSE 
#loose-group_replication_enforce_update_everywhere_checks= TRUE

03、复制授权

进入数据库

mysql> SET SQL_LOG_BIN=0; 
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl_pass'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%'; 
mysql> FLUSH PRIVILEGES; 
mysql> SET SQL_LOG_BIN=1; 
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass'      FOR CHANNEL 'group_replication_recovery';

04、启动组复制

安装插件

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

启动复制通道

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

查看集群成员

mysql> SELECT * FROM performance_schema.replication_group_members;

创建测试同步数据

mysql> CREATE DATABASE test; 
mysql> USE test; 
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); 
mysql> INSERT INTO t1 VALUES (1, 'Luis');

检查同步数据和 Binlog

05、加入集群

其余节点加入集群

1）先定义主配文件与主机 hosts

2）建立通道授权

SET SQL_LOG_BIN=0; 
CREATE USER rpl_user@'%' IDENTIFIED BY 'rpl_pass' ; 
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass'; 
SET SQL_LOG_BIN=1; 
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass'  
    FOR CHANNEL 'group_replication_recovery';

3）添加插件

mysql>INSTALLPLUGINgroup_replicationSONAME'group_replication.so';

4）启动复制通道

mysql>STARTGROUP_REPLICATION;

5）查看集群成员

mysql>SELECT*FROMperformance_schema.replication_group_members;

06、集群验证

在各个节点都能看到同步的数据，说明集群搭建成功。

查看集群中的主节点

mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME= 'group_replication_primary_member';

性能测试

01、工具选型

对于 MySQL 压力测试，大多用 sysbench/tpcc-mysql 工具，主要针对数据库 OLTP（在线事务处理）指标。

在编辑 tpcc-mysql 环境时依赖 mysql_config，MySQL 5.7 版本以后 mysql_config 更换为了 pkg--config，明显 tpcc-mysql 跟不上节奏。

本文档性能测试工具选用sysbench 1.0.6

02、工具用法

准备测试数据

sysbencholtp_read_write.lua --mysql-host=172.16.200.4 --mysql-port=3306 --mysql-db=test --mysql-user=root --mysql-password=MyNewPass4! --table_size=5000 --tables=10 --threads=300 --time=60 --report-interval=10 prepare

开始压测

清除测试数据

03、压测 screen view

准备测试数据

集群测试

单机测试

04、测试结果

	tps	qps	备注
单机	576.86	11617.78
集群(3节点single mode)	263.48	7507.35	单个IP读写
集群(3节点multi mode)	307.19	8343.02	单个IP读写

调研结果

组复制模式配置比异步复制，半同步复制简单便捷，数据同步效率也较高，组复制以插件形式工作。不管组复制单主还是多主的故障切换都无法让应用无感知，需要自主实现。

参考文档

单库安装：https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html

集群安装：https://dev.mysql.com/doc/refman/5.7/en/group-replication-deploying-in-single-primary-mode.html

【51CTO原创稿件，合作站点转载请注明原文作者和出处为51CTO.com】