SQL篇——【MySQL篇：运维】高可用架构搭建（主从、读写分离、分库分表）
·
目录
本文系统介绍了MySQL数据库管理的核心技术，包括日志系统、主从复制、分库分表和读写分离。日志系统详细解析了错误日志、二进制日志(BINLOG)、查询日志和慢查询日志的配置与使用。主从复制部分阐述了原理及搭建方法，包括主库和从库的具体配置步骤。分库分表章节深入探讨了垂直拆分和水平拆分策略，并详细说明了MyCat中间件的配置方法，涵盖逻辑结构、用户权限管理和分片规则设置。读写分离部分重点讲解了一主一从和双主双从架构的配置要点，以及负载均衡策略的选择。最后提供了生产环境部署建议，包括监控、备份和测试等关键环节。全文内容丰富，涵盖了MySQL数据库管理的核心知识点，为数据库性能优化和高可用架构设计提供了实用指导。
1. 日志
1.1 错误日志
-
作用：记录 MySQL 启动、停止和运行中的严重错误信息
-
默认开启：
/var/log/mysqld.log -
查看位置：
SHOW VARIABLES LIKE '%log_error%';
1.2 二进制日志（BINLOG）
1.2.1 介绍
-
记录内容：所有 DDL 和 DML 语句（不包括查询语句）
-
作用：
-
灾难时的数据恢复
-
MySQL 主从复制
-
-
默认开启（MySQL 8.0）
1.2.2 日志格式
| 格式 | 含义 |
|---|---|
| STATEMENT | 基于 SQL 语句的日志记录（记录 SQL 语句） |
| ROW | 基于行的日志记录（记录每一行数据变更）默认 |
| MIXED | 混合格式，默认 STATEMENT，特殊时切换为 ROW |
1.2.3 查看二进制日志
# 使用 mysqlbinlog 工具查看
mysqlbinlog [参数选项] logfilename
# 示例
mysqlbinlog -v binlog.000008 # 将行事件重构为 SQL 语句1.2.4 删除二进制日志
-- 删除全部 binlog 日志（从 binlog.000001 重新开始）
RESET MASTER;
-- 删除指定编号之前的所有日志
PURGE MASTER LOGS TO 'binlog.000004';
-- 删除指定时间之前的所有日志
PURGE MASTER LOGS BEFORE '2023-01-01 00:00:00';1.2.5 二进制日志过期时间
-- 查看二进制日志过期时间（默认30天）
SHOW VARIABLES LIKE '%binlog_expire_logs_seconds%';1.3 查询日志
-
作用：记录客户端的所有操作语句（包括查询）
-
默认关闭（日志量巨大）
-
开启配置（在
/etc/my.cnf中）：general_log=1 general_log_file=mysql_query.log
1.4 慢查询日志
-
作用：记录执行时间超过指定值的 SQL 语句
-
默认关闭
# 在 /etc/my.cnf 中配置
slow_query_log=1
long_query_time=2 # 执行时间超过2秒视为慢查询
# 记录管理语句和未使用索引的查询
log_slow_admin_statements=1
log_queries_not_using_indexes=12. 主从复制
2.1 主从复制概述
-
概念：将主数据库的 DDL 和 DML 操作通过二进制日志传到从库，从库重做这些日志，保持数据同步
-
优点：
-
主库故障时可快速切换到从库
-
实现读写分离，降低主库压力
-
在从库备份，不影响主库服务
-
2.2 主从复制原理
Master → 二进制日志(Binlog) → Slave → 中继日志(Relay Log) → 重做 → 数据同步2.3 主从复制搭建
2.3.1 主库配置
# /etc/my.cnf 配置
server-id=1 # 服务ID，集群中唯一
read-only=0 # 0代表读写，1代表只读
# binlog-ignore-db=mysql # 忽略同步的数据库
# binlog-do-db=db01 # 指定同步的数据库-- 创建远程连接账号并授权
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
-- 查看二进制日志坐标（记下 File 和 Position）
SHOW MASTER STATUS;2.3.2 从库配置
# /etc/my.cnf 配置
server-id=2 # 与主库不同
read-only=1 # 从库设为只读-- 设置主库配置（MySQL 8.0.23+）
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.200.200',
SOURCE_USER='itcast',
SOURCE_PASSWORD='Root@123456',
SOURCE_LOG_FILE='binlog.000004',
SOURCE_LOG_POS=663;
-- MySQL 8.0.23 之前版本
CHANGE MASTER TO
MASTER_HOST='192.168.200.200',
MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456',
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=663;
-- 开启同步
START REPLICA; -- 8.0.22之后
START SLAVE; -- 8.0.22之前
-- 查看同步状态
SHOW REPLICA STATUS; -- 8.0.22之后
SHOW SLAVE STATUS; -- 8.0.22之前3. 分库分表
3.1 分库分表概述
3.1.1 问题分析
-
IO瓶颈：热点数据多，数据库缓存不足
-
CPU瓶颈：复杂查询耗费大量CPU资源
3.1.2 拆分策略
| 拆分方式 | 描述 |
|---|---|
| 垂直分库 | 按业务将不同表拆分到不同库 |
| 垂直分表 | 按字段属性将字段拆分到不同表 |
| 水平分库 | 按规则将一个库数据拆分到多个库 |
| 水平分表 | 按规则将一个表数据拆分到多个表 |
3.1.3 实现技术
-
ShardingJDBC：基于AOP原理，应用程序中拦截SQL（Java，性能高）
-
MyCat：数据库中间件，无需调整代码（支持多语言）
3.2 MyCat概述
-
介绍：开源的MySQL数据库中间件，对开发透明
-
目录结构：
-
bin/：可执行文件 -
conf/：配置文件 -
lib/：依赖包 -
logs/：日志文件
-
3.3 MyCat入门配置
3.3.1 schema.xml（逻辑结构配置）
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 定义逻辑库 -->
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
<!-- 定义逻辑表：数据分布在dn1,dn2,dn3节点，使用auto-sharding-long规则 -->
<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"/>
</schema>
<!-- 定义数据节点 -->
<dataNode name="dn1" dataHost="dhost1" database="db01"/>
<dataNode name="dn2" dataHost="dhost2" database="db01"/>
<dataNode name="dn3" dataHost="dhost3" database="db01"/>
<!-- 定义节点主机 -->
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc">
<heartbeat>select user()</heartbeat>
<writeHost host="master"
url="jdbc:mysql://192.168.200.210:3306?useSSL=false"
user="root" password="1234"/>
</dataHost>
</mycat:schema>3.3.2 server.xml（用户权限配置）
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">DB01</property>
</user>
<user name="user">
<property name="password">123456</property>
<property name="schemas">DB01</property>
<property name="readOnly">true</property> <!-- 只读用户 -->
</user>3.3.3 MyCat管理命令
# 启动MyCat
bin/mycat start
# 停止MyCat
bin/mycat stop
# 连接MyCat（端口8066）
mysql -h 192.168.200.210 -P 8066 -uroot -p1234563.4 垂直拆分（分库）
场景：不同业务模块的表拆分到不同数据库
-
商品相关表 → 数据库1
-
订单相关表 → 数据库2
-
用户相关表 → 数据库3
全局表：数据字典表（如省市区表）在所有节点中都存在，解决跨库join问题
<table name="tb_areas_provinces" dataNode="dn1,dn2,dn3"
primaryKey="id" type="global"/>3.5 水平拆分（分表）
场景：单表数据量过大时，按规则拆分到多个表
<table name="tb_log" dataNode="dn4,dn5,dn6"
primaryKey="id" rule="mod-long"/>3.6 分片规则
3.6.1 范围分片
<!-- rule.xml 配置 -->
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>autopartition-long.txt：
# range start-end ,data node index
0-500M=0
500M-1000M=1
1000M-1500M=23.6.2 取模分片
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<property name="count">3</property> <!-- 节点数量 -->
</function>3.6.3 常用分片规则对比
| 规则 | 适用场景 | 特点 |
|---|---|---|
| 范围分片 | 数字类型字段，连续值 | 易于扩展，可能数据分布不均 |
| 取模分片 | 数字类型字段 | 数据分布均匀，扩展困难 |
| 一致性hash | 数字类型字段 | 解决扩展问题，数据迁移少 |
| 枚举分片 | 状态、类型等字段 | 按枚举值分配 |
| 日期分片 | 时间类型字段 | 按时间范围分配 |
3.7 MyCat管理及监控
3.7.1 MyCat管理端口（9066）
# 连接管理端口
mysql -h 192.168.200.210 -P 9066 -uroot -p1234563.7.2 管理命令
-- 查看帮助
SHOW @@HELP;
-- 查看版本
SHOW @@VERSION;
-- 重新加载配置
RELOAD @@CONFIG;
-- 查看数据源
SHOW @@DATASOURCE;
-- 查看分片节点
SHOW @@DATANODE;
-- 查看执行的SQL
SHOW @@SQL;
-- 查看SQL统计
SHOW @@SQL.SUM;3.7.3 MyCat-Web监控
-
端口：8082
-
依赖：Zookeeper
-
功能：监控 MyCat 流量、连接、线程、内存等
4. 读写分离
4.1 读写分离概念
-
主数据库：处理写操作（INSERT、UPDATE、DELETE）
-
从数据库：处理读操作（SELECT）
-
优点：减轻单台数据库压力，提高并发性能
4.2 一主一从配置
4.2.1 schema.xml 配置
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306"
user="root" password="1234">
<readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306"
user="root" password="1234"/>
</writeHost>
</dataHost>4.2.2 balance 参数详解
| 值 | 含义 |
|---|---|
| 0 | 不开启读写分离，所有操作都发送到 writeHost |
| 1 | 所有 readHost 与备用 writeHost 参与读负载均衡 |
| 2 | 所有读写操作随机分发到 writeHost 和 readHost |
| 3 | 所有读操作随机分发到 writeHost 对应的 readHost |
一主一从推荐：balance="1" 或 balance="3"
4.3 双主双从架构
4.3.1 架构优势
-
高可用：一个主库故障，另一个主库接管写操作
-
负载均衡：读操作可以分发到多个从库
-
结构：
M1 ←→ M2（互为主备） ↓ ↓ S1 S2
4.3.2 双主配置
主库相互复制配置：
-- 在 M1 上执行（复制 M2）
CHANGE MASTER TO
MASTER_HOST='192.168.200.213',
MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=663;
-- 在 M2 上执行（复制 M1）
CHANGE MASTER TO
MASTER_HOST='192.168.200.211',
MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=663;4.3.3 MyCat 双主双从配置
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306"
user="root" password="1234">
<readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306"
user="root" password="1234"/>
</writeHost>
<writeHost host="master2" url="jdbc:mysql://192.168.200.213:3306"
user="root" password="1234">
<readHost host="slave2" url="jdbc:mysql://192.168.200.214:3306"
user="root" password="1234"/>
</writeHost>
</dataHost>关键参数：
-
balance="1"：所有 readHost 和备用 writeHost 参与读负载均衡
-
writeType="0"：写操作转发到第一个 writeHost，故障时切换到第二个
-
switchType="1"：自动切换
4.4 读写分离测试
-- 在 MyCat 中执行
-- 写操作测试
INSERT INTO tb_user(id, name) VALUES(1, 'Tom');
-- 读操作测试
SELECT * FROM tb_user;
-- 检查主从数据同步
-- 主库执行写操作，观察从库是否同步
-- 从库执行读操作，验证是否读到最新数据核心知识点总结
1. 日志系统
-
错误日志：故障排查首选
-
二进制日志：数据恢复和主从复制的核心
-
慢查询日志：SQL 优化的重要依据
2. 主从复制
-
原理：基于二进制日志的异步复制
-
作用：数据备份、读写分离、高可用
-
配置要点：server-id 唯一，正确设置日志坐标
3. 分库分表
何时需要分库分表？
-
单表数据超过千万行
-
数据库服务器 IO/CPU 压力大
-
复杂查询响应时间过长
拆分策略选择
-
垂直拆分：不同业务模块的表分开
-
水平拆分：单表数据量过大时拆分
-
全局表：解决跨库 join 问题
MyCat 核心配置
-
schema.xml：逻辑库、表、分片规则
-
server.xml：用户、权限配置
-
rule.xml：分片规则算法
4. 读写分离
配置模式
-
一主一从：简单，成本低
-
双主双从：高可用，推荐生产环境
负载均衡策略
-
balance="1"：推荐用于双主双从
-
balance="3"：推荐用于一主一从
5. 生产环境建议
-
监控：使用 MyCat-Web 或 Prometheus 监控
-
备份：定期备份配置和数据
-
测试：任何配置变更前充分测试
-
文档：详细记录架构和配置信息
北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台
更多推荐
13
0- 0
已为社区贡献1条内容
所有评论(0)