ProxySQL 2.0 + MGR 8.0 读写分离实验

来源：应IMG群友之约，匆忙写成，安装如有报错写在评论里，看见了我会解答的。

主要测试内容：
测试ProxySQL是否能自动识别MGR主从切换
测试ProxySQL对MGR读写分离

软件版本：
操作系统：CentOS 7.6
数据库：MySQL 8.0.19
中间件：ProxySQL 2.0.10
虚拟机：VM Workstation 15 Pro

IP地址分配：
ProxySQL 192.168.5.131
DB1 192.168.5.130:3306
DB2 192.168.5.130:3307
DB3 192.168.5.130:3308

关闭防火墙：
systemctl stop firewalld
systemctl disable firewalld.service

一、安装MGR(过程省略了)

#不会安装看我这篇文章：https://www.cnblogs.com/9527l/p/12435860.html
笔记本没有那么多资源，在一台主机上安装了3台MySQL以端口区分。

#启动数据库
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3306.cnf --user=mysql &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my3308.cnf --user=mysql &

二、安装ProxySQL
yum -y install proxysql-2.0.10-1-centos7.x86_64.rpm
service proxysql start

注：6032 是 ProxySQL 的管理端口号，6033 是对外服务的端口号。
/etc/init.d/proxysql #proxysql的启动控制文件
/etc/proxysql.cnf #proxysql配置文件

#连接测试
mysql -uadmin -padmin -h127.0.0.1 -P6032

三、ProxySQL 监控 MGR配置

1、增加server

insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.5.130',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.5.130',3307);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.5.130',3308);

load mysql servers to runtime;
save mysql servers to disk;

2、添加user

#到192.168.5.130:3306 MySQL上增加

CREATE USER 'monitor'@'%' IDENTIFIED BY "monitor@1025";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "proxysql@1025";

GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;

#在Proxysql上设置监控账号与程序账号

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor@1025';

insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','proxysql@1025',1,10,1);

3、ProxySQL监控MGR视图

#到192.168.5.130:3306 MySQL上增加

#注意哦，这是8.0的监控视图和5.7不通用哦

USE sys;
 
DELIMITER $$
 
CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$
 
CREATE FUNCTION gr_member_in_primary_partition()
    RETURNS VARCHAR(3)
    DETERMINISTIC
    BEGIN
      RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
    performance_schema.replication_group_members WHERE MEMBER_STATE NOT IN ('ONLINE', 'RECOVERING')) >=
    ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
    'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
    performance_schema.replication_group_member_stats USING(member_id) where member_id=my_id());
END$$
 
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' 
from performance_schema.replication_group_member_stats where member_id=my_id();$$

4、设置读写组

主负责写、从负责读，当MGR主库切换后，代理自动识别主从。
ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40，

 insert into mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup, offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) values (10,20,30,40,1,1,0,100);

注意：max_transactions_behind 是设置延迟大小，可以给大点,建议自己去开个并行复制。

load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

5、测试ProxySQL是否能自动识别MGR主从切换

#原来主为3306，手动重启MySQL 3306,后查看
select hostgroup_id, hostname, port,status from runtime_mysql_servers;

+--------------+---------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+---------------+------+--------+
| 10 | 192.168.5.130 | 3307 | ONLINE |
| 30 | 192.168.5.130 | 3306 | ONLINE |
| 30 | 192.168.5.130 | 3308 | ONLINE |
+--------------+---------------+------+--------+

#查看ProxySQL对MGR监控状态

mysql> select hostname,
-> port,
-> viable_candidate,
-> read_only,
-> transactions_behind,
-> error
-> from mysql_server_group_replication_log
-> order by time_start_us desc
-> limit 6;
+---------------+------+------------------+-----------+---------------------+-------+
| hostname | port | viable_candidate | read_only | transactions_behind | error |
+---------------+------+------------------+-----------+---------------------+-------+
| 192.168.5.130 | 3308 | YES | YES | 0 | NULL |
| 192.168.5.130 | 3307 | YES | NO | 0 | NULL |
| 192.168.5.130 | 3306 | YES | YES | 0 | NULL |
| 192.168.5.130 | 3308 | YES | YES | 0 | NULL |
| 192.168.5.130 | 3307 | YES | NO | 0 | NULL |
| 192.168.5.130 | 3306 | YES | YES | 0 | NULL |
+---------------+------+------------------+-----------+---------------------+-------+
6 rows in set (0.00 sec)

6、配置读写分离规则

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',30,1);

load mysql query rules to runtime;
save mysql query rules to disk;

load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

四、测试是否可以读写分离

#proxysql上执行

mysql -uproxysql -pproxysql@1025 -h127.0.0.1 -P6033

#下面测试sql是否会写入走10组，读取走30

mysql> create database test;

mysql>use test

mysql> create table t(id int primary key,name varchar(20,age int);

mysql> insert into t values(1,"hehe");

mysql> select * from t;

#查看路由规则

mysql -uadmin -padmin -h127.0.0.1 -P6032

mysql> select hostgroup,digest_text from stats_mysql_query_digest order by digest_text desc limit 5;
+-----------+----------------------------------+
| hostgroup | digest_text |
+-----------+----------------------------------+
| 10 | show databases |
| 30 | select host,user from mysql.user |
| 10 | select @@version_comment limit ? |
| 30 | select * from t |
| 10 | insert into t values(?,?) |
+-----------+----------------------------------+
5 rows in set (0.00 sec)