文章

MySQL 读写分离

Posted 2021-03-15 Updated 2025-03- 11
By 韩壮壮
41~53 min read

MySQL读写分离

1、MySQL读写分离概述

当今MySQL使用相当广泛，随着用户的增多以及数据量的增大，高并发随之而来；然而我们有很多办法可以缓解数据库的压力，分布式数据库集群、负载均衡、读写分离、增加缓存服务器等等。采用读写分离技术缓解数据库的压力是一种不错的方式。

1.1 MySQL读写分离的好处

  • 物理服务器增加，机器处理能力提升，拿硬件换性能。
  • 主从只负责各自的读和写，极大程度缓解X锁和S锁争用。
  • slave可以配置myiasm引擎，提升查询性能以及节约系统开销。
  • slave可以单独设置一些参数来提升其读的性能。
  • master的写是并发，slave通过主库发送来的binlog同步数据。
  • 增加冗余，提高可用性。

1.2 读写分离实现方式

  • 代码封装
    在代码层面抽出一个中间层，由中间层来实现读写分离和数据库连接。通过代理类代码，对外暴露正常的读写接口，里面封装了逻辑，将读操作指向从库的数据源，写操作指向主库的数据源。

    • 优点：简单（开发写代码实现），并且可以根据业务定制化变化，随心所欲。
    • 缺点：如果数据库宕机了，发生主从切换了之后，就得修改配置重启。如果系统是多语言的话，需要为每个语言都实现一个中间层代码，重复开发。

  • 中间件
    一般而言是独立部署的系统，客户端与这个中间件的交互是通过 SQL 协议的。所以在客户端看来连接的就是一个数据库，通过SQL协议交互也可以屏蔽多语言的差异。

    • 缺点：就是整体架构多了一个系统需要维护，并且可能成为性能瓶颈，毕竟交互都需要经过它中转。

常见的开源数据库中间件有：官方的MySQL-Proxy、360的Atlas、Mycat、ProxySQL等。

1.3 MySQL读写分离原理

读写分离的基本原理是让主数据库处理事务性的增、删、改操作（INSERT、DELETE、UPDATE），而从数据库处理SELECT查询操作。MySQL主从复制将事务性的增、删、改操作同步到集群中的从数据库。

MySQL读写分离架构图
MySQL读写分离.jpg

在应用和数据库之间增加代理层（中间件），代理层接收应用对数据库的请求，根据不同请求类型转发到不同的实例，在实现读写分离的同时可以实现负载均衡。

2、MySQLProxy实现读写分离

2.1 MySQLProxy简介

MySQL Proxy是MySQL官方提供的数据库代理工具；MySQL Proxy实际上是在客户端请求与MySQLServer之间建立了一个连接池，所有客户端请求都是发向MySQL Proxy，然后经由MySQL Proxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上；对于多节点Slave集群，可以实现负载均衡的效果。

2.2 MySQLProxy读写分离配置

实验拓扑图
mysqlproxy.jpg

实验环境：

主机 IP地址
Master 192.168.52.128
Slave 192.168.52.129
Proxy 192.168.52.130
  • 配置MySQL主从复制
//主服务器和从服务器安装并初始化mariadb
[root@master ~]# dnf -y install mariadb mariadb-server
[root@master ~]# systemctl enable mariadb
[root@master ~]# systemctl start mariadb
[root@master ~]# mysql_secure_installation 

[root@slave ~]# dnf -y install mariadb mariadb-server
[root@slave ~]# systemctl enable mariadb
[root@slave ~]# systemctl start mariadb
[root@slave ~]# mysql_secure_installation 

//主服务器关闭防火墙和selinux
[root@master ~]# systemctl stop firewalld.service 
[root@master ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

//从服务器关闭防火墙和selinux
[root@slave ~]# systemctl stop firewalld.service 
[root@slave ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@slave ~]# setenforce 0

//在主服务器上配置主从复制，开启二进制日志，设置服务id
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin=mysql-bin			//开启二进制日志
server_id=1				//服务id号，不可重复,为0则拒绝从服务器连接
innodb_flush_log_at_trx_commit=1	//每个事务提交时立即写入binlog
sync_binlog=1				//事务提交后立刻刷新binlog

[root@master ~]# systemctl restart mariadb.service 

//在主服务器授权一个数据库用户用于复制，创建完成后在从服务器测试登录
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'zcfz'@'%' IDENTIFIED BY 'hzz123!';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

//在从服务器配置主从复制，设置服务id
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server_id=2			//服务id号，不可重复,为0则拒绝从服务器连接
read_only=1			//设置从服务器普通用户只读，不会影响主从复制

[root@slave ~]# systemctl restart mariadb.service 

//登录到从服务器数据库，在数据库中配置主服务器信息
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.52.128',MASTER_PORT=3306,MASTER_USER='zcfz',MASTER_PASSWORD='hzz123!';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.001 sec)

//查看从服务器主从复制是否开启
MariaDB [(none)]> SHOW SLAVE STATUS \G 
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.52.128
                   Master_User: zcfz
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 638
                Relay_Log_File: mariadb-relay-bin.000003
                 Relay_Log_Pos: 937
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
.....省略.....
  • MySQL Proxy实现读写分离
//proxy节点关闭防火墙和selinux
[root@proxy ~]# systemctl stop firewalld.service 
[root@proxy ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@proxy ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@proxy ~]# setenforce 0

//在proxy节点下载MySQL-Proxy工具
[root@proxy ~]# wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

//解压MySQL-Proxy工具
[root@proxy ~]# tar -xvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@proxy ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

//设置MySQL-Proxy工具环境变量
[root@proxy ~]# echo 'export PATH=$PATH:/usr/local/mysql-proxy/bin' >> ~/.bash_profile 
[root@proxy ~]# source ~/.bash_profile 

//配置MySQL-Proxy配置文件设置读写分离
[root@proxy ~]# mkdir /usr/local/mysql-proxy/conf
[root@proxy ~]# mkdir /usr/local/mysql-proxy/logs

[root@proxy ~]# vim /usr/local/mysql-proxy/conf/mysql-proxy.conf
[mysql-proxy]
plugins=proxy						//proxy插件
user=root 						//以root身份运行mysql-proxy
proxy-address=192.168.52.130:3306			//监听本机中IP地址和端口，默认端口4040。
proxy-backend-addresses=192.168.52.128:3306		//进行写的数据库master
proxy-read-only-backend-addresses=192.168.52.129:3306	//进行读的数据库slave
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua	//指定读写分离脚本文件
pid-file=/usr/local/mysql-proxy/logs/mysql-proxy.pid	//pid文件位置
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log    //生成日志文件
log-level=debug						//日志等级为debug
keepalive=true     					//心跳检测，崩溃时，尝试重启
daemon=true        					//守护进程
:wq！

//修改配置文件权限，防止信息泄露，如果不修改启动报错
[root@proxy ~]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf 

//设置读写分离连接数量
[root@proxy ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,	//默认超过4个连接数时，才开始读写分离，改为1
                max_idle_connections = 8,

                is_debug = false
        }
end

//开启MySQL-Porxy实现读写分离
[root@proxy ~]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

//在主服务器和从服务器上授权一个用户进行登录测试，主服务器和从服务器用户需一致
//只需在主服务器授权一个用户，主从复制会将此用户同步至从服务器
MariaDB [(none)]> GRANT ALL ON *.* TO 'hzz'@'%' IDENTIFIED BY 'hzz123!';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

//测试，在测试端上登录到proxy代理
[root@test ~]# mysql -uhzz -phzz123! -h192.168.52.130

//登录到proxy代理后,创建一个数据库和表
MariaDB [(none)]> create database hzz;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hzz                |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> create table hzz.hzztable(id int(7));
Query OK, 0 rows affected (0.004 sec)

//然后在从服务器上停止主从复制
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.001 sec)

//回到测试端，登录到proxy代理，插入一条数据，发现select查看不到，说明读写分离成功
MariaDB [(none)]> insert into hzz.hzztable(id)value(1);
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> select * from hzz.hzztable;
0 row in set (0.001 sec)

3、ProxySQL实现读写分离

3.1 ProxySQL简介

ProxySQL是灵活强大的MySQL代理层, 是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持Query路由功能，支持动态指定某个SQL进行 cache，支持动态加载配置、故障切换和一些SQL的过滤功能。还有一些同类产品比如DBproxy、MyCAT、OneProxy等。但经过反复对比和测试之后，还是觉得ProxySQL是一款性能不谙，靠谱稳定的MySQL中间件产品！

官方站点：https://proxysql.com
官方github：https://github.com/sysown/proxysql
官方手册：https://proxysql.com/documentation

3.2 ProxySQL架构

ProxySQL架构图
proxy SQL.png

ProxySQL启动后，将监听两个端口：

  • admin管理端口，默认端口为6032。该端口用于查看、配置ProxySQL
  • 服务端口，默认端口为6033，这个接口类似于MySQL的3306端口

ProxySQL多层管理配置设计 (有三层配置)：

  • runtime：运行中正在使用的配置
  • memory：提供用户动态修改配置
  • disk：持久层，将修改的配置保存到磁盘SQLite表中（即：proxysql.db）
  • config：配置文件（即：proxysql.cnf），和disk一样处于持久层，一般不使用它，仅仅在第一次开启ProxySQL时加载

ProxySQL配置系统分为三层的目的：

  • 自动更新
  • 尽可能的不重启proxysql就可以修改配置
  • 错误配置时方便回滚

注意事项：

  • ProxySQL每一个配置项在三层中都存在，但是这三层是互相独立的，也就是说proxysql可以同时拥有三份配置，每层都是独立的，可能三份配置都不一样，也可能三份都一样
  • RUNTIME层代表ProxySQL当前生效的正在使用的配置，无法直接修改这里的配置，必须要从下一层"load"进来
  • MEMORY这一层上面连接RUNTIME层，下面连接持久化层，在这层可以正常操作 ProxySQL配置，随便修改，不会影响生产环境。修改一个配置一般都是先在 MEMORY层完成，然后确认正常之后再加载到RUNTIME和持久化到磁盘上
  • DISK和CONFIG FILE层持久化配置信息，重启后内存中的配置信息会丢失，所以需要将配置信息保留在磁盘中，重启时可以从磁盘快速加载回来。

3.3 ProxySQL配置文件介绍

//第一次启动前修改CONFIG FILE配置文件
[root@proxysql ~]# vim /etc/proxysql.cnf
datadir="/var/lib/proxysql"			#数据目录
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
        admin_credentials="admin:admin"		#管理员用户名与密码
        mysql_ifaces="0.0.0.0:6032"		#管理端口，用来连接proxysql的管理数据库
}

mysql_variables=
{
        threads=4				#指定转发端口开启的线程数量
        max_connections=2048
        default_query_delay=0
        default_query_timeout=36000000
        have_compress=true
        poll_timeout=2000
        interfaces="0.0.0.0:6033"		#指定代理端口
        default_schema="information_schema"
        stacksize=1048576
        server_version="5.5.30"			#指定后端mysql的版本
        connect_timeout_server=3000
        monitor_username="monitor"
        monitor_password="monitor"
        monitor_history=600000
        monitor_connect_interval=60000
        monitor_ping_interval=10000
        monitor_read_only_interval=1500
        monitor_read_only_timeout=500
        ping_interval_server_msec=120000
        ping_timeout_server=500
        commands_stats=true
        sessions_sort=true
        connect_retries_on_failure=10
}

mysql_servers =
(
)

mysql_users:
(
)

mysql_query_rules:
(
)

scheduler=
(
)

mysql_replication_hostgroups=
(
)

[root@proxysql ~]# systemctl start proxysql

3.4 ProxySQL在线配置数据库介绍

//通过mysql客户端登录到ProxySQL管理端口
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032

MySQL [(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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
库名 作用
main memory层的内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以runtime_开头的表示proxysql当前运行的配置内容，不可修改，只能修改对应的非runtime_ 开头的（在内存）里的表，然后LOAD使其生效，SAVE使其存到硬盘以供下次重启加载。
disk 持久化到硬盘的sqlite数据文件。在重新启动时，“main” 不会持久存在，需要从“磁盘”数据库或配置文件中加载。
stats 包含proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总、执行时间等等。
monitor 存储monitor模块收集的信息，主要是对后端db的健康/延迟检查。

3.5 main数据库常用配置表介绍

//main内存配置数据库中常用配置表
MySQL [(none)]> show tables;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| global_variables                                   |
| mysql_collations                                   |
| mysql_query_rules                                  |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
+----------------------------------------------------+
表名 作用
global_variables 该表定义管理变量和MySQL变量；管理变量，控制管理界面的行为，他们的名字以标记“admin-”开头；mysql变量，控制代理的MySQL功能，他们的名字以标记“mysql-”开头。
global_variables官网参考文档：https://proxysql.com/documentation/global-variables
mysql_collations 相关字符集和校验规则。
mysql_query_rules 定义查询路由规则。
mysql_replication_hostgroups 监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组。ProxySQL monitor模块会监控hostgroups后端所有servers的read_only 变量，如果发现从库的read_only变为0、主库变为1，则认为角色互换了，自动改写mysql_servers表里面 hostgroup关系，达到自动Failover效果。
mysql_servers 包含要连接的ProxySQL的后端服务器列表。
mysql_users 包含配置后端数据库的程序账号和监控账号。

3.6 stats数据库常用配置表介绍

//stats库中常用的表
MySQL [(none)]> show tables from stats;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| stats_mysql_commands_counters        |
| stats_mysql_connection_pool          |
| stats_mysql_processlist              |
| stats_mysql_query_digest             |
| stats_mysql_query_rules              |
+--------------------------------------+
表名 作用
stats_mysql_commands_counters 统计各种SQL类型的执行次数和时间，通过参数mysql-commands_stats控制开关，默认是ture。
stats_mysql_connection_pool 连接后端MySQL的连接信息。
stats_mysql_processlist 类似MySQL的show processlist的命令，查看各线程的状态。
stats_mysql_query_digest 表示SQL的执行次数、时间消耗等。通过变量mysql-query_digests控制开关，默认是开。
stats_mysql_query_rules 路由命中次数统计。

3.7 monitor数据库常用配置表介绍

//monitor库中常用的表        
MySQL [(none)]> show tables from monitor;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| mysql_server_connect_log             |
| mysql_server_ping_log                |
| mysql_server_replication_lag_log     |
+--------------------------------------+
表名 作用
mysql_server_connect_log 连接到所有MySQL服务器以检查它们是否可用，该表用来存放检测连接的日志。
mysql_server_ping_log 使用mysql_ping API ping后端MySQL服务器，检查它们是否可用，该表用来存放ping的日志。
mysql_server_replication_lag_log 后端MySQL服务主从延迟的检测。

3.8 ProxySQL配置流程

ProxySQL安装部署官方文档：https://proxysql.com/documentation/installing-proxysql/

ProxySQL基础配置官方文档：https://proxysql.com/documentation/ProxySQL-Configuration

ProxySQL读写分离官方文档：https://proxysql.com/documentation/proxysql-read-write-split-howto

ProxySQL分片官方文档：https://proxysql.com/documentation/how-to-setup-proxysql-sharding/

MySQL
MySQL
License: