背景
之前测试了mysqlrouter with innodb cluster来解决mysql 读写分类和负载均衡的方案，不甚理想
文章见：https://blog.csdn.net/eaglejiawo1120/article/details/84301142
参考了 参考1，2
觉得proxysql可能是个不错的方案。特验证如下。
proxysql介绍及安装
参考： https://blog.csdn.net/eaglejiawo1120/article/details/84580419
支持proxysql的mgr环境搭建：
1. 安装myql5.7
参照我写的博文： https://blog.csdn.net/eaglejiawo1120/article/details/84301142 中的安装mysql
2. 下载测试脚本并修改配置：
下载地址：https://download.csdn.net/download/eaglejiawo1120/10802581。该代码的原始代码见参照4。
在参照4代码的基础上修改了如下部分：
check_node.sh：
${base_dir}/bin/mysql -P${node_port} -S ${base_data_dir}${node_name}/${node_name}.sock -e "select uuid(); select *from performance_schema.replication_group_members;"
修改为：
${base_dir}/bin/mysql -P${node_port} -S ${base_data_dir}/${node_name}/${node_name}.sock -e "select uuid(); select *from performance_schema.replication_group_members;"
cat init.lst
#实例占用的端口，实例名称，mgr占用的端口，是否为写主机（Y-写主机，N-只读主机）
3310 s1 33100 Y
3320 s2 33200 N
3330 s3 33300 N
cat auto.cnf
# This file should be configured following your needs
#mysql的基地址
export base_dir=/usr/local/mysql5.7.24
#存放mysql实例数据的基地址。
export base_data_dir=/home/mysql_test/mysql_5.7/mgr_data
#cat /etc/hosts
需要配置localhost bogon(这个可以为其他名字)
3. 启动实例：
./init.sh
过一会执行 ./check_node.sh s3
[root@bogon mgr_shell]# ./check_node.sh s3
3330
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 80d20bbf-eed0-11e8-b9bc-005056a17264 |
+--------------------------------------+
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 316e3981-eed0-11e8-9a0b-005056a17264 | bogon | 3310 | ONLINE |
| group_replication_applier | 3da50494-eed0-11e8-ac69-005056a17264 | bogon | 3320 | ONLINE |
| group_replication_applier | 4c74968b-eed0-11e8-bee3-005056a17264 | bogon | 3330 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
VIEW_ID: 15429438755052221:3
MEMBER_ID: 4c74968b-eed0-11e8-bee3-005056a17264
COUNT_TRANSACTIONS_IN_QUEUE: 0
COUNT_TRANSACTIONS_CHECKED: 0
COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 0
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 1bb1b861-f776-11e6-be42-782bcb377193:1-3
LAST_CONFLICT_FREE_TRANSACTION:
如果出现如上结果表明mgr已经配置成功。
下来配置proxysql需要的用户和表
在写节点s1上执行, 其中 addition_to_sys.sql可以从 https://github.com/lefred/mysql_gr_routing_check获取。
如果访问较慢也可以从：https://download.csdn.net/download/eaglejiawo1120/10802722下载。
#mysql -uroot -h127.0.0.1 -P3310 -p
grant all privileges on *.* to 'monitor'@'%' identified by 'beacon';
grant INSERT,UPDATE,DELETE,SELECT on *.* to 'run'@'%' identified by 'beacon';
flush privileges;
source addition_to_sys.sql;
4.删除测试数据：
./stop_node.sh s1
./stop_node.sh s2
./stop_node.sh s3
#有僵尸进程时执行： ps -A -o stat,ppid,pid,cmd | grep -e '^[Zz]' | awk '{print $2}' | xargs kill -9
#该步请谨慎操作
rm -rf ${ base_data_dir}/*
4. 参数配置总结：
| 主机 | 读写类型 |
| localhost:3310 | 写 |
| localhost:3320 | 读 |
| localhost:3330 | 读 |
| 用户名 | 密码 | 场景 |
| run | beacon | 客户端连接proxysql |
| monitor | beacon | proxysql监控mgr状态 |
| proxysql函数/view名 | 类型 |
| IFZERO | 函数 |
| LOCATE2 | 函数 |
| GTID_NORMALIZE | 函数 |
| GTID_COUNT | 函数 |
| gr_applier_queue_length | 函数 |
| gr_member_in_primary_partition | 函数 |
| gr_member_routing_candidate_status | 视图(proxysql用到最主要的view) |
proxysql配置管理mgr相关参数
mysql -uadmin -padmin -h 127.0.0.1 -P6032
#配置监控账号
set mysql-monitor_username='monitor';
set mysql-monitor_password='beacon';
#配置默认组信息
insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(10,20,30,40,1);
组ID含义如下：写组：10，备写组：20，读组：30，离线组（不可用）：40
#配置后端节点信息
主节点定义为写组10,从节点定义为只读组30
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(10,'127.0.0.1',3310,'init write');
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(30,'127.0.0.1',3320,'init read');
insert into mysql_servers(hostgroup_id,hostname,port,comment) values(30,'127.0.0.1',3330,'init read');
#配置读写分离参数
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',30,1);
select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
#配置用户（主要是添加程序端的这个用户，也就是run，将其设置到写组10里面）
insert into mysql_users(username,password,default_hostgroup) values('run','beacon',10);
#保存配置
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;
#测试是否配置成功：
mysql> select * from runtime_mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| 10 | 127.0.0.1 | 3310 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init write |
| 30 | 127.0.0.1 | 3330 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
| 30 | 127.0.0.1 | 3320 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
3 rows in set (0.00 sec)
如果出现以上结果，表明proxysql对接mgr成功。
#proxysql with mgr测试
约定：
监控端：使用monitor用户访问：127.0.0.1@6032----mysql -umonitor -pbeacon -h 127.0.0.1 -P6032
管理端：使用monitor用户访问：127.0.0.1@6032----mysql -uadmin -padmin -h 127.0.0.1 -P6032
程序端：使用run用户访问127.0.0.1@6033---mysql -urun -pbeacon -h 127.0.0.1 -P6033
注意： 如果出现 ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 10 after 10000ms
大多数情况是因为配置mgr时没有导入proxysql需要的视图。
节点端：使用root用户（默认密码为空）访问节点
测试读写分离：
节点端访问3310写实例创建测试库和测试表：
create database test;
use test;
CREATE TABLE t1 (c1 int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 TEXT NOT NULL);
程序端查看和插入数据
show databases;
use test;
insert into t1(`c2`) values('tom');
insert into t1(`c2`) values('harray');
select * from t1;
select * from t1;
监控端查看数据：
mysql> select hostgroup,digest_text from stats_mysql_query_digest;
+-----------+--------------------------------------------------------------------------------------------+
| hostgroup | digest_text |
+-----------+--------------------------------------------------------------------------------------------+
| 30 | select * from t1 |
| 10 | insert into t1(`c2`) values(?) |
| 10 | CREATE TABLE t1 (c1 int(?) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 TEXT NOT NULL) |
| 10 | show databases |
| 30 | select @@port |
| 10 | show databases |
| 10 | create database test |
| 10 | select @@version_comment limit ? |
| 30 | select * from version |
| 10 | desc t1 |
| 10 | show tables |
| 30 | SELECT DATABASE() |
| 10 | show databases |
| 10 | show tables |
| 10 | show tables |
| 30 | SELECT DATABASE() |
+-----------+--------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
如上图，可以看到写请求均发向了写实例，读请求发向了读实例。读写分离是ok的。
测试故障转移
管理端查看现在的状态
mysql> select * from runtime_mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| 10 | 127.0.0.1 | 3310 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init write |
| 30 | 127.0.0.1 | 3330 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
| 30 | 127.0.0.1 | 3320 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
可以看到，当前的写节点是3310实例。
现在关闭3310实例，cd到mgr数据库测试脚本目录，执行： ./stop_node.sh s1
节点端访问3320数据库，执行如下命令：
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3da50494-eed0-11e8-ac69-005056a17264 | bogon | 3320 | ONLINE |
| group_replication_applier | 4c74968b-eed0-11e8-bee3-005056a17264 | bogon | 3330 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | NO | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)
可以看到3310已经被踢出了mgr.
而后在管理端查看目前的后端实例情况：
mysql> select * from runtime_mysql_servers;
+--------------+-----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| 10 | 127.0.0.1 | 3320 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
| 40 | 127.0.0.1 | 3310 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | init write |
| 30 | 127.0.0.1 | 3330 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
+--------------+-----------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
可以看到proxysql已经监控到3310实例已经不可用。并将其置位离线状态。
重新启动3310实例并且恢复mgr： cd到mgr数据库测试脚本目录，执行： ./start_node.sh s1
节点端访问3310：
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| NO | NO | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (3.31 sec)
mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES | YES | 0 | 0 |
+------------------+-----------+---------------------+----------------------+
1 row in set (0.00 sec)
可以看到3310恢复到mgr组中，并且置位了从机。再次从管理端查看是否检测到3310实例的恢复：
mysql> select * from runtime_mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
| 10 | 127.0.0.1 | 3320 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
| 30 | 127.0.0.1 | 3310 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init write |
| 30 | 127.0.0.1 | 3330 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | init read |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------+
3 rows in set (0.00 sec)
可以看到proxysql也检测到了故障恢复。
参考：
1. mycat优劣分析，分布式方案解析：http://www.cnblogs.com/yangjian1/p/9774093.html
2. proxysql support mgr: http://blog.51cto.com/l0vesql/2090721
3. proxysql git: https://github.com/sysown/proxysql
官方配置手册：https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
4. mgr shell test:https://github.com/jeanron100/mysql_mgr_test
5.proxysql深入解析:https://blog.csdn.net/kai404/article/details/52664838
6.proxy测试：
http://www.php-master.com/post/354897.html
https://blog.csdn.net/d6619309/article/details/54602556
7. mysql5.7 新特性及测试： https://www.cnblogs.com/zhoujinyi/p/5704567.html
8.proxysql配置原理：https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
