关系型数据库MySQL（四）：读写分离

MySQL 读写分离理论基础1. 什么是读写分离在数据库访问压力较大的应用中读写操作的比例往往是不均衡的。通常读操作SELECT的数量远多于写操作INSERT, UPDATE, DELETE。读写分离的核心思想就是将读操作和写操作分发到不同的数据库服务器或集群上去执行。写操作由一个专门的服务器通常称为主节点或主库处理。所有数据的修改增、删、改都只发生在主库上。读操作由一个或多个服务器称为从节点或从库处理。这些从库的数据是通过某种机制通常是主从复制从主库同步过来的。应用系统可以将查询请求分发到这些从库上执行。2. 为什么需要读写分离分担负载将读压力分散到多个从库减轻主库的负载提高整体系统的并发处理能力和响应速度。提高可用性当主库出现故障时可以快速将一个从库提升为新的主库需要配合高可用方案。读操作在从库故障时也可以转移到其他可用从库或主库需权衡一致性。提升性能主库可以更专注于处理写操作避免因大量读操作导致的锁竞争或 I/O 瓶颈。从库可以配置不同的硬件或优化策略如只读模式、不同索引来加速读查询。数据备份与恢复从库可以作为主库数据的实时备份用于数据恢复或离线分析。3. 如何实现实现读写分离的关键在于两点数据同步确保从库的数据与主库尽可能保持一致存在延迟。MySQL 自身提供了强大的主从复制功能来实现这一点。请求路由应用程序在发出 SQL 请求时需要能够判断该请求是读操作还是写操作并将请求发送到正确的服务器主库或某个从库。这通常需要借助数据库中间件来实现或者由应用程序框架如 ShardingSphere-JDBC在代码层面处理。4. MySQL 主从复制原理简述MySQL 主从复制是读写分离的基础。其基本原理如下主库当主库上有数据修改操作写操作时这些操作会被记录到二进制日志中。主库上有一个线程Binlog Dump Thread负责读取二进制日志事件并发送给连接的从库。从库从库启动一个 I/O 线程I/O Thread连接到主库请求获取主库的二进制日志事件。I/O 线程将接收到的日志事件写入从库本地的中继日志。从库启动一个 SQL 线程SQL Thread读取中继日志中的事件并在从库上重放执行这些事件从而使得从库的数据与主库同步。复制格式主要有基于语句的复制和基于行的复制。现代 MySQL 版本通常使用混合模式或基于行的复制来保证更好的数据一致性。复制延迟由于网络传输、从库重放速度等原因从库的数据可能会稍微落后于主库即存在复制延迟。这是读写分离架构中需要考虑的一个重要因素。MySQL 读写分离OpenEuler 系统运维实例场景描述假设我们有一个运行在 OpenEuler 22.03 LTS 上的 Web 应用数据库使用 MySQL 8.0。随着用户量增长数据库读压力显著增加主库经常出现高负载。我们决定实施读写分离方案来缓解压力。目标架构采用一主master两从slave1,slave2的拓扑结构。使用数据库中间件Mycat来实现 SQL 请求的路由。---------------- ---------------- ---------------- | Application | ---- | Mycat | ---- | MySQL Master | | (Web App) | | (Proxy/Middleware)| | (Write) | ---------------- ---------------- ---------------- | | | ------ | MySQL Slave1 | | | (Read) | ------ | MySQL Slave2 | | (Read) | ----------------实施步骤第一步环境准备所有节点均使用 OpenEuler 22.03 LTS安装 MySQL 8.0 (主、从1、从2)使用 OpenEuler 的包管理器dnf安装 MySQL。sudo dnf install mysql-server sudo systemctl start mysqld sudo systemctl enable mysqld初始安全设置 (主、从1、从2)运行mysql_secure_installation脚本设置 root 密码、移除测试数据库和匿名用户等。第二步配置主库 (master)编辑 MySQL 配置文件/etc/my.cnf添加或修改以下配置项开启二进制日志并设置唯一的服务器 ID。[mysqld] server-id 1 # 主库唯一ID必须大于0且与其他节点不同 log-bin mysql-bin # 启用二进制日志指定日志文件前缀 binlog_format ROW # 推荐使用基于行的复制格式 expire_logs_days 7 # 自动清理7天前的binlog max_binlog_size 100M # 单个binlog文件最大100MB # 可选确保复制的事务是安全的 gtid_mode ON enforce_gtid_consistency ON重启 MySQL 服务使配置生效sudo systemctl restart mysqld创建用于复制的专用用户登录 MySQL (mysql -u root -p)执行CREATE USER repl% IDENTIFIED BY StrongReplPassword!; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES;记录下主库当前的二进制日志文件名和位置或 GTID 位置SHOW MASTER STATUS;输出类似------------------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | ------------------------------------------------------------------------------- | mysql-bin.000001 | 785 | | | | -------------------------------------------------------------------------------记下File和Position的值或Executed_Gtid_Set。第三步配置从库 (slave1,slave2)编辑 MySQL 配置文件/etc/my.cnf为每个从库设置唯一的server-id并可选开启中继日志和 GTID。[mysqld] server-id 2 # slave1 用 2slave2 用 3必须唯一且不同于主库 relay-log relay-log # 启用中继日志 read_only ON # 建议设置为只读模式防止误写 # 如果主库启用了GTID从库也需要配置 gtid_mode ON enforce_gtid_consistency ON重启 MySQL 服务sudo systemctl restart mysqld配置复制源登录从库 MySQL (mysql -u root -p)执行-- 如果使用 GTID (推荐) CHANGE MASTER TO MASTER_HOST master_ip_address, -- 主库IP MASTER_USER repl, MASTER_PASSWORD StrongReplPassword!, MASTER_AUTO_POSITION 1; -- 启用基于GTID的自动定位 -- 或者如果不使用GTID (使用 File 和 Position) CHANGE MASTER TO MASTER_HOST master_ip_address, MASTER_USER repl, MASTER_PASSWORD StrongReplPassword!, MASTER_LOG_FILE mysql-bin.000001, -- 之前SHOW MASTER STATUS记录的File MASTER_LOG_POS 785; -- 之前记录的Position启动复制线程START SLAVE;检查复制状态SHOW SLAVE STATUS\G关键观察点Slave_IO_Running: Yes(I/O 线程运行正常)Slave_SQL_Running: Yes(SQL 线程运行正常)Seconds_Behind_Master: 0(或很小的值表示延迟低)如果使用 GTID检查Retrieved_Gtid_Set和Executed_Gtid_Set是否在增长并与主库接近。检查Last_IO_Error和Last_SQL_Error是否为空。重复以上步骤配置slave2。第四步安装与配置 Mycat (读写分离中间件)Mycat 是一个流行的开源数据库中间件支持读写分离、分库分表等。这里我们用它来做 SQL 路由。安装 Java 环境 (Mycat 依赖)Mycat 需要 Java 运行环境。sudo dnf install java-11-openjdk-devel下载并安装 Mycat从 Mycat 官网下载稳定版本 (例如Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz)。解压到合适目录如/opt/mycat。sudo wget https://github.com/MyCATApache/Mycat-Server/releases/download/xxxx/mycat-server-xxxx.tar.gz # 替换为实际下载链接 sudo tar -zxvf mycat-server-xxxx.tar.gz -C /opt sudo mv /opt/mycat-server-xxxx /opt/mycat配置 MycatMycat 的核心配置文件在/opt/mycat/conf目录下。server.xml定义 Mycat 服务本身参数、系统用户和权限、连接属性。user namemycat_user !-- Mycat 连接用户名 -- property namepasswordMycatPass123/property property nameschemasTESTDB/property !-- Mycat 逻辑数据库名 -- /user user nameroot !-- 通常保留一个管理员用户 -- property namepassword123456/property property nameschemasTESTDB/property property namereadOnlyfalse/property /userschema.xml定义逻辑库、逻辑表、数据节点、数据源连接物理数据库。?xml version1.0? !DOCTYPE mycat:schema SYSTEM schema.dtd mycat:schema xmlns:mycathttp://io.mycat/ schema nameTESTDB checkSQLschematrue sqlMaxLimit100 !-- 逻辑库名 -- !-- 可以在此定义逻辑表及其分片/路由规则本例简单读写分离不定义表 -- /schema !-- 定义数据节点 (DataNode)一个节点对应一个物理数据库分片 -- dataNode namedn_master dataHostdh_master databaseyour_real_db / !-- 主库节点 -- dataNode namedn_slave1 dataHostdh_slave1 databaseyour_real_db / !-- 从库1节点 -- dataNode namedn_slave2 dataHostdh_slave2 databaseyour_real_db / !-- 从库2节点 -- !-- 定义数据主机 (DataHost)包含具体的物理数据库连接信息 -- dataHost namedh_master maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType-1 slaveThreshold100 heartbeatselect user()/heartbeat !-- 心跳检测SQL -- writeHost hostmasterHost urljdbc:mysql://master_ip:3306 useryour_db_user passwordYourDBPass123 !-- 主库连接 -- /writeHost /dataHost dataHost namedh_slave1 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType-1 slaveThreshold100 heartbeatselect user()/heartbeat writeHost hostslave1Host urljdbc:mysql://slave1_ip:3306 useryour_db_user passwordYourDBPass123 !-- 从库1连接 -- /writeHost /dataHost dataHost namedh_slave2 maxCon1000 minCon10 balance0 writeType0 dbTypemysql dbDriverjdbc switchType-1 slaveThreshold100 heartbeatselect user()/heartbeat writeHost hostslave2Host urljdbc:mysql://slave2_ip:3306 useryour_db_user passwordYourDBPass123 !-- 从库2连接 -- /writeHost /dataHost /mycat:schema注意your_real_db需要替换为实际的物理数据库名。your_db_user和YourDBPass123替换为应用程序连接物理数据库的账号密码需具有所需权限。rule.xml(可选)定义分片规则。对于纯读写分离且不涉及分库分表的情况可能不需要修改此文件。Mycat 内置了读写分离的规则如readindex。配置读写分离规则Mycat 通过schema.xml中的balance属性和dataHost下的readHost(本例未使用因为我们为每个从库定义了单独的dataHost) 来实现读写分离。在本例架构中我们定义了三个独立的dataHost(dh_master,dh_slave1,dh_slave2)。要让 Mycat 知道如何路由我们需要配置路由规则。这可以通过在schema.xml的schema下不定义表所有表默认路由或者使用 Mycat 的注解Hint功能但更常见的做法是配置 Mycat 的默认读写分离策略。 一个更贴近实际读写分离的schema.xml配置可能是这样的使用一个dataHost包含主库和一个或多个从库dataHost namedh_all maxCon1000 minCon10 balance1 !-- balance1 表示读操作在所有 readHost writeHost 中随机负载均衡 -- writeType0 dbTypemysql dbDriverjdbc switchType1 slaveThreshold100 heartbeatselect user()/heartbeat writeHost hostmasterHost urljdbc:mysql://master_ip:3306 userdbuser passworddbpass !-- 定义读库 -- readHost hostslave1Host urljdbc:mysql://slave1_ip:3306 userdbuser passworddbpass/ readHost hostslave2Host urljdbc:mysql://slave2_ip:3306 userdbuser passworddbpass/ /writeHost /dataHost dataNode namedn_all dataHostdh_all databaseyour_real_db / schema nameTESTDB checkSQLschematrue dataNodedn_all !-- 整个逻辑库指向这个包含主从的节点 -- /schema这种配置下所有写操作 (INSERT,UPDATE,DELETE,ALTER TABLE等) 和涉及事务的读操作默认路由到writeHost(主库)。非事务性的SELECT语句会根据balance的设置这里是1随机负载均衡路由到writeHost或任意一个readHost(从库)。switchType1通常表示在主库故障时从库可以接管写操作需要配合其他高可用机制。 选择哪种配置方式取决于管理偏好和具体的业务需求。本实例为了清晰展示主从角色采用了第一种独立dataHost的方式但需要额外的路由规则配置如使用注解或在 Mycat 中定义规则。对于新手建议先采用第二种一个dataHost包含主从的方式配置schema.xml来实现读写分离这样更简单直观。启动 Mycatcd /opt/mycat/bin ./mycat start # 启动 ./mycat status # 查看状态 ./mycat stop # 停止Mycat 默认管理端口是9066服务端口是8066。配置防火墙 (如果需要)开放 Mycat 的服务端口默认8066和应用服务器访问 Mycat 的端口。sudo firewall-cmd --permanent --add-port8066/tcp sudo firewall-cmd --reload第五步修改应用程序连接配置不再直接连接 MySQL 主库或从库而是连接 Mycat 服务。将应用程序中的数据库连接字符串JDBC URL, ORM 配置等修改为指向 Mycat 服务器和端口默认8066并使用在server.xml中配置的 Mycat 用户名和密码如mycat_user/MycatPass123数据库名使用TESTDB(Mycat 逻辑库名)。示例 (Java JDBC)String url jdbc:mysql://mycat_server_ip:8066/TESTDB?useSSLfalsecharacterEncodingutf8; String user mycat_user; String password MycatPass123; Connection conn DriverManager.getConnection(url, user, password);第六步测试与验证连接测试使用 MySQL 客户端或应用程序尝试连接 Mycat (mycat_server_ip:8066)。写操作测试通过 Mycat 执行INSERT语句检查数据是否成功写入主库并观察是否同步到了两个从库。读操作测试通过 Mycat 执行多个SELECT查询。观察 Mycat 的日志 (/opt/mycat/logs/mycat.log) 或使用SHOW PROCESSLIST在master,slave1,slave2上查看确认读请求被分发到了不同的从库或在负载均衡策略下随机分发。延迟测试在主库写入后立即通过 Mycat 读取观察是否能读到新数据取决于复制延迟。对于强一致性要求的读操作可能需要使用 Mycat 的注解强制路由到主库例如/*#mycat:sqlselect * from table where id1*/ SELECT * FROM table WHERE id 1;具体注解语法需参考 Mycat 文档压力测试使用工具如sysbench模拟大量读请求观察 Mycat 和各个从库的负载情况验证读写分离是否生效。运维注意事项监控必须监控主库、从库、Mycat 的 CPU、内存、磁盘 I/O、网络流量、连接数、复制延迟 (Seconds_Behind_Master) 等关键指标。备份定期备份数据库包括 Mycat 的配置文件。高可用读写分离本身不解决主库单点故障。需要结合主库高可用方案如 MHA, Raft-based solutions, InnoDB Cluster和 Mycat 自身的高可用如 Keepalived VIP。复制延迟理解业务对数据一致性的容忍度。对于无法容忍延迟的读操作如刚下订单后的查询需要特殊处理如路由回主库。中间件维护Mycat 需要维护和升级。关注其社区动态和版本更新。安全确保复制用户、应用连接用户、Mycat 管理用户的密码强度。限制数据库端口的访问来源。总结通过 MySQL 主从复制和 Mycat 中间件的配合我们在 OpenEuler 系统上成功部署了一个读写分离架构。这个架构有效分散了数据库的读负载提升了系统的整体性能和扩展性。实施过程中需要仔细配置主从复制关系、Mycat 的数据源和路由规则并在应用程序端调整数据库连接指向 Mycat。后续运维的重点在于监控、备份、处理复制延迟以及规划高可用方案。请记住读写分离是解决特定性能瓶颈读多写少的一种有效手段但它也引入了额外的复杂性复制延迟、中间件管理。在实施前务必评估业务需求和可能带来的影响。