1.不同类型的读写分离方案解析
数据库中间件最基本的功能就是实现读写分离, ProxySQL 当然也支持。而且 ProxySQL 支持的路由规则非常灵活,不仅可以实现最简单的读写分离,还可以将读/写都分散到多个不同的组,以及实现分库 sharding (分表sharding的规则比较难写,但也能实现)。
本文只描述通过规则制定的语句级读写分离,不讨论通过 ip/port, client, username, schemaname 实现的读写分离。
下面描述了ProxySQL能实现的常见读写分离类型
1.1 最简单的读写分离
这种模式的读写分离,严格区分后端的master和slave节点,且slave节点必须设置选项read_only=1
在ProxySQL上,分两个组,一个写组HG=10,一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能,让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)
这种模式的读写分离是最简单的,只需在mysql_users表中设置用户的默认路由组为写组HG=10,并在mysql_query_rules中加上两条简单的规则(一个select for update,一个select)即可
这种读写分离模式,在环境较小时能满足绝大多数需求。但是需求复杂、环境较大时,这种模式就太过死板,因为一切都是monitor模块控制的
2.2 多个读组或写组的分离模式
前面那种读写分离模式,是通过 monitor 模块监控 read_only 来调整的,所以每一个后端集群必须只能分为一个写组,一个读组。
但如果想要区分不同的 select ,并将不同的 select 路由到不同的节点上。例如有些查询语句的开销非常大,想让它们独占一个节点/组,其它查询共享一个节点/组,怎么实现?
例如,下面这种模式
看上去非常简单。但是却能适应各种需求。例如,后端做了分库,对某库的查询要路由到特定的主机组
至于各个主机组是同一个主从集群(下图左边),还是互相独立的主从集群环境(下图右边),要看具体的需求,不过这种读写分离模式都能应付
在实现这种模式时,前提是不能开启monitor模块的read_only监控功能,也不要设置mysql_replication_hostgroup 表
例如,下面的配置实现的是上图左边的结构:写请求路由给HG=10,对test1库的select语句路由给HG=20,其它select路由给HG=30
mysql_servers:
+--------------+----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------+------+--------+--------+
| 10 | host1 | 3306 | ONLINE | 1 |
| 20 | host2 | 3306 | ONLINE | 1 |
| 30 | host3 | 3306 | ONLINE | 1 |
+--------------+----------+------+--------+--------+
mysql_users:
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| root | 10 |
+----------+-------------------+
mysql_query_rules:
+---------+-----------------------+----------------------+
| rule_id | destination_hostgroup | match_digest |
+---------+-----------------------+----------------------+
| 1 | 10 | ^SELECT.*FOR UPDATE$ |
| 2 | 20 | ^SELECT.*test1\..* |
| 3 | 30 | ^SELECT |
+---------+-----------------------+----------------------+
查看表结构的方式:
PRAGMA table_info("表名");
2.ProxySQL实现读写分离示例
环境说明:
IP | 角色 | 应用 | 系统平台 |
---|---|---|---|
192.168.20.135 | 读写分离解析主机 | proxysql | rhel7.4 |
192.168.20.120 | master | mariadb | rhel7.4 |
192.168.20.99 | slave | mariadb | rhel7.4 |
准备工作:
- 关闭防火墙
- 关闭SELINUX
- 安装mysql并配置主从
2.1安装ProxySQL
2.2配置ProxySQL
2.2.1 mysql主库添加proxysql可以增删改查的账号
MariaDB [(none)]> grant all on *.* to 'zzl'@'192.168.20.135' identified by 'zzl123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
2.2.2 登录proxysql管理端
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+--