MySQL 数据库备份与还原

作者：IvanCodes 日期：2025年5月18日 专栏：MySQL教程

思维导图

在这里插入图片描述

在这里插入图片描述

备份 (Backup) 与 冗余 (Redundancy) 的核心区别: 🎯

备份是指创建数据的副本并将其存储在不同位置或介质，主要目的是在发生数据丢失、损坏或逻辑错误时进行恢复到某一历史状态。冗余是指在系统中维护数据的多个实时或近实时副本（例如 RAID 磁盘阵列、数据库主从复制），主要目的是提高系统的可用性和容错性，当主副本故障时可以快速切换到备用副本。冗余通常不能防止逻辑错误（如误删除数据会同步到所有副本）或恢复到特定历史时间点。

mysqldump 逻辑备份详解: 📄➡️SQL mysqldump 是 MySQL 官方提供的命令行工具，用于生成数据库的逻辑备份。它将数据库的结构定义（如 CREATE TABLE 语句）和数据内容（如 INSERT INTO 语句）导出为一系列 SQL 语句，通常保存为一个 .sql 文件。这种备份方式灵活，跨平台和版本兼容性好。

一、表级操作 📦

• 表级备份 (备份指定表): 备份特定数据库中的一个或多个表，包含其结构和数据。 语法:

mysqldump -u [username] -p[password] [db_name] [table_name1] [table_name2] ... > tables_backup.sql

示例：备份 company_db 数据库中的 employees 和 departments 表。

mysqldump -u appuser -psecret company_db employees departments > company_db_emp_dept.sql

• 表级还原 (还原指定表): 将之前备份的表结构和数据导入到目标数据库中。通常目标数据库需要已存在。如果备份文件包含 CREATE TABLE 语句 (默认) 且目标库中已有同名表，可能需要先手动 DROP 目标表或备份文件中有 DROP TABLE IF EXISTS (mysqldump 默认添加)。 语法:

mysql -u [username] -p[password] [db_name] < tables_backup.sql

示例：将 company_db_emp_dept.sql 中的表还原到 company_db 数据库。

mysql -u appuser -psecret company_db < company_db_emp_dept.sql

注意：此方法会将备份文件中的所有表导入指定数据库。若只想从多表备份文件中还原特定一个表，需手动编辑 SQL 文件提取该表相关语句，或使用 sed/awk 等工具处理。

二、库级操作 🗄️

• 库级备份 (备份单个或多个数据库): 备份一个或多个完整的数据库，包含其所有对象（表、视图、存储过程等）的结构和数据。 备份单个数据库 (不含 CREATE DATABASE 语句):

mysqldump -u [username] -p[password] [db_name] > single_db_backup.sql

示例：备份 sales_db 数据库。

mysqldump -u data_analyst -psales_pass sales_db > sales_db_backup.sql

备份多个数据库 (使用 --databases 选项，备份文件会包含 CREATE DATABASE IF NOT EXISTS 语句):

mysqldump -u [username] -p[password] --databases [db_name1] [db_name2] ... > multiple_dbs_backup.sql

示例：备份 hr_db 和 finance_db 数据库。

mysqldump -u admin_user -padmin_pass --databases hr_db finance_db > hr_finance_dbs_backup.sql

• 库级还原 (还原单个或多个数据库): 如果备份文件是使用 --databases 选项生成的，它通常包含创建数据库的语句，可以直接导入。如果备份的是单个数据库且不含 CREATE DATABASE 语句，则还原前目标数据库通常需要已存在（或手动创建）。 还原单个数据库 (假设 single_db_backup.sql 不含 CREATE DATABASE):

-- 1. (如果数据库不存在) 在 mysql 客户端手动创建数据库:
-- CREATE DATABASE IF NOT EXISTS sales_db;

-- 2. 导入备份文件:
mysql -u [username] -p[password] [db_name] < single_db_backup.sql

示例：还原 sales_db_backup.sql 到 sales_db。

mysql -u data_analyst -psales_pass sales_db < sales_db_backup.sql

还原多个数据库 (备份文件由 --databases 生成，包含 CREATE DATABASE):

mysql -u [username] -p[password] < multiple_dbs_backup.sql

示例：还原 hr_finance_dbs_backup.sql。

mysql -u admin_user -padmin_pass < hr_finance_dbs_backup.sql

三、全库操作 🌍

• 全库备份 (备份 MySQL 服务器上的所有数据库): 使用 --all-databases (或 -A) 选项备份实例上的所有用户数据库。备份文件会包含所有数据库的 CREATE DATABASE IF NOT EXISTS 语句。通常建议同时备份存储过程、函数、触发器和事件。 语法:

mysqldump -u [root_or_admin_user] -p[password] --all-databases --routines --triggers --events > full_instance_backup.sql

示例：以 root 用户备份所有数据库及相关对象。

mysqldump -u root -proot_password --all-databases --routines --triggers --events > mysql_server_full_backup.sql

• 全库还原 (还原所有数据库): 由于 --all-databases 生成的备份文件包含所有必要信息，可以直接导入到目标 MySQL 服务器 (通常是空实例或用于灾难恢复)。 语法:

mysql -u [root_or_admin_user] -p[password] < full_instance_backup.sql

示例：还原 mysql_server_full_backup.sql。

mysql -u root -proot_password < mysql_server_full_backup.sql

警告：全库还原是破坏性操作，会覆盖目标服务器上所有同名数据库。通常用于新环境搭建或灾难恢复。在生产环境执行前务必三思并确保有回退计划。

四、mysqldump 常用重要选项详解 🔧

• --user=[username] 或 -u [username]: 指定连接 MySQL 的用户名。
• --password=[password] 或 -p[password]: 指定密码。如果密码为空或包含特殊字符，建议使用 -p 然后按提示输入。
• --host=[hostname] 或 -h [hostname]: 指定连接的 MySQL 服务器主机名或 IP 地址 (默认为 localhost)。
• --port=[port_num] 或 -P [port_num]: 指定连接的 MySQL 服务器端口号 (默认为 3306)。
• --single-transaction: 对于 InnoDB 表，在单个事务中进行备份，不阻塞写操作，实现热备份并保证数据一致性。对 MyISAM 表无效。 示例：

mysqldump -u dbuser -pdbpass --single-transaction my_innodb_db > my_innodb_db_consistent.sql

• --master-data=1 或 --master-data=2:
  • =1: 在备份文件中输出 CHANGE MASTER TO 语句，注释掉。用于设置从库。
  • =2: 与 =1 类似，但 CHANGE MASTER TO 语句是注释掉的。更常用的是，它会在备份文件头部以注释形式记录当前二进制日志文件名和位置 (MASTER_LOG_FILE, MASTER_LOG_POS)。这对于基于二进制日志的时间点恢复 (PITR) 至关重要。 示例：

mysqldump -u dbuser -pdbpass --single-transaction --master-data=2 my_db > my_db_with_binlog_info.sql

• --flush-logs 或 -F: 在开始备份前，关闭当前的二进制日志文件并创建一个新的。通常与 --master-data 配合使用，以确保全备之后的所有更改都记录在新 binlog 中。
• --routines 或 -R: 备份数据库中的存储过程和函数。
• --triggers: 备份表上的触发器 (通常是默认行为，但显式指定更清晰)。
• --events 或 -E: 备份事件调度器中定义的事件。
• --no-data 或 -d: 只备份数据库结构（表定义、视图、存储过程等），不包含任何行数据。 示例：

mysqldump -u dbuser -pdbpass --no-data my_db > my_db_structure_only.sql

• --no-create-info 或 -t: 只备份数据（INSERT 语句），不包含 CREATE TABLE 语句。通常与 --no-data 备份配合使用，或用于向已存在的表追加数据。 示例：

mysqldump -u dbuser -pdbpass --no-create-info my_db my_table > my_table_data_only.sql

• --databases: 明确指定一个或多个数据库进行备份，并在输出的 SQL 文件中包含 CREATE DATABASE IF NOT EXISTS 和 USE 语句。
• --all-databases 或 -A: 备份 MySQL 服务器上的所有数据库。
• --add-drop-database: 在每个 CREATE DATABASE 语句前添加 DROP DATABASE IF EXISTS 语句。谨慎使用。
• --add-drop-table: 在每个 CREATE TABLE 语句前添加 DROP TABLE IF EXISTS 语句 (默认行为)。
• --skip-add-drop-table: 禁止在 CREATE TABLE 前添加 DROP TABLE IF EXISTS。
• --where='condition_string': 只导出表中满足指定 WHERE 条件的行。 示例：只备份 orders 表中2023年的订单

mysqldump -u dbuser -pdbpass my_db orders --where="YEAR(order_date) = 2023" > orders_2023.sql

• --ignore-table=db_name.table_name: 在备份数据库或所有数据库时，忽略指定的表。可以多次使用此选项来忽略多个表。 示例：备份 my_db 但忽略 my_db.logs 表

mysqldump -u dbuser -pdbpass my_db --ignore-table=my_db.logs > my_db_no_logs.sql

• --result-file=/path/to/output.sql 或 -r /path/to/output.sql: 直接将输出写入指定文件，而不是标准输出。在 Windows 上可能比重定向 > 更可靠。
• --default-character-set=charset_name: 指定备份时使用的字符集，如 utf8mb4。
• --set-gtid-purged=OFF|AUTO|ON: 控制备份文件中 GTID 信息的包含。对于基于 GTID 的复制环境非常重要。
• --dump-slave 或 --master-data: 已提及，对于复制和 PITR 至关重要。
• --tz-utc: 备份时将会话时区设置为 UTC，并在备份文件中添加 SET TIME_ZONE='+00:00'。有助于在不同时区的服务器间迁移数据时保持时间戳一致性。

5. mysqldump + 二进制日志 (Binlog) 实现增量备份与时间点恢复 (PITR) ⏳🔄 mysqldump 执行的是全量逻辑备份。为了实现真正的增量备份效果和精确的时间点恢复 (Point-in-Time Recovery, PITR)，必须结合 MySQL 的二进制日志 (Binary Log)。

基本策略与流程:

1. 确保二进制日志已启用: 在 MySQL 配置文件 (my.cnf 或 my.ini) 中检查或添加/修改以下参数：
   • log_bin = /path/to/your/binlog/mysql-bin (指定 binlog 文件名前缀和路径)
   • server_id = unique_server_id (每个 MySQL 服务器实例必须有唯一的 ID)
   • binlog_format = ROW (推荐，基于行的复制更准确，尤其是有不确定性函数时) 修改配置后需重启 MySQL 服务。
2. 执行定期全量备份并记录 Binlog 位置: 例如，每天或每周执行一次全量备份。关键在于备份时记录下当时的二进制日志文件名和位置 (position)或GTID（如果使用 GTID 复制）。使用 mysqldump 的 --master-data=2 和 --flush-logs 选项可以方便地做到这一点。 示例：每日全量备份脚本的一部分

BACKUP_DIR="/mnt/backups/mysql"
DATE_SUFFIX=$(date +%Y-%m-%d_%H-%M-%S)
FULL_BACKUP_FILE="${BACKUP_DIR}/full_backup_${DATE_SUFFIX}.sql.gz"
LOG_FILE="${BACKUP_DIR}/backup_log_${DATE_SUFFIX}.log"

echo "Starting full backup at $(date)" >> ${LOG_FILE}
mysqldump -u root -pYourRootPassword --all-databases \
    --master-data=2 \
    --single-transaction \
    --flush-logs \
    --routines --triggers --events \
    --default-character-set=utf8mb4 \
    | gzip > ${FULL_BACKUP_FILE}

if [ ${PIPESTATUS[0]} -eq 0 ]; then
    echo "Full backup successful: ${FULL_BACKUP_FILE}" >> ${LOG_FILE}
else
    echo "Full backup FAILED!" >> ${LOG_FILE}
fi
echo "Backup finished at $(date)" >> ${LOG_FILE}

备份完成后，查看 .sql.gz 解压后的文件头部，会有类似这样的注释： -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.0000XX', MASTER_LOG_POS=YYYY; 这里的 mysql-bin.0000XX 和 YYYY 就是全备完成时的 binlog 文件和位置，是后续增量恢复的起点。

持续备份二进制日志文件: 在两次全量备份之间，MySQL 会持续产生新的二进制日志文件。这些文件记录了自上次日志刷新（例如由 --flush-logs触发）以来的所有数据修改操作。你需要将这些物理的 binlog 文件定期从 MySQL 服务器的数据目录（或 log_bin 指定的路径）复制到安全的备份存储位置。这些是你的“增量”数据。 可以通过 SHOW BINARY LOGS; 查看当前的 binlog 文件列表。通常，当一个 binlog 文件达到 max_binlog_size 或执行 FLUSH LOGS; 时，会生成新的 binlog 文件。

时间点恢复 (PITR) 流程: 🚑 假设数据库在某个时间点 T_failure 发生故障，而你希望恢复到故障前的某个时间点 T_restore。 a. 确定恢复基准: 找到在 T_restore 之前的最近一次成功的全量备份文件，以及该备份对应的binlog 文件名 (start_log_file)和位置 (start_log_pos)。 b. 准备恢复环境: 通常在一个新的或已清理的 MySQL 实例上进行恢复。 c. 还原全量备份: bash gunzip < /mnt/backups/mysql/full_backup_YYYY-MM-DD_HH-MM-SS.sql.gz | mysql -u root -pYourRootPassword d. 找到并准备需要应用的 Binlog 文件: 收集中从 start_log_file 开始，一直到包含 T_restore 时间点的所有 binlog 文件。将这些文件按顺序放在一个可访问的目录。 e. 应用 Binlog 进行增量恢复: 使用 mysqlbinlog 工具读取这些 binlog 文件，并将其中的 SQL 语句通过管道传递给 mysql 客户端执行。你需要精确控制应用的范围。 示例：假设全备对应 mysql-bin.000005 位置 154，要恢复到 2023-10-27 10:30:00，期间的 binlog 文件是 mysql-bin.000005, mysql-bin.000006, mysql-bin.000007。

 # 应用第一个相关的 binlog，从指定位置开始
 mysqlbinlog --start-position=154 /path/to/binlogs/mysql-bin.000005 | mysql -u root -pYourRootPassword

 # 应用中间完整的 binlog 文件
 mysqlbinlog /path/to/binlogs/mysql-bin.000006 | mysql -u root -pYourRootPassword

 # 应用最后一个相关的 binlog，直到指定停止时间点
 mysqlbinlog --stop-datetime="2023-10-27 10:30:00" /path/to/binlogs/mysql-bin.000007 | mysql -u root -pYourRootPassword
 ```

mysqlbinlog 的其他重要选项：

• --start-datetime="YYYY-MM-DD HH:MM:SS"
• --stop-position=pos_num
• --database=db_name (如果只想恢复特定数据库的更改)

如果使用 GTID，恢复过程会略有不同，通常依赖于 GTID 集合来确定恢复点。 通过这种“全量备份 + 增量二进制日志”的策略，可以实现非常灵活和精确的数据恢复。

练习题 ✍️

假设你有一个用户 backup_user 密码为 backup_pass，数据库 mydb 中有表 table1, table2, logs。

1. 编写命令备份 mydb 数据库中的 table1 和 table2 到文件 mydb_tables.sql。 答案:

mysqldump -u backup_user -pbackup_pass mydb table1 table2 > mydb_tables.sql

1. 编写命令备份整个 mydb 数据库 (包含结构和数据) 到 mydb_full.sql.gz (压缩格式)。 答案:

mysqldump -u backup_user -pbackup_pass mydb | gzip > mydb_full.sql.gz

1. 编写命令仅备份 mydb 数据库的结构 (不含数据) 到 mydb_structure.sql。 答案:

mysqldump -u backup_user -pbackup_pass --no-data mydb > mydb_structure.sql

1. 编写命令备份所有数据库，包含存储过程和触发器，到 all_dbs_with_routines.sql，并记录二进制日志位置。 答案:

mysqldump -u backup_user -pbackup_pass --all-databases --routines --triggers --master-data=2 > all_dbs_with_routines.sql

1. 编写命令还原 mydb_full.sql.gz 备份文件到名为 mydb_restored 的新数据库 (假设 mydb_restored 已手动创建且为空)。 答案:

gunzip < mydb_full.sql.gz | mysql -u backup_user -pbackup_pass mydb_restored

1. 编写命令备份 mydb 数据库，但忽略 mydb.logs 表，输出到 mydb_no_logs.sql。 答案:

mysqldump -u backup_user -pbackup_pass mydb --ignore-table=mydb.logs > mydb_no_logs.sql

1. 编写命令备份 mydb.table1 中 status 列为 ‘active’ 的所有行到 active_rows_table1.sql。 答案:

mysqldump -u backup_user -pbackup_pass mydb table1 --where="status='active'" > active_rows_table1.sql

1. 编写命令备份 mydb 数据库，使用单事务模式，并且在备份开始前刷新日志，输出到 mydb_flushed_single_trans.sql。 答案:

mysqldump -u backup_user -pbackup_pass --single-transaction --flush-logs mydb > mydb_flushed_single_trans.sql

1. 假设 all_dbs_with_routines.sql 是通过 --all-databases 生成的。编写命令将其还原到 MySQL 服务器。 答案:

mysql -u backup_user -pbackup_pass < all_dbs_with_routines.sql

1. 编写命令备份 mydb 数据库，并指定使用 utf8mb4 字符集，输出到 mydb_utf8mb4.sql。 答案:

mysqldump -u backup_user -pbackup_pass --default-character-set=utf8mb4 mydb > mydb_utf8mb4.sql