6.1 MySQL复制基础
6.1.1 复制原理与架构
class MySQLReplication:
def __init__(self):
self.replication_types = {
"主从复制": {
"描述": "一个主服务器，一个或多个从服务器",
"特点": "读写分离，主服务器处理写操作，从服务器处理读操作",
"优势": "简单易配置，适合读多写少的场景",
"劣势": "主服务器单点故障，写操作无法扩展"
},
"主主复制": {
"描述": "两个服务器互为主从",
"特点": "双向复制，两个服务器都可以处理写操作",
"优势": "消除单点故障，提高写操作可用性",
"劣势": "配置复杂，容易产生数据冲突"
},
"级联复制": {
"描述": "从服务器作为其他从服务器的主服务器",
"特点": "多级复制结构，减轻主服务器压力",
"优势": "减少主服务器的复制负载",
"劣势": "复制延迟增加，故障影响范围大"
},
"多源复制": {
"描述": "一个从服务器从多个主服务器复制数据",
"特点": "MySQL 5.7+支持，用于数据汇总",
"优势": "数据集中，便于分析和备份",
"劣势": "配置复杂，冲突处理困难"
}
}
self.replication_formats = {
"基于语句的复制（SBR）": {
"原理": "复制SQL语句到从服务器执行",
"优点": "日志文件小，网络传输量少",
"缺点": "某些函数可能导致数据不一致",
"适用场景": "简单的DML操作"
},
"基于行的复制（RBR）": {
"原理": "复制数据行的变化到从服务器",
"优点": "数据一致性好，支持所有SQL语句",
"缺点": "日志文件大，网络传输量大",
"适用场景": "复杂的SQL操作，要求数据一致性"
},
"混合模式复制（MBR）": {
"原理": "根据SQL语句类型自动选择复制格式",
"优点": "兼顾性能和一致性",
"缺点": "复杂度较高",
"适用场景": "大多数生产环境的默认选择"
}
}
def explain_replication_types(self):
"""解释复制类型"""
print("MySQL复制类型:")
print("=" * 50)
for repl_type, details in self.replication_types.items():
print(f"\n{repl_type}:")
for key, value in details.items():
print(f" {key}: {value}")
def explain_replication_formats(self):
"""解释复制格式"""
print("\n复制格式对比:")
print("=" * 40)
for format_name, details in self.replication_formats.items():
print(f"\n{format_name}:")
for key, value in details.items():
print(f" {key}: {value}")
def replication_workflow(self):
"""复制工作流程"""
workflow = """
MySQL复制工作流程：
1. 主服务器操作：
- 执行SQL语句修改数据
- 将变更记录到二进制日志（binlog）
- 二进制日志包含所有数据变更事件
2. 从服务器请求：
- I/O线程连接到主服务器
- 请求主服务器的二进制日志
- 主服务器的dump线程发送日志事件
3. 从服务器接收：
- I/O线程接收二进制日志事件
- 将事件写入中继日志（relay log）
- 中继日志存储在从服务器本地
4. 从服务器应用：
- SQL线程读取中继日志
- 解析并执行日志中的事件
- 更新从服务器的数据
5. 位置跟踪：
- 从服务器记录复制位置
- 包括主服务器的日志文件名和位置
- 用于断点续传和故障恢复
复制线程说明：
- 主服务器dump线程：负责发送二进制日志
- 从服务器I/O线程：负责接收和写入中继日志
- 从服务器SQL线程：负责执行中继日志中的事件
复制延迟因素：
- 网络延迟：主从服务器之间的网络状况
- 磁盘I/O：日志写入和读取的速度
- SQL执行：从服务器执行SQL语句的速度
- 负载差异：主从服务器的硬件配置差异
"""
print("\n复制工作流程:")
print("=" * 40)
print(workflow)
def replication_architecture_examples(self):
"""复制架构示例"""
examples = """
复制架构设计示例：
1. 简单主从架构：
[主服务器] → [从服务器1]
→ [从服务器2]
→ [从服务器3]
特点：
- 主服务器处理所有写操作
- 从服务器处理读操作
- 适合读多写少的应用
2. 主主架构：
[主服务器A] ←→ [主服务器B]
特点：
- 双向复制
- 两个服务器都可以写入
- 需要避免自增ID冲突
3. 级联复制架构：
[主服务器] → [中间从服务器] → [叶子从服务器1]
→ [叶子从服务器2]
特点：
- 减轻主服务器复制压力
- 适合大量从服务器的场景
- 复制延迟会累积
4. 多源复制架构：
[主服务器A] ↘
[汇总从服务器]
[主服务器B] ↗
特点：
- 数据汇总和分析
- MySQL 5.7+支持
- 适合数据仓库场景
5. 读写分离架构：
[应用程序] → [读写分离中间件] → [主服务器]（写）
→ [从服务器]（读）
特点：
- 应用透明的读写分离
- 提高系统整体性能
- 需要处理复制延迟
"""
print("\n复制架构示例:")
print("=" * 40)
print(examples)
# 复制基础演示
replication_demo = MySQLReplication()
replication_demo.explain_replication_types()
replication_demo.explain_replication_formats()
replication_demo.replication_workflow()
replication_demo.replication_architecture_examples()
6.1.2 主从复制配置
class MasterSlaveSetup:
def __init__(self):
self.setup_steps = {
"主服务器配置": [
"启用二进制日志",
"设置服务器ID",
"创建复制用户",
"配置复制参数",
"重启MySQL服务"
],
"从服务器配置": [
"设置服务器ID",
"配置中继日志",
"设置主服务器信息",
"启动复制进程",
"验证复制状态"
]
}
self.configuration_parameters = {
"主服务器参数": {
"log-bin": "启用二进制日志",
"server-id": "唯一的服务器标识",
"binlog-format": "二进制日志格式",
"binlog-do-db": "指定复制的数据库",
"binlog-ignore-db": "忽略复制的数据库",
"expire_logs_days": "二进制日志保留天数",
"max_binlog_size": "单个二进制日志文件最大大小"
},
"从服务器参数": {
"server-id": "唯一的服务器标识（与主服务器不同）",
"relay-log": "中继日志文件名",
"relay-log-index": "中继日志索引文件",
"replicate-do-db": "指定复制的数据库",
"replicate-ignore-db": "忽略复制的数据库",
"replicate-do-table": "指定复制的表",
"replicate-ignore-table": "忽略复制的表",
"slave-skip-errors": "跳过指定的复制错误"
}
}
def show_setup_steps(self):
"""显示配置步骤"""
print("主从复制配置步骤:")
print("=" * 50)
for category, steps in self.setup_steps.items():
print(f"\n{category}:")
for i, step in enumerate(steps, 1):
print(f" {i}. {step}")
def show_configuration_parameters(self):
"""显示配置参数"""
print("\n配置参数说明:")
print("=" * 40)
for category, params in self.configuration_parameters.items():
print(f"\n{category}:")
for param, description in params.items():
print(f" {param}: {description}")
def master_configuration_example(self):
"""主服务器配置示例"""
config_example = """
# 主服务器配置示例
## 1. 修改主服务器配置文件 /etc/mysql/my.cnf
[mysqld]
# 启用二进制日志
log-bin = mysql-bin
# 设置服务器ID（必须唯一）
server-id = 1
# 设置二进制日志格式
binlog-format = ROW
# 指定要复制的数据库（可选）
# binlog-do-db = myapp
# binlog-do-db = myapp2
# 忽略复制的数据库（可选）
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
# 二进制日志保留时间（天）
expire_logs_days = 7
# 单个二进制日志文件最大大小
max_binlog_size = 100M
# 同步二进制日志到磁盘的频率
sync_binlog = 1
# InnoDB相关设置
innodb_flush_log_at_trx_commit = 1
## 2. 重启MySQL服务
sudo systemctl restart mysql
## 3. 创建复制用户
mysql -u root -p
-- 创建复制用户
CREATE USER 'replication'@'%' IDENTIFIED BY 'replication_password';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看主服务器状态
SHOW MASTER STATUS;
-- 记录File和Position值，从服务器配置时需要用到
## 4. 锁定表并备份数据（如果需要）
-- 锁定所有表（阻止写入）
FLUSH TABLES WITH READ LOCK;
-- 查看主服务器状态（记录位置）
SHOW MASTER STATUS;
-- 在另一个终端备份数据
mysqldump -u root -p --all-databases --master-data=2 > master_backup.sql
-- 解锁表
UNLOCK TABLES;
## 5. 验证配置
-- 查看二进制日志
SHOW BINARY LOGS;
-- 查看二进制日志事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
-- 查看复制用户
SELECT User, Host FROM mysql.user WHERE User = 'replication';
"""
print("\n主服务器配置示例:")
print("=" * 40)
print(config_example)
def slave_configuration_example(self):
"""从服务器配置示例"""
config_example = """
# 从服务器配置示例
## 1. 修改从服务器配置文件 /etc/mysql/my.cnf
[mysqld]
# 设置服务器ID（必须与主服务器不同）
server-id = 2
# 启用中继日志
relay-log = mysql-relay-bin
# 中继日志索引文件
relay-log-index = mysql-relay-bin.index
# 指定要复制的数据库（可选）
# replicate-do-db = myapp
# replicate-do-db = myapp2
# 忽略复制的数据库（可选）
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys
# 跳过特定错误（谨慎使用）
# slave-skip-errors = 1062,1053
# 从服务器只读（推荐）
read-only = 1
# 超级用户也只读（MySQL 5.6+）
super-read-only = 1
# 中继日志自动清理
relay_log_purge = 1
# 从服务器更新日志
log-slave-updates = 1
## 2. 重启MySQL服务
sudo systemctl restart mysql
## 3. 恢复主服务器备份（如果有）
mysql -u root -p < master_backup.sql
## 4. 配置主服务器信息
mysql -u root -p
-- 配置主服务器连接信息
CHANGE MASTER TO
MASTER_HOST='192.168.1.100', -- 主服务器IP
MASTER_USER='replication', -- 复制用户名
MASTER_PASSWORD='replication_password', -- 复制用户密码
MASTER_LOG_FILE='mysql-bin.000001', -- 主服务器日志文件
MASTER_LOG_POS=154; -- 主服务器日志位置
-- 启动从服务器复制
START SLAVE;
-- 查看从服务器状态
SHOW SLAVE STATUS\G
## 5. 验证复制状态
-- 检查关键状态信息
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0（或较小值）
-- Last_IO_Error: （应该为空）
-- Last_SQL_Error: （应该为空）
## 6. 测试复制
-- 在主服务器上创建测试数据
-- 主服务器
CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO test_table VALUES (1, 'test data');
-- 从服务器验证
USE test_replication;
SELECT * FROM test_table;
-- 应该能看到刚插入的数据
## 7. 常用管理命令
-- 停止复制
STOP SLAVE;
-- 启动复制
START SLAVE;
-- 重置从服务器
RESET SLAVE;
-- 跳过一个错误
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
-- 查看复制位置
SHOW SLAVE STATUS\G
"""
print("\n从服务器配置示例:")
print("=" * 40)
print(config_example)
def troubleshooting_guide(self):
"""故障排除指南"""
guide = """
复制故障排除指南：
1. 常见问题及解决方案：
a) Slave_IO_Running: No
- 检查网络连接
- 验证复制用户权限
- 检查主服务器防火墙设置
- 验证主服务器地址和端口
b) Slave_SQL_Running: No
- 查看Last_SQL_Error信息
- 检查数据冲突（如主键重复）
- 验证从服务器权限
- 检查磁盘空间
c) 复制延迟过大
- 检查网络带宽
- 优化从服务器硬件
- 调整复制参数
- 考虑并行复制
d) 数据不一致
- 检查复制过滤设置
- 验证字符集设置
- 检查时区配置
- 使用pt-table-checksum工具
2. 诊断命令：
-- 查看主服务器状态
SHOW MASTER STATUS;
-- 查看从服务器状态
SHOW SLAVE STATUS\G
-- 查看复制错误日志
SHOW SLAVE STATUS\G
-- 关注Last_IO_Error和Last_SQL_Error
-- 查看进程列表
SHOW PROCESSLIST;
-- 查看二进制日志
SHOW BINARY LOGS;
-- 查看二进制日志事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
3. 修复步骤：
a) 重新同步从服务器：
-- 停止从服务器
STOP SLAVE;
-- 重置从服务器
RESET SLAVE;
-- 重新配置主服务器信息
CHANGE MASTER TO ...
-- 启动从服务器
START SLAVE;
b) 跳过错误：
-- 跳过一个错误
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
c) 重建从服务器：
-- 从主服务器重新备份
-- 恢复到从服务器
-- 重新配置复制
4. 监控脚本示例：
#!/bin/bash
# 复制状态监控脚本
MYSQL_USER="root"
MYSQL_PASS="password"
# 检查复制状态
IO_RUNNING=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running:" | awk '{print $2}')
DELAY=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master:" | awk '{print $2}')
if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
echo "复制异常！IO: $IO_RUNNING, SQL: $SQL_RUNNING"
# 发送告警
elif [ "$DELAY" -gt 60 ]; then
echo "复制延迟过大：$DELAY 秒"
# 发送告警
else
echo "复制正常，延迟：$DELAY 秒"
fi
"""
print("\n故障排除指南:")
print("=" * 40)
print(guide)
# 主从配置演示
setup_demo = MasterSlaveSetup()
setup_demo.show_setup_steps()
setup_demo.show_configuration_parameters()
setup_demo.master_configuration_example()
setup_demo.slave_configuration_example()
setup_demo.troubleshooting_guide()
6.2 高可用架构设计
6.2.1 MySQL高可用方案
class MySQLHighAvailability:
def __init__(self):
self.ha_solutions = {
"MySQL Replication + VIP": {
"描述": "基于主从复制的简单高可用方案",
"组件": ["主从复制", "虚拟IP", "故障检测脚本"],
"优点": ["简单易实现", "成本低", "适合小型应用"],
"缺点": ["手动切换", "数据可能丢失", "切换时间长"],
"适用场景": "对可用性要求不高的应用"
},
"MHA (Master High Availability)": {
"描述": "专门为MySQL设计的高可用管理工具",
"组件": ["MHA Manager", "MHA Node", "主从复制"],
"优点": ["自动故障切换", "数据一致性好", "切换时间短"],
"缺点": ["配置复杂", "需要额外组件", "社区版功能有限"],
"适用场景": "对数据一致性要求高的应用"
},
"MySQL Group Replication": {
"描述": "MySQL官方的组复制解决方案",
"组件": ["Group Replication插件", "多个MySQL实例"],
"优点": ["官方支持", "强一致性", "自动故障检测"],
"缺点": ["性能开销大", "网络要求高", "配置复杂"],
"适用场景": "MySQL 5.7+，对一致性要求极高的应用"
},
"Galera Cluster": {
"描述": "基于同步复制的MySQL集群解决方案",
"组件": ["Galera库", "多个MySQL实例", "负载均衡器"],
"优点": ["多主架构", "强一致性", "自动故障恢复"],
"缺点": ["性能开销大", "网络敏感", "脑裂风险"],
"适用场景": "需要多主写入的高可用应用"
},
"MySQL InnoDB Cluster": {
"描述": "MySQL官方的完整高可用解决方案",
"组件": ["Group Replication", "MySQL Router", "MySQL Shell"],
"优点": ["官方支持", "完整解决方案", "管理简单"],
"缺点": ["版本要求高", "资源消耗大", "相对较新"],
"适用场景": "MySQL 8.0+，企业级应用"
}
}
self.ha_metrics = {
"RTO (Recovery Time Objective)": {
"定义": "系统从故障到恢复服务的最大可接受时间",
"影响因素": ["故障检测时间", "切换执行时间", "应用重连时间"],
"优化方法": ["快速故障检测", "自动化切换", "连接池配置"]
},
"RPO (Recovery Point Objective)": {
"定义": "系统能够容忍的最大数据丢失量",
"影响因素": ["复制延迟", "同步方式", "网络状况"],
"优化方法": ["同步复制", "半同步复制", "网络优化"]
},
"可用性": {
"定义": "系统正常运行时间的百分比",
"计算公式": "(总时间 - 故障时间) / 总时间 * 100%",
"等级标准": ["99.9% (8.76小时/年)", "99.99% (52.56分钟/年)", "99.999% (5.26分钟/年)"]
}
}
def explain_ha_solutions(self):
"""解释高可用方案"""
print("MySQL高可用方案对比:")
print("=" * 60)
for solution, details in self.ha_solutions.items():
print(f"\n{solution}:")
print(f" 描述: {details['描述']}")
print(f" 组件: {', '.join(details['组件'])}")
print(f" 优点: {', '.join(details['优点'])}")
print(f" 缺点: {', '.join(details['缺点'])}")
print(f" 适用场景: {details['适用场景']}")
def explain_ha_metrics(self):
"""解释高可用指标"""
print("\n高可用关键指标:")
print("=" * 40)
for metric, details in self.ha_metrics.items():
print(f"\n{metric}:")
for key, value in details.items():
if isinstance(value, list):
print(f" {key}: {', '.join(value)}")
else:
print(f" {key}: {value}")
def ha_architecture_design(self):
"""高可用架构设计"""
design_principles = """
高可用架构设计原则：
1. 消除单点故障：
- 数据库服务器冗余
- 网络链路冗余
- 存储系统冗余
- 应用服务器冗余
2. 故障快速检测：
- 心跳检测机制
- 健康检查脚本
- 监控告警系统
- 自动化故障发现
3. 自动故障切换：
- 自动化切换脚本
- VIP漂移机制
- DNS切换策略
- 应用层重连机制
4. 数据一致性保证：
- 同步复制机制
- 数据校验工具
- 一致性检查
- 冲突解决策略
5. 容量规划：
- 性能基准测试
- 容量增长预测
- 资源监控告警
- 弹性扩展能力
典型高可用架构：
1. 双机热备架构：
[应用] → [负载均衡] → [主数据库]
[从数据库]（热备）
特点：
- 主从复制
- VIP切换
- 手动或自动故障切换
2. 多主集群架构：
[应用] → [负载均衡] → [数据库节点1]
[数据库节点2]
[数据库节点3]
特点：
- 多主写入
- 自动故障检测
- 数据强一致性
3. 读写分离架构：
[应用] → [读写分离中间件] → [主数据库]（写）
[从数据库1]（读）
[从数据库2]（读）
特点：
- 读写分离
- 读负载均衡
- 主库故障切换
4. 异地多活架构：
[机房A] → [主数据库A] ←→ [主数据库B] ← [机房B]
特点：
- 异地容灾
- 双向复制
- 就近访问
"""
print("\n高可用架构设计:")
print("=" * 40)
print(design_principles)
def ha_implementation_guide(self):
"""高可用实施指南"""
guide = """
高可用实施指南：
1. 需求分析：
- 确定RTO和RPO要求
- 评估业务影响
- 分析故障场景
- 制定可用性目标
2. 方案选择：
- 根据业务需求选择合适的高可用方案
- 考虑技术复杂度和维护成本
- 评估性能影响
- 制定实施计划
3. 环境准备：
- 硬件资源规划
- 网络环境配置
- 操作系统优化
- MySQL安装配置
4. 高可用配置：
- 复制环境搭建
- 故障检测配置
- 切换脚本开发
- 监控系统部署
5. 测试验证：
- 功能测试
- 性能测试
- 故障切换测试
- 数据一致性测试
6. 上线部署：
- 数据迁移
- 应用配置修改
- 灰度发布
- 全量切换
7. 运维管理：
- 监控告警
- 定期巡检
- 故障演练
- 持续优化
关键成功因素：
- 充分的测试验证
- 完善的监控体系
- 自动化运维工具
- 团队技能培训
- 应急响应流程
"""
print("\n高可用实施指南:")
print("=" * 40)
print(guide)
# 高可用架构演示
ha_demo = MySQLHighAvailability()
ha_demo.explain_ha_solutions()
ha_demo.explain_ha_metrics()
ha_demo.ha_architecture_design()
ha_demo.ha_implementation_guide()
6.2.2 MHA配置与管理
class MHAConfiguration:
def __init__(self):
self.mha_components = {
"MHA Manager": {
"功能": "监控主服务器，执行故障切换",
"部署": "独立服务器，不与MySQL服务器共用",
"配置文件": "/etc/mha/app1.cnf",
"关键进程": "masterha_manager"
},
"MHA Node": {
"功能": "解析和应用差异中继日志",
"部署": "每个MySQL服务器上都需要安装",
"关键工具": ["save_binary_logs", "apply_diff_relay_logs", "purge_relay_logs"],
"SSH免密": "所有节点之间需要配置SSH免密登录"
}
}
self.mha_workflow = {
"正常监控": [
"MHA Manager定期检查主服务器状态",
"监控复制延迟和从服务器状态",
"记录主服务器的binlog位置"
],
"故障检测": [
"检测到主服务器不可访问",
"尝试多种方式确认故障",
"检查从服务器状态和数据一致性"
],
"故障切换": [
"选择最合适的从服务器作为新主服务器",
"应用差异日志保证数据一致性",
"重新配置其他从服务器指向新主服务器",
"更新应用程序连接配置"
]
}
def explain_mha_components(self):
"""解释MHA组件"""
print("MHA组件说明:")
print("=" * 50)
for component, details in self.mha_components.items():
print(f"\n{component}:")
for key, value in details.items():
if isinstance(value, list):
print(f" {key}: {', '.join(value)}")
else:
print(f" {key}: {value}")
def explain_mha_workflow(self):
"""解释MHA工作流程"""
print("\nMHA工作流程:")
print("=" * 40)
for phase, steps in self.mha_workflow.items():
print(f"\n{phase}:")
for i, step in enumerate(steps, 1):
print(f" {i}. {step}")
def mha_installation_guide(self):
"""MHA安装指南"""
installation = """
MHA安装配置指南：
## 1. 环境准备
### 服务器规划：
- 主服务器：192.168.1.100 (master)
- 从服务器1：192.168.1.101 (slave1)
- 从服务器2：192.168.1.102 (slave2)
- MHA管理服务器：192.168.1.103 (mha-manager)
### 系统要求：
- CentOS 7+ 或 Ubuntu 16.04+
- Perl 5.8+
- SSH免密登录
- MySQL 5.5+
## 2. SSH免密配置
# 在所有服务器上创建mha用户
sudo useradd mha
sudo passwd mha
# 配置sudo权限
echo "mha ALL=(ALL) NOPASSWD: ALL" | sudo tee /etc/sudoers.d/mha
# 在MHA管理服务器上生成密钥
su - mha
ssh-keygen -t rsa -b 2048 -f ~/.ssh/id_rsa -N ""
# 将公钥复制到所有MySQL服务器
ssh-copy-id mha@192.168.1.100
ssh-copy-id mha@192.168.1.101
ssh-copy-id mha@192.168.1.102
# 测试SSH免密登录
ssh mha@192.168.1.100 'hostname'
ssh mha@192.168.1.101 'hostname'
ssh mha@192.168.1.102 'hostname'
## 3. 安装MHA Node（在所有MySQL服务器上）
# 安装依赖
sudo yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
# 下载并安装MHA Node
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz
tar -zxf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL
make
sudo make install
## 4. 安装MHA Manager（在管理服务器上）
# 安装依赖
sudo yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
# 下载并安装MHA Manager
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz
tar -zxf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL
make
sudo make install
## 5. 创建MHA配置目录
sudo mkdir -p /etc/mha
sudo mkdir -p /var/log/mha
sudo chown -R mha:mha /etc/mha /var/log/mha
"""
print("\nMHA安装指南:")
print("=" * 40)
print(installation)
def mha_configuration_example(self):
"""MHA配置示例"""
config_example = """
MHA配置文件示例：
## 1. MHA Manager配置文件 (/etc/mha/app1.cnf)
[server default]
# MHA Manager工作目录
manager_workdir=/var/log/mha/app1
# MHA Manager日志文件
manager_log=/var/log/mha/app1/manager.log
# 远程工作目录
remote_workdir=/tmp
# SSH用户
ssh_user=mha
# 复制用户
repl_user=replication
repl_password=replication_password
# 检查复制延迟的时间间隔（秒）
ping_interval=3
# 关闭时间（秒）
shutdown_script=""
# 故障切换脚本
master_ip_failover_script=/etc/mha/scripts/master_ip_failover
# 发送报告脚本
report_script=/etc/mha/scripts/send_report
# 在线切换脚本
master_ip_online_change_script=/etc/mha/scripts/master_ip_online_change
# 二次检查脚本
secondary_check_script=/etc/mha/scripts/masterha_secondary_check -s 192.168.1.101 -s 192.168.1.102
[server1]
hostname=192.168.1.100
port=3306
candidate_master=1
check_repl_delay=0
[server2]
hostname=192.168.1.101
port=3306
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.1.102
port=3306
no_master=1
## 2. VIP切换脚本 (/etc/mha/scripts/master_ip_failover)
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.1.200/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host " $ssh_start_vip "`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host " $ssh_stop_vip "`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
## 3. 设置脚本权限
chmod +x /etc/mha/scripts/master_ip_failover
## 4. 检查MHA配置
# 检查SSH连接
masterha_check_ssh --conf=/etc/mha/app1.cnf
# 检查复制状态
masterha_check_repl --conf=/etc/mha/app1.cnf
# 检查状态（应该显示所有检查都通过）
"""
print("\nMHA配置示例:")
print("=" * 40)
print(config_example)
def mha_management_commands(self):
"""MHA管理命令"""
commands = """
MHA管理命令：
## 1. 启动MHA监控
# 启动MHA Manager
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
# 检查MHA状态
masterha_check_status --conf=/etc/mha/app1.cnf
## 2. 停止MHA监控
masterha_stop --conf=/etc/mha/app1.cnf
## 3. 手动故障切换
# 停止MHA监控
masterha_stop --conf=/etc/mha/app1.cnf
# 执行手动切换
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=dead --dead_master_host=192.168.1.100 --new_master_host=192.168.1.101 --ignore_last_failover
## 4. 在线主服务器切换
# 在线切换（主服务器正常运行时）
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.1.101 --orig_master_is_new_slave
## 5. 检查命令
# 检查SSH连接
masterha_check_ssh --conf=/etc/mha/app1.cnf
# 检查复制状态
masterha_check_repl --conf=/etc/mha/app1.cnf
# 检查MHA状态
masterha_check_status --conf=/etc/mha/app1.cnf
## 6. 日志查看
# 查看MHA Manager日志
tail -f /var/log/mha/app1/manager.log
# 查看故障切换日志
ls -la /var/log/mha/app1/
## 7. 故障恢复后重新加入
# 修复原主服务器后，将其作为从服务器加入
# 1. 在原主服务器上配置复制
CHANGE MASTER TO
MASTER_HOST='192.168.1.101',
MASTER_USER='replication',
MASTER_PASSWORD='replication_password',
MASTER_AUTO_POSITION=1;
START SLAVE;
# 2. 修改MHA配置文件，重新添加服务器配置
# 3. 重启MHA监控
## 8. 监控脚本示例
#!/bin/bash
# MHA状态监控脚本
MHA_CONF="/etc/mha/app1.cnf"
LOG_FILE="/var/log/mha/mha_monitor.log"
# 检查MHA状态
STATUS=$(masterha_check_status --conf=$MHA_CONF 2>/dev/null)
if echo "$STATUS" | grep -q "is running"; then
echo "$(date): MHA is running normally" >> $LOG_FILE
else
echo "$(date): MHA is not running, attempting to restart" >> $LOG_FILE
# 检查配置
if masterha_check_ssh --conf=$MHA_CONF >/dev/null 2>&1 &&
masterha_check_repl --conf=$MHA_CONF >/dev/null 2>&1; then
# 启动MHA
nohup masterha_manager --conf=$MHA_CONF --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
echo "$(date): MHA restarted successfully" >> $LOG_FILE
else
echo "$(date): MHA configuration check failed" >> $LOG_FILE
# 发送告警
fi
fi
# 添加到crontab
# */5 * * * * /path/to/mha_monitor.sh
"""
print("\nMHA管理命令:")
print("=" * 40)
print(commands)
# MHA配置演示
mha_demo = MHAConfiguration()
mha_demo.explain_mha_components()
mha_demo.explain_mha_workflow()
mha_demo.mha_installation_guide()
mha_demo.mha_configuration_example()
mha_demo.mha_management_commands()
6.3 读写分离与负载均衡
6.3.1 读写分离实现
class ReadWriteSplitting:
def __init__(self):
self.splitting_methods = {
"应用层分离": {
"实现方式": "在应用程序中实现读写分离逻辑",
"优点": ["实现简单", "性能开销小", "控制精确"],
"缺点": ["代码侵入性强", "维护复杂", "不够透明"],
"适用场景": "小型应用，开发资源充足"
},
"中间件分离": {
"实现方式": "使用专门的中间件进行读写分离",
"优点": ["对应用透明", "功能丰富", "易于管理"],
"缺点": ["增加复杂度", "性能开销", "单点故障"],
"适用场景": "中大型应用，需要透明分离"
},
"代理层分离": {
"实现方式": "通过数据库代理实现读写分离",
"优点": ["完全透明", "功能强大", "易于扩展"],
"缺点": ["配置复杂", "性能影响", "故障点增加"],
"适用场景": "大型应用，复杂的分离需求"
}
}
self.popular_tools = {
"MySQL Router": {
"类型": "官方中间件",
"特点": ["官方支持", "与InnoDB Cluster集成", "自动故障切换"],
"适用版本": "MySQL 5.7+"
},
"ProxySQL": {
"类型": "第三方代理",
"特点": ["功能强大", "查询缓存", "连接池", "查询重写"],
"适用版本": "MySQL 5.1+"
},
"MaxScale": {
"类型": "MariaDB代理",
"特点": ["功能丰富", "插件架构", "监控完善"],
"适用版本": "MySQL/MariaDB"
},
"Atlas": {
"类型": "360开源代理",
"特点": ["轻量级", "简单易用", "中文文档"],
"适用版本": "MySQL 5.0+"
},
"Amoeba": {
"类型": "Java中间件",
"特点": ["Java开发", "配置灵活", "社区活跃度低"],
"适用版本": "MySQL 5.0+"
}
}
def explain_splitting_methods(self):
"""解释读写分离方法"""
print("读写分离实现方法:")
print("=" * 60)
for method, details in self.splitting_methods.items():
print(f"\n{method}:")
print(f" 实现方式: {details['实现方式']}")
print(f" 优点: {', '.join(details['优点'])}")
print(f" 缺点: {', '.join(details['缺点'])}")
print(f" 适用场景: {details['适用场景']}")
def explain_popular_tools(self):
"""解释常用工具"""
print("\n常用读写分离工具:")
print("=" * 40)
for tool, details in self.popular_tools.items():
print(f"\n{tool}:")
print(f" 类型: {details['类型']}")
print(f" 特点: {', '.join(details['特点'])}")
print(f" 适用版本: {details['适用版本']}")
def application_level_example(self):
"""应用层读写分离示例"""
example = """
应用层读写分离实现示例：
## 1. Python实现示例
import pymysql
import random
from contextlib import contextmanager
class DatabaseManager:
def __init__(self):
# 主数据库配置（写操作）
self.master_config = {
'host': '192.168.1.100',
'port': 3306,
'user': 'app_user',
'password': 'app_password',
'database': 'myapp',
'charset': 'utf8mb4'
}
# 从数据库配置（读操作）
self.slave_configs = [
{
'host': '192.168.1.101',
'port': 3306,
'user': 'app_user',
'password': 'app_password',
'database': 'myapp',
'charset': 'utf8mb4'
},
{
'host': '192.168.1.102',
'port': 3306,
'user': 'app_user',
'password': 'app_password',
'database': 'myapp',
'charset': 'utf8mb4'
}
]
self.master_pool = None
self.slave_pools = []
self.init_connections()
def init_connections(self):
"""初始化连接池"""
# 创建主数据库连接池
self.master_pool = pymysql.connect(**self.master_config)
# 创建从数据库连接池
for config in self.slave_configs:
try:
conn = pymysql.connect(**config)
self.slave_pools.append(conn)
except Exception as e:
print(f"从数据库连接失败: {config['host']} - {e}")
@contextmanager
def get_read_connection(self):
"""获取读连接（从数据库）"""
if not self.slave_pools:
# 如果没有可用的从数据库，使用主数据库
yield self.master_pool
else:
# 随机选择一个从数据库
slave_conn = random.choice(self.slave_pools)
yield slave_conn
@contextmanager
def get_write_connection(self):
"""获取写连接（主数据库）"""
yield self.master_pool
def execute_read_query(self, sql, params=None):
"""执行读查询"""
with self.get_read_connection() as conn:
with conn.cursor() as cursor:
cursor.execute(sql, params)
return cursor.fetchall()
def execute_write_query(self, sql, params=None):
"""执行写查询"""
with self.get_write_connection() as conn:
with conn.cursor() as cursor:
cursor.execute(sql, params)
conn.commit()
return cursor.rowcount
def close_connections(self):
"""关闭所有连接"""
if self.master_pool:
self.master_pool.close()
for slave_conn in self.slave_pools:
slave_conn.close()
# 使用示例
class UserService:
def __init__(self):
self.db = DatabaseManager()
def get_user_by_id(self, user_id):
"""读操作：获取用户信息"""
sql = "SELECT * FROM users WHERE id = %s"
result = self.db.execute_read_query(sql, (user_id,))
return result[0] if result else None
def get_users_list(self, page=1, size=10):
"""读操作：获取用户列表"""
offset = (page - 1) * size
sql = "SELECT * FROM users LIMIT %s OFFSET %s"
return self.db.execute_read_query(sql, (size, offset))
def create_user(self, username, email, password):
"""写操作：创建用户"""
sql = "INSERT INTO users (username, email, password, created_at) VALUES (%s, %s, %s, NOW())"
return self.db.execute_write_query(sql, (username, email, password))
def update_user(self, user_id, **kwargs):
"""写操作：更新用户"""
if not kwargs:
return 0
set_clause = ", ".join([f"{key} = %s" for key in kwargs.keys()])
sql = f"UPDATE users SET {set_clause}, updated_at = NOW() WHERE id = %s"
params = list(kwargs.values()) + [user_id]
return self.db.execute_write_query(sql, params)
def delete_user(self, user_id):
"""写操作：删除用户"""
sql = "DELETE FROM users WHERE id = %s"
return self.db.execute_write_query(sql, (user_id,))
## 2. Java Spring Boot实现示例
@Configuration
public class DataSourceConfig {
@Bean
@Primary
public DataSource masterDataSource() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://192.168.1.100:3306/myapp");
config.setUsername("app_user");
config.setPassword("app_password");
config.setMaximumPoolSize(20);
return new HikariDataSource(config);
}
@Bean
public DataSource slaveDataSource() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://192.168.1.101:3306/myapp");
config.setUsername("app_user");
config.setPassword("app_password");
config.setMaximumPoolSize(20);
return new HikariDataSource(config);
}
@Bean
public JdbcTemplate masterJdbcTemplate(@Qualifier("masterDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
@Bean
public JdbcTemplate slaveJdbcTemplate(@Qualifier("slaveDataSource") DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
@Service
public class UserService {
@Autowired
@Qualifier("masterJdbcTemplate")
private JdbcTemplate masterJdbcTemplate;
@Autowired
@Qualifier("slaveJdbcTemplate")
private JdbcTemplate slaveJdbcTemplate;
// 读操作使用从数据库
public User getUserById(Long id) {
String sql = "SELECT * FROM users WHERE id = ?";
return slaveJdbcTemplate.queryForObject(sql, new Object[]{id}, new UserRowMapper());
}
public List<User> getUsersList(int page, int size) {
int offset = (page - 1) * size;
String sql = "SELECT * FROM users LIMIT ? OFFSET ?";
return slaveJdbcTemplate.query(sql, new Object[]{size, offset}, new UserRowMapper());
}
// 写操作使用主数据库
@Transactional
public int createUser(String username, String email, String password) {
String sql = "INSERT INTO users (username, email, password, created_at) VALUES (?, ?, ?, NOW())";
return masterJdbcTemplate.update(sql, username, email, password);
}
@Transactional
public int updateUser(Long id, String username, String email) {
String sql = "UPDATE users SET username = ?, email = ?, updated_at = NOW() WHERE id = ?";
return masterJdbcTemplate.update(sql, username, email, id);
}
@Transactional
public int deleteUser(Long id) {
String sql = "DELETE FROM users WHERE id = ?";
return masterJdbcTemplate.update(sql, id);
}
}
"""
print("\n应用层读写分离示例:")
print("=" * 40)
print(example)
def proxysql_configuration_example(self):
"""ProxySQL配置示例"""
config_example = """
ProxySQL中间件配置示例：
## 1. ProxySQL安装
# CentOS/RHEL安装
wget https://github.com/sysown/proxysql/releases/download/v2.4.4/proxysql-2.4.4-1-centos7.x86_64.rpm
sudo rpm -ivh proxysql-2.4.4-1-centos7.x86_64.rpm
# Ubuntu/Debian安装
wget https://github.com/sysown/proxysql/releases/download/v2.4.4/proxysql_2.4.4-ubuntu20_amd64.deb
sudo dpkg -i proxysql_2.4.4-ubuntu20_amd64.deb
# 启动ProxySQL
sudo systemctl start proxysql
sudo systemctl enable proxysql
## 2. ProxySQL基础配置
# 连接ProxySQL管理接口
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL Admin> '
-- 添加MySQL服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, comment) VALUES
(0, '192.168.1.100', 3306, 1000, 'Master'),
(1, '192.168.1.101', 3306, 900, 'Slave1'),
(1, '192.168.1.102', 3306, 900, 'Slave2');
-- 加载服务器配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;
-- 保存配置到磁盘
SAVE MYSQL SERVERS TO DISK;
-- 添加MySQL用户
INSERT INTO mysql_users(username, password, default_hostgroup, comment) VALUES
('app_user', 'app_password', 0, 'Application User');
-- 加载用户配置
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
-- 配置查询路由规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply, comment) VALUES
(1, 1, '^SELECT.*', 1, 1, 'Route SELECT to slaves'),
(2, 1, '^INSERT|UPDATE|DELETE.*', 0, 1, 'Route writes to master');
-- 加载查询规则
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
## 3. 高级配置
-- 配置监控用户
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';
-- 配置健康检查
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_interval';
UPDATE global_variables SET variable_value='120000' WHERE variable_name='mysql-monitor_ping_interval';
UPDATE global_variables SET variable_value='10000' WHERE variable_name='mysql-monitor_read_only_interval';
-- 配置连接池
UPDATE global_variables SET variable_value='200' WHERE variable_name='mysql-max_connections';
UPDATE global_variables SET variable_value='10' WHERE variable_name='mysql-default_max_connections';
-- 加载全局变量
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
## 4. 查询缓存配置
-- 启用查询缓存
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, cache_ttl, apply, comment) VALUES
(10, 1, '^SELECT.*FROM users WHERE id=.*', 60000, 1, 'Cache user queries for 1 minute');
-- 缓存统计查询
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, cache_ttl, apply, comment) VALUES
(11, 1, '^SELECT COUNT\\(\\*\\).*', 300000, 1, 'Cache count queries for 5 minutes');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
## 5. 故障切换配置
-- 配置读写分离组
INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, comment) VALUES
(0, 1, 'Master-Slave replication');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- 查看服务器状态
SELECT hostgroup_id, hostname, port, status, weight FROM mysql_servers;
-- 查看连接统计
SELECT * FROM stats_mysql_connection_pool;
-- 查看查询统计
SELECT * FROM stats_mysql_commands_counters;
## 6. 应用程序连接配置
# Python连接示例
import pymysql
# 连接ProxySQL而不是直接连接MySQL
connection = pymysql.connect(
host='127.0.0.1', # ProxySQL地址
port=6033, # ProxySQL MySQL接口端口
user='app_user',
password='app_password',
database='myapp',
charset='utf8mb4'
)
# Java连接示例
String url = "jdbc:mysql://127.0.0.1:6033/myapp";
String username = "app_user";
String password = "app_password";
Connection conn = DriverManager.getConnection(url, username, password);
## 7. 监控和维护
-- 查看ProxySQL状态
SELECT * FROM stats_mysql_global;
-- 查看查询路由统计
SELECT * FROM stats_mysql_query_rules;
-- 查看服务器连接统计
SELECT * FROM stats_mysql_connection_pool ORDER BY srv_host, srv_port;
-- 查看慢查询
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;
-- 重置统计信息
SELECT * FROM stats_mysql_query_digest_reset;
## 8. 配置文件示例 (/etc/proxysql.cnf)
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin;cluster1:secret1"
mysql_ifaces="0.0.0.0:6032"
refresh_interval=2000
debug=true
}
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.7.25"
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
}
"""
print("\nProxySQL配置示例:")
print("=" * 40)
print(config_example)
def load_balancing_strategies(self):
"""负载均衡策略"""
strategies = """
负载均衡策略详解：
## 1. 权重轮询（Weighted Round Robin）
特点：
- 根据服务器权重分配请求
- 权重高的服务器处理更多请求
- 适合服务器性能差异较大的场景
配置示例：
-- ProxySQL权重配置
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES
(1, '192.168.1.101', 3306, 1000), -- 高性能服务器
(1, '192.168.1.102', 3306, 500), -- 中等性能服务器
(1, '192.168.1.103', 3306, 200); -- 低性能服务器
## 2. 最少连接（Least Connections）
特点：
- 将请求分配给当前连接数最少的服务器
- 适合请求处理时间差异较大的场景
- 能够更好地平衡服务器负载
实现方式：
- 中间件自动检测各服务器连接数
- 动态选择连接数最少的服务器
- 需要中间件支持此功能
## 3. 响应时间（Response Time）
特点：
- 根据服务器响应时间选择最快的服务器
- 适合网络延迟差异较大的场景
- 能够提供最佳的用户体验
监控指标：
- 平均响应时间
- 95%响应时间
- 99%响应时间
## 4. 一致性哈希（Consistent Hashing）
特点：
- 根据请求特征（如用户ID）进行哈希
- 相同特征的请求总是路由到同一服务器
- 适合需要会话保持的场景
配置示例：
-- 基于用户ID的路由规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(100, 1, '^SELECT.*FROM users WHERE id IN \\(1,2,3,4,5\\).*', 1, 1),
(101, 1, '^SELECT.*FROM users WHERE id IN \\(6,7,8,9,10\\).*', 2, 1);
## 5. 地理位置（Geographic）
特点：
- 根据客户端地理位置选择最近的服务器
- 适合多地域部署的场景
- 能够减少网络延迟
实现方式：
- DNS解析返回最近的服务器IP
- 应用层根据客户端IP选择服务器
- CDN加速访问
## 6. 健康检查与故障切换
健康检查类型：
- TCP连接检查
- MySQL ping检查
- 只读状态检查
- 复制延迟检查
- 自定义SQL检查
ProxySQL健康检查配置：
-- 配置监控参数
UPDATE global_variables SET variable_value='2000' WHERE variable_name='mysql-monitor_connect_interval';
UPDATE global_variables SET variable_value='10000' WHERE variable_name='mysql-monitor_ping_interval';
UPDATE global_variables SET variable_value='5000' WHERE variable_name='mysql-monitor_read_only_interval';
-- 查看服务器健康状态
SELECT hostname, port, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM mysql_servers;
故障切换策略：
- 自动摘除故障服务器
- 故障恢复后自动加入
- 手动干预机制
- 告警通知机制
## 7. 性能优化建议
连接池优化：
- 合理设置连接池大小
- 配置连接超时时间
- 启用连接复用
- 监控连接使用情况
查询优化：
- 启用查询缓存
- 配置查询重写规则
- 监控慢查询
- 优化查询路由规则
监控指标：
- QPS（每秒查询数）
- 响应时间
- 连接数
- 错误率
- 复制延迟
## 8. 最佳实践
架构设计：
- 读写分离
- 多从库负载均衡
- 故障自动切换
- 监控告警完善
配置管理：
- 版本控制配置文件
- 自动化部署
- 配置变更审核
- 回滚机制
运维管理：
- 定期健康检查
- 性能基准测试
- 容量规划
- 故障演练
"""
print("\n负载均衡策略:")
print("=" * 40)
print(strategies)
# 读写分离演示
splitting_demo = ReadWriteSplitting()
splitting_demo.explain_splitting_methods()
splitting_demo.explain_popular_tools()
splitting_demo.application_level_example()
splitting_demo.proxysql_configuration_example()
splitting_demo.load_balancing_strategies()
6.4 总结
MySQL复制与高可用是构建稳定、可扩展数据库系统的关键技术。本章详细介绍了：
6.4.1 核心技术要点
复制机制：
- 主从复制原理和配置
- 复制格式选择（SBR、RBR、MBR）
- 复制架构设计（主从、主主、级联、多源）
- 故障排除和监控
高可用方案：
- MHA自动故障切换
- MySQL Group Replication
- Galera Cluster多主架构
- InnoDB Cluster完整解决方案
读写分离：
- 应用层实现
- 中间件代理（ProxySQL、MaxScale）
- 负载均衡策略
- 性能优化
6.4.2 最佳实践
架构选择：
- 根据业务需求选择合适的复制架构
- 平衡性能、一致性和可用性要求
- 考虑运维复杂度和成本
监控告警：
- 复制延迟监控
- 服务器健康检查
- 自动故障检测
- 完善的告警机制
故障处理：
- 自动化故障切换
- 数据一致性保证
- 快速恢复机制
- 定期故障演练
6.4.3 技术发展趋势
云原生架构：
- 容器化部署
- Kubernetes集群管理
- 云数据库服务
- 自动化运维
智能化运维：
- AI驱动的故障预测
- 自动化性能调优
- 智能容量规划
- 机器学习优化
下一章将介绍MySQL的备份与恢复策略，包括逻辑备份、物理备份、增量备份和灾难恢复等关键技术。
