内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Disaster Recovery、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 容灾的重要性
容灾设计是保障数据库业务连续性的关键，通过建立异地备份和快速恢复机制，确保在灾难发生时能够快速恢复业务。 02 学习交流加群风哥微信: itpux-com
1.2 RTO与RPO
容灾设计的两个核心指标： 03 学习交流加群风哥QQ113257174
1. RTO (Recovery Time Objective) – 恢复时间目标
– 定义：从故障发生到业务恢复所需的最长时间
– 示例：RTO = 4小时，表示故障后4小时内必须恢复业务
– 影响因素：备份恢复速度、故障切换时间、数据同步延迟
2. RPO (Recovery Point Objective) – 恢复点目标
– 定义：业务可以容忍的数据丢失量
– 示例：RPO = 1小时，表示最多丢失1小时的数据
– 影响因素：备份频率、日志同步延迟、复制延迟
3. 容灾等级
– 等级1：RTO 24小时，RPO 24小时（每日备份）
– 等级2：RTO 4小时，RPO 1小时（每小时备份+日志）
– 等级3：RTO 1小时，RPO 5分钟（实时复制）
– 等级4：RTO 0，RPO 0（双活架构）
Part02-生产环境规划与建议
2.1 备份类型选择
设计合理的备份策略：
1. 全量备份
# 每周日执行全量备份
0 2 * * 0 mysqldump -u root -p –all-databases –single-transaction > /backup/full_$(date +\%Y\%m\%d).sql
# 或使用XtraBackup
0 2 * * 0 xtrabackup –backup –target-dir=/backup/full_$(date +\%Y\%m\%d) –user=root –password=password
2. 增量备份
# 每天执行增量备份（基于周日的全量）
0 2 * * 1-6 xtrabackup –backup –target-dir=/backup/incr_$(date +\%Y\%m\%d) –incremental-basedir=/backup/full_$(date -d “last sunday” +\%Y\%m\%d) –user=root –password=password
3. 差异备份
# 每天执行差异备份（基于周日的全量）
0 2 * * 1-6 xtrabackup –backup –target-dir=/backup/diff_$(date +\%Y\%m\%d) –incremental-basedir=/backup/full_$(date -d “last sunday” +\%Y\%m\%d) –user=root –password=password
4. 日志备份
# 实时备份二进制日志
mysqlbinlog –read-from-remote-server –raw –stop-never mysql-bin.000001 > /backup/binlog/mysql-bin.log
5. 异地备份
# 同步到异地存储
rsync -avz –progress /backup/ remote-server:/backup/mysql/
# 或使用云存储
aws s3 sync /backup/ s3://my-bucket/mysql-backup/
2.2 备份验证
定期验证备份的可用性： 04 风哥提示：
1. 验证备份文件完整性
# 检查文件大小
ls -lh /backup/full_20260404.sql
输出示例：
-rw-r–r– 1 root root 2.5G Apr 4 02:00 /backup/full_20260404.sql
# 验证SQL文件语法
mysql -u root -p -e “source /backup/full_20260404.sql” –dry-run 2>&1 | head -20
2. 定期恢复测试
# 创建测试环境
mysql -u root -p -e “CREATE DATABASE backup_test;”
# 恢复备份到测试库
mysql -u root -p backup_test < /backup/full_20260404.sql
输出示例：
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
…
Query OK, 0 rows affected (0.00 sec)
# 验证数据完整性
mysql -u root -p -e “USE backup_test; SELECT COUNT(*) FROM users;”
输出示例：
+———-+
| COUNT(*) |
+———-+
| 100000 |
+———-+
# 对比数据一致性
mysql -u root -p -e “
SELECT
(SELECT COUNT(*) FROM production_db.users) as prod_count,
(SELECT COUNT(*) FROM backup_test.users) as backup_count;
“
输出示例：
+————+————–+
| prod_count | backup_count |
+————+————–+
| 100000 | 100000 |
+————+————–+
3. 自动化验证脚本
vim /scripts/verify_backup.sh
#!/bin/bash
BACKUP_FILE=”/backup/full_$(date +%Y%m%d).sql”
TEST_DB=”backup_test_$(date +%s)”
# 创建测试数据库
mysql -u root -p -e “CREATE DATABASE $TEST_DB;”
# 恢复备份
if mysql -u root -p $TEST_DB < $BACKUP_FILE; then
echo “[OK] 备份恢复成功”
# 验证表数量
TABLE_COUNT=$(mysql -u root -p -e “SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=’$TEST_DB’;” | tail -1)
echo “[INFO] 恢复后的表数量: $TABLE_COUNT”
# 清理测试数据库
mysql -u root -p -e “DROP DATABASE $TEST_DB;”
echo “[OK] 验证完成”
else
echo “[ERROR] 备份恢复失败!”
exit 1
fi
Part03-生产环境项目实施方案
3.1 主从复制
设计主从复制架构：
1. 一主一从架构
# 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync-binlog = 1
innodb_flush_log_at_trx_commit = 1
# 从库配置
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1
log-slave-updates = 1
# 查看复制状态
mysql> SHOW SLAVE STATUS\G
输出示例：
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1234567
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1234567
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 1234567
Relay_Log_Space: 1234567
Seconds_Behind_Master: 0
2. 一主多从架构
# 配置多个从库
[mysqld]
server-id = 3 # 第二个从库
relay-log = relay-bin
read-only = 1
[mysqld]
server-id = 4 # 第三个从库
relay-log = relay-bin
read-only = 1
# 查看所有从库状态
mysql> SHOW PROCESSLIST;
输出示例：
+—-+————-+———–+——+———+——+—————————————————————+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+—————————————————————+——————+
| 1 | system user | | NULL | Connect | 100 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 100 | Slave has read all relay log; waiting for more updates | NULL |
| 5 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+—-+————-+———–+——+———+——+—————————————————————+——————+
3. 级联复制
# 从库作为其他从库的主库
[mysqld]
server-id = 2
relay-log = relay-bin
log-bin = mysql-bin
log-slave-updates = 1
read-only = 1
# 三级从库配置
[mysqld]
server-id = 3
relay-log = relay-bin
read-only = 1
3.2 GTID复制
使用GTID进行复制： 05更多学习教程公众号风哥教程itpux_com
1. 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
# 查看GTID状态
mysql> SHOW GLOBAL VARIABLES LIKE ‘%gtid%’;
输出示例：
+———————————-+—————————————-+
| Variable_name | Value |
+———————————-+—————————————-+
| gtid_executed | 12345678-1234-1234-1234-1234567890ab:1-100 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+———————————-+—————————————-+
2. 从库配置
[mysqld]
server-id = 2
relay-log = relay-bin
gtid-mode = ON
enforce-gtid-consistency = ON
read-only = 1
# 配置GTID复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’password’,
-> MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
3. GTID故障切换
# 查看从库已执行的GTID
mysql> SHOW GLOBAL VARIABLES LIKE ‘gtid_executed’;
# 提升从库为主库
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
mysql> SET GLOBAL read_only = OFF;
# 其他从库指向新主库
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’password’,
-> MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
Part04-生产案例与实战讲解
4.1 故障切换流程
设计故障切换流程： 06 from mysql视频:www.itpux.com
1. 故障检测
# 检查主库状态
mysql -h 192.168.1.100 -u root -p -e “SELECT 1;”
输出示例（故障时）：
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.1.100’ (111)
# 检查从库复制延迟
mysql -h 192.168.1.101 -u root -p -e “SHOW SLAVE STATUS\G” | grep Seconds_Behind_Master
输出示例：
Seconds_Behind_Master: 0
2. 选择新主库
# 选择复制延迟最小的从库
mysql -h 192.168.1.101 -u root -p -e “SHOW SLAVE STATUS\G” | grep -E “(Master_Log_File|Read_Master_Log_Pos)”
mysql -h 192.168.1.102 -u root -p -e “SHOW SLAVE STATUS\G” | grep -E “(Master_Log_File|Read_Master_Log_Pos)”
# 对比binlog位置，选择最新的从库
3. 提升从库为主库
# 在选定的从库上执行
mysql -h 192.168.1.101 -u root -p -e “
STOP SLAVE;
RESET SLAVE ALL;
SET GLOBAL read_only = OFF;
“
输出示例：
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
4. 切换应用连接
# 更新应用配置文件
vim /app/config/database.yml
production:
host: 192.168.1.101
port: 3306
username: app_user
password: app_password
database: production_db
# 重启应用服务
systemctl restart app-service
5. 重新配置其他从库
# 在其他从库上执行
mysql -h 192.168.1.102 -u root -p -e “
STOP SLAVE;
CHANGE MASTER TO
MASTER_HOST=’192.168.1.101′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’password’,
MASTER_AUTO_POSITION=1;
START SLAVE;
“
6. 验证切换结果
# 检查新主库状态
mysql -h 192.168.1.101 -u root -p -e “SHOW MASTER STATUS;”
输出示例：
+——————+———-+————–+——————+——————————————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————————————+
| mysql-bin.000001 | 123456 | | | 12345678-1234-1234-1234-1234567890ab:1-100 |
+——————+———-+————–+——————+——————————————+
# 检查从库复制状态
mysql -h 192.168.1.102 -u root -p -e “SHOW SLAVE STATUS\G” | grep -E “(Slave_IO_Running|Slave_SQL_Running)”
输出示例：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.2 自动故障切换
使用MHA实现自动故障切换：
1. 安装MHA
# 安装依赖
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
# 下载MHA
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
# 安装MHA
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
2. 配置MHA
vim /etc/mha/app1.cnf
[server default]
user=mha
password=mha_password
ssh_user=root
repl_user=repl
repl_password=repl_password
ping_interval=1
master_binlog_dir=/var/lib/mysql
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
[server1]
hostname=192.168.1.100
port=3306
[server2]
hostname=192.168.1.101
port=3306
candidate_master=1
[server3]
hostname=192.168.1.102
port=3306
3. 检查SSH连接
masterha_check_ssh –conf=/etc/mha/app1.cnf
输出示例：
[info] All SSH connection tests passed successfully.
4. 检查复制状态
masterha_check_repl –conf=/etc/mha/app1.cnf
输出示例：
[info] Slaves settings check done.
[info]
192.168.1.100(192.168.1.100:3306) (current master)
+–192.168.1.101(192.168.1.101:3306)
+–192.168.1.102(192.168.1.102:3306)
[info] Checking replication health on 192.168.1.101..
[info] ok.
[info] Checking replication health on 192.168.1.102..
[info] ok.
[info] Got exit code 0 (Not master dead).
5. 启动MHA管理器
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 &
6. 查看MHA状态
masterha_check_status –conf=/etc/mha/app1.cnf
输出示例：
app1 (pid:1234) is running(0:PING_OK), master:192.168.1.100
7. 手动触发故障切换
masterha_master_switch –master_state=dead –conf=/etc/mha/app1.cnf –dead_master_host=192.168.1.100 –new_master_host=192.168.1.101
输出示例：
Master 192.168.1.100(192.168.1.100:3306) is dead!
From:
192.168.1.100(192.168.1.100:3306) (current master)
+–192.168.1.101(192.168.1.101:3306)
+–192.168.1.102(192.168.1.102:3306)
To:
192.168.1.101(192.168.1.101:3306) (new master)
+–192.168.1.102(192.168.1.102:3306)
Starting master switch from 192.168.1.100(192.168.1.100:3306) to 192.168.1.101(192.168.1.101:3306)? (yes/NO): yes
[info] New master is 192.168.1.101(192.168.1.101:3306)
[info] Starting master switch…
[info]
[info] * Phase 1: Configuration Check Phase..
[info]
[info] * Phase 2: Dead Master Shutdown Phase..
[info]
[info] * Phase 3: Master Recovery Phase..
[info]
[info] * Phase 4: Slaves Recovery Phase..
[info]
[info] * Phase 5: New master cleanup phase..
[info]
[info] Switching master to 192.168.1.101(192.168.1.101:3306) completed successfully.
Part05-风哥经验总结与分享
5.1 容灾设计最佳实践
容灾设计的最佳实践：
1. 3-2-1备份原则
– 3份数据副本
– 2种不同存储介质
– 1份异地备份
2. 定期演练
– 每季度进行故障切换演练
– 每半年进行灾难恢复演练
– 记录演练结果和改进措施
3. 监控和告警
– 监控复制延迟
– 监控备份状态
– 监控存储空间
4. 文档化
– 容灾架构图
– 故障处理手册
– 联系人清单
5. 自动化
– 自动故障检测
– 自动故障切换
– 自动恢复流程
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
