什么是读写分离？
读写分离就是将对数据库的读操作和写操作分散到不同的数据库节点上
如何实现读写分离？
因为更多的读多写少，所以为了缓解主库的读能力从而引入了从库，这样就可以减少主库的负担，从而解决了应用的并发能力。
实现原理
（1）部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
（2）保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从同步。
（3）系统将写请求交给主数据库处理，读请求交给从数据库处理。
实现方法
使用proxysql、MariaDB MaxScale、MyCAT实现读写分离
在这里我来使用rpoxysql来实现
下载链接
https://proxysql.com/documentation/installing-proxysql/
首先我们需要有主从同步的环境在上篇博客，用binlog，gtid方法演示，可以参考先将主从环境搭建完成。
检查一下环境没有问题就可以进行下载配置
slave_IO_Running和slave SQL Running 都是yes状态代表没有问题
proxysql
ProxySQL是 MySQL 的高性能、高可用性、协议感知代理。以下为结合主从复制对ProxySQL读写分离、黑白名单、路由规则等做些基本测试。
基本介绍
先简单介绍下ProxySQL及其功能和配置，主要包括：
最基本的读/写分离，且方式有多种；
可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由，规则很灵活；
动态加载配置，即绝大部分的配置可以在线修改，但有少部分参数还是需要重启来生效；
可缓存查询结果。虽然缓存策略比较简陋，但实现了基本的缓存功能；
过滤危险的SQL，增加防火墙等功能；
提供连接池、日志记录、审计日志等功能；
请求流程
流量从客户端发出 → ProxySQL进行处理转发 → 后端处理 → ProxySQL的前端连接 → 返回客户端的基本流程
核心功能
读写分离：可查询走从库，写入走主库
简单Sharding：ProxySQL的sharding是通过正则匹配来实现的，对于需要拆分SQL以及合并SQL执行结果的不能支持，所以写了简单sharding
连接池管理：常规功能，为了提高SQL执行效率。
多路复用：主要优化点在后端mysql连接的复用，对比smart client，中间层不仅对前端建连也会对后端建连，可自行控制后端连接的复用逻辑。
流量管控：kill连接和kill query；whitelist配置。
高可用：底层mysql，如果从库挂了，自动摘除流量；主库挂了暂不处理。proxysql自身高可用，提供cluster的功能，cluster内部会自行同步元数据以及配置变更信息。
查询缓存：对username+schema+query的key进行缓存，设置ttl过期，不适合写完就查的场景，因为在数据在未过期之前可能是脏数据。
动态配置：大部分的配置可动态变更，先load到runtime，在save到disk，通过cluster的功能同步到其他的节点。
流量镜像：同一份流量可以多出写入，但是并不保证mirror的流量一定成功。
SQL改写：在query rules中配置replace规则，可以对指定的SQL进行改写。
监听端口
当ProxySQL启动后，将监听两个端口：
(1).admin管理接口，默认端口为6032。该端口用于查看、配置ProxySQL。
(2).接收SQL语句的接口，默认端口为6033，这个接口类似于MySQL的3306端口。
我们直接获取proxysql的rpm包来进行安装
#帮助我们可以直接通过拖拽方式将包获取
[root@master ~]# yum install lrzsz -y
#下载proxysql
[root@master ~]# yum install proxysql-2.5.5-1-centos8.x86_64.rpm
我安装后打开时有报错
报错解决方法
[root@master ~]# netstat -ntelp | grep 6032
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 986 25730 1852/proxysql
#端口没问题，程序运行但是登录不了
[root@master ~]# service proxysql stop
Redirecting to /bin/systemctl stop proxysql.service
#更改一下proxysql的权限
[root@master ~]# chown proxysql:proxysql /var/lib/proxysql/proxysql.db
[root@master ~]# service proxysql start
Redirecting to /bin/systemctl start proxysql.service
[root@master ~]# mysql -uadmin -padmin -h127.0.0.0 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.0:6032' (101)
#这里还是登录不了最后查到通过加--default-auth=mysql_native_password可以登录
[root@master ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --default-auth=mysql_native_password
#这里的报错是因为密码显示在命令行的警告，可以不予理会
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
在proxysql的数据库中有五个库
main
main用来存放内存配置，用来存放后端db实例，用户验证，路由规则等信息
mysql_servers:后端可以连接MySQL服务器的列表
mysql_users：配置后端数据库的账号和监控的账号
mysql_query_rules:指定Query路由到厚度那不同服务器的规则列表
#注意：表名以runtime_开头的表示proxysql当前运行的配置内容，不能通过DML语句修改。只能修改对应的不以runtime开头的表；然后‘LOAD’使生效，‘SAVE’存到硬盘以供下次重启加载
disk
disk：是持久化到硬盘的配置，sqlite数据文件。SQLite3 数据库，默认位置为 $（DATADIR）/proxysql.db,在重新启动时，未保留的内存中配置将丢失。因此，将配置保留在 DISK 中非常重要。(SQLite是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎)
stats
stats：proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor
monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
stats_history
stats_history:统计信息历史库
配置proxysql所需要的账户
在master（192.168.75.42）的mysql上创建proxysql的监控账户和对外访问账户
#监控账户
create user 'monitor'@'192.168.%.%' identified with mysql_native_password by 'Monitor@123.com';
#给用户所有权限，并且可以给别人授权
grant all privileges on *.* to 'monitor'@'192.168.%.%' with grant option;
#对外账户
这里的密码过于简单也不行
create user 'proxysql'@'192.168.%.%' identified with mysql_native_password by 'Mhn@2001';
grant all privileges on *.* to 'proxysql'@'192.168.%.%' with grant option;配置proxysql
使用proxysql，主要需要完成以下几项内容的配置：
1、配置监控账号。监控账号用于检测后端mysql实例是否健康（是否能连接、复制是否正常、复制是否有延迟等）。
2、到后端mysql实例创建监控账号。
3、配置后端mysql实例连接信息。实例连接信息存储在mysql_servers表。
4、配置连接proxysql和后端实例的账号。账号信息存储在mysql_users表。
5、配置查询路由信息。路由信息存储在mysql_query_rules表。
6、配置后端mysql集群信息。根据后端mysql集群架构，配置分别存储在mysql_replication_hostgroups、mysql_group_replication_hostgroups、runtime_mysql_galera_hostgroups、runtime_mysql_aws_aurora_hostgroups等表中。
7、根据具体需要，调优相关参数。参数存储在global_variables表。
mysql> show create table mysql_replication_hostgroups \G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)
#注意：wirte_hostgroup和reader_hostgroup写组和读组都要大于等于0且不能相同
insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
#写的组为1，读组为0，描述信息是做代理proxy，然后需要将所做操作加载到当前配置，并且永久保存
load mysql servers to runtime;
save mysql servers to disk;
注意：ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server，master被分到编号为1的写组，read_only=1的server，slave则分到编号为0的读组
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 1 | 0 | read_only | proxy |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)
配置主从节点
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.75.42',3306);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.75.43',3306);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.75.44',3306);
Query OK, 1 row affected (0.00 sec)
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from 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.75.42 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | 192.168.75.43 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 0 | 192.168.74.44 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
为ProxySQL监控MySQL后端节点
mysql> use monitor;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
#设置监控账号和密码
mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='Monitor@123.com';
Query OK, 1 row affected (0.00 sec)
#加载运行时，并且保存
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 158 rows affected (0.01 sec)
#查看监控账户
mysql> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor |
+--------------------------+
1 row in set (0.00 sec)
#查看监控密码
mysql> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| Monitor@123.com |
+--------------------------+
1 row in set (0.00 sec)
#查看montior.mysql的连接日志
发现44并不能连接成功，发现我们在刚才配置连接节点时IP输入错误
修改过后从这里可以看到三个主机都连接成功
接下来查看心跳信息的监控
mysql> select * from mysql_server_ping_log ;
这里查看到我们的三个节点都没有问题
查看read_only日志监控
mysql> select * from mysql_server_read_only_log limit 10;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.168.75.42 | 3306 | 1709287002904746 | 1439 | 0 | NULL |
| 192.168.75.43 | 3306 | 1709287002905105 | 1110 | 0 | NULL |
| 192.168.75.44 | 3306 | 1709287002904967 | 1261 | 0 | NULL |
| 192.168.75.43 | 3306 | 1709287004405423 | 569 | 0 | NULL |
| 192.168.75.42 | 3306 | 1709287004405528 | 510 | 0 | NULL |
| 192.168.75.44 | 3306 | 1709287004405408 | 646 | 0 | NULL |
| 192.168.75.44 | 3306 | 1709287005905071 | 1270 | 0 | NULL |
| 192.168.75.42 | 3306 | 1709287005905413 | 979 | 0 | NULL |
| 192.168.75.43 | 3306 | 1709287005905294 | 1122 | 0 | NULL |
| 192.168.75.44 | 3306 | 1709287007406363 | 577 | 0 | NULL |
+---------------+------+------------------+-----------------+-----------+-------+
10 rows in set (0.00 sec)
这里发现都是0，都变成了master所以需要进行修改
所以需要在两个从库的配置文件中加上read_only=1，并且记得重启mysql
现在就可以看到我们的主库为0，两个从库为1
配置proxysql对外账号（要在mysql节点上建立）
前面已经配置：配置ProxySQL 账户，我创建的对外访问账户是：用户：proxysql，密码：Mhn@2001
将对外账号添加到mysql users表中
mysql> insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','Mhn@2001',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.01 sec)
在从库端192.168.75.43上通过对方访问账号proxy连接，测试是否路由能默认到hostgroup_id=1，它是一个写组
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
在我们的192.168.75.44中查看一下是否同步
可以看到已经同步。
添加读写分离规则（mysql_query_rules）
proxysql支持正则，这里添加两条匹配规则, 1) 表示像select * from xxx for update这种语句都会分到到写组，2）表示像select这种语句都会被分配到读组。
mysql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',0,1);
Query OK, 1 row affected (0.01 sec)
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)测试读写分离
[root@slave1 ~]# mysql -uproxysql -pMhn@2001 -h 192.168.75.41 -P 6033 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
[root@slave1 ~]# mysql -uproxysql -pMhn@2001 -h 192.168.75.41 -P 6033 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
[root@slave1 ~]# mysql -uproxysql -pMhn@2001 -h 192.168.75.41 -P 6033 -e "begin;select @@server_id commit;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| commit |
+--------+
| 1 |
+--------+
