MySQL教程FG014-MySQL备份与恢复详解

本文档风哥主要介绍MySQL 8.4的备份与恢复，包括逻辑备份、物理备份、增量备份和恢复操作等方面。风哥教程参考MySQL官方文档Backup and Recovery等。更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 备份与恢复概述

MySQL备份与恢复是指将数据库数据备份到安全的存储介质中，以便在数据丢失或损坏时能够恢复数据的过程。学习交流加群风哥微信: itpux-com

1.2 备份类型

MySQL备份类型包括：

逻辑备份：使用mysqldump等工具生成SQL语句备份
物理备份：直接复制数据文件的备份
增量备份：只备份自上次备份以来更改的数据
差异备份：只备份自上次完整备份以来更改的数据

1.3 备份策略

MySQL备份策略包括：

完整备份：定期进行完整备份，如每天或每周
增量备份：在完整备份之间进行增量备份，如每小时
备份验证：定期验证备份的完整性和可恢复性
备份存储：将备份存储在安全的位置，如异地存储

Part02-生产环境规划与建议

2.1 备份规划

根据业务需求规划MySQL备份策略，包括备份频率、备份类型和备份工具等。

2.2 存储规划

合理规划备份存储，包括存储介质、存储位置和存储容量等。

2.3 恢复演练

定期进行恢复演练，确保备份的可恢复性和恢复过程的可靠性。

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

3.1 逻辑备份

MySQL逻辑备份的操作：

# 使用mysqldump备份所有数据库
# mysqldump -u root -p –all-databases –routines –triggers –events > all_databases.sql
Enter password: Fgedu123!

# 使用mysqldump备份单个数据库
# mysqldump -u root -p fgedudb > fgedudb.sql
Enter password: Fgedu123!

# 使用mysqldump备份单个表
# mysqldump -u root -p fgedudb fgedu_users > fgedu_users.sql
Enter password: Fgedu123!

# 使用mysqldump备份带压缩
# mysqldump -u root -p fgedudb | gzip > fgedudb.sql.gz
Enter password: Fgedu123!

# 查看备份文件
# ls -la *.sql*
-rw-r–r– 1 root root 10485760 Apr 1 18:00 all_databases.sql
-rw-r–r– 1 root root 1048576 Apr 1 18:05 fgedudb.sql
-rw-r–r– 1 root root 102400 Apr 1 18:10 fgedudb.sql.gz
-rw-r–r– 1 root root 10240 Apr 1 18:15 fgedu_users.sql

3.2 物理备份

MySQL物理备份的操作：

# 使用xtrabackup进行热备份
# xtrabackup –backup –target-dir=/backup/mysql/$(date +%Y%m%d)

# 查看备份目录
# ls -la /backup/mysql/
2026-04-01/

# 查看备份文件
# ls -la /backup/mysql/2026-04-01/
-rw-r—–. 1 root root 4194304 Apr 1 18:20 ib_buffer_pool
-rw-r—–. 1 root root 12582912 Apr 1 18:20 ibdata1
drwxr-x—. 2 root root 4096 Apr 1 18:20 fgedudb
-rw-r—–. 1 root root 473 Apr 1 18:20 backup-my.cnf
-rw-r—–. 1 root root 133 Apr 1 18:20 xtrabackup_checkpoints
-rw-r—–. 1 root root 438 Apr 1 18:20 xtrabackup_info
-rw-r—–. 1 root root 2097152 Apr 1 18:20 xtrabackup_logfile

# 使用cp命令进行冷备份
# systemctl stop mysqld
# cp -r /mysql/data /backup/mysql/cold_backup
# systemctl start mysqld

3.3 增量备份

MySQL增量备份的操作：

# 进行完整备份
# xtrabackup –backup –target-dir=/backup/mysql/full

# 进行增量备份
# xtrabackup –backup –target-dir=/backup/mysql/incremental1 –incremental-basedir=/backup/mysql/full

# 进行第二次增量备份
# xtrabackup –backup –target-dir=/backup/mysql/incremental2 –incremental-basedir=/backup/mysql/incremental1

# 查看增量备份目录
# ls -la /backup/mysql/
full/
incremental1/
incremental2/

3.4 恢复操作

MySQL恢复操作：

# 逻辑备份恢复
# mysql -u root -p fgedudb < fgedudb.sql Enter password: Fgedu123! # 物理备份恢复（使用xtrabackup） # 准备备份 # xtrabackup --prepare --target-dir=/backup/mysql/full # 停止MySQL服务 # systemctl stop mysqld # 恢复数据 # rm -rf /mysql/data/* # xtrabackup --copy-back --target-dir=/backup/mysql/full # 设置权限 # chown -R mysql:mysql /mysql/data # 启动MySQL服务 # systemctl start mysqld # 增量备份恢复 # 准备完整备份 # xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full # 应用第一个增量备份 # xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full --incremental-dir=/backup/mysql/incremental1 # 应用第二个增量备份 # xtrabackup --prepare --target-dir=/backup/mysql/full --incremental-dir=/backup/mysql/incremental2 # 恢复数据 # systemctl stop mysqld # rm -rf /mysql/data/* # xtrabackup --copy-back --target-dir=/backup/mysql/full # chown -R mysql:mysql /mysql/data # systemctl start mysqld

Part04-生产案例与实战讲解

4.1 完整备份与恢复案例

MySQL完整备份与恢复的实际案例：

# 案例1：使用mysqldump进行完整备份
# 创建备份脚本
# vi backup_full.sh
#!/bin/bash
# backup_full.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DATE=$(date +%Y%m%d%H%M%S)
BACKUP_DIR=/backup/mysql

mkdir -p $BACKUP_DIR

mysqldump -u root -pFgedu123! –all-databases –routines –triggers –events > $BACKUP_DIR/all_databases_$DATE.sql

gzip $BACKUP_DIR/all_databases_$DATE.sql

echo “Backup completed: $BACKUP_DIR/all_databases_$DATE.sql.gz”

# 执行备份
# chmod +x backup_full.sh
# ./backup_full.sh
Backup completed: /backup/mysql/all_databases_20260401190000.sql.gz

# 案例2：使用xtrabackup进行完整备份
# 创建备份脚本
# vi backup_xtrabackup.sh
#!/bin/bash
# backup_xtrabackup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DATE=$(date +%Y%m%d)
BACKUP_DIR=/backup/mysql/$DATE

mkdir -p $BACKUP_DIR

xtrabackup –backup –target-dir=$BACKUP_DIR

echo “Backup completed: $BACKUP_DIR”

# 执行备份
# chmod +x backup_xtrabackup.sh
# ./backup_xtrabackup.sh
Backup completed: /backup/mysql/20260401

# 案例3：恢复备份
# 停止MySQL服务
# systemctl stop mysqld

# 恢复数据
# xtrabackup –copy-back –target-dir=/backup/mysql/20260401

# 设置权限
# chown -R mysql:mysql /mysql/data

# 启动MySQL服务
# systemctl start mysqld

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

4.2 增量备份与恢复案例

MySQL增量备份与恢复的实际案例：

# 案例1：创建增量备份脚本
# vi backup_incremental.sh
#!/bin/bash
# backup_incremental.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`

DATE=$(date +%Y%m%d)
HOUR=$(date +%H)
BACKUP_BASE=/backup/mysql
FULL_BACKUP_DIR=$BACKUP_BASE/full_$DATE
INCREMENTAL_BACKUP_DIR=$BACKUP_BASE/incremental_$DATE-$HOUR

# 每周日进行完整备份
if [ $(date +%u) -eq 7 ] && [ $HOUR -eq 0 ]; then
mkdir -p $FULL_BACKUP_DIR
xtrabackup –backup –target-dir=$FULL_BACKUP_DIR
echo “Full backup completed: $FULL_BACKUP_DIR”
else
# 查找最新的完整备份
LATEST_FULL=$(ls -d $BACKUP_BASE/full_* | sort -r | head -1)
if [ -z “$LATEST_FULL” ]; then
echo “No full backup found, creating one…”
mkdir -p $FULL_BACKUP_DIR
xtrabackup –backup –target-dir=$FULL_BACKUP_DIR
echo “Full backup completed: $FULL_BACKUP_DIR”
else
# 查找最新的增量备份
LATEST_INCREMENTAL=$(ls -d $BACKUP_BASE/incremental_* | sort -r | head -1)
if [ -z “$LATEST_INCREMENTAL” ]; then
# 基于完整备份创建增量备份
mkdir -p $INCREMENTAL_BACKUP_DIR
xtrabackup –backup –target-dir=$INCREMENTAL_BACKUP_DIR –incremental-basedir=$LATEST_FULL
echo “Incremental backup completed: $INCREMENTAL_BACKUP_DIR”
else
# 基于上一个增量备份创建增量备份
mkdir -p $INCREMENTAL_BACKUP_DIR
xtrabackup –backup –target-dir=$INCREMENTAL_BACKUP_DIR –incremental-basedir=$LATEST_INCREMENTAL
echo “Incremental backup completed: $INCREMENTAL_BACKUP_DIR”
fi
fi
fi

# 执行增量备份
# chmod +x backup_incremental.sh
# ./backup_incremental.sh
Incremental backup completed: /backup/mysql/incremental_20260401-19

# 案例2：恢复增量备份
# 准备完整备份
# xtrabackup –prepare –apply-log-only –target-dir=/backup/mysql/full_20260401

# 应用所有增量备份
# for inc in $(ls -d /backup/mysql/incremental_20260401-* | sort); do
# xtrabackup –prepare –apply-log-only –target-dir=/backup/mysql/full_20260401 –incremental-dir=$inc
# done

# 完成准备
# xtrabackup –prepare –target-dir=/backup/mysql/full_20260401

# 恢复数据
# systemctl stop mysqld
# rm -rf /mysql/data/*
# xtrabackup –copy-back –target-dir=/backup/mysql/full_20260401
# chown -R mysql:mysql /mysql/data
# systemctl start mysqld

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

Part05-风哥经验总结与分享

5.1 常见备份问题

MySQL备份与恢复中常见的问题包括：

备份失败：磁盘空间不足或权限问题
恢复失败：备份文件损坏或不完整
备份时间过长：影响数据库性能
备份存储安全：备份文件丢失或被篡改
恢复时间过长：影响业务恢复

5.2 最佳实践建议

1. 制定备份策略：根据业务需求制定合理的备份策略
2. 定期备份：定期进行完整备份和增量备份
3. 备份验证：定期验证备份的完整性和可恢复性
4. 备份存储：将备份存储在安全的位置，如异地存储
5. 恢复演练：定期进行恢复演练，确保备份的可恢复性
6. 自动化备份：使用脚本自动化备份过程
7. 监控备份：监控备份过程，及时发现和解决问题
8. 备份压缩：使用压缩减少备份文件大小
9. 备份清理：定期清理过期的备份文件
10. 文档记录：详细记录备份和恢复过程

风哥提示：在生产环境中，建议使用多种备份方式，如逻辑备份和物理备份结合，确保数据的安全性和可恢复性。from MySQL:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html