在上一篇文章《使用ProxySQL实现MySQL Group Replication的故障转移、读写分离(一) 》 中,已经完成了MGR+ProxySQL集群的搭建,也测试了ProxySQL实现业务层面的故障转移,接下来继续测试读写分离。
王国维大师笔下的人生三境界:
第一重境界:昨夜西风凋碧树。独上高楼,望尽天涯路; 第二重境界:衣带渐宽终不悔,为伊消得人憔悴; 第三重境界:众里寻他千百度,蓦然回首,那人却在灯火阑珊处。
作为一个一根筋的学渣程序员,我还没能想透彻。但是数据库读写分离的三境界却有了一定的了解,我们不妨来看一看MySQL数据库读写分离的三境界。
第一重境界:人工实现读写分离。通过IP、端口读写分离,业务层面人工识别读写语句,然后将其分配到不同的主机,实现读写分离; 第二重境界:正则实现读写分离。通过路由中间件识别SQL语句,通过正则表达式匹配SQL语句,然后根据匹配结果分发到不同的主机; 第三重境界:识别TOP SQL,将高负载SQL分发到不同的主机;
(一)第一境界:人工实现读写分离
通过IP、端口读写分离,业务层面人工识别读写语句,然后使用不同的连接数据库配置信息,将其分配到不同的主机,实现读写分离。在ProxySQL里面,我们是通过端口来实现读写分离的。具体操作如下:
STEP1:配置ProxySQL在两个端口上侦听,并且重新启动ProxySQL
mysql -uadmin -padmin -h127.0.0.1 -P6032 mysql> SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402'; -- save it on disk and restart proxysql mysql> SAVE MYSQL VARIABLES TO DISK; mysql> PROXYSQL RESTART;
STEP2:配置路由规则,通过端口将请求分发到不同的组
mysql> INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply) VALUES (1,1,6401,10,1), (3,1,6402,20,1); mysql> LOAD MYSQL QUERY RULES TO RUNTIME; mysql> SAVE MYSQL QUERY RULES TO DISK;
这样,通过6401端口访问数据库的请求就会被转发到组1(写组)中,通过6402端口访问数据库的请求会被转发到组3(读组)中,从而实现读写分离,具体使用6401端口还是6402端口访问数据库,取决于开发人员人工识别SQL的读写特性。
(二)第二境界:使用正则表达式实现读写分离
通过路由中间件识别SQL语句,通过正则表达式匹配SQL语句,然后根据匹配结果分发到不同的主机。操作过程如下
STEP1:为避免干扰测试,删除之前定义的规则
DELETE FROM mysql_query_rules;
STEP2:定义新的读写分离规则
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT.*FOR UPDATE$',1,1); INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES(2,1,'^SELECT',3,1); LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
现在,ProxySQL的路由规则为:
- SELECT FOR UPDATE操作将被路由到组1(写组);
- 其它的SELECT语句将被路由到组3(读组);
- 其它的路由到默认组,即组1。
这里对使用正则表达式方式进行测试,整个过程如下:
(1)测试之前读写组信息修改
-- 根据组的规则:最多1个写节点,其余的写节点放入备用写组。目前我们可以看到节点192.168.10.13是写节点,其余2个节点是备用写节点,没有读节点 mysql> select * from mysql_group_replication_hostgroups; +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ | 1 | 2 | 3 | 4 | 1 | 1 | 0 | 100 | NULL | +------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+ 1 row in set (0.00 sec) mysql> mysql> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.10.13 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.10.12 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | 192.168.10.11 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.01 sec) -- 为了实现读写分离,需要有读节点,我们可以修改writer_is_also_reader参数,让backup_writer_hostgroup中的节点既做备用写节点,又做读节点 mysql> update mysql_group_replication_hostgroups set writer_is_also_reader = 2 ; Query OK, 1 row affected (0.00 sec) m