Appearance
数据库集群读写分离
数据库服务读写分离概述介绍
在应用数据库主从架构或者数据库冗余架构时，都可以有效保证数据库逻辑故障或物理故障对业务的影响；
但是，这两种架构进行应用时，业务访问过程只是访问主数据库节点，进行读写操作，当并发量大时无型中会对主节点造成不小压力；
因此，可以设计一种新型的业务访问架构方式，可以实现将写数据请求发送到主节点，将读数据请求发送到从节点；
最终，可以有效减少主节点的业务访问压力，这样设计的数据库架构称之为读写分离架构；
数据库服务读写分离架构搭建
步骤一：读写分离架构部署环境规划
为了实现读写分离架构构建，需要准备好三节点数据库+GTID复制环境+MHA环境（普通主从环境也可以构建）；
| 主机角色 | 主机名称 | 地址信息 |
|---|---|---|
| 主库服务器 | 192.168.30.101 | 3306 |
| 从库服务器 | 192.168.30.102 | 3306 |
| 从库服务器 | 192.168.30.103(兼做管理节点) | 3306 |
步骤二：读写分离架构软件下载安装
通过官方网站或者github可以下载proxySQL软件程序，并上传到数据库服务器中进行安装；
shell
# 上传安装软件程序
[root@xiaoQ-03 ~]# rpm -ivh proxysql-2.4.6-1-centos7.x86_64.rpm
警告：proxysql-2.4.6-1-centos7.x86_64.rpm: V4 RSA/SHA512 Signature, 密钥 ID 8217c97e: NOKEY
准备中... ################################# [100%]
# 启动运行软件程序
[root@xiaoQ-03 ~]# systemctl start proxysql
[root@xiaoQ-03 ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 83020/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 83020/proxysql
-- 启动生成的6032端口为管理端口，用于配置数据库中间件的功能信息连接此端口
-- 启动生成的6033端口为访问端口，用于提供对外的业务访问此端口步骤三：读写分离架构软件管理配置
在连接进入6032端口之后，表示进行proxysql的管理终端环境，终端环境中会加载五个重要的功能库：
| 序号 | 库信息 | 配置信息 | 解释说明 |
|---|---|---|---|
| 01 | main | mysql_servers | 表示后端可以连接mysql服务器的列表 |
| mysql_users | 表示配置后端数据库的连接账号和监控账号 | ||
| mysql_query_rules | 表示指定query路由到后端不同服务器的规则列表 | ||
| mysql_replication_hostgroups | 表示节点分组配置信息，可以配置多个写或读节点到一个组中 | ||
| 02 | disk | 表示持久化的磁盘配置信息 | |
| 03 | stats | 表示统计信息的汇总 | |
| 04 | monitor | 表示监控收集的信息，比如数据库的监控状态等 | |
| 05 | stats_history | 表示收集的有关软件内部功能的历史指标 |
说明：一般服务是通过配置文件保存功能配置信息，proxySQL是通过数据库中的表进行配置信息的存储设置；
shell
# 连接进入到proxySQL管理终端
[root@xiaoQ-03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
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)
# 查看proxySQL终端数据库信息
>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)
-- 不需要use到相应数据库中进行操作，可以操作的数据表信息如下
# 查看proxySQL终端数据表信息
>show tables ;
+-----------------------------------------------------------------+
| tables
+-----------------------------------------------------------------+
| global_variables
| mysql_aws_aurora_hostgroups
| mysql_collations
| mysql_firewall_whitelist_rules
| mysql_firewall_whitelist_sqli_fingerprints
| mysql_firewall_whitelist_users
| mysql_galera_hostgroups
| mysql_group_replication_hostgroups
| mysql_query_rules
| mysql_query_rules_fast_routing
| mysql_replication_hostgroups
| mysql_servers
| mysql_users
| proxysql_servers
| restapi_routes
| runtime_checksums_values
| runtime_global_variables
| runtime_mysql_aws_aurora_hostgroups
| runtime_mysql_firewall_whitelist_rules
| runtime_mysql_firewall_whitelist_sqli_fingerprints
| runtime_mysql_firewall_whitelist_users
| runtime_mysql_galera_hostgroups
| runtime_mysql_group_replication_hostgroups
| runtime_mysql_query_rules
| runtime_mysql_query_rules_fast_routing
| runtime_mysql_replication_hostgroups
| runtime_mysql_servers
| runtime_mysql_users
| runtime_proxysql_servers
| runtime_restapi_routes
| runtime_scheduler
| scheduler
+-----------------------------------------------------------------+
32 rows in set (0.00 sec)
-- 表名以runtiem_开头的表示proxySQL服务中当前运行的配置内容，不能直接修改，不带runtime是下文图中mem相关的配置ProxySQL管理接口的多层配置关系：
ProxySQL整套配置系统分为三层：
第一层：RUNTIME:
代表proxySQL当前正在使用的配置，无法直接修改此配置，必须要从下一层（MEM层）load加载进来；
第二层：MEMORY（主要修改的配置表）
memory层上面连接runtime层，下面连接disk持久化存储层；
在这层可以在线操作ProxySQL配置，随意进行修改，不会影响生产环境，确认正常之后再加载到runtime和持久化保存到磁盘上
具体修改操作方法为：insert、update、delete、select；
第三层：DISK/CFG FILE
持久化配置信息，重启时可以从磁盘快速加载回来；
ProxySQL基于SQL语句进行读写分离实践配置：
①. 在mysql_replication_hostgroup表中，配置读写组编号：
proxySQL会根据server的read only的取值将服务器进行分组：
- read_only=0的server，即master会被分到编号为10的写组；
- read_only=1的server，即slave会被分到编号为20的读组；（
所以需要将从库设置：set global read_only=1）
SQL
db03 [(none)]>insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
Query OK, 1 row affected (0.00 sec)
db03 [(none)]>save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
db03 [(none)]>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
db03 [(none)]>select * from mysql_replication_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
reader_hostgroup: 20
check_type: read_only
comment: proxy
1 row in set (0.00 sec)② 添加主机到ProxySQL
sql
db03 [(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.30.110',3306);
Query OK, 1 row affected (0.00 sec)
db03 [(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values (20,'192.168.30.102',3306);
Query OK, 1 row affected (0.00 sec)
db03 [(none)]>insert into mysql_servers(hostgroup_id,hostname,port) values (20,'192.168.30.103',3306);
Query OK, 1 row affected (0.00 sec)
db03 [(none)]>save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
db03 [(none)]>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
db03 [(none)]>select * from mysql_servers\G;
*************************** 1. row ***************************
hostgroup_id: 20
hostname: 192.168.30.102
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 20
hostname: 192.168.30.103
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 10
hostname: 192.168.30.110
port: 3306
gtid_port: 0
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
3 rows in set (0.00 sec)③ 创建监控用户，并开启监控
利用监控用户对后端节点的运行情况进行监控数据同步，一旦后端节点出现数据同步异常，就不要再向故障节点发送相应业务请求；
shell
# 主库创建监控用户
db01 [(none)]>create user monitor@'%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.02 sec)
db01 [(none)]>grant replication client on *.* to monitor@'%';
Query OK, 0 rows affected (0.01 sec)
# 在proxysql中修改variables表配置信息
db03 [(none)]>set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
db03 [(none)]>set mysql-monitor_password='123';
Query OK, 1 row affected (0.00 sec)
-- 以上变量信息修改为方法一
db03 [(none)]>update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
db03 [(none)]>update global_variables set variable_value='123' where variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
-- 以上变量信息修改为方法二
db03 [(none)]>load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)
db03 [(none)]>save mysql variables to disk;
Query OK, 154 rows affected (0.00 sec)
# 检查核实配置信息
db03 [(none)]>select @@mysql-monitor_username\G
*************************** 1. row ***************************
@@mysql-monitor_username: monitor
1 row in set (0.00 sec)
db03 [(none)]>select @@mysql-monitor_password\G
*************************** 1. row ***************************
@@mysql-monitor_password: 123
1 row in set (0.00 sec)
# 查询监控日志信息
db03 [(none)]>select * from mysql_server_connect_log;
+-------------------+------+-------------------------+---------------------------------+----------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+-------------------+------+-------------------------+---------------------------------+----------------------------------------+
| 192.168.30.110 | 3306 | 1674026545375939 | 2831 | NULL |
| 192.168.30.103 | 3306 | 1674026546137911 | 1480 | NULL |
| 192.168.30.102 | 3306 | 1674026546899730 | 3781 | NULL |
-- 检查确认所有节点的连接访问情况
db03 [(none)]>select * from mysql_server_ping_log;
+-------------------+------+-------------------------+-----------------------------+---------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-------------------+------+-------------------------+-----------------------------+---------------+
| 192.168.30.102 | 3306 | 1674026696004217 | 1139 | NULL |
| 192.168.30.103 | 3306 | 1674026696095455 | 194 | NULL |
| 192.168.30.110 | 3306 | 1674026696186794 | 1466 | NULL |
-- 检查确认所有节点的网络连通情况
db03 [(none)]>select * from mysql_server_read_only_log limit 3;
+-------------------+------+-------------------------+----------------------+-------------+--------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+-------------------+------+-------------------------+----------------------+-------------+--------+
| 192.168.30.110 | 3306 | 1674027579464285 | 1325 | 0 | NULL |
| 192.168.30.102 | 3306 | 1674027579479777 | 1743 | 1 | NULL |
| 192.168.30.103 | 3306 | 1674027579494993 | 308 | 1 | NULL |
-- 检查确认所有节点的只读状态信息（获取主库或从库主机信息）
db03 [(none)]>select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)
-- 检查确认所有节点的主从延时情况④ 创建应用用户信息
创建数据库应用用户信息，利用应用用户，可以使proxySQL进行数据库节点的操作管理；
shell
# 主库创建应用用户
db01 [(none)]>create user root@'%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.00 sec)
db01 [(none)]>grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
# 在proxysql中添加数据库节点的管理用户信息
db03 [(none)]>insert into mysql_users(username,password,default_hostgroup) values('root','123',10);
Query OK, 1 row affected (0.00 sec)
db03 [(none)]>load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
db03 [(none)]>save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
# 早期版本，需要开启事务的持续化（忽略）
update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;
save mysql users to disk;
-- 事务路由分配持续性，同一个事务的语句不会被分配到不同的组实用的读写规则配置
sql
> 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的顺序进行规则匹配的；
⑥ 测试读写分离效果
shell
[root@xiaoQ-03 ~]# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "begin;select @@server_id;commit"
+-----------------+
| @@server_id |
+-----------------+
| 51 |
+-----------------+
-- 非查询操作走的是主节点
[root@xiaoQ-03 ~]# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "select @@server_id;"
+-----------------+
| @@server_id |
+-----------------+
| 52 |
+-----------------+
[root@xiaoQ-03 ~]# mysql -uroot -p123 -P6033 -h127.0.0.1 -e "select @@server_id;"
+-----------------+
| @@server_id |
+-----------------+
| 53 |
+-----------------+
-- 查询操作走的是从节点
>select * from stats_mysql_query_digest\G
-- 这个表对于分析SQL语句至关重要，是分析语句性能、定制路由规则指标的最主要来源读写分离配置过程总结：
| 步骤 | 操作说明 | 涉及数据表信息 | 涉及操作信息 |
|---|---|---|---|
| 01 | 设置从库只读模式 | read_only=1 | |
| 02 | 添加主机组信息 | mysql_replication_hostgroups | |
| 03 | 添加主机组节点信息 | mysql_servers | |
| 04 | 添加用户信息（监控用户 应用用户） | global_variables mysql_users | |
| 05 | 添加读写分离规则 | mysql_query_rules |