文章目录
- 一、读写分离方案类型
- 1.1 最简单的读写分离
- 1.2 多个读组或写组的分离模式
- 二、案例
- 2.1 初始化操作
- 2.2 mysql主添加proxysql连接用户
- 2.3 Proxysql添加连接mysql集群参数
- 2.4 添加健康检测用户
- 2.5 添加读写分离的路由规则
- 2.6 验证
一、读写分离方案类型
基本了解：
- 实现数据库读写分离的中间件很多，比如Mycat、Cobar、Atlas、TDDL、Proxysql等等。
- ProxySQL 可以灵活配置路由规则，不仅可以实现最简单的读写分离，还可以将读/写都分散到多个不同的组，以及实现分库 sharding (分表sharding的规则比较难写，但也能实现)。
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模块控制的。
1.2 多个读组或写组的分离模式
- 后端做了分库，对某库的查询要路由到特定的主机组。
- 至于各个主机组是同一个主从集群(下图左边)，还是互相独立的主从集群环境(下图右边)，要看具体的需求，不过这种读写分离模式都能应付。
- 在实现这种模式时，前提是不能开启monitor模块的read_only监控功能，也不要设置mysql_replication_hostgroup 表。
1.如下配置实现的是上图左边的结构：写请求路由给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 |
+---------+-----------------------+----------------------+
二、案例
| IP | 角色 | 应用 | 系统平台 |
|---|---|---|---|
| 192.168.161.129 | 读写分离解析主机 | proxysql、mariadb客户端 | CentOS7 |
| 192.168.161.130 | master | mariadb服务端 | CentOS7 |
| 192.168.161.131 | slave | mariadb服务端 | CentOS7 |
2.1 初始化操作
1.配置mysql主从，参考文章。
2.安装proxysql服务，参考文章，并添加连接mysql显示信息参数。
export MYSQL_PS1="(\u@\h:\p) [\d]> "
2.2 mysql主添加proxysql连接用户
1.在mysql主上进行添加，让proxysql服务器能连接到后端mysql集群。
grant all on *.* to 'proadmin'@'192.168.161.129' identified by 'proadmin';
flush privileges;
2.peoxysql服务器上验证。
mysql -uproadmin -pproadmin -h192.168.161.130
2.3 Proxysql添加连接mysql集群参数
- 以下所有操作，在proxysql服务器上操作。
1.添加mysql集群信息到proxysql自己的mysql_servers表中，包括mysql分组、ip端口、权重。
//连接proxysql自己。
mysql -uadmin -padmin -P6032 -h127.0.0.1
//添加mysql主机信息到mysql_servers 表中。hostgroup_id 10表示写组，20表示读组。
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'192.168.161.130',3306,1,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'192.168.161.131',3306,1,'Read Group');
//保存配置。
load mysql servers to run;
save mysql servers to disk;
//查看配置
select hostgroup_id,hostname,port,weight,comment from mysql_servers;
2.添加mysql上已创建的连接mysql用户信息添加到proxysql自己的mysql_users表中。这里添加的用户就是之前在mysql主上添加的那个用户。
//default_hostgroup表示默认组设置为写组。当读写分离的路由规则不符合时，会访问默认组的数据库。
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proadmin','proadmin',10,1);
//保存配置。
load mysql users to run;
save mysql users to disk;
//查看。
select username,password,default_hostgroup,transaction_persistent from mysql_users;
(admin@127.0.0.1:6032) [(none)]> select * from mysql_users \G
*************************** 1. row ***************************
username: proadmin # 后端mysql实例的用户名
password: proadmin # 后端mysql实例的密码
active: 1 # active=1表示用户生效，0表示不生效
use_ssl: 0
default_hostgroup: 10 # 用户默认登录到哪个hostgroup_id下的实例
default_schema: NULL # 用户默认登录后端mysql实例时连接的数据库，这个地方为NULL的话，则由全局变量mysql-default_schema决定，默认是information_schema
schema_locked: 0
transaction_persistent: 1 # 如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不论是否会匹配上其它路由规则，直到事务结束。虽然默认是0
fast_forward: 0 # 忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .* 就行了
backend: 1
frontend: 1
max_connections: 10000 # 该用户允许的最大连接数
comment:
2.4 添加健康检测用户
1.在mysql主上操作，给proxysql服务器添加一个只读用户，可自定义。
grant select on *.* to 'qingjun'@'192.168.161.129' identified by 'citms';
flush privileges;
2.在proxysql上操作，修改变量，设置健康检测的账号。
//连接proxysql自己。
mysql -uadmin -padmin -P6032 -h127.0.0.1
set mysql-monitor_username='qingjun';
set mysql-monitor_password='citms';
load mysql variables to runtime;
save mysql variables to disk;
2.5 添加读写分离的路由规则
注意事项：
- 规则id不能是1、2、3开头，因为规则匹配是根据id以小到大的优先级匹配，id越小的规则应该越是精确匹配。
//规则id由小到大，必须是精准匹配——>模糊匹配。
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(9,1,'^SELECT.*FOR DROP$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(10,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(20,1,'^SELECT.*school\..*',20,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(29,1,'^DESC.*',20,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(30,1,'^SHOW.*',20,1);
load mysql query rules to run;
save mysql query rules to disk;
select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
2.6 验证
1.使用mysql第一次授权的可读可写账户登录proxysql，进去修改mysql集群数据。
//我这里把连接mysql集群端口改成了3306，默认是6032
mysql -uproadmin -pproadmin -P3306 -h127.0.0.1
//proxysql上的操作命令。
show databases;
create database baimu;
create database school;
select * from school.abc;
2.使用proxysql服务端admin账户登录，查看读写分离效果。
mysql -uadmin -padmin -P6032 -h127.0.0.1 -e 'select * from stats_mysql_query_digest\G' |less
