MySQL读写分离中间件

1.什么是读写分离中间件？
就是实现当[写]的时候转发到主库，当[读]的时候转发到从库的工具。
很类似学习过的proxy,比如nginx proxy做动静分离.
2.为什么要实现读写分离？
  1）让主库专注于写，因为读可以有很多从库可以干。
  2）让多个从库接收并发读请求。
好处，增加读和写的并发,防止锁竞争，减轻主数据库的压力。

生产场景:读写比基本都比较大,超过10/1.

3.读写分离中间件有哪些？
  1）程序实现（JAVA,PHP,PYTHON,SHELL）
     需要读写分离的SQL语句DML语句关键字:insert,delete,update,select
     读：select开头找从库，否则就找主库。
     优点：少配置一个服务，降低了连接延迟以及服务宕机问题。     
     程序修改mysql操作，直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配，需要开发人员协助。
  
  2）中间件插件(对应用层透明,ip1 读,ip2写)
    amoeba:    直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案，自己分配账户，和后端数据库权限管理独立，权限处理不够灵活。
    mysql-proxy:
    直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，效率低
    proxySQL
    Atlas
    maxscale
    cobar
    mycat
    ====================
    proxySQL（推荐使用）类似学习过的proxy NGINX（nginx 反向代理，代理web服务请求）

4. ProxySQL介绍
ProxySQL是一个高性能的MySQL代理软件，可以实现数据库读写分离，支持 Query路由功能，支持动态指定某个SQL进行缓存，支持动态加载配置信息（无需重启ProxySQL ），支持故障切换和SQL过滤功能。
 Scale 100K+ connections
across thousands of servers
ProxySQL网站：
https://www.proxysql.com/
https://github.com/sysown/proxysql/wiki

5. 基本架构逻辑原理
见架构图。

6.检查修改主从复制环境
GTID环境:
51:Master mha node/mha management
52/53:Slave mha node

7. 安装ProxySQL插件
# 下载proxySQL
https://proxysql.com/
https://github.com/sysown/proxysql/releases

Scale 100K+ connections
across thousands of servers

# 安装proxySQL 
# 理论选择哪个机器都可以，本次选择51安装。
[root@db03 ~]# rpm -ivh proxysql-2.0.10-1-centos7.x86_64.rpm 
[root@db03 bin]# systemctl start proxysql
[root@db03 bin]# netstat -tulnp|grep 603
tcp        0      0 0.0.0.0:6033      0.0.0.0:*     LISTEN      15595/proxysql      
tcp        0      0 0.0.0.0:6032      0.0.0.0:*     LISTEN      15595/proxysql    

注意:
#6033 对外提供服务端口,用户连接
#6032 管理员管理端口

# 登录proxySQL
[root@db03 bin]# mysql -uadmin -padmin -h127.0.0.1 -P6032

db03 [(none)]>show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
db03 [(none)]>use main
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
db03 [main]>show tables;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| mysql_group_replication_hostgroups                 |
| mysql_query_rules                                  |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
| runtime_*
+----------------------------------------------------+

8. ProxySQL中管理结构自带系统库

在ProxySQL中，6032端口管理共五个库： main、disk、stats 、monitor、stats_history 
核心是main库：
main: 
    main 库中有如下信息：
    
1)mysql_replication_hostgroups : 读写节点分组配置信息
db03 [main]>select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+

2)mysql_servers: 后端可以连接MySQL服务器的列表   ###nginx upstream池子
db03 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+----------------+
| hostgroup_id | hostname  | port | gtid_port | status | weight |max_connections |
+--------------+-----------+------+-----------+--------+--------+----------------+
| 20           | 10.0.0.52 | 3306 | 0         | ONLINE | 1      |1000            |
| 10           | 10.0.0.51 | 3306 | 0         | ONLINE | 1      |1000            |
| 20           | 10.0.0.53 | 3306 | 0         | ONLINE | 1      |1000            |
| 20           | 10.0.0.51 | 3306 | 0         | ONLINE | 1      |1000            |
+--------------+-----------+------+-----------+--------+--------+----------------+

3)mysql_users:   配置后端数据库的账号和监控的账号。 
db03 [main]>select username,password,default_hostgroup from mysql_users;
+----------+----------+-------------------+
| username | password | default_hostgroup |
+----------+----------+-------------------+
| root     | 123      | 10                |
+----------+----------+-------------------+

4)mysql_query_rules: 指定Query路由到后端不同服务器的规则列表。
db03 [main]>select rule_id,active,match_pattern,destination_hostgroup from mysql_query_rules;
+---------+--------+----------------------+-----------------------+
| rule_id | active | match_pattern        | destination_hostgroup |
+---------+--------+----------------------+-----------------------+
| 1       | 1      | ^select.*for update$ | 10                    |
| 2       | 1      | ^select              | 20                    |
+---------+--------+----------------------+-----------------------+

注： 表名以 runtime_开头的表示ProxySQL当前运行的配置内容，不能直接修改。
不带runtime_是下文图中Mem相关的配置。

disk ：
    持久化的磁盘的配置 
stats： 
    统计信息的汇总 
monitor：
    监控的收集信息，比如数据库的健康状态等 
stats_history: 
    ProxySQL收集的有关其内部功能的历史指标

9. ProxySQL管理接口的多层配置关系
见架构图。

整套配置系统分为三层：
顶层   RUNTIME 
中间层 MEMORY（主要修改的配置表）
持久层 DISK和CFG FILE 

RUNTIME ： 
    代表ProxySQL当前正在使用的配置，无法直接修改此配置，必须要从下一层（MEM层）load进来。 

MEMORY： 
    MEMORY层上面连接RUNTIME层，下面连接disk持久层。这层可以在线操作ProxySQL 配置，随便修改，不会影响生产环境。确认正常之后在加载到RUNTIME和持久化磁盘上。
    修改方法： 使用SQL DML语句：insert、update、delete、select。

DISK和CONFIG FILE：
    持久化配置信息。重启时，可以从磁盘快速加载回来。

10. 在不同层次间移动配置

为了将配置持久化到磁盘或者应用到 runtime，在管理接口下有一系列管理命令来实现它们。

1. user相关配置
## MEM加载到runtime
LOAD MYSQL USERS TO RUNTIME;

##  runtime保存至MEM
SAVE MYSQL USERS TO MEMORY;

## disk加载到MEM
LOAD MYSQL USERS FROM DISK;

## MEM到disk 
SAVE MYSQL USERS TO DISK;

## CFG到MEM
LOAD MYSQL USERS FROM CONFIG
=============================
2. server相关配置
##  MEM加载到runtime
LOAD MYSQL SERVERS TO RUNTIME;

##  runtime保存至MEM
SAVE MYSQL SERVERS TO MEMORY;

## disk加载到 MEM
LOAD MYSQL SERVERS FROM DISK;

## MEM 到disk 
SAVE MYSQL SERVERS TO DISK;

## CFG 到 MEM
LOAD MYSQL SERVERS FROM CONFIG
===============================
3. mysql query rules配置 
##  MEM 加载到runtime
LOAD MYSQL QUERY RULES TO RUNTIME;

##  runtime 保存至 MEM
SAVE MYSQL QUERY RULES TO MEMORY;

## disk 加载到 MEM
LOAD MYSQL QUERY RULES FROM DISK;

## MEM  到 disk 
SAVE MYSQL QUERY RULES TO DISK;

## CFG 到 MEM
LOAD MYSQL QUERY RULES FROM CONFIG

=================================
4. MySQL variables配置
##  MEM 加载到runtime
LOAD MYSQL VARIABLES TO RUNTIME;

##  runtime 保存至 MEM
SAVE MYSQL VARIABLES TO MEMORY;

## disk 加载到 MEM
LOAD MYSQL VARIABLES FROM DISK;

## MEM  到 disk 
SAVE MYSQL VARIABLES TO DISK;

## CFG 到 MEM
LOAD MYSQL VARIABLES FROM CONFIG

总结： 
日常配置其实大部分时间在MEM层配置，
然后load到RUNTIME，然后SAVE到Disk。cfg很少使用。

常见命令： 
使用SQL DML语句修改：insert、update、delete、select。

使用LOAD,SAVE生效和保存。
load xxx to runtime;
save xxx to disk;
XXX就是上面配置里关键字部分

注意：
    所有配置保MEM或disk层时，都不会发生任何警告或错误。
    只有load到runtime状态时才会验证配置。当load到 runtime时，如果出现错误，将恢复为之前保存的状态，这时可以去检查错误日志。　　

11. ProxySQL应用————基于SQL实现读写分离

11.1 MySQL从库要设定read_only参数

1)主从复制的从库应该禁止用户写入。

2)ProxySQL如何判断主从（根据是否有read-only=1）
read-only=1就是从，否则就是主。

将52,53设置为read-only=1,修改配置步骤略.

11.2 配置读写节点分组信息

在mysql_replication_hostgroup表中，配置读写组编号
10编号对应就是写库的组，20编号对应的就是读库的组。

[root@db01 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
db03 [(none)]>use main

insert into mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) 
values (10,20,'proxy');

db01 [main]>select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 10               | 20               | read_only  | proxy   |
+------------------+------------------+------------+---------+

db01 [main]>load mysql servers to runtime;
db01 [main]>save mysql servers to disk;

db01 [main]>select * from mysql_replication_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
reader_hostgroup: 20
      check_type: read_only
         comment: proxy
说明：
    ProxySQL会根据server的read_only的取值将服务器进行分组。
    read_only=0的server，master被分到编号为10的写组，
    read_only=1的server，slave则被分到编号20的读组。
    所以需要将从库设置：set global read_only=1;

11.3. 添加主机到ProxySQL
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.51',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.52',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.53',3306);

db01 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+-----------------+
| hostgroup_id | hostname  | port | gtid_port | status | weight | max_connections |
+--------------+-----------+------+-----------+--------+--------+-----------------+
| 10           | 10.0.0.51 | 3306 | 0         | ONLINE | 1      | 1000            |
| 20           | 10.0.0.52 | 3306 | 0         | ONLINE | 1      | 1000            |
| 20           | 10.0.0.53 | 3306 | 0         | ONLINE | 1      | 1000            |
+--------------+-----------+------+-----------+--------+--------+-----------------+

load mysql servers to runtime;
save mysql servers to disk;

11.4. 创建监控用户，并开启监控

1）# 51主库创建监控用户，给ProxySQL连接。
create user monitor@'%' identified with mysql_native_password  by '123';
grant replication client on *.* to monitor@'%';

# proxySQL库修改variables表，让proxySQL可以通过monitor用户和123密码去连接和监控数据库
set mysql-monitor_username='monitor';
set mysql-monitor_password='123';

或者 ： 
UPDATE global_variables SET variable_value='monitor'
WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123'
WHERE variable_name='mysql-monitor_password';

 
load mysql variables to runtime;
save mysql variables to disk;

2）# 查询监控日志
db01 [(none)]>select * from mysql_server_connect_log;
db01 [(none)]>select * from mysql_server_ping_log; 
db01 [(none)]>select * from mysql_server_read_only_log;
db01 [(none)]>select * from mysql_server_replication_lag_log;

3）配置web节点等应用连接ProxySQL的用户 
设定应用连接ProxySQL的用户为root，密码为123

# proxysql代理
insert into mysql_users(username,password,default_hostgroup) values('root','123',10);
load mysql users to runtime;
save mysql users to disk;

# 51主库创建允许proxysql代理访问的用户和密码
create user root@'%' identified with mysql_native_password  by '123';
grant all on *.* to root@'%';

注意；早期版本，需要开启事务持续化。今天版本不需要，只是提及。
update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;
save mysql users to disk;

让事务里的所有SQL都读写同一个库：
begin;
   select
   update
   insert
   delete
commit;

4） 读写规则流程或原理
架构图

5）配置读写规则mysql_query_rules
# ProxySQL
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

注： select … for update规则的rule_id必须要小于普通的select规则的rule_id，
ProxySQL是根据rule_id的顺序进行规则匹配。　

6. 模拟web测试读写分离
成功:
[root@db01 /usr/local/bin]# mysql -uroot -p123  -P6033 -h 127.0.0.1 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          51 |
+-------------+
[root@db01 /usr/local/bin]# mysql -uroot -p123  -P6033 -h 127.0.0.1 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          52 |
+-------------+
[root@db01 /usr/local/bin]# mysql -uroot -p123  -P6033 -h 127.0.0.1 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          53 |
+-------------+

[root@db01 ~]#  mysql -uroot -p123  -P6033 -h 127.0.0.1 -e "begin;select @@server_id;commit;"
遇到问题：上面语句可以访问到51,52,53，原因：
db01 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+-------------------------+
| hostgroup_id | hostname  | port | gtid_port | status | weight  max_connections |
+--------------+-----------+------+-----------+--------+-------------------------+
| 10           | 10.0.0.51 | 3306 | 0         | ONLINE | 1       1000            |
| 20           | 10.0.0.51 | 3306 | 0         | ONLINE | 1       1000            |
| 20           | 10.0.0.53 | 3306 | 0         | ONLINE | 1       1000            |
| 10           | 10.0.0.52 | 3306 | 0         | ONLINE | 1       1000            |
| 10           | 10.0.0.53 | 3306 | 0         | ONLINE | 1       1000            |
| 20           | 10.0.0.52 | 3306 | 0         | ONLINE | 1       1000            |
+--------------+-----------+------+-----------+--------+-------------------------+
从上面那内容可以发现，所有节点都在写组10里，所有节点都在读组20里。

最终罪魁祸首，没有在从库配置read_only=1;临时设置如下；
52/53上配置；
set global read_only=1;       ##普通用户只读。
set global super_read_only=1; ##管理员用户只读。

返回：proxysql查看，立刻发生吧变化
db01 [main]>select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+---------+
| hostgroup_id | hostname  | port | gtid_port | status | weight | comment |
+--------------+-----------+------+-----------+--------+--------+---------+
| 10           | 10.0.0.51 | 3306 | 0         | ONLINE | 1      |         |
| 20           | 10.0.0.51 | 3306 | 0         | ONLINE | 1      |         |
| 20           | 10.0.0.53 | 3306 | 0         | ONLINE | 1      |         |
| 20           | 10.0.0.52 | 3306 | 0         | ONLINE | 1      |         |
+--------------+-----------+------+-----------+--------+--------+---------+
10是写组有51,20是读组，有51,52,53.

[root@db01 ~]#  mysql -uroot -p123  -P 6033 -h 127.0.0.1 -e "select @@server_id;"
db01 [(none)]>select * from stats_mysql_query_digest\G

给开发人员:
root 123
6033
10.0.0.51
自动实现读写分离,根据语句过滤select读,其他写.

8.  ProxySQL应用扩展——花式路由规则
1. 基于端口实现读写分离
## 修改ProxySQL监听SQL流量的端口号，监听多端口上。
set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
save mysql variables to disk;
## 重启生效
systemctl restart proxysql

## 设定路由规则
delete from mysql_query_rules;      # 为了测试，先清空已有规则

insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) 
values(1,1,6033,10,1), (2,1,6034,20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

说明：    除了基于端口进行分离，还可以基于监听地址(修改字段proxy_addr即可)，也可以基于客户端地址(修改字段client_addr字段即可)。
给开发人员:
写:
root
123
6033
10.0.0.51

读
root
123
6034
10.0.0.51

2. 基于用户实现读写分离
insert into mysql_users(username,password,default_hostgroup) 
values('writer','123',10),('reader','123',20);
load mysql users to runtime;
save mysql users to disk;

delete from mysql_query_rules;      # 为了测试，先清空已有规则

insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply) 
values(1,1,'writer',10,1),(2,1,'reader',20,1);

load mysql query rules to runtime;
save mysql query rules to disk;

#最后在主库创建writer,reader用户和密码。

作业：实践基于端口和基于用户实现读写分离。

给开发人员:
写:
writer
123
6033
10.0.0.51

读:
reader
123
6033
10.0.0.51