MySQL教程FG285-MySQL备份恢复优化

内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Backup and Recovery、MySQL Server Administration。

Part01-基础概念与理论知识

1.1 备份的重要性

MySQL备份是数据库运维的核心组成部分，确保在数据丢失、硬件故障或人为错误时能够快速恢复数据。

1.2 恢复的重要性

有效的恢复策略确保在发生故障时能够快速恢复业务，减少停机时间，保障数据安全。

Part02-生产环境规划与建议

2.1 备份类型

MySQL支持多种备份类型： 01 更多视频教程www.fgedu.net.cn

# 备份类型

1. 物理备份
– 冷备份：停止MySQL服务后备份
– 热备份：MySQL运行时备份
– 温备份：锁定表后备份

2. 逻辑备份
– 全量备份：备份所有数据
– 增量备份：备份自上次备份以来的变更
– 差异备份：备份自上次全量备份以来的变更

3. 备份工具
– mysqldump：逻辑备份工具
– XtraBackup：物理热备份工具
– mysqlpump：并行逻辑备份工具
– MySQL Enterprise Backup：企业级备份工具

2.2 备份计划

制定合理的备份计划：

# 备份计划

1. 全量备份
– 频率：每天一次
– 时间：业务低峰期（如凌晨2点）
– 存储：异地存储

2. 增量备份
– 频率：每小时一次
– 时间：整点
– 存储：本地存储，定期归档

3. 二进制日志备份
– 频率：实时
– 存储：异地存储

4. 备份验证
– 频率：每天一次
– 内容：验证备份文件的完整性和可恢复性

5. 备份清理
– 全量备份：保留7天
– 增量备份：保留2天
– 二进制日志：保留30天

Part03-生产环境项目实施方案

3.1 mysqldump

mysqldump是MySQL自带的逻辑备份工具：

# mysqldump使用

1. 全量备份
mysqldump -u root -p –all-databases –single-transaction –flush-logs –master-data=2 > full_backup.sql

输出示例：
— MySQL dump 10.13 Distrib 8.0.36, for Linux (x86_64)
—
— Host: localhost Database:
— ——————————————————
— Server version 8.0.36

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

2. 单库备份
mysqldump -u root -p –databases test_db –single-transaction > test_db_backup.sql

3. 单表备份
mysqldump -u root -p test_db users –single-transaction > users_backup.sql

4. 压缩备份
mysqldump -u root -p –all-databases –single-transaction | gzip > full_backup.sql.gz

5. 并行备份
mysqldump -u root -p –all-databases –single-transaction –parallel=4 > full_backup.sql

3.2 XtraBackup

XtraBackup是Percona开发的物理热备份工具：

# XtraBackup使用

1. 安装XtraBackup
apt-get install percona-xtrabackup-80

2. 全量备份
xtrabackup –backup –target-dir=/backup/full –user=root –password=password

输出示例：
xtrabackup: recognized server arguments: –datadir=/var/lib/mysql –server-id=1
xtrabackup: recognized client arguments: –backup –target-dir=/backup/full –user=root –password=*
xtrabackup version 8.0.36-30 based on MySQL server 8.0.36 Linux (x86_64) (revision id: 560c93738e6)
260401 10:00:00 version_check Connecting to MySQL server with DSN ‘dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/var/run/mysqld/mysqld.sock’ as ‘root’ (using password: YES)
260401 10:00:00 version_check Connected to MySQL server
260401 10:00:00 version_check Executing a version check against the server…
260401 10:00:00 version_check Done.
260401 10:00:00 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /var/run/mysqld/mysqld.sock
Using server version 8.0.36

3. 增量备份
xtrabackup –backup –target-dir=/backup/incremental1 –incremental-basedir=/backup/full –user=root –password=password

4. 准备备份
xtrabackup –prepare –target-dir=/backup/full

5. 恢复备份
xtrabackup –copy-back –target-dir=/backup/full –datadir=/var/lib/mysql

6. 压缩备份
xtrabackup –backup –target-dir=/backup/full –compress –compress-threads=4 –user=root –password=password

7. 流式备份
xtrabackup –backup –stream=xbstream –target-dir=/backup | gzip > /backup/full_backup.xbstream.gz

3.3 mysqlpump

mysqlpump是MySQL 5.7+提供的并行逻辑备份工具：

# mysqlpump使用

1. 全量备份
mysqlpump -u root -p –all-databases –default-parallelism=4 > full_backup.sql

输出示例：
— MySQL dump 10.18 Distrib 8.0.36, for Linux (x86_64)
—
— Host: localhost Database:
— ——————————————————
— Server version 8.0.36

2. 排除数据库
mysqlpump -u root -p –exclude-databases=mysql,information_schema,performance_schema > backup.sql

3. 包含特定数据库
mysqlpump -u root -p –databases test_db app_db > backup.sql

4. 压缩备份
mysqlpump -u root -p –all-databases –compress > full_backup.sql

Part04-生产案例与实战讲解

4.1 恢复方法

MySQL支持多种恢复方法：

# 恢复方法

1. 逻辑备份恢复
– 使用mysql命令恢复
– 使用source命令恢复

2. 物理备份恢复
– 直接复制文件
– 使用XtraBackup恢复

3. 点-in-time恢复
– 使用二进制日志恢复
– 使用GTID恢复

4. 复制恢复
– 提升从库为主库
– 重新配置复制

4.2 恢复步骤

恢复MySQL数据的基本步骤：

# 恢复步骤

1. 逻辑备份恢复

a. 停止MySQL服务
systemctl stop mysqld

b. 清理数据目录
rm -rf /var/lib/mysql/*

c. 初始化MySQL
mysqld –initialize –user=mysql

d. 启动MySQL
systemctl start mysqld

e. 恢复备份
mysql -u root -p < full_backup.sql

f. 应用二进制日志（如需）
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

2. 物理备份恢复

a. 停止MySQL服务
systemctl stop mysqld

b. 清理数据目录
rm -rf /var/lib/mysql/*

c. 恢复备份
xtrabackup –copy-back –target-dir=/backup/full –datadir=/var/lib/mysql

d. 调整权限
chown -R mysql:mysql /var/lib/mysql

e. 启动MySQL
systemctl start mysqld

3. 点-in-time恢复

a. 恢复全量备份
mysql -u root -p < full_backup.sql

b. 应用二进制日志到指定时间点
mysqlbinlog –stop-datetime=’2026-04-01 10:00:00′ binlog.000001 | mysql -u root -p

Part05-风哥经验总结与分享

5.1 备份优化

优化备份过程，提高备份效率： 02 学习交流加群风哥微信: itpux-com

# 备份优化

1. 并行备份
– 使用mysqldump的–parallel参数
– 使用mysqlpump的–default-parallelism参数
– 使用XtraBackup的–compress-threads参数

2. 压缩备份
– 使用gzip或bzip2压缩备份文件
– 使用XtraBackup的–compress选项
– 减少存储空间和传输时间

3. 增量备份
– 减少备份时间和存储空间
– 适用于大型数据库
– 结合全量备份使用

4. 备份验证
– 定期验证备份文件的完整性
– 测试恢复过程
– 确保备份可用于恢复

5. 备份存储
– 异地存储：防止本地灾难
– 多副本：提高可靠性
– 定期归档：满足合规要求

5.2 恢复优化

优化恢复过程，减少恢复时间： 03 学习交流加群风哥QQ113257174

# 恢复优化

1. 并行恢复
– 使用mysql的–parallel参数
– 拆分大型备份文件
– 并行应用二进制日志

2. 增量恢复
– 先恢复全量备份
– 再应用增量备份
– 最后应用二进制日志

3. 快速恢复
– 使用物理备份而非逻辑备份
– 预配置MySQL实例
– 优化恢复脚本

4. 恢复验证
– 验证数据完整性
– 检查应用功能
– 确认业务连续性

5. 恢复测试
– 定期进行恢复测试
– 记录恢复时间
– 优化恢复流程

6. 最佳实践

6.1 备份最佳实践

备份的最佳实践：

# 备份最佳实践

1. 制定备份策略
– 根据业务需求制定备份计划
– 选择合适的备份工具
– 确定备份频率和保留期

2. 备份验证
– 定期验证备份文件的完整性
– 测试恢复过程
– 确保备份可用于恢复

3. 备份存储
– 异地存储：防止本地灾难
– 多副本：提高可靠性
– 加密存储：保护敏感数据

4. 备份监控
– 监控备份任务的执行状态
– 告警备份失败
– 定期检查备份存储使用情况

5. 文档记录
– 记录备份策略和流程
– 记录备份文件的位置和命名规则
– 记录恢复步骤和测试结果

6.2 恢复最佳实践

恢复的最佳实践：

# 恢复最佳实践

1. 制定恢复计划
– 定义恢复流程和步骤
– 确定恢复时间目标（RTO）
– 确定恢复点目标（RPO）

2. 恢复测试
– 定期进行恢复测试
– 模拟各种故障场景
– 记录恢复时间和结果

3. 恢复准备
– 准备恢复环境
– 确保备份文件可用
– 准备恢复脚本和工具

4. 恢复执行
– 按照恢复计划执行
– 监控恢复过程
– 记录恢复步骤和结果

5. 恢复验证
– 验证数据完整性
– 检查应用功能
– 确认业务连续性

6. 恢复文档
– 记录恢复过程和结果
– 分析恢复时间和效率
– 优化恢复流程

7. 实战案例

7.1 全量备份与恢复

使用XtraBackup进行全量备份和恢复： 04 风哥提示：

# 全量备份与恢复

1. 全量备份
xtrabackup –backup –target-dir=/backup/full –user=root –password=password

2. 准备备份
xtrabackup –prepare –target-dir=/backup/full

3. 模拟故障
# 停止MySQL服务
systemctl stop mysqld

# 删除数据目录
rm -rf /var/lib/mysql/*

4. 恢复备份
xtrabackup –copy-back –target-dir=/backup/full –datadir=/var/lib/mysql

5. 调整权限
chown -R mysql:mysql /var/lib/mysql

6. 启动MySQL
systemctl start mysqld

7. 验证恢复
mysql -u root -p -e “SHOW DATABASES;”

8. 验证数据
mysql -u root -p test_db -e “SELECT COUNT(*) FROM users;”

输出示例：
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+

7.2 增量备份与恢复

使用XtraBackup进行增量备份和恢复：

# 增量备份与恢复

1. 全量备份
xtrabackup –backup –target-dir=/backup/full –user=root –password=password

2. 第一次增量备份
# 插入测试数据
mysql -u root -p test_db -e “INSERT INTO users (name, email) VALUES (‘John Doe’, ‘john@fgedu.net.cn’);”

# 执行增量备份
xtrabackup –backup –target-dir=/backup/inc1 –incremental-basedir=/backup/full –user=root –password=password

3. 第二次增量备份
# 插入更多测试数据
mysql -u root -p test_db -e “INSERT INTO users (name, email) VALUES (‘Jane Smith’, ‘jane@fgedu.net.cn’);”

# 执行增量备份
xtrabackup –backup –target-dir=/backup/inc2 –incremental-basedir=/backup/inc1 –user=root –password=password

4. 准备备份
# 准备全量备份
xtrabackup –prepare –apply-log-only –target-dir=/backup/full

# 应用第一次增量备份
xtrabackup –prepare –apply-log-only –target-dir=/backup/full –incremental-dir=/backup/inc1

# 应用第二次增量备份
xtrabackup –prepare –target-dir=/backup/full –incremental-dir=/backup/inc2

5. 模拟故障
# 停止MySQL服务
systemctl stop mysqld

# 删除数据目录
rm -rf /var/lib/mysql/*

6. 恢复备份
xtrabackup –copy-back –target-dir=/backup/full –datadir=/var/lib/mysql

7. 调整权限
chown -R mysql:mysql /var/lib/mysql

8. 启动MySQL
systemctl start mysqld

9. 验证恢复
mysql -u root -p test_db -e “SELECT * FROM users ORDER BY id DESC LIMIT 2;”

输出示例：
+—-+———–+——————+
| id | name | email |
+—-+———–+——————+
| 1002 | Jane Smith | jane@fgedu.net.cn |
| 1001 | John Doe | john@fgedu.net.cn |
+—-+———–+——————+

7.3 点-in-time恢复

使用二进制日志进行点-in-time恢复： 05更多学习教程公众号风哥教程itpux_com

# 点-in-time恢复

1. 启用二进制日志
vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin
binlog-format=ROW

2. 重启MySQL
systemctl restart mysqld

3. 全量备份
mysqldump -u root -p –all-databases –single-transaction –flush-logs –master-data=2 > full_backup.sql

4. 记录当前二进制日志位置
mysql -u root -p -e “SHOW MASTER STATUS;”

5. 模拟误操作
mysql -u root -p test_db -e “DELETE FROM users WHERE id > 900;”

6. 查看二进制日志
mysqlbinlog –start-position=12345 mysql-bin.000002

7. 确定恢复时间点
# 找到误操作前的位置或时间

8. 恢复全量备份
mysql -u root -p < full_backup.sql

9. 应用二进制日志到误操作前
mysqlbinlog –stop-position=23456 mysql-bin.000002 | mysql -u root -p

10. 验证恢复
mysql -u root -p test_db -e “SELECT COUNT(*) FROM users;”

输出示例：
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+

7.4 灾难恢复

灾难恢复演练： 06 from mysql视频:www.itpux.com

# 灾难恢复

1. 灾难场景模拟
– 主库服务器故障
– 数据中心断电
– 存储设备损坏

2. 恢复策略
– 使用异地备份
– 启动备用服务器
– 恢复数据

3. 恢复步骤

a. 准备备用服务器
– 安装MySQL
– 配置网络
– 准备存储

b. 恢复备份
– 从异地存储获取最新备份
– 恢复全量备份
– 应用增量备份
– 应用二进制日志

c. 验证恢复
– 检查数据完整性
– 测试应用功能
– 确认业务连续性

d. 切换服务
– 更新DNS或负载均衡配置
– 引导流量到新服务器
– 监控服务状态

4. 恢复验证
– 检查业务功能
– 验证数据一致性
– 确认服务可用性

5. 恢复文档
– 记录恢复过程
– 分析恢复时间
– 优化恢复流程