CentOS 7.6配置MySQL 5.7 MGR单主高可用+ProxySQL实现读写分离和故障转移

一、 相关概念介绍：

1、 MGR 简介：

MySQL Group Replication （简称 MGR ）是 MySQL 官方推出的一种基于 paxos 协议的状态机复制，实现了分布式下数据的最终一致性。 MySQL 组复制提供了高可用、高扩展、高可靠的 MySQL 集群解决方案。相关网址 https://dev.mysql.com/doc/refman/5.7/en/group-replication.html 。

2、 MGR 特性：

（1） 高一致性：基于原生复制及 paxos 协议的组复制技术，并以插件的方式提供，保证数据一致性

（2） 高容错性：只要不是大多数节点宕机就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置自动化脑裂防护机制

（3） 高扩展性：节点的新增和移除都是自动的，新节点加入后，会自动从其它节点同步状态，直到新节点和其它节点保持一致，如果某节点被移除了，其它节点自动更新组信息

（4） 高灵活性：有单主模式和多主模式，单主模式下会自动选择主节点，所有更新操作都在主节点上进行；多主模式下所有 server 都可以同时处理更新操

3、 MGR 局限性：

（1） 仅支持 InnoDB 表，并且每张表一定要有一个主键，用于 write set 的冲突检测

（2） 必须启用 GTID 特性，二进制日志格式必须设置为 ROW ，用于选主与 write set

（3） COMMIT 可能会导致失败，类似于快照事务隔离级别的失败场景

（4） 目前一个 MGR 集群最多支持 9 个节点

（5） 不支持外键于 save point 特性，无法做全局间的约束检测与部分回滚

（6） 二进制日志不支持 binlog event checksum

4、 MGR 集群架构：

5、 MGR 与其它复制方式的对比：

（1） MySQL 异步复制：

master 节点事务的提交不需要经过 slave 节点的确认， slave 节点是否接收到 master 节点的 binlog ， master 节点并不关心。 slave 节点接收到 master 节点的 binlog 后先写 relay log ，最后异步地去执行 relay log 中的 sql 应用到本地。由于 master 节点的提交不需要确保 slave 节点的 relay log 是否被正确接收，当 slave 节点接收 master 节点的 binlog 失败或 relay log 应用失败， master 节点无法感知。假设 master 节点发生宕机且 binlog 还未被 slave 节点接收，而切换程序将 slave 节点提升为新的 master 节点，就会出现数据不一致的情况。另外，在高并发的情况下，传统的主从复制， slave 节点可能会与 master 节点产生较大的延迟。

（2） MySQL 半同步复制：

基于传统异步复制存在的缺陷， MySQL 在 5.5 版本中推出半同步复制，可以说半同步复制是传统异步复制的改进，在 master 节点事务 commit 之前，必须确保一个 slave 节点收到 relay log 且响应给 master 节点以后，才能进行事务的 commit ，但是 slave 节点对于 relay log 的应用仍然是异步进行的。

（3） MySQL 组复制：

基于传统异步复制和半同步复制的缺陷，即数据的一致性问题无法保证， MySQL 官方在 5.7.17 版本中正式推出组复制（ MySQL Group Replication ，简称 MGR ）。由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（ N/2+1 ）决议并通过，才能得以提交。由三个节点组成一个复制组， Consensus 层为一致性协议层，在事务提交过程中，发生组间通讯，由两个节点决议（ certify ）通过这个事务，事务才能最终得以提交并响应。一个复制组由若干个节点（数据库实例）组成，组内各个节点维护各自的数据副本，通过一致性协议实现原子消息和全局有序消息，来实现组内实例数据的一致。

6、 ProxySQL 简介：

ProxySQL 是 MySQL 的一款中间件产品，是灵活强大的 MySQL 代理层，可以实现读写分离，可自定义基于用户、基于 schema 、基于语句的规则对 SQL 语句进行路由，支持动态指定某个 SQL 进行缓存，支持动态加载配置、故障转移和一些 SQL 的过滤功能，可以实现简单的 sharding 。

7、 ProxySQL 的多层配置系统：

ProxySQL 有一套很完整的配置系统，方便 DBA 对线上的操作。整套配置系统分为三层，顶层为 RUNTIME ，中间层为 MEMORY ，底层也就是持久层的 DISK 和 CONFIG FILE 。配置结构如下图所示：

（1） RUNTIME ： ProxySQL 当前生效的生产环境正在使用的配置，包括 global_variables 、 mysql_servers 、 mysql_users 、 mysql_query_rules 等，无法直接修改 RUNTIME 的配置，必须要从下一层“ load ”进来，也就是说 RUNTIME 这个顶级层，就是 ProxySQL 运行过程中实际采用的那一份配置，这一份配置就是要影响实际生产环境的。

（2） MEMORY ：用户可以将 MySQL 客户端连接到此接口（ admin 接口），并查询不同的表和数据库是在 MySQL 命令行修改的 main 数据库中的配置，可以认为是 SQLite 数据库在内存的镜像，也就是说 MEMORY 这个中间层，上面连接着生产配置项 RUNTIME 层，下面连接着持久化层 DISK 和 CONFIG FILE 。 MEMORY 也是修改 ProxySQL 的唯一正常入口。修改一个配置一般都是先在 MEMORY 层完成，确认无误后再加载到 RUNTIME 和持久化到磁盘上，也就是说在这层可以正常操作 ProxySQL 配置， MEMORY 层中的配置修改不会影响生产环境，也不影响磁盘中保存的数据，通过此接口可以修改 main 数据库中以 mysql_ 开头的表（如： mysql_servers 、 mysql_users 、 mysql_query_rules ）和 global_variables 表等。

（3） DISK 和 CONFIG FILE ：持久化配置信息，一般保存在 /var/lib/proxysql/proxysql.db 数据文件中，重启后内存中的配置信息会丢失，所以需要将配置信息保留在磁盘中。重启时，可以从磁盘快速加载回来。配置文件 /etc/proxysql.cnf 只在第一次初始化时用到。 DISK 和 CONFIG FILE 这一层是持久化层，做的任何配置更改，如果不持久化，重启后，配置将丢失。

8、 ProxySQL 相关网址：

（1） GitHub 官网： https://github.com/sysown/proxysql/releases

（2） Percona 官网： https://www.percona.com/downloads/proxysql/

二、 准备工作（四个节点都需要执行如下操作）：

1、 演示环境：

IP	操作系统	主机名	角色	软件包版本	安装方式
192.168.1.143	CentOS   7.6 x86_64	node1	proxysql	2.0.4-percona-1.2	yum
192.168.1.144	CentOS   7.6 x86_64	node2	master	5.7.26-log   MySQL Community Server	rpm
192.168.1.145	CentOS   7.6 x86_64	node3	slave1	5.7.26-log   MySQL Community Server	rpm
192.168.1.146	CentOS   7.6 x86_64	node4	slave2	5.7.26-log   MySQL Community Server	rpm

2、 实验架构：

3、 关闭 SELinux 和 firewalld

4、 配置 epel 源

5、 配置节点时间同步

6、 配置主机名

7、 配置 /etc/hosts 文件：

# vim /etc/hosts

192.168.1.143 node1

192.168.1.144 node2

192.168.1.145 node3

192.168.1.146 node4

8、 下载 MySQL 5.7 软件包， https://dev.mysql.com/downloads/mysql/5.7.html#downloads

三、 构建 MGR 单主高可用集群（如未特殊说明，在 master 、 slave1 和 slave2 节点中分别执行如下操作）：

1、 安装配置 MySQL ：

（1） 卸载 CentOS 7.6 自带的 MariaDB ：

# rpm -qa | grep -i mariadb --> mariadb-libs-5.5.60-1.el7_5.x86_64

# rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64

（2） 如果之前安装过 MySQL ，先卸载： # rpm -qa | grep -i mysql

（3） 如果存在 /etc/my.cnf 配置文件，先删除： # rm -rf /etc/my.cnf

（4） 将软件包上传至主机 /tmp 目录下：

mysql-community-client-5.7.26-1.el7.x86_64.rpm

mysql-community-libs-5.7.26-1.el7.x86_64.rpm

mysql-community-common-5.7.26-1.el7.x86_64.rpm

mysql-community-libs-compat-5.7.26-1.el7.x86_64.rpm

mysql-community-devel-5.7.26-1.el7.x86_64.rpm

mysql-community-server-5.7.26-1.el7.x86_64.rpm

（5） 安装 MySQL 软件包： # yum -y localinstall *.rpm

（6） 初始化 MySQL 数据： # mysqld --initialize --user=mysql --datadir=/var/lib/mysql

备注：初始化之前确保 /var/lib/mysql 目录为空

（7） 修改 MySQL 配置文件：

# mv /etc/my.cnf /etc/my.cnf.bak

# vim /etc/my.cnf

master 节点：

[mysqld]

port=3306

socket=/var/lib/mysql/mysql.sock

datadir=/var/lib/mysql

pid-file=/var/run/mysqld/mysqld.pid

log-error=/var/log/mysqld.log

lower_case_table_names=1

character_set_server=utf8mb4

collation_server=utf8mb4_general_ci

innodb_file_per_table=1

skip_name_resolve=1

slow_query_log=1

slow_query_log_file=mysql-slow.log

symbolic-links=0

explicit_defaults_for_timestamp=1

log_bin=mysql-bin

log_bin_index=mysql-bin.index

binlog_format=row

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

备注：

a、 slave1 节点中 server_id=2 ，增加 read_only=1 ，其它参数保持不变

b、 slave2 节点中 server_id=3 ，增加 read_only=1 ，其它参数保持不变

（8） 启动 MySQL 服务：

# systemctl start mysqld.service

# ss -tunlp | grep mysqld

# systemctl enable mysqld.service

# systemctl status mysqld.service

（9） 查看 root@localhost 用户的初始密码： # grep password /var/log/mysqld.log

（10） 配置 MySQL 安全向导： # mysql_secure_installation

2、 安装 group_replication 插件：

# mysql -uroot -p

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

mysql> show plugins;

备注：插件位于 /usr/lib64/mysql/plugin/group_replication.so

3、 修改 MySQL 配置文件，增加 MGR 相关配置：

# vim /etc/my.cnf

master 节点：

[mysqld]

transaction_write_set_extraction=XXHASH64

group_replication_group_name='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'

group_replication_start_on_boot=off

group_replication_local_address='192.168.1.144:24901'

group_replication_group_seeds='192.168.1.144:24901,192.168.1.145:24901,192.168.1.146:24901'

group_replication_bootstrap_group=off

group_replication_enforce_update_everywhere_checks=off

group_replication_single_primary_mode=on

# systemctl restart mysqld.service

备注 1 ：

（1） slave1 节点中 group_replication_local_address='192.168.1.145:24901' ，其它参数保持不变

（2） slave2 节点中 group_replication_local_address='192.168.1.146:24901' ，其它参数保持不变

备注 2 ：参数说明

（1） gtid_mode=on ：启用 GTID 模式

（2） enforce_gtid_consistency=on ：启用强制 GTID 一致性

（3） master_info_repository=table ：将 master.info 元数据保存在系统表中

（4） relay_log_info_repository=table ：将 relay.info 元数据保存在系统表中

（5） binlog_checksum=none ：禁用二进制日志事件校验

（6） log_slave_updates=on ：启用级联复制

（7） transaction_write_set_extraction=XXHASH64 ：使用 XXHASH64 哈希算法将其编码为散列

（8） group_replication_group_name='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' ：加入或创建的组名称，值必须是有效的 UUID

（9） group_replication_start_on_boot=off ： server 启动时不自动启动组复制

（10） group_replication_local_address='192.168.1.144:24901' ：该节点的 MGR 模块通信地址，自定义端口 24901 用于与组中的其它成员进行内部通信

（11） group_replication_group_seeds='192.168.1.144:24901,192.168.1.145:24901,192.168.1.146:24901' ：组成员的 IP 和端口

（12） group_replication_bootstrap_group=off ：不执行 MGR 复制组的初始化操作，如果该参数设置为 on ，表示会创建一个 MGR 复制组，在此处 master 是第一个初始化节点，动态开启该参数，然后再启动 MGR

（13） group_replication_enforce_update_everywhere_checks=off ：该参数设置为 off ，表示启用单主模式，设置为 on ，表示启用多主模式

（14） group_replication_single_primary_mode=on ：该参数设置为 on ，表示启用单主模式，设置为 off ，表示启用多主模式

4、 创建具有复制权限的用户 repluser ：

mysql> set sql_log_bin=0;

mysql> create user 'repluser'@'192.168.1.%' identified by '123456';

mysql> grant replication slave on *.* to 'repluser'@'192.168.1.%';

mysql> flush privileges;

mysql> set sql_log_bin=1;

备注：组复制使用异步复制协议来实现分布式恢复，在将组成员加入组之前将其同步，该用户为故障恢复的异步复制通道 group_replication_recovery 所用，复制通道用于在组成员之间传输事务

5、 构建 MGR 集群：

mysql> change master to master_user='repluser',master_password='123456' for channel 'group_replication_recovery';

6、 启动 MGR 集群：

master 节点执行如下操作：

mysql> set global group_replication_bootstrap_group=on;

mysql> start group_replication;

mysql> set global group_replication_bootstrap_group=off;

slave1 和 slave2 节点分别执行如下操作，加入复制组：

mysql> set global group_replication_allow_local_disjoint_gtids_join=on;

mysql> start group_replication;

备注：

（1） set global group_replication_bootstrap_group=on ：只需在 master 节点中执行，表示以后加入集群的成员以 master 节点为准，且只需执行一次

（2） set global group_replication_bootstrap_group=off ：确保 master 节点下一次启动时不会再进行初始化，导致复制组出现分裂

7、 查看 MGR 状态信息：

mysql> select * from performance_schema.replication_group_members;

备注：按照 MEMBER_ID 进行升序排序，每个节点执行结果相同

mysql> select * from performance_schema.replication_connection_status\G

master 节点：

slave1 节点：

slave2 节点：

mysql> show global variables like '%group_replication%';

mysql> select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;

master 节点：

备注： is_primary_mode 的值为 1 说明此节点为 master 节点

slave1 节点：

slave2 节点：

8、 master 节点创建测试数据：

mysql> create database db;

mysql> use db;

mysql> create table tb(id int unsigned auto_increment primary key not null,age int not null);

mysql> desc tb;

mysql> insert into tb(age) values(35),(40);

mysql> select * from tb;

9、 slave1 和 slave2 节点分别查看测试数据：

mysql> show databases like 'db';

mysql> select * from db.tb;

四、 安装配置 ProxySQL （如未特殊说明，在 proxysql 节点中执行如下操作）：

1、 配置 percona 源：

# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# yum repolist

2、 安装 MySQL 客户端工具： # yum -y install Percona-Server-client-57

3、 安装 ProxySQL ： # yum list | grep -i percona  # yum -y install proxysql2  # proxysql --version

备注： ProxySQL 相关文件

（1） 配置文件： /etc/proxysql-admin.cnf 、 /etc/proxysql.cnf

（2） SQLite 数据文件： /var/lib/proxysql/proxysql.db

（3） 日志文件： /var/lib/proxysql/proxysql.log

备注：如果存在 proxysql.db 数据文件，则 ProxySQL 启动过程中将不会读取 proxysql.cnf 配置文件的内容来初始化 ProxySQL

4、 启动 ProxySQL ：

# systemctl start proxysql.service

# ss -tunlp | grep proxysql

备注： 6032 是 ProxySQL 的管理端口号， 6033 是对外服务的端口号

5、 使用默认的 admin 用户（密码也为 admin ）登录 ProxySQL 管理界面：

# mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql> '

备注：如果需要创建一个新用户 marion ，密码为 123456 ，则可执行如下操作

proxysql> update global_variables set variable_value='admin:admin;marion:123456' where variable_name='admin-admin_credentials';

proxysql> load admin variables to runtime;

proxysql> save admin variables to disk;

备注：无论执行任何操作，都需要执行 load *** to runtime （从 memory 加载到 runtime ）、 save *** to disk （持久化到磁盘）

6、 master 节点创建 ProxySQL 的监控用户 monitor 和对外访问用户 proxysql ，并赋予权限：

mysql> create user 'monitor'@'192.168.1.%' identified by '123456';

mysql> grant all on *.* to 'monitor'@'192.168.1.%';

mysql> create user 'proxysql'@'192.168.1.%' identified by '123456';

mysql> grant all on *.* to 'proxysql'@'192.168.1.%';

mysql> flush privileges;

7、 添加主从服务器列表：

proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'192.168.1.144',3306,1,'master'),(10,'192.168.1.145',3306,1,'slave1'),(10,'192.168.1.146',3306,3,'slave2');

proxysql> load mysql servers to runtime;

proxysql> save mysql servers to disk;

proxysql> select * from mysql_servers;

备注：

（1） 表 mysql_servers ：后端可以连接 MySQL 主机的列表

（2） 所有节点都是 ONLINE 状态

（3） slave2 节点的查询权重调整为 3 ，为了让更多的读请求路由到这台配置更好的主机上

（4） 表 mysql_servers 常用字段说明：

字段	说明
hostgroup_id	ProxySQL 通过 hostgroup 的形式组织后端 db 实例，一个 hostgroup 代表同属于一个角色，默认为 0
hostname	后端实例 IP
port	后端实例监听端口，默认为 3306
status	后端实例状态，默认为 online ，可取值为： Ø     online ：当前后端实例状态正常 Ø     shunned ：临时被剔除，可能因为后端 too many connections error ，或者超过了可容忍延迟阀值 max_replication_lag Ø     offline_soft ：“软离线”状态，不再接收新的连接，但已建立的连接会等待活跃事务完成 Ø     offline_hard ：“硬离线”状态，不再接收新的连接，已建立的连接或被强制中断，当后端实例宕机或网络不可达时，会出现
weight	后端实例权重，默认为 1
max_connections	允许连接到该后端实例的最大连接数，不能大于 MySQL 设置的 max_connections ，如果后端实例 hostname:port 在多个 hostgroup 中，以较大者为准，而不是各自独立允许的最大连接数，默认为 1000
max_replication_lag	允许的最大延迟， master 节点不受此影响，默认为 0 ，如果 >0 ， monitor 模块监控主从延迟大于阀值时，会临时把它变为 shunned
max_latency_ms	mysql_ping 响应时长，大于这个阀值会把它从连接池中剔除（即使是 ONLINE 状态），默认为 0
comment	备注

（5） 查看表中的字段可使用命令 proxysql> show create table mysql_servers\G

（6） 查看表中的数据可使用命令 proxysql> select * from mysql_servers\G

8、 配置和注册监控用户 monitor ，并验证监控信息：

proxysql> set mysql-monitor_username='monitor';

proxysql> set mysql-monitor_password='123456';

proxysql> load mysql variables to runtime;

proxysql> save mysql variables to disk;

proxysql> select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');

proxysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;

proxysql> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;

9、 配置 ProxySQL 主从分组信息：

proxysql> insert into mysql_replication_hostgroups values (10,20,'read_only','proxysql');

proxysql> load mysql servers to runtime;

proxysql> save mysql servers to disk;

备注：

（1） 表 mysql_replication_hostgroups 常用字段说明：

字段	说明
writer_hostgroup	写入组的编号，此处为 10
reader_hostgroup	读取组的编号，此处为 20
check_type	可取值为： 'read_only' 、 'innodb_read_only' 、 'super_read_only' ，默认值为 'read_only'
comment	备注

（2） 查看表中的字段可使用命令 proxysql> show create table mysql_replication_hostgroups\G

（3） 查看表中的数据可使用命令 proxysql> select * from mysql_replication_hostgroups\G

proxysql> select * from mysql_replication_hostgroups;

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;

备注： ProxySQL 会根据 MySQL 中 read_only 的取值将主机进行分组， read_only=0 的 master 节点被分配到编号为 10 的写入组，而 read_only=1 的两个 slave 节点则被分配到编号为 20 的读取组

10、 配置对外访问用户 proxysql ：

proxysql> insert into mysql_users(username,password,default_hostgroup) values('proxysql','123456',10);

proxysql> load mysql users to runtime;

proxysql> save mysql users to disk;

proxysql> select * from mysql_users\G

备注：

（1） 表 mysql_users ：配置后端数据库用户和监控用户

（2） 表 mysql_users 常用字段说明：

字段	说明
username	前端连接 ProxySQL ，及 ProxySQL 将 SQL 语句路由给后端 MySQL 实例中所使用的用户名
password	后端 MySQL 实例中用户名对应的密码，可以是明文密码，也可以是 hash 加密后的密文，如果想使用 hash 密码，可以先在某个 MySQL 节点中执行 select password(PASSWORD) ，然后将加密结果复制到该字段
active	只有 active=1 的用户才是有效的用户
default_hostgroup	该用户名默认的路由目标，如：指定 root 用户的该字段值为 10 时，则使用 root 用户发送的 SQL 语句默认情况下将路由到 hostgroup_id=10 组中的某个节点，如果该用户的请求没有匹配到规则时，默认发送到这个 hostgroup
default_schema	用户默认登录后端 MySQL 实例时连接的数据库，如果为 NULL ，则由全局变量 mysql-default_schema 决定，默认为 information_schema
transaction_persistent	默认为 1 ，表示启用事务持久化，连接上 ProxySQL 的会话后，如果在一个 hostgroup 中开启了事务，那么在事务提交 / 回滚之前，所有的语句都路由到同一个组中，无论是否会匹配上其它路由规则，直到事务结束，避免语句分散到不同组（更进一步的，它会自动禁用 multiplexing ，让同一个事务的语句从同一个连接路由出去，保证路由到同一个组的同一个节点），避免发生脏读、幻读等情况

（3） 查看表中的字段可使用命令 proxysql> show create table mysql_users\G

（4） 查看表中的数据可使用命令 proxysql> select * from mysql_users\G

11、 验证通过对外服务 6033 端口登录的是 master 节点：

# mysql -uproxysql -p -h192.168.1.143 -P6033 -e 'select @@hostname;'

备注： node2 为 master 节点的主机名

12、 配置路由规则，实现读写分离：

proxysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',10,1),(1,'^select',20,1);

proxysql> load mysql query rules to runtime;

proxysql> save mysql query rules to disk;

proxysql> select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;

备注：

（1） select for update 会产生一个写锁，对数据查询的时效性要求较高，所以将它分配到编号为 10 的写入组

（2） 除了 select for update 以外所有 select 开头的语句全部分配到编号为 20 的读取组

（3） 其它所有操作都默认路由到写入组（ mysql_users 表中字段 default_hostgroup 定义的值）

（4） 表 mysql_query_rules ：指定 query 路由到后端不同主机的规则列表

（5） 表 mysql_query_rules 常用字段说明：

字段	说明
rule_id	表主键，自增，规则处理是以 rule_id 的顺序进行
active	默认为 0 ，值为 1 时表示启用此路由规则
username	如果不为空，该规则只匹配该用户
schemaname	如果不为空，该规则只匹配该数据库名称，如果为 NULL ，不代表连接没有使用 schema ，而是无论任何 schema 都进一步匹配
client_addr	匹配客户端来源 IP
proxy_addr	匹配本地 ProxySQL 的 IP
proxy_port	匹配本地 ProxySQL 的端口
match_digest	描述规则的正则表达式， 1.4 版本以后，正则表达式支持 pcre 和 pcre2 两种，默认使用 pcre ，即 perl 语言支持的正则表达式， query   digest 是指对查询去掉具体值后进行“模糊化”后的查询
match_pattern	正则表达式匹配查询
negate_match_pattern	反向匹配，相当于对 match_digest/match_pattern 的匹配取反，默认为 0
re_modifiers	修改正则匹配的参数，如默认的：忽略大小写 CASELESS 、禁用 GLOBAL ，默认为 CASELESS
destination_hostgroup	该路由规则发往哪个组
cache_ttl	用户查询缓存的时间阈值，单位为毫秒
timeout	这一类查询执行的最大时间（单位为毫秒），超时则自动 kill ，这是对后端 db 的保护机制，默认 mysql-default_query_timeout 的值是 10h
retries	语句在执行失败时，重试的次数，默认由 mysql-query_retries_on_failure 变量指定，默认为 1
delay	查询延迟执行，这是 ProxySQL 提供的限流机制，会让其它的查询优先执行
apply	默认为 0 ，值为 1 时表示该正则匹配后，将不再接受其它匹配，直接转发
comment	备注

（6） 查看表中的字段可使用命令 proxysql> show create table mysql_query_rules\G

（7）查 看表中的数据可使用命令 proxysql> select * from mysql_query_rules\G

13、 ProxySQL 中的内置数据库及常用表、字段说明：

proxysql> show databases;

备注：默认已处于 main 数据库中

（1） main ：内存配置数据库，即 memory ，表中存放后端 db 实例、用户验证、路由规则等信息

查看 main 数据库中的表： proxysql> show tables from main;

备注：表名以 runtime_ 开头的表示 ProxySQL 当前正在运行的配置内容，不能通过 DML 语句修改，但能从其它层级加载，只能修改对应的不以 runtime_ 开头的表，然后 load *** to runtime （从 memory 加载到 runtime ）、 save *** to disk （持久化到磁盘）

（2） disk ：持久化到磁盘的配置， SQLite 数据文件

查看 disk 数据库中的表： proxysql> show tables from disk;

（3） stats ： ProxySQL 将收集的统计数据存储在 stats 数据库中，包括到后端各命令的执行次数、流量、 processlist 、查询种类汇总 / 执行时间等等

查看 stats 数据库中的表： proxysql> show tables from stats;

a、 表 stats_mysql_commands_counters ：统计各种 SQL 类型的执行次数和时间

表 stats_mysql_commands_counters 常用字段说明：

字段	说明
Command	已执行的 SQL 命令的类型，如： FLUSH 、 INSERT 、 KILL 、 SELECT 等
Total_Time_us	执行该类型命令的总时间（以微秒为单位）
Total_cnt	执行该类型命令的总数
cnt_100us 、 cnt_500us 、 cnt_1ms 、 cnt_5ms 、 cnt_10ms 、 cnt_50ms 、 cnt_100ms 、 cnt_500ms 、 cnt_1s 、 cnt_5s 、 cnt_10s 、 cnt_INFs	在指定的时间内执行该类型的命令总数和前一个命令的总数

b、 表 stats_mysql_connection_pool ：连接后端 MySQL 的连接池信息

表 stats_mysql_connection_pool 常用字段说明：

字段	说明
hostgroup	后端主机所属的主机组，单个后端主机可以属于多个主机组
srv_host	后端主机正在侦听连接的 IP
srv_port	后端主机正在侦听连接的 TCP Port
status	后端实例状态，默认为 online ，可取值为： Ø     online ：当前后端实例状态正常 Ø     shunned ：临时被剔除，可能因为后端 too many connections error ，或者超过了可容忍延迟阀值 max_replication_lag Ø     offline_soft ：“软离线”状态，不再接收新的连接，但已建立的连接会等待活跃事务完成 Ø     offline_hard ：“硬离线”状态，不再接收新的连接，已建立的连接或被强制中断，当后端实例宕机或网络不可达时，会出现
ConnUsed	ProxySQL 当前使用多少个连接来向后端主机发送查询
ConnFree	目前有多少个空闲连接
ConnOK	成功建立了多少个连接
ConnERR	没有成功建立多少个连接
Queries	路由到此特定后端主机的查询数
Bytes_data_sent	发送到后端主机的数据量
Bytes_data_recv	从后端主机接收的数据量
Latency_us	从 monitor 报告的当前 ping （以毫秒为单位）的延迟时间

c、 表 stats_mysql_global ：与 MySQL 相关的代理级别的全局统计

表 stats_mysql_global 常用字段说明：

字段	说明
Client_Connections_aborted	由于无效凭据或 max_connections 而导致的前端连接数已达到
Client_Connections_connected	当前连接的前端连接数
Client_Connections_created	到目前为止创建的前端连接数
Questions	从前端发送的查询总数
Slow_queries	在全局变量中定义的运行时间超过阈值的毫秒数的查询数 mysql-long_query_time

d、 表 stats_mysql_processlist ：类似 MySQL 的 show processlist 的命令，查看各线程的状态

表 stats_mysql_processlist 常用字段说明：

字段	说明
ThreadID	ProxySQL 线程 ID
SessionID	ProxySQL 会话 ID ，通过此 ID 可以进行 kill 操作
user	通过 MySQL 客户端连接 ProxySQL 的用户
db	当前选择的数据库
cli_host	连接 ProxySQL 的 MySQL 客户端 IP
cli_port	连接 ProxySQL 的 TCP 端口
hostgroup	当前主机组，如果正在处理查询，则是查询已被路由或将要路由的主机组，或默认主机组，可以查看该 SQL 到底是路由到哪个主机组中
l_srv_host	ProxySQL 的 IP
l_srv_port	ProxySQL 的 TCP 端口
srv_host	后端 MySQL 服务器的 IP
srv_port	后端 MySQL 服务器的 TCP 端口
command	正在执行的 MySQL 查询类型
time_ms	命令执行的时长（以毫秒为单位）
info	正在执行的 SQL

e、 表 stats_mysql_query_rules ：统计路由命中次数

表 stats_mysql_query_rules 常用字段说明：

字段	说明
rule_id	路由规则的 id 与 main.mysql_query_rules 的 id 对应
hits	此路由规则的匹配总数，如果当前传入的查询符合规则，则会记录一次命中

（4） monitor ：存储 monitor 模块收集的信息，主要是对后端 db 的健康 / 延迟检查

查看 monitor 数据库中的表： proxysql> show tables from monitor;

表名	说明
mysql_server_connect_log	连接所有 MySQL 主机以检查它们是否可用，该表用来存放检测连接的日志，由变量 mysql-monitor_connect_interval 来控制其检测的时间间隔，由参数 mysql-monitor_connect_timeout 控制连接是否超时（默认为 200 毫秒）
mysql_server_ping_log	使用 mysql_ping API ping 后端 MySQL 主机检查它们是否可用，该表用来存放 ping 的日志，由变量 mysql-monitor_ping_interval 控制 ping 的时间间隔，默认为 10000 （单位毫秒，相当于 10 秒）
mysql_server_replication_lag_log	后端 MySQL 服务主从延迟的检测，由参数 mysql-monitor_replication_lag_interval 控制检测时间间隔，由 mysql_servers.max_replication_lag 列控制，默认为 10000 （单位毫秒，相当于 10 秒）

备注：

a、 查看表中的字段可使用命令 proxysql> show create table 表名 \G

b、 查看表中的数据可使用命令 proxysql> select * from 表名 \G

五、 测试 ProxySQL ：

1、 测试读写分离：

（1） 清空 stats_mysql_query_digest 表：

# mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql> '

proxysql> select * from stats_mysql_query_digest_reset;

proxysql> select * from stats_mysql_query_digest;

# mysql -uproxysql -p -h192.168.1.143 -P6033

mysql> select * from db.tb;

mysql> insert into db.tb(age) values(0),(100);

mysql> select * from db.tb for update;

备注：

a、 表 stats_mysql_query_digest ： SQL 的执行次数、时间消耗等

b、 表 stats_mysql_query_digest 常用字段说明：

字段	说明
hostgroup	发送查询的主机组，值为 -1 表示查询的是查询缓存
schemaname	查询的数据库
username	连接 ProxySQL 的用户名
digest_text	参数剥离的实际 SQL 文本
count_star	执行查询的总次数
first_seen	unix 时间戳，查询通过代理路由查询的第一时刻
last_seen	unix 时间戳，查询通过代理路由查询的最后一刻（到目前为止）
sum_time	执行此类查询的总时间（以微秒为单位）
min_time	执行此类查询时期望的持续时间范围， min_time 表示到目前为止所看到的最小执行时间
max_time	执行此类查询时期望的持续时间范围， max_time 表示最大执行时间，以微秒为单位

c、 查看表中的字段可使用命令 proxysql> show create table stats.stats_mysql_query_digest\G

d、 查看表中的数据可使用命令 proxysql> select * from stats.stats_mysql_query_digest\G

e、 表 stats_mysql_query_digest_reset 和表 stats_mysql_query_digest 结构一致

（2） 通过查询 stats_mysql_query_digest 表来监控查询状态：

proxysql> select hostgroup,schemaname,username,digest_text from stats_mysql_query_digest;

备注：

a、 select * from tb for update 语句已经自动路由到编号为 10 的写入组，即 master 节点

b、 select * from tb 语句已经自动路由到编号为 20 的读取组，即 slave 节点

2、 测试读请求负载均衡：

# for i in {1..12}; do mysql -uproxysql -p123456 -h192.168.1.143 -P6033 -e 'select @@hostname' -s -N; done

选项说明：

（1） -s ：以制表符作为分隔符打印结果

（2）- N ：结果中不包含列名

备注：上述命令执行结果可得出读请求在两台 slave 节点间切换，且 node3 和 node4 的出现比例接近 1:3

3、 测试 ProxySQL 故障转移：

（1） 单主模型脚本 gr_sw_mode_checker.sh ， https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker

备注：共有三个脚本提供下载

a、 proxysql_groupreplication_checker.sh ：用于 multi-primary 模式，可以实现读写分离及故障转移，同一时间点多个节点都可以写

b、 gr_mw_mode_cheker.sh ：用于 multi-primary 模式，可以实现读写分离及故障转移，但在同一时间点只能有一个节点可以写

c、 gr_sw_mode_checker.sh ：用于 single-primary 模式，可以实现读写分离及故障转移

（2） 将下载的脚本 gr_sw_mode_checker.sh 放置至 /var/lib/proxysql 目录中，并赋予执行权限和修改属主属组：

# chmod +x /var/lib/proxysql/gr_sw_mode_checker.sh

# chown proxysql.proxysql /var/lib/proxysql/gr_sw_mode_checker.sh

（3） 下载 addition_to_sys.sql ， https://github.com/lefred/mysql_gr_routing_check/

（4） master 节点创建表 sys.gr_member_routing_candidate_status ： # mysql -uroot -p < addition_to_sys.sql

mysql> select * from sys.gr_member_routing_candidate_status;

master 节点：

slave1 节点：

slave2 节点：

（5） 配置 scheduler ：

proxysql> insert into scheduler(active,interval_ms,filename,arg1,arg2,arg3,arg4) values(1,5000,'/var/lib/proxysql/gr_sw_mode_checker.sh',10,20,1,'/var/lib/proxysql/gr_sw_mode_checker.log');

proxysql> load scheduler to runtime;

proxysql> save scheduler to disk;

备注 1 ：

a、 表 scheduler 常用字段说明

字段	说明
active	值为 1 时允许计划程序定期执行提供的脚本，默认为 1
interval_ms	每隔多少毫秒执行一次脚本， 5000 毫秒为 5 秒
filename	脚本的存放路径
arg1~arg4	脚本接收到的输入参数
comment	备注

b、 查看表中的字段可使用命令 proxysql> show create table scheduler\G

c、 查看表中的数据可使用命令 proxysql> select * from scheduler\G

d、 问题排查可查看日志文件 /var/lib/proxysql/gr_sw_mode_checker.log

备注 2 ：脚本 gr_sw_mode_checker.sh 用法

gr_sw_mode_cheker.sh [write node can be read : 1(YES: default) or 0(NO)] [log_file]

a、 arg1 ： hostgroup_id write

b、 arg2 ： hostgroup_id read

c、 arg3 ： write node can be read : 1(YES: default) or 0(NO)

d、 arg4 ： log_file, default: './checker.log'

proxysql> select * from scheduler\G

（6）查 看 MGR 状态信息：

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;

mysql> select * from performance_schema.replication_group_members;

mysql> select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;

master 节点：

备注： is_primary_mode 的值为 1 说明此节点为 master 节点

slave1 节点：

slave2 节点：

（7） master 节点模拟 MySQL 服务故障： # systemctl stop mysqld.service

（8） 查看 MGR 状态信息：

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;

mysql> select * from performance_schema.replication_group_members;

mysql> select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;

slave1 节点：

slave2 节点：

备注：当 master 节点意外宕机或下线，在满足大多数节点存活的情况下， group 内部发起选举，选出下一个可用的读节点，提升其为 master 节点。 master 节点选举根据 group 内剩余存活节点的 UUID 按字典升序排列，然后选择排在最前的节点作为新的 master 节点。 is_primary_mode 的值为 1 说明 slave2 节点已经被选举为新的 master 节点。

mysql> select * from sys.gr_member_routing_candidate_status;

slave1 节点：

slave2 节点：

备注： slave2 节点中的 read_only 参数的值已经自动修改为 0

（9） slave2 节点创建测试数据：

mysql> insert into db.tb(age) values(60),(80);

mysql> select * from db.tb;

（10） slave1 节点查看测试数据：

mysql> select * from db.tb;

（11） 原 master 节点恢复 MySQL 服务： # systemctl start mysqld.service

（12） 原 master 节点加入复制组： mysql> start group_replication;

（13） 查看 MGR 状态信息：

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;

mysql> select * from performance_schema.replication_group_members;

mysql> select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;

原 master 节点：

备注：新上线的原 master 节点现已变为 slave 节点

slave1 节点：

slave2 节点：

mysql> select * from sys.gr_member_routing_candidate_status;

原 master 节点：

备注：原 master 节点中的 read_only 参数的值已经自动修改为 1

slave1 节点：

slave2 节点：

（14） 原 master 节点已经复制了最新的测试数据：

mysql> select * from db.tb;

4、 使用 sysbench 进行压测：

（1） 安装 sysbench ： # yum -y install sysbench

（2） 将用户 proxysql 的 transaction_persistent 值修改为 0 ：

proxysql> update mysql_users set transaction_persistent=0 where username='proxysql';

proxysql> load mysql users to runtime;

proxysql> save mysql users to disk;

（3） prepare ：

# sysbench --threads=8 --events=100000000 --time=2000 --report-interval=5 --mysql-host=192.168.1.143 --mysql-port=6033 --mysql-user=proxysql --mysql-password=123456 --mysql-db=db /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua prepare

备注：常用选项说明

a、 --threads ：使用的线程数

b、 --events ：事件总数

c、 --time ：以秒为单位的总执行时间

d、 --report-interval ：以秒为单位定期报告具有指定间隔的中间统计信息

e、 --mysql-host ： ProxySQL 所在主机的 IP

f、 --mysql-port ： ProxySQL 对外服务的端口号

g、- -mysql-user ： MySQL user

h、 --mysql-password ： MySQL password

i、 --mysql-db ：在 db 数据库中创建表 sbtest1 ，并插入 10000 条数据

（4） run ：

# sysbench --threads=8 --events=100000000 --time=2000 --report-interval=5 --mysql-host=192.168.1.143 --mysql-port=6033 --mysql-user=proxysql --mysql-password=123456 --mysql-db=db /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run

备注：不要 Ctrl + c 终止压测

（5） 压测过程中复制会话，观察读写分离状态：

proxysql> select hostgroup,schemaname,username,digest_text from stats_mysql_query_digest\G

备注：上述命令执行结果可得出写请求都路由到编号为 10 的写入组，而读请求都路由到编号为 20 的读取组

5、 测试查询缓存：

ProxySQL 的查询缓存和 MySQL 的查询缓存有点类似，但不完全一样， ProxySQL 的查询缓存如果在指定时间大小范围内发送的 SQL 一摸一样，那么直接返回结果集，而返回的结果集可能并不是准确的查询结果，所以需要设置合适的时间范围，既能提升性能，又得满足业务需求，即查询结果足够的“新”。针对一些查询频率很高但结果并不需要太精确的业务，可以单独给这些 SQL 配置查询缓存。

（1） 启用查询缓存：

proxysql> update mysql_query_rules set cache_ttl=2000 where active=1 and destination_hostgroup=20;

proxysql> load mysql query rules to runtime;

proxysql> save mysql query rules to disk;

（2） 压测：

# sysbench --threads=8 --events=100000000 --time=2000 --report-interval=5 --mysql-host=192.168.1.143 --mysql-port=6033 --mysql-user=proxysql --mysql-password=123456 --mysql-db=db /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua run

（3） 压测过程中复制会话，观察读写分离状态：

proxysql> select hostgroup,digest_text,sum_time from stats_mysql_query_digest\G

备注：可以看到 hostgroup 的值部分为 -1 ，表示这些查询使用了查询缓存，且耗时为 0

6、 启用 Web 统计功能：

（1） 查看变量 admin-web_enabled 是否启用：

proxysql> select * from global_variables where variable_name like 'admin-web%';

（2） 启用变量 admin-web_enabled ：

proxysql> set admin-web_enabled='true';

proxysql> load admin variables to runtime;

proxysql> save admin variables to disk;

proxysql> select * from global_variables where variable_name like 'admin-web%';

（3） 查看登录 Web 界面的用户名、密码和端口：

proxysql> select * from global_variables where variable_name like 'admin-web%' or variable_name LIKE 'admin-stats%';

（4） 浏览器中访问 192.168.1.143:6080 ，用户名和密码均为 stats ：