备份与恢复

数据备份是数据库管理中最重要的工作之一。本章将详细介绍MySQL的各种备份方法、恢复策略以及备份计划的制定。

备份概述

为什么需要备份

sql

-- 数据备份的重要性：
-- 1. 硬件故障：磁盘损坏、服务器故障
-- 2. 人为错误：误删数据、错误操作
-- 3. 软件问题：程序bug、数据库损坏
-- 4. 安全事件：数据泄露、恶意攻击
-- 5. 自然灾害：火灾、水灾、地震
-- 6. 业务需求：数据归档、迁移、测试

备份类型

sql

-- 按备份方式分类：

-- 1. 逻辑备份
--    使用SQL语句导出数据（mysqldump、SELECT INTO OUTFILE）
--    优点：可读性强、跨版本兼容
--    缺点：速度慢、大数据量不适用

-- 2. 物理备份
--    直接复制数据库文件（文件系统备份、xtrabackup）
--    优点：速度快、适合大数据量
--    缺点：跨平台兼容性差

-- 按备份完整性分类：

-- 1. 完全备份
--    备份所有数据
--    恢复简单，但备份时间长、占用空间大

-- 2. 增量备份
--    只备份自上次备份以来变化的数据
--    备份速度快、占用空间小，但恢复复杂

-- 3. 差异备份
--    只备份自上次完全备份以来变化的数据
--    介于完全备份和增量备份之间

-- 按服务器状态分类：

-- 1. 冷备份
--    停止MySQL服务后备份
--    数据一致性好，但影响业务

-- 2. 热备份
--    MySQL运行时备份
--    不影响业务，但需要特殊工具

-- 3. 温备份
--    只读锁定后备份
--    短暂影响业务

mysqldump逻辑备份

基本用法

bash

# mysqldump是MySQL自带的逻辑备份工具
# 基本语法：mysqldump [选项] 数据库名 [表名] > 备份文件.sql

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 db3 > multi_db_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_db_backup.sql

# 备份单个表
mysqldump -u root -p mydb users > users_backup.sql

# 备份多个表
mysqldump -u root -p mydb users orders products > tables_backup.sql

常用选项

bash

# 常用选项说明：

# --single-transaction
# InnoDB表在一致性状态下备份，不锁表（推荐用于生产环境）
mysqldump -u root -p --single-transaction mydb > mydb_backup.sql

# --lock-tables
# 锁定所有表进行备份（MyISAM表使用）
mysqldump -u root -p --lock-tables mydb > mydb_backup.sql

# --lock-all-tables
# 锁定所有数据库的所有表
mysqldump -u root -p --lock-all-tables --all-databases > all_backup.sql

# --routines
# 包含存储过程和函数
mysqldump -u root -p --routines --single-transaction mydb > mydb_backup.sql

# --triggers
# 包含触发器（默认包含）
mysqldump -u root -p --triggers mydb > mydb_backup.sql

# --events
# 包含事件
mysqldump -u root -p --events mydb > mydb_backup.sql

# --no-data
# 只导出表结构，不导出数据
mysqldump -u root -p --no-data mydb > mydb_structure.sql

# --no-create-info
# 只导出数据，不导出表结构
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

# --where
# 导出满足条件的数据
mysqldump -u root -p mydb users --where="id < 1000" > users_partial.sql

# --compact
# 减少输出信息，适合调试
mysqldump -u root -p --compact mydb > mydb_backup.sql

# --complete-insert
# 使用完整的INSERT语句，包含列名
mysqldump -u root -p --complete-insert mydb > mydb_backup.sql

生产环境备份示例

bash

# 生产环境推荐的备份命令

# 完整备份（包含存储过程、触发器、事件）
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --flush-logs \
  --hex-blob \
  --default-character-set=utf8mb4 \
  mydb > mydb_full_$(date +%Y%m%d_%H%M%S).sql

# 选项说明：
# --single-transaction: InnoDB一致性备份，不锁表
# --routines: 包含存储过程和函数
# --triggers: 包含触发器
# --events: 包含事件
# --master-data=2: 记录binlog位置，注释形式
# --flush-logs: 刷新日志，开始新的binlog
# --hex-blob: 十六进制导出BLOB字段
# --default-character-set: 指定字符集

# 压缩备份
mysqldump -u root -p --single-transaction mydb | gzip > mydb_backup_$(date +%Y%m%d).sql.gz

# 远程备份
mysqldump -h 192.168.1.100 -u root -p --single-transaction mydb > mydb_remote_backup.sql

# 备份到远程服务器
mysqldump -u root -p --single-transaction mydb | ssh user@backup-server "cat > /backup/mydb.sql"

数据恢复

使用mysql命令恢复

bash

# 恢复数据库的基本语法
mysql -u root -p 数据库名 < 备份文件.sql

# 恢复整个数据库
mysql -u root -p mydb < mydb_backup.sql

# 恢复所有数据库
mysql -u root -p < all_db_backup.sql

# 恢复压缩的备份文件
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# 恢复前先创建数据库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb"
mysql -u root -p mydb < mydb_backup.sql

# 指定字符集恢复
mysql -u root -p --default-character-set=utf8mb4 mydb < mydb_backup.sql

使用source命令恢复

sql

-- 在MySQL客户端中使用source命令恢复
-- 首先登录MySQL
mysql -u root -p

-- 创建数据库（如果需要）
CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;

-- 执行备份文件
SOURCE /path/to/backup/mydb_backup.sql;

-- 注意：source命令需要指定完整路径

部分数据恢复

bash

# 从备份文件中提取特定表

# 方法1：使用sed提取
sed -n '/^-- Table structure for table `users`/,/^-- Table structure for table/p' mydb_backup.sql > users.sql

# 方法2：使用awk提取
awk '/Table structure for table .users./, /Table structure for table/' mydb_backup.sql > users.sql

# 恢复特定表
mysql -u root -p mydb < users.sql

二进制日志备份

二进制日志简介

sql

-- 二进制日志（binlog）记录了所有修改数据的SQL语句
-- 可用于：
-- 1. 增量备份
-- 2. 主从复制
-- 3. 数据恢复

-- 查看二进制日志是否开启
SHOW VARIABLES LIKE 'log_bin';

-- 查看二进制日志文件列表
SHOW BINARY LOGS;

-- 查看当前正在使用的二进制日志
SHOW MASTER STATUS;

-- 查看二进制日志内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

配置二进制日志

ini

# 在my.cnf中配置二进制日志

[mysqld]
# 开启二进制日志
log-bin = mysql-bin

# 二进制日志格式（ROW、STATEMENT、MIXED）
binlog_format = ROW

# 二进制日志过期时间（天）
expire_logs_days = 7

# 单个日志文件最大大小
max_binlog_size = 100M

# 服务器ID（主从复制必须）
server-id = 1

# 同步写入（更安全但性能略低）
sync_binlog = 1

使用mysqlbinlog工具

bash

# mysqlbinlog用于查看和解析二进制日志

# 查看二进制日志内容
mysqlbinlog mysql-bin.000001

# 查看指定时间范围的日志
mysqlbinlog --start-datetime="2024-01-01 00:00:00" \
            --stop-datetime="2024-01-01 23:59:59" \
            mysql-bin.000001

# 查看指定位置范围的日志
mysqlbinlog --start-position=1000 --stop-position=2000 mysql-bin.000001

# 导出为SQL文件
mysqlbinlog mysql-bin.000001 > binlog_recovery.sql

# 恢复数据
mysql -u root -p < binlog_recovery.sql

# 直接通过管道恢复
mysqlbinlog mysql-bin.000001 | mysql -u root -p

# 恢复多个日志文件
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

基于时间点的恢复

bash

# 场景：在某个时间点发生了误操作，需要恢复到该时间点之前

# 步骤1：恢复完全备份
mysql -u root -p mydb < mydb_full_20240101.sql

# 步骤2：应用二进制日志到指定时间点
mysqlbinlog --stop-datetime="2024-01-02 10:00:00" \
            mysql-bin.000002 mysql-bin.000003 | mysql -u root -p

# 或者从某个时间点开始恢复
mysqlbinlog --start-datetime="2024-01-02 08:00:00" \
            --stop-datetime="2024-01-02 10:00:00" \
            mysql-bin.000002 | mysql -u root -p

物理备份

直接复制数据文件

bash

# 冷备份：停止MySQL后复制数据文件

# 1. 停止MySQL服务
systemctl stop mysql

# 2. 复制数据目录
cp -r /var/lib/mysql /backup/mysql_cold_$(date +%Y%m%d)

# 3. 启动MySQL服务
systemctl start mysql

# 恢复时：
# 1. 停止MySQL
# 2. 删除或重命名原数据目录
# 3. 复制备份数据到数据目录
# 4. 启动MySQL

使用Percona XtraBackup

bash

# Percona XtraBackup是开源的热备份工具
# 支持InnoDB和XtraDB存储引擎

# 安装（以Ubuntu为例）
apt-get install percona-xtrabackup-80

# 完全备份
xtrabackup --backup --target-dir=/backup/full --user=root --password=your_password

# 准备备份（使数据文件一致）
xtrabackup --prepare --target-dir=/backup/full

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=root --password=your_password

# 准备增量备份
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# 恢复数据
# 1. 停止MySQL
systemctl stop mysql

# 2. 清空数据目录
rm -rf /var/lib/mysql/*

# 3. 恢复数据
xtrabackup --copy-back --target-dir=/backup/full

# 4. 修改权限
chown -R mysql:mysql /var/lib/mysql

# 5. 启动MySQL
systemctl start mysql

SELECT INTO OUTFILE备份

导出数据到文件

sql

-- 使用SELECT INTO OUTFILE导出数据

-- 导出查询结果到CSV文件
SELECT * FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees.csv'
FIELDS TERMINATED BY ','  -- 字段分隔符
ENCLOSED BY '"'           -- 字段包围符
LINES TERMINATED BY '\n'; -- 行分隔符

-- 导出指定列
SELECT id, name, salary 
FROM employees
WHERE department = '技术部'
INTO OUTFILE '/var/lib/mysql-files/tech_employees.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导出为制表符分隔的文件
SELECT * FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees.txt'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

-- 注意：MySQL需要有文件写入权限
-- 默认只能写入secure_file_priv指定的目录
SHOW VARIABLES LIKE 'secure_file_priv';

导入数据

sql

-- 使用LOAD DATA INFILE导入数据

-- 导入CSV文件
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导入并忽略第一行（标题行）
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

-- 导入到指定列
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, name, salary, department);

-- 设置字段转换
LOAD DATA INFILE '/var/lib/mysql-files/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
SET created_at = NOW();  -- 设置创建时间

备份策略

备份计划制定

bash

# 根据业务需求制定备份策略

# 小型数据库（< 10GB）
# - 每天完全备份
# - 保留7天备份

# 中型数据库（10GB - 100GB）
# - 每周完全备份
# - 每天增量备份
# - 保留4周备份

# 大型数据库（> 100GB）
# - 每周完全备份
# - 每天增量备份
# - 实时binlog备份
# - 保留8周备份

# 备份脚本示例
#!/bin/bash

# 配置变量
DB_USER="root"
DB_PASS="your_password"
DB_NAME="mydb"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  $DB_NAME | gzip > $BACKUP_FILE

# 检查备份是否成功
if [ $? -eq 0 ]; then
    echo "备份成功: $BACKUP_FILE"
else
    echo "备份失败"
    exit 1
fi

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "旧备份已清理"

定时备份

bash

# 使用crontab设置定时备份

# 编辑crontab
crontab -e

# 每天凌晨2点执行备份
0 2 * * * /path/to/backup_script.sh >> /var/log/mysql_backup.log 2>&1

# 每周日凌晨3点执行完全备份
0 3 * * 0 /path/to/full_backup_script.sh >> /var/log/mysql_backup.log 2>&1

# 每小时备份binlog
0 * * * * /path/to/binlog_backup_script.sh >> /var/log/mysql_backup.log 2>&1

# crontab时间格式说明：
# 分钟 小时 日 月 星期
# *    *    *  *   *
# |    |    |  |   |
# |    |    |  |   +---- 星期几 (0-7, 0和7都是周日)
# |    |    |  +-------- 月份 (1-12)
# |    |    +----------- 日期 (1-31)
# |    +---------------- 小时 (0-23)
# +--------------------- 分钟 (0-59)

备份验证

bash

# 定期验证备份的完整性和可恢复性

#!/bin/bash

# 验证备份文件完整性

BACKUP_FILE=$1

# 检查文件是否存在
if [ ! -f "$BACKUP_FILE" ]; then
    echo "备份文件不存在: $BACKUP_FILE"
    exit 1
fi

# 检查压缩文件完整性
if [[ $BACKUP_FILE == *.gz ]]; then
    gzip -t $BACKUP_FILE
    if [ $? -ne 0 ]; then
        echo "压缩文件损坏: $BACKUP_FILE"
        exit 1
    fi
fi

# 测试恢复（到测试数据库）
TEST_DB="test_restore_$(date +%Y%m%d_%H%M%S)"
mysql -u root -p -e "CREATE DATABASE $TEST_DB"

# 恢复数据
if [[ $BACKUP_FILE == *.gz ]]; then
    gunzip < $BACKUP_FILE | mysql -u root -p $TEST_DB
else
    mysql -u root -p $TEST_DB < $BACKUP_FILE
fi

# 检查表数量
TABLE_COUNT=$(mysql -u root -p -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$TEST_DB'")
echo "恢复的表数量: $TABLE_COUNT"

# 清理测试数据库
mysql -u root -p -e "DROP DATABASE $TEST_DB"

echo "备份验证完成: $BACKUP_FILE"

灾难恢复

恢复流程

bash

# 灾难恢复的标准流程：

# 1. 评估损失
#    - 确定数据丢失的范围
#    - 确定需要恢复的时间点

# 2. 准备恢复环境
#    - 确保MySQL服务正常
#    - 确保有足够的磁盘空间

# 3. 恢复完全备份
mysql -u root -p mydb < mydb_full_20240101.sql

# 4. 应用增量备份
mysql -u root -p mydb < mydb_inc_20240102.sql

# 5. 应用二进制日志
mysqlbinlog --start-datetime="2024-01-02 00:00:00" \
            --stop-datetime="2024-01-02 10:00:00" \
            mysql-bin.000002 | mysql -u root -p

# 6. 验证数据完整性
#    - 检查表数量
#    - 检查记录数
#    - 抽查数据内容

# 7. 恢复业务
#    - 重启应用
#    - 监控运行状态

常见恢复场景

bash

# 场景1：误删表

# 恢复单个表
# 1. 从备份中提取表结构和数据
sed -n '/^-- Table structure for table `deleted_table`/,/^-- Table structure for table/p' backup.sql > table_recovery.sql

# 2. 恢复表
mysql -u root -p mydb < table_recovery.sql

# 场景2：误删数据

# 1. 恢复到删除操作之前的时间点
mysqlbinlog --stop-datetime="2024-01-02 10:00:00" \
            mysql-bin.000002 | mysql -u root -p

# 场景3：数据库损坏

# 1. 尝试修复
mysqlcheck -u root -p --auto-repair mydb

# 2. 如果修复失败，从备份恢复
mysql -u root -p mydb < mydb_backup.sql

# 场景4：整个服务器崩溃

# 1. 安装MySQL
# 2. 配置与原服务器相同
# 3. 恢复数据
cp -r /backup/mysql/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

本章小结

本章详细介绍了MySQL备份与恢复的相关知识：

备份类型：理解逻辑备份、物理备份、完全备份、增量备份等概念
mysqldump备份：掌握逻辑备份工具的使用方法和常用选项
数据恢复：学会使用mysql命令和source命令恢复数据
二进制日志：理解binlog的作用，学会基于时间点的恢复
物理备份：了解直接复制文件和使用XtraBackup的方法
备份策略：学会制定合理的备份计划和验证备份完整性
灾难恢复：掌握常见恢复场景的处理方法

备份是数据安全的最后一道防线，建立完善的备份策略并定期验证备份的有效性至关重要。下一章我们将学习MySQL性能优化的相关知识。

备份与恢复 ​

备份概述 ​

为什么需要备份 ​

备份类型 ​

mysqldump逻辑备份 ​

基本用法 ​

常用选项 ​

生产环境备份示例 ​

数据恢复 ​

使用mysql命令恢复 ​

使用source命令恢复 ​

部分数据恢复 ​

二进制日志备份 ​

二进制日志简介 ​

配置二进制日志 ​

使用mysqlbinlog工具 ​

基于时间点的恢复 ​

物理备份 ​

直接复制数据文件 ​

使用Percona XtraBackup ​

SELECT INTO OUTFILE备份 ​

导出数据到文件 ​

导入数据 ​

备份策略 ​

备份计划制定 ​

定时备份 ​

备份验证 ​

灾难恢复 ​

恢复流程 ​

常见恢复场景 ​

本章小结 ​

备份与恢复

备份概述

为什么需要备份

备份类型

mysqldump逻辑备份

基本用法

常用选项

生产环境备份示例

数据恢复

使用mysql命令恢复

使用source命令恢复

部分数据恢复

二进制日志备份

二进制日志简介

配置二进制日志

使用mysqlbinlog工具

基于时间点的恢复

物理备份

直接复制数据文件

使用Percona XtraBackup

SELECT INTO OUTFILE备份

导出数据到文件

导入数据

备份策略

备份计划制定

定时备份

备份验证

灾难恢复

恢复流程

常见恢复场景

本章小结