Day61-64 - MySQL 高可用集群
本部分涵盖 MySQL 高可用集群架构、性能优化和 MySQL 8.0 新特性。
1. MySQL 高可用概述
1.1 高可用指标
| 指标 |
计算公式 |
说明 |
| 可用性 |
(1 - 故障时间/总时间) × 100% |
99.9% = 8.76h/年 |
| RTO |
Recovery Time Objective |
恢复时间目标 |
| RPO |
Recovery Point Objective |
恢复点目标 |
1.2 高可用方案对比
| 方案 |
原理 |
适用场景 |
| 主从复制 |
数据异步同步 |
读写分离 |
| MHA |
主从 + 自动切换 |
故障自动转移 |
| MySQL Cluster |
多主复制 |
分布式集群 |
| Galera Cluster |
同步多主复制 |
强一致性场景 |
| MySQL Group Replication |
组复制 |
MySQL 8.0+ |
2. MHA 高可用方案
2.1 MHA 架构
graph TB
subgraph MHA架构
subgraph 主从集群
Master["主库<br/>Master"]
Slave1["从库1<br/>Slave1"]
Slave2["从库2<br/>Slave2"]
end
MHAnode["MHA Node<br/>监控脚本"]
MHAmanager["MHA Manager<br/>故障转移"]
end
MHAnode --> Master
MHAnode --> Slave1
MHAnode --> Slave2
MHAmanager --> MHAnode
Master --> Slave1
Master --> Slave2
2.2 MHA 工作原理
- 监控：MHA Node 每秒 Ping 主库
- 检测：主库不可达时触发故障转移
- 选主：从多个从库中选择数据最新的作为新主库
- 切换：原主库无法恢复时，进行在线切换
2.3 MHA 安装配置
# 安装 MHA Node
yum install -y mha4mysql-node
# 安装 MHA Manager
yum install -y mha4mysql-manager
# 配置 SSH 免密登录
ssh-keygen -t rsa
ssh-copy-id root@slave1
2.4 MHA 配置文件
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
remote_workdir=/var/log/mha/app1
ssh_user=root
ssh_port=22
repl_user=repl
repl_password=repl_pass
master_binlog_dir=/var/lib/mysql
candidate_master=1
3. MySQL 性能优化
3.1 性能优化维度
graph TB
subgraph 优化层级
O["MySQL 性能优化"]
O --> S["SQL优化"]
O --> S1["索引优化"]
O --> S2["架构优化"]
O --> S3["配置优化"]
O --> S4["硬件优化"]
end
3.2 SQL 优化 Checklist
| 检查点 |
说明 |
| EXPLAIN 分析 |
确保使用了索引 |
| 避免 SELECT * |
只查询需要的字段 |
| 避免深度分页 |
使用延迟关联 |
| 批量操作 |
减少网络开销 |
| 避免大事务 |
分解为小事务 |
3.3 索引优化 Checklist
| 检查点 |
说明 |
| 覆盖索引 |
减少回表 |
| 最左前缀 |
遵循索引顺序 |
| 前缀索引 |
减少存储空间 |
| 联合索引 |
减少索引数量 |
3.4 配置参数优化
[mysqld]
# 连接优化
max_connections = 2000
wait_timeout = 600
interactive_timeout = 600
# 缓存优化
innodb_buffer_pool_size = 12G # 设置为可用内存的 60-80%
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M
# IO 优化
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
# 线程优化
innodb_read_io_threads = 16
innodb_write_io_threads = 16
3.5 慢查询优化实战
-- 识别慢查询
SELECT
digest_text,
count_star,
avg_timer_wait / 1000000000000 as avg_seconds,
sum_timer_wait / 1000000000000 as total_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;
-- 分析索引使用
SELECT * FROM mysql.innodb_index_stats
WHERE database_name = 'app_db';
4. MySQL 分库分表
4.1 分库分表策略
| 策略 |
说明 |
适用场景 |
| 垂直分库 |
按业务拆分不同库 |
微服务架构 |
| 垂直分表 |
按字段冷热拆分 |
大宽表 |
| 水平分库 |
数据分到不同库 |
数据量大 |
| 水平分表 |
数据分到不同表 |
单表数据多 |
4.2 分片键选择
-- 按用户ID分片
SELECT * FROM orders WHERE user_id = 1;
-- 按时间分片
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';
4.3 分片中间件
| 中间件 |
特点 |
| ShardingSphere-JDBC |
Java SDK，侵入性强 |
| ShardingSphere-Proxy |
代理模式，侵入性低 |
| MyCat |
老牌分库分表中间件 |
| Vitess |
YouTube 开源方案 |
5. MySQL 备份与恢复
5.1 物理备份
# 使用 xtrabackup 全量备份
xtrabackup --backup \
--target-dir=/backup/full \
--user=root \
--password=123456
# 增量备份
xtrabackup --backup \
--target-dir=/backup/incr1 \
--incremental-basedir=/backup/full \
--user=root \
--password=123456
# 恢复
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full
5.2 逻辑备份
# mysqldump 全量备份
mysqldump -u root -p \
--single-transaction \
--routines \
--triggers \
--events \
--all-databases > /backup/full.sql
# 备份指定库
mysqldump -u root -p \
--databases app_db > /backup/app_db.sql
# 备份单表
mysqldump -u root -p app_db users > /backup/users.sql
5.3 数据恢复
# 恢复全量备份
mysql -u root -p < /backup/full.sql
# 恢复指定库
mysql -u root -p app_db < /backup/app_db.sql
6. MySQL 监控
6.1 关键监控指标
| 指标 |
说明 |
阈值建议 |
| QPS |
每秒查询数 |
关注趋势 |
| TPS |
每秒事务数 |
关注趋势 |
| 连接数 |
当前连接/最大连接 |
> 80% 报警 |
| 缓冲池命中率 |
InnoDB 缓冲池命中率 |
> 95% |
| 锁等待 |
当前锁等待数量 |
> 100 报警 |
| 慢查询 |
慢查询数量 |
关注趋势 |
6.2 监控工具
| 工具 |
说明 |
| Prometheus + Grafana |
监控系统 |
| PMM (Percona Monitoring) |
Percona 监控平台 |
| mysqld_exporter |
MySQL 监控导出器 |
| show processlist |
查看当前连接 |
-- 查看当前连接
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
-- 查看状态
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections%';
-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;