时间：2021-07-01 10:21:17 帮助过：32人阅读
Distributor ID： Ubuntu Description ： Ubuntu 14.04.5 LTS Release ： 14.04 Codename ： trusty
MySQL Master ：192.168.200.202 MySQL Slave ：192.168.200.132 APP IP ：192.168.200.25/64
本文测试环境是在上一篇文章的基础上进行的，所以已经做了一主一从的读写分离。在此基础上，若从库挂了，会怎么样？这里先把从库（132）shutdown掉，看看读去了哪里。
tips：如何修改管理接口的用户名密码？除了初始化时候修改配置文件，还有一个方法是在管理端口设置：
admin@127.0.0.1 : (none) 12:52:53>set admin-admin_credentials=‘zjy:zjy‘; Query OK, 1 row affected (0.00 sec)
admin模块修改（select * from global_variables where variable_name like ‘admin%‘;）需要用admin加载：
admin@127.0.0.1 : (none) 12:53:02>load admin variables to runtime; Query OK, 0 rows affected (0.00 sec) admin@127.0.0.1 : (none) 12:53:33>save admin variables to disk; Query OK, 9 rows affected (0.00 sec)
1，从库不可用
① 关闭从库
当前ProxySQL下后端MySQL的运行情况：
admin@127.0.0.1 : (none) 11:25:26>select hostgroup_id,hostname,port,status from runtime_mysql_servers; +--------------+-----------------+------+--------+ | hostgroup_id | hostname | port | status | +--------------+-----------------+------+--------+ | 100 | 192.168.200.202 | 3306 | ONLINE | | 1000 | 192.168.200.132 | 3306 | ONLINE | +--------------+-----------------+------+--------+
shutdown从库（132）后，后端MySQL的运行情况：
admin@127.0.0.1 : (none) 11:33:24>select hostgroup_id,hostname,port,status from runtime_mysql_servers; +--------------+-----------------+------+---------+ | hostgroup_id | hostname | port | status | +--------------+-----------------+------+---------+ | 100 | 192.168.200.202 | 3306 | ONLINE | | 1000 | 192.168.200.132 | 3306 | SHUNNED | +--------------+-----------------+------+---------+
此时读的操作会报超时：
sbuser@192.168.200.24 : sbtest 11:30:40>select * from x; ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 1000 after 10000ms
原因是proxysql的核心都在规则，shutdown从之后，proxysql还是想路由到 hostgroup=1000，它不会自动选择默认的100(mysql_users里配置的default_hostgroup) 。
这里解决的办法是：在mysql_servers的hostgroup 1000 里面要插一条主库的记录，然后把weight设小，当读不到从库，回去主库查询。
admin@127.0.0.1 : (none) 11:50:13>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,‘192.168.200.202‘,3306,1,1000,10,‘test proxysql‘); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 : (none) 11:50:42>update mysql_servers set weight=9 where hostgroup_id=1000 and hostname=‘192.168.200.132‘; Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 : (none) 11:53:41>select hostgroup_id,hostname,port,weight from mysql_servers; +--------------+-----------------+------+--------+ | hostgroup_id | hostname | port | weight | +--------------+-----------------+------+--------+ | 100 | 192.168.200.202 | 3306 | 1 | | 1000 | 192.168.200.132 | 3306 | 9 | | 1000 | 192.168.200.202 | 3306 | 1 | +--------------+-----------------+------+--------+ admin@127.0.0.1 : (none) 11:54:03>load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) admin@127.0.0.1 : (none) 11:54:28>save mysql servers to disk; Query OK, 0 rows affected (0.00 sec) admin@127.0.0.1 : (none) 11:54:38>select hostgroup_id,hostname,port,status from runtime_mysql_servers; +--------------+-----------------+------+---------+ | hostgroup_id | hostname | port | status | +--------------+-----------------+------+---------+ | 100 | 192.168.200.202 | 3306 | ONLINE | | 1000 | 192.168.200.132 | 3306 | SHUNNED | | 1000 | 192.168.200.202 | 3306 | ONLINE | +--------------+-----------------+------+---------+
此时读的操作正常：
sbuser@192.168.200.24 : sbtest 11:52:37>select * from x; +------+ | id | +------+ | 123 | | 123 | | 123 | +------+ 3 rows in set (0.01 sec)
说明从关闭了之后读操作确实去主上执行了。当从库恢复之后，以后的读操作主库也可以处理 1/10 的读请求。
② 从库延迟/从库停止复制
在上一篇文章中已经建立了监控账号：proxysql，由于需要执行show slave status的命令来获得延迟时间，所以需要权限SUPER 和 REPLICATION CLIENT。并且需要设置mysql_servers.max_replication_lag的值，由于mysql_servers.max_replication_lag仅适用于从，但也可以将其配置为所有主机，无论是从还是主（不会有任何影响）。
-- 设置监控账号权限 dba@192.168.200.202 : sbtest 10:44:38>GRANT SUPER, REPLICATION CLIENT ON *.* TO ‘proxysql‘@‘192.168.200.24‘ IDENTIFIED BY PASSWORD ‘*BF27B4C7AAD278126E228AA8427806E870F64F39‘; Query OK, 0 rows affected (0.00 sec) -- 设置延迟的阈值 admin@127.0.0.1 : (none) 11:04:50>UPDATE mysql_servers SET max_replication_lag=5; Query OK, 3 rows affected (0.00 sec) -- 应用配置 admin@127.0.0.1 : (none) 11:04:54>load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) admin@127.0.0.1 : (none) 11:05:04>save mysql servers to disk; Query OK, 0 rows affected (0.01 sec)
主从复制正常的情况下，后端MySQL的情况：
admin@127.0.0.1 : (none) 11:05:13>select hostgroup_id,hostname,port,status,max_replication_lag from runtime_mysql_servers; +--------------+-----------------+------+--------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------------+------+--------+---------------------+ | 1000 | 192.168.200.132 | 3306 | ONLINE | 5 | | 1000 | 192.168.200.202 | 3306 | ONLINE | 5 | | 100 | 192.168.200.202 | 3306 | ONLINE | 5 | +--------------+-----------------+------+--------+---------------------+ 3 rows in set (0.00 sec)
从库执行stop slave之后，后端MySQL的情况：
admin@127.0.0.1 : (none) 11:06:52>select hostgroup_id,hostname,port,status,max_replication_lag from runtime_mysql_servers; +--------------+-----------------+------+---------+---------------------+ | hostgroup_id | hostname | port | status | max_replication_lag | +--------------+-----------------+------+---------+---------------------+ | 1000 | 192.168.200.132 | 3306 | SHUNNED | 5 | | 1000 | 192.168.200.202 | 3306 | ONLINE | 5 | | 100 | 192.168.200.202 | 3306 | ONLINE | 5 | +--------------+-----------------+------+---------+---------------------+ 3 rows in set (0.00 sec)
此时，132从库不可用，读都到了HG 1000的202上去了，可以自行测试。 也可以在日志里看到：
2017-05-11 11:06:43 MySQL_HostGroups_Manager.cpp:934:replication_lag_action(): [WARNING] Shunning server 192.168.200.132:3306 with replication lag of 60 second
日志显示延迟60s，这个是怎么回事？这里需要说明下几个变量：
mysql-monitor_replication_lag_interval：主从延迟检测时间，默认10秒。
mysql-monitor_slave_lag_when_null：当为null时，设置的延迟值，默认为60。
admin@127.0.0.1 : (none) 11:08:35>select * from global_variables where variable_name like ‘mysql-monitor%lag%‘; +----------------------------------------+----------------+ | variable_name | variable_value | +----------------------------------------+----------------+ | mysql-monitor_replication_lag_interval | 10000 | | mysql-monitor_replication_lag_timeout | 1000 | | mysql-monitor_slave_lag_when_null | 60 | +----------------------------------------+----------------+ 3 rows in set (0.00 sec)
根据mysql_servers.max_replication_lag设置的阈值，这2个参数可以根据自己的情况来设置，比如设置检测时间为1500。延迟的记录也可以通过表来查看：
admin@127.0.0.1 : (none) 11:19:47>select * from mysql_server_replication_lag_log limit 3; +-----------------+------+------------------+-----------------+----------+-------+ | hostname | port | time_start_us | success_time_us | repl_lag | error | +-----------------+------+------------------+-----------------+----------+-------+ | 192.168.200.132 | 3306 | 1494472189886932 | 411 | 0 | NULL | | 192.168.200.202 | 3306 | 1494472189887224 | 372 | NULL | NULL | | 192.168.200.202 | 3306 | 1494472189887640 | 325 | NULL | NULL | +-----------------+------+------------------+-----------------+----------+-------+ 3 rows in set (0.00 sec)
主从延迟的情况和stop slave的情况一样，只是stop slave是把延迟设置成了60s。
小结：通过上面的测试说明ProxySQL可以在从库不可用时进行下线，不需要人为再进行干预，等到恢复正常之后自动上线提供服务。
2，多路由规则
① 根据库路由
在现有基础上再增加一个主从：
M：192.168.200.97 S：192.168.200.245
授权账号：程序和监控账号
dba@192.168.200.97 : proxysql 12:39:39>GRANT SUPER, REPLICATION CLIENT ON *.* TO ‘proxysql‘@‘192.168.200.24‘ IDENTIFIED BY PASSWORD ‘*BF27B4C7AAD278126E228AA8427806E870F64F39‘; Query OK, 0 rows affected (0.01 sec) dba@192.168.200.97 : proxysql 12:42:50>grant select,insert,update,delete on proxysql.* to proxysql@192.168.200.24 identified by ‘proxysql‘; Query OK, 0 rows affected (0.00 sec)
配置ProxySQL：
admin@127.0.0.1 : (none) 12:43:35>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,‘192.168.200.97‘,3306,1,1000,10,‘test proxysql‘); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 : (none) 12:45:15>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1001,‘192.168.200.245‘,3306,9,1000,10,‘test proxysql‘); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 : (none) 12:45:24>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1001,‘192.168.200.97‘,3306,1,1000,10,‘test proxysql‘); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 : (none) 12:45:36>insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values(‘proxysql‘,‘proxysql‘,1,101,1); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 : (none) 12:46:55>INSERT INTO mysql_query_rules(active,schemaname,match_pattern,destination_hostgroup,apply) VALUES(1,‘proxysql‘,‘^SELECT.*FOR UPDATE$‘,101,1); Query OK, 1 row affected (0.00 sec) admin@127.0.0.1 : (none) 12:56:47> admin@127.0.0.1 : (none) 12:56:47>INSERT INTO mysql_query_rules(active,schemaname,match_pattern,destination_hostgroup,apply) VALUES(1,‘proxysql‘,‘^SELECT‘,1001,1); Query OK, 1 row affected (0.00 sec) -- 应用保存配置 admin@127.0.0.1 : (none) 12:56:55>load mysql servers to runtime; admin@127.0.0.1 : (none) 12:57:00>load mysql users to runtime;
admin@127.0.0.1 : (none) 12:57:04>load mysql query rules to runtime; admin@127.0.0.1 : (none) 12:57:11>save mysql servers to disk; admin@127.0.0.1 : (none) 12:57:17>save mysql users to disk; admin@127.0.0.1 : (none) 12:57:21>save mysql query rules to disk;
rules、servers、users信息：
admin@127.0.0.1 : (none) 03:28:11>select rule_id,active,username,schemaname,client_addr,destination_hostgroup,match_pattern,flagIN,flagOUT,apply from mysql_query_rules; +---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+ | rule_id | active | username | schemaname | client_addr | destination_hostgroup | match_pattern | flagIN | flagOUT | apply | +---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+ | 3 | 1 | NULL | NULL | NULL | 100 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 1 | | 4 | 1 | NULL | NULL | NULL | 1000 | ^SELECT | 0 | NULL | 1 | | 5 | 1 | NULL | proxysql | NULL | 101 | ^SELECT.*FOR UPDATE$ | 0 | NULL | 1 | | 6 | 1 | NULL | proxysql | NULL | 1001 | ^SELECT | 0 | NULL | 1 | +---------+--------+----------+------------+-------------+-----------------------+----------------------+--------+---------+-------+ 4 rows in set (0.00 sec) admin@127.0.0.1 : (none) 03:29:10>select username,default_hostgroup from mysql_users; +----------+-------------------+ | username | default_hostgroup | +----------+-------------------+ | sbuser |