MySQL 数据库备份与恢复

MySQL备份恢复方案解析 MySQL数据库备份主要包括全量、增量和差异三种方式：全量备份完整但耗时，适用于数据量小的场景；增量备份高效但恢复复杂，适合频繁更新的大数据量；差异备份则介于两者之间。关键工具为mysqldump和二进制日志，前者实现逻辑备份（全库/单库/单表），后者支持基于时间点的恢复。配置时需修改my.cnf开启二进制日志功能，通过"全量+日志"组合实现灵活的数

Sadsvit

519人浏览 · 2025-09-25 10:13:01

Sadsvit · 2025-09-25 10:13:01 发布

MySQL 数据库备份与恢复

在这里插入图片描述

数据是业务的核心资产，定期备份与高效恢复是数据库管理的必备技能。MySQL 常用备份方案包括全量备份、增量备份、差异备份，核心工具为 mysqldump（逻辑备份）和 mysqlbinlog（二进制日志恢复）。

1. 常用备份方案对比

备份方案	核心定义	优点	缺点	适用场景
全量备份	备份某一时间点的所有数据	恢复简单（直接恢复即可）、数据完整	备份时间长、占用空间大	数据量小、需快速恢复的场景
增量备份	备份“上一次备份后新增/修改”的数据	备份时间短、占用空间小	恢复复杂（需按顺序恢复全量+所有增量）	数据量大、更新频繁的场景
差异备份	备份“上一次全量备份后新增/修改”的数据	恢复简单（全量+最后一次差异）、比增量占用空间小	备份时间比增量长	数据更新频率中等的场景

2. 全量备份与恢复（mysqldump）

mysqldump 是 MySQL 官方提供的逻辑备份工具，支持备份单个数据库、多个数据库或所有数据库，备份结果为 SQL 脚本文件，恢复时直接执行脚本即可。

（1）核心语法

# 备份所有数据库
mysqldump -u<用户名> -p<密码> --all-databases > <备份文件名.sql>

# 备份指定数据库
mysqldump -u<用户名> -p<密码> --databases <数据库名1> [数据库名2...] > <备份文件名.sql>

# 备份指定数据库的指定表
mysqldump -u<用户名> -p<密码> <数据库名> <表1> [表2...] > <备份文件名.sql>

常用选项：-u（指定用户名）、-p（指定密码，可后输入）、-h（指定数据库主机，默认 localhost）、-P（指定端口，默认 3306）。

（2）实战案例：备份与恢复“zhang3 数据库”

步骤1：查看备份前的数据库状态

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xuexiao            |
| zhang3             |
+--------------------+
6 rows in set (0.00 sec)


mysql> use zhang3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed


mysql> show tables;
+------------------+
| Tables_in_zhang3 |
+------------------+
| course           |
| student          |
| zhangsan         |
+------------------+
3 rows in set (0.00 sec)

步骤2：全量备份 zhang3 数据库

# 备份 zhang3 数据库到 zhang3-20250917.sql
[root@zhang3 ~]# mysqldump -uroot -p123456 --databases zhang3 > zhang3-20250917.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zhang3 ~]# ls
zhang3-20250917.sql

步骤3：模拟数据丢失（删除 zhang3 数据库）

mysql> drop database zhang3;
Query OK, 3 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xuexiao            |
+--------------------+
5 rows in set (0.00 sec)

步骤4：恢复 zhang3 数据库

# 执行备份脚本恢复数据库
[root@zhang3 ~]# mysql -uroot -p123456 < zhang3-20250917.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

# 验证：
[root@zhang3 ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xuexiao            |
| zhang3             |
| zyw                |
+--------------------+

（3）备份指定表（如 zhang3 数据库的 zhangsan 和 student 表）

# 备份 zhang3 数据库的 zhangsan 和 student 表
[root@zhang3 ~]# mysqldump -uroot -p123456 zhang3 zhangsan student > zhang3_tables-20250917.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zhang3 ~]# ls
 zhang3-20250917.sql         
 zhang3_tables-20250917.sql  


# 删除表
mysql> use zhang3;
mysql> drop table zhangsan,student;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+------------------+
| Tables_in_zhang3 |
+------------------+
| course           |
+------------------+
1 row in set (0.00 sec)

# 恢复表（需先进入 zhang3 数据库）
mysql> use zhang3;
Database changed

mysql> source zhang3_tables-20250917.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)
         
         ****省略****

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


mysql> show tables;
+------------------+
| Tables_in_zhang3 |
+------------------+
| course           |
| student          |
| zhangsan         |
+------------------+
3 rows in set (0.00 sec)

3. 差异备份与恢复（二进制日志）

差异备份需依赖 MySQL 的二进制日志（记录所有数据修改操作，如 INSERT/UPDATE/DELETE），核心流程为：“全量备份 + 二进制日志备份”，恢复时先恢复全量备份，再通过二进制日志恢复“全量备份后新增的数据”。

（1）前提：开启二进制日志

修改 MySQL 配置文件（如 /etc/my.cnf 或 /etc/mysql/my.cnf），添加以下配置：

[root@zhang3 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql-5.7.37-linux-glibc2.12-x86_64  # MySQL 安装目录（根据实际情况修改）
datadir = /opt/data  # 数据存储目录
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 1              # 必须设置，唯一标识服务器（主从复制也需此配置）
log-bin = mysql_bin        # 开启二进制日志，日志文件前缀为 mysql_bin

重启 MySQL 服务使配置生效：

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

# 验证二进制日志是否开启
[root@zhang3 ~]# ll /opt/data/ | grep mysql_bin
-rw-r-----  1 mysql mysql      154 9月  17 20:50 mysql_bin.000001  # 二进制日志文件
-rw-r-----  1 mysql mysql       19 9月  17 20:50 mysql_bin.index   # 日志索引文件

（2）差异备份流程

步骤1：执行全量备份（作为差异备份的基础）

[root@zhang3 ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20250917.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@zhang3 ~]# ls
all-20250917.sql
zhang3-20250917.sql
zhang3_tables-20250917.sql

步骤2：模拟数据修改（新增/更新数据）

mysql> use zhang3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed


# 新增数据
mysql> INSERT INTO zhangsan (id, name, age) VALUES (3, 'hina', 20), (4, 'maki', 19);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


# 更新数据
mysql> UPDATE zhangsan SET age = 21 WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


# 查看修改后的数据
mysql> select * from zhangsan;
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | hina |   21 |
|  4 | maki |   19 |
+----+------+------+
2 rows in set (0.00 sec)

步骤3：备份二进制日志（差异数据）

# 刷新日志，将当前日志内容写入文件（避免丢失）
[root@zhang3 ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

# 新生成的日志文件（mysql_bin.000002）包含全量备份后的所有操作
[root@zhang3 ~]# ll /opt/data/ | grep mysql_bin
-rw-r-----  1 mysql mysql     1055 9月  17 23:02 mysql_bin.000003
-rw-r-----  1 mysql mysql      154 9月  17 23:02 mysql_bin.000004
-rw-r-----  1 mysql mysql       38 9月  17 23:02 mysql_bin.index

（3）差异恢复流程

步骤1：模拟数据丢失（删除 zhangsan 数据库）

[root@zhang3 ~]# mysql -uroot -p123456 -e 'drop database zhang3;'
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xuexiao            |
+--------------------+
5 rows in set (0.00 sec)

步骤2：恢复全量备份

[root@zhang3 ~]# mysql -uroot -p123456 < all-20250917.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> SELECT * FROM cy.chenyu;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
+------+-------+------+

步骤3：通过二进制日志恢复差异数据

首先查看二进制日志，确定“需恢复的操作范围”（避免恢复“删除数据库”的操作）：

# 查看 mysql_bin.000001 日志中的事件（找到删除数据库的位置）
mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.22-log, Binlog ver: 4 |
-- 省略中间事件...
| mysql_bin.000001 | 794 | Query          |         1 |         898 | drop database cy                |  -- 删除数据库的位置（Pos=794）
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

通过 mysqlbinlog 工具恢复“Pos=4 到 Pos=794”的操作（即新增/更新数据，排除删除操作）：

# 恢复二进制日志中 Pos=4 到 Pos=794 的操作
[root@zhang3 ~]# mysqlbinlog --start-position=4 --stop-position=794 /opt/data/mysql_bin.000001 | mysql -uroot -predhat

# 验证差异数据已恢复
mysql> SELECT * FROM cy.chenyu;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | tom   |   10 |
|    2 | jerry |   30 |
|    3 | hehe  |   40 |
|    4 | xixi  |   50 |
+------+-------+------+
4 rows in set (0.00 sec)