MySQL 8.0 MGR With ProxySQL
1 概述
1.1 MySQL MGR
1.1.1 MGR简介
MySQL Group Replication（简称MGR）是MySQL官方推出的一种基于paxos协议的状态机复制，实现了分布式下数据的最终一致性。MySQL组复制提供了高可用、高扩展、高可靠的MySQL集群解决方案。
1.1.2 MGR特性
1) 高一致性：基于原生复制及paxos协议的组复制技术，并以插件的方式提供，保证数据一致性
2) 高容错性：只要不是大多数节点宕机就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置自动化脑裂防护机制
3) 高扩展性：节点的新增和移除都是自动的，新节点加入后，会自动从其它节点同步状态，直到新节点和其它节点保持一致，如果某节点被移除了，其它节点自动更新组信息
4) 高灵活性：有单主模式和多主模式，单主模式下会自动选择主节点，所有更新操作都在主节点上进行；多主模式下所有server都可以同时处理更新操
1.1.3 MGR局限性：
1) 仅支持InnoDB表，并且每张表一定要有一个主键，用于write set的冲突检测
2) 必须启用GTID特性，二进制日志格式必须设置为ROW，用于选主与write set
3) COMMIT可能会导致失败，类似于快照事务隔离级别的失败场景
4) 目前一个MGR集群最多支持9个节点
5) 不支持外键于save point特性，无法做全局间的约束检测与部分回滚
6) 二进制日志不支持binlog event checksum
1.1.4 MGR集群架构
1.1.5 MGR与其它复制方式的对比：
1.1.5.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节点产生较大的延迟。
1.1.5.2 MySQL半同步复制
基于传统异步复制存在的缺陷，MySQL在5.5版本中推出半同步复制，可以说半同步复制是传统异步复制的改进，在master节点事务commit之前，必须确保一个slave节点收到relay log且响应给master节点以后，才能进行事务的commit，但是slave节点对于relay log的应用仍然是异步进行的。
1.1.5.3 MySQL组复制
基于传统异步复制和半同步复制的缺陷，即数据的一致性问题无法保证，MySQL官方在5.7.17版本中正式推出组复制（MySQL Group Replication，简称MGR）。由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N/2+1）决议并通过，才能得以提交。由三个节点组成一个复制组，Consensus层为一致性协议层，在事务提交过程中，发生组间通讯，由两个节点决议（certify）通过这个事务，事务才能最终得以提交并响应。一个复制组由若干个节点（数据库实例）组成，组内各个节点维护各自的数据副本，通过一致性协议实现原子消息和全局有序消息，来实现组内实例数据的一致。
1.2 ProxySQL
1.2.1 简介
ProxySQL是MySQL的一款中间件产品，是灵活强大的MySQL代理层，可以实现读写分离，可自定义基于用户、基于schema、基于语句的规则对SQL语句进行路由，支持动态指定某个SQL进行缓存，支持动态加载配置、故障转移和一些SQL的过滤功能，可以实现简单的sharding。
1.2.2 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这一层是持久化层，做的任何配置更改，如果不持久化，重启后，配置将丢失。
1.2.3 ProxySQL相关网址
（1）GitHub官网：https://github.com/sysown/proxysql/releases
（2）Percona官网：https://www.percona.com/downloads/proxysql/
2 配置规范
2.1 操作系统配置规范
1) 建议关闭selinux,iptables
2) 配置数据库数据默认存储路径为：
/dbdata为数据库数据存储主目录
/dbdata/binlog 存储MySQL的binlog文件
/dbdata/redo 存储MySQL的redo日志文件
/dbdata/undo 存储MySQL的undo日志文件
/dbdata/mysql3306 存储MySQL的数据文件
/dbdata/log 存储MySQL的错误日志文件、慢日志文件
3) 按照3.1.3章节配置优化操作系统参数
2.2 MySQL参数配置规范
1) 按照MySQL版本不同，选择不同的基线配置文件
2) 针对服务器硬件配置的不同调整其中my.cnf的参数设置
#必须根据实际情况修改：
server-id=65 #统一集群ID必须修改不同
read_only=0 #根据节点的不同进行设置
innodb_buffer_pool_size = 8G #通常设置为可用物理内存的20%-40%
innodb_log_file_size = 1G #设置每个日志文件大小与日志组配合设置
innodb_log_files_in_group=4 #设置redo日志组
#集群参数配置相关
group_replication_group_name='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa' #设置集群的编号，可通过mysql 的uuid()函数进行生成。
group_replication_local_address='10.136.11.7:24901' #该节点的MGR模块通信地址，自定义端口24901用于与组中的其它成员进行内部通信
group_replication_group_seeds='10.136.11.7:24901,10.136.11.8:24901,10.136.11.9:24901' #组成员的IP和端口
###以下为按需进行修改：
transaction_isolation = READ-COMMITTED #事务隔离级别，默认为可重复读，按需进行修改配置。
max_connections = 3000 #最大连接数 按需进行修改
interactive_timeout = 1800 #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
wait_timeout = 1800 #MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
connect_timeout=10 #The number of seconds the mysqld server is waiting for a connect packet before responding with 'Bad handshake'
- open_files_limit=65535 ##mysql打开最大文件数
2.3 Proxysql配置规范
Proxyql主要消耗CPU、内存、网络资源，需要按照3.1.3进行参数优化调整。基本配置文件参考基线配置文件。
#必须修改
admin_credentials="admin:admin;cluster_admin:123456" #配置proxysql 自身数据库访问用户和集群管理用户 #
threads=4 #
max_connections=2048
server_version="5.5.30" #修改为支撑的后台MySQL版本
monitor_username="monitor" #MySQL集群状态检测用户名称
monitor_password="monitor" # MySQL集群状态检测用户口令
proxysql_servers 模块中各个服务器上的主机名和别名：
hostname='xxx.xxx.xxx.xxx'
comment='Porxysql-node1'
hostname='xxx.xxx.xxx.xxx'
comment='Porxysql-node1'
#按需修改
datadir="/var/lib/proxysql" #配置proxysql 的数据存储路径
#proxysql集群配置参数设置
cluster_username="cluster_admin"
cluster_password="123456"
2.3.1 注意事项
1） 配置msyql_users 必须设置default_schema,避免客户端工具访问异常。
2） 可按需配置用户的读写分离规则。
3 实施步骤
3.1 系统配置
3.1.1 系统安装及配置
参考《X86架构数据库服务器安装与配置规范》，进行系统初始化安装和配置。
3.1.2 关闭SELinux和firewalld
1、查看防火墙状态。
systemctl status firewalld
2、临时关闭防火墙
systemctl stop firewalld
3、永久关闭防火墙
systemctl disable firewalld
4、关闭selinux
sed-i '/SELINUX/s/enforcing/disabled/' /etc/selinux/config
5、reboot
3.1.3 配置主机参数
echo "调整系统参数设置"
echo "--------------------------------------------------------------------------"
echo "net.core.somaxconn = 2048 " |tee -a /etc/sysctl.conf
echo "net.core.netdev_max_backlog = 10000 " |tee -a /etc/sysctl.conf
echo "net.core.rmem_default = 16777216 " |tee -a /etc/sysctl.conf
echo "net.core.wmem_default = 16777216 " |tee -a /etc/sysctl.conf
echo "net.core.rmem_max = 16777216 " |tee -a /etc/sysctl.conf
echo "net.core.wmem_max = 16777216 " |tee -a /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 3500 65535 " |tee -a /etc/sysctl.conf
echo "net.ipv4.ip_forward = 0 " |tee -a /etc/sysctl.conf
echo "net.ipv4.conf.default.rp_filter = 1 " |tee -a /etc/sysctl.conf
echo "net.ipv4.conf.default.accept_source_route = 0 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_syncookies = 0 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_rmem = 4096 87380 16777216 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_wmem = 4096 65536 16777216 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_max_syn_backlog = 16384 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_fin_timeout = 15 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_max_syn_backlog = 16384 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_tw_reuse = 1 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_tw_recycle = 1 " |tee -a /etc/sysctl.conf
echo "net.ipv4.tcp_slow_start_after_idle=0 " |tee -a /etc/sysctl.conf
echo "vm.swappiness = 0 " |tee -a /etc/sysctl.conf
echo "vm.min_free_kbytes = 2097152 " |tee -a /etc/sysctl.conf
echo "sysctl -p 结果输出："
/sbin/sysctl -p /etc/sysctl.conf
3.1.4 配置节点时间同步
3.1.5 配置主机名
主机名配置范例：
hostnamectl set-hostname node1
hostnamectl set-hostname node2
hostnamectl set-hostname node3
3.1.6 配置/etc/hosts文件：
# vim /etc/hosts
192.168.1.143 node1
192.168.1.144 node2
192.168.1.145 node3
3.1.7 关联目录创建
3.1.7.1 数据文件存储目录
mkdir -p /dbdata/mysql3306/
mkdir -p /dbdata/log
mkdir -p /dbdata/binlog
mkdir -p /dbdata/redo
mkdir –p /dbdata/undo
chown mysql.mysql -R /dbdata/
3.1.8 配置本地ISO YUM源
3.1.8.1 配置系统ISO为yum源
mkdir /yum/
mount /dev/sr0 /mnt
cp -a /mnt/* /yum/
创建并编辑/etc/yum.repos.d/rhel7.repo
内容如下：
[base]
name=base
baseurl=file:///yum/
gpgcheck=0
3.1.8.2 开发测试环境配置远程ftp yum源
创建并编辑/etc/yum.repos.d/rhel7.repo
[base]
name=rhel7.4
baseurl=ftp://10.3.1.200/rhel7.4/
gpgcheck=0
enable=1
3.1.9 配置集群间信任关系
使用root账户执行
3.1.9.1 root用户生成sshkey
[root@node1 ~]# ssh-keygen -t rsa
[root@node2 ~]# ssh-keygen -t rsa
[root@node3 ~]# ssh-keygen -t rsa
[root@mgmt01 ~]# ssh-keygen -t rsa
3.1.9.2 将公钥传输各个节点
node1:
cd /root/.ssh
[root@node1 .ssh]# ssh-copy-id -i id_rsa.pub node1
[root@node1 .ssh]# ssh-copy-id -i id_rsa.pub node2
[root@node1 .ssh]# ssh-copy-id -i id_rsa.pub node3
[root@node1 .ssh]# ssh-copy-id -i id_rsa.pub mgmt01
node2:
cd /root/.ssh
[root@node2 .ssh]# ssh-copy-id -i id_rsa.pub node1
[root@node2 .ssh]# ssh-copy-id -i id_rsa.pub node2
[root@node2 .ssh]# ssh-copy-id -i id_rsa.pub node3
[root@node2 .ssh]#
ssh-copy-id -i id_rsa.pub mgmt01
node3:
cd /root/.ssh
[root@node3 .ssh]# ssh-copy-id -i id_rsa.pub node1
[root@node3 .ssh]# ssh-copy-id -i id_rsa.pub node2
[root@node3 .ssh]# ssh-copy-id -i id_rsa.pub node3
[root@node3 .ssh]# ssh-copy-id -i id_rsa.pub mgmt01
mgmt01:
cd /root/.ssh
[root@mgmt01 .ssh]# ssh-copy-id -i id_rsa.pub node1
[root@mgmt01 .ssh]# ssh-copy-id -i id_rsa.pub node2
[root@mgmt01 .ssh]# ssh-copy-id -i id_rsa.pub node3
[root@mgmt01 .ssh]# ssh-copy-id -i id_rsa.pub mgmt01
3.1.9.3 免密登陆验证
Node1:
Ssh node2
Node2:
Ssh node3
Node3:
Ssh mgmt01
Mgmt01:
Ssh node1
3.2 MySQL MGR 集群部署及测试
（如未特殊说明，在master、slave1和slave2节点中分别执行如下操作）：
3.2.1 安装MySQL：
3.2.1.1 环境清理
1） 清理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
3.2.1.2 将软件包上传至主机/tmp目录下：
mysql-community-client-*.rpm
mysql-community-libs-*.rpm
mysql-community-common-*.rpm
mysql-community-libs-compat*.rpm
mysql-community-devel*.rpm
mysql-community-server-*.rpm
libev-4.15-7.el7.x86_64.rpm\
percona-xtrabackup-80-8.0.9-1.el7.x86_64.rpm
3.2.1.3 安装MySQL软件包
# yum -y localinstall *.rpm
3.2.2 配置mysql参数
# mv /etc/my.cnf /etc/my.cnf.bak
# vim /etc/my.cnf
3.2.2.1 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
备注：
3.2.2.2 slave节点
a、slave1节点中server_id=2，增加read_only=1，其它参数保持不变
b、slave2节点中server_id=3，增加read_only=1，其它参数保持不变
3.2.3 初始化MySQL数据
备注：初始化之前确保/dbdata/mysql目录为空
shell> mysqld --initialize --user=mysql
shell> mysql_ssl_rsa_setup
tail -f mysqld.log 查看root@localhost的密码
vi ~/.my.cnf
[client]
socket=/dbdata/mysql3306/mysql.sock
user=root
password=cVIL_yU<j11t #
host=localhost
3.2.3.1 启动MySQL服务：
# systemctl start mysqld.service
# ss -tunlp | grep mysqld
# systemctl enable mysqld.service
# systemctl status mysqld.service
3.2.3.2 配置MySQL安全向导
# mysql_secure_installation
shell>msyql
mysql> alter user root@localhost identified by 'root';
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
3.2.4 安装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.2.5 修改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，表示启用多主模式
3.2.6 创建具有复制权限的用户repluser
mysql> set sql_log_bin=0;
mysql> create user 'repluser'@'%' identified by 'repluser22$';
mysql> grant replication slave on *.* to 'repluser'@'%';
mysql> flush privileges;
mysql> set sql_log_bin=1;
create user 'admin'@'%' identified by 'hxbk123$';
Query OK, 0 rows affected (0.00 sec) 建立额外管理用户
备注：组复制使用异步复制协议来实现分布式恢复，在将组成员加入组之前将其同步，该用户为故障恢复的异步复制通道group_replication_recovery所用，复制通道用于在组成员之间传输事务
3.2.7 构建MGR集群，配置复制用户：
所有节点执行
mysql>change master to master_user='repluser',master_password='repluser22$' for channel 'group_replication_recovery';
3.2.8 启动MGR集群：
3.2.8.1 master节点执行如下操作：
mysql> set global group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=off;
3.2.8.2 slave1和slave2节点分别执行如下操作，加入复制组：
mysql> set group_replication_allow_local_disjoint_gtids_join=on;
mysql> start group_replication;
3.2.9 查看MGR状态信息：
mysql> select * from performance_schema.replication_group_members;
备注：按照MEMBER_ID进行升序排序，每个节点执行结果相同
mysql> select * from performance_schema.replication_connection_status\G
3.2.9.1 master节点：
select * from performace_schema.replication_connection_status \G ;
3.2.9.2 slave1节点：
select * from performace_schema.replication_connection_status \G ;
3.2.9.3 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;
3.2.9.4 确认主节点信息
master节点：
备注：is_primary_mode的值为1说明此节点为master节点
slave1节点：
slave2节点：
3.2.10 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;
3.2.11 slave1和slave2节点分别查看测试数据：
mysql> show databases like 'db';
mysql> select * from db.tb;
3.3 proxysql部署及测试
3.3.1 安装配置ProxySQL
（如未特殊说明，在proxysql节点中执行如下操作）：
3.3.1.1 配置percona源：
# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# yum repolist
3.3.1.2 安装MySQL客户端工具：
1） 清理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）# yum -y install Percona-Server-client-57
3.3.1.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
3.3.1.4 启动ProxySQL：
# systemctl start proxysql.service
# ss -tunlp | grep proxysql
备注：6032是ProxySQL的管理端口号，6033是对外服务的端口号
3.3.1.5 用户配置
3.3.1.5.1 登录用户
1、使用默认的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（持久化到磁盘）
3.3.1.5.2 Proxysql监控账户
master节点创建ProxySQL的监控用户monitor和对外访问用户proxysql，并赋予权限：
mysql> create user 'monitor'@'%' identified by 'monitor123$';
mysql> grant all on *.* to 'monitor'@'%';
mysql> create user 'proxysql'@'%' identified by 'proxysql123$';
mysql> grant all on *.* to 'proxysql'@'%';
mysql> flush privileges;
3.3.1.6 服务器配置
3.3.1.6.1 添加主从服务器列表：
proxysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port,max_connections,weight,comment) VALUES (10,'10.1.73.18','3306','3000',1,'master')
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment)
values(10,'10.1.73.18',3306,1,'master'),(20,'10.1.73.19',3306,1,'slave1'),(20,'10.1.73.20',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
3.3.1.6.2 配置和注册监控用户monitor，并验证监控信息：
proxysql> set mysql-monitor_username='monitor';
proxysql> set mysql-monitor_password='monitor123$';
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;
3.3.1.6.3 配置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的读取组
3.3.1.6.4 配置对外访问用户proxysql：
proxysql> insert into mysql_users(username,password,default_hostgroup,default_schema) values('proxysql','123456',10, 'mysql');
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
3.3.1.6.5 验证通过对外服务6033端口登录的是master节点：
# mysql -uproxysql -p -h192.168.1.143 -P6033 -e 'select @@hostname;'
备注：node2为master节点的主机名
3.3.1.7 配置路由规则，实现读写分离：
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
3.3.1.8 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、查询种类汇总/执行时间等等
3.3.1.8.1 查看stats数据库中的表：
proxysql> show tables from stats;
3.3.1.8.1.1 表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 |
在指定的时间内执行该类型的命令总数和前一个命令的总数 |
3.3.1.8.1.2 表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（以毫秒为单位）的延迟时间 |
3.3.1.8.1.3 表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 |
3.3.1.8.1.4 表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 |
3.3.1.8.1.5 表stats_mysql_query_rules
统计路由命中次数
表stats_mysql_query_rules常用字段说明：
|
字段 |
说明 |
|
rule_id |
路由规则的id与main.mysql_query_rules的id对应 |
|
hits |
此路由规则的匹配总数，如果当前传入的查询符合规则，则会记录一次命中 |
3.3.1.8.2 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
3.3.2 测试ProxySQL：
3.3.2.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节点
3.3.2.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.3.2.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 <hostgroup_id write> <hostgroup_id read> [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：
4 单主模式与多主模式切换
4.1 单主切换到多主模式
1） 在所有的节点上执行，停止组复制
stop group_replication;
set global group_replication_single_primary_mode=OFF;
set global group_replication_enforce_update_everywhere_checks=ON;
2） 重新配置主复制，选择一个节点先执行:
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
3） 在其他节点执行：
START GROUP_REPLICATION;
4.2 多主模式切换到单主
1） 在所以的数据库节点上执行
stop group_replication;
set global group_replication_enforce_update_everywhere_checks=OFF;
set global group_replication_single_primary_mode=ON;
2） 重新配置主复制，选择一个节点先执行:
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
3） 在其他节点执行：
START GROUP_REPLICATION;
5 异常处理
5.1 集群配置过程异常
5.1.1 处置方式：
1、 将主库备份并在备库还原
2、 查询主备节点
|
mysql> select @@global.gtid_executed ; | @@global.gtid_executed | | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-7 | |
3、 备机节点执行：
reset master ;set global gtid_purged='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-6';
start group_replication;
4、 检查集群同步情况
备注：
（1）set global group_replication_bootstrap_group=on：只需在master节点中执行，表示以后加入集群的成员以master节点为准，且只需执行一次
（2）set global group_replication_bootstrap_group=off：确保master节点下一次启动时不会再进行初始化，导致复制组出现分裂
5.2 全部宕机时
确认在binlog最新的一台服务器启动后并执行(设置为主节点)
mysql> set global group_replication_bootstrap_group=on;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=off;
其他节点只需执行
start group_replication;
5.3 单台宕机时
重启mysql 后需要，执行：
start group_replication;
并时刻查询select * from performance_schema.replication_group_members;直到自身节点已经成为slave
相关文章
- Mysql 面试题（一网打尽，收藏版）
- MySQL Binlog Digger 4.28 【mysql日志分析工具】
- QT连接mysql（解决QT5.12无mysql驱动）
- MySQL 当记录不存在时插入（insert if not exists）
- VS2013与MySql建立连接；您的项目引用了最新实体框架；但是，找不到数据链接所需的与版本兼容的实体框架数据库 EF6使用Mysql的技巧
- Mysql错误check the manual that corresponds to your MySQL server version for the right syntax
- Mysql mysql lost connection to server during query 问题解决方法
- MySQL连接IDEA详细教程
- 【mysql问题】完美解决this is incompatible with sql_mode=only_full_group_by
- 《PHP、MySQL和Apache入门经典（第5版）》一2.6 安装故障排除
- 基于ASP.NET+MySQL实现待办任务清单系统【100010180】
- 基于JavaFX+Mysql实现（PC）足球联赛评分系统【100010048】
- JDBC 连接 MySQL 异常：com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
- python操作mysql数据库系列-操作MySql数据库(五)
- Mysql命令mysql：连接Mysql数据库
- Mysql_mysql多个TimeStamp设置
- Amoeba for MySQL 非常好用的mysql集群软件
- MySQL经常使用命令--create命令使用
- mysql如何替换字符串中换行符及回车符
- Unity 使用数据库（MySql）
- Mysql 将结果保存到文件 从文件里运行sql语句 记录操作过程（tee 命令的使用）
- Linux 安装和配置MySQL
- MySQL语句的所有模型（详细）
- [Mysql] PERCENT_RANK()函数 | CUME_DIST()函数