【MySQL管理】：MySQL备份与恢复

zyplanke

已于 2025-01-02 15:53:17 修改

阅读量1.7k

点赞数

分类专栏： MySQL 文章标签： mysql

于 2020-05-21 14:19:28 首次发布

本文链接：https://blog.youkuaiyun.com/zyplanke/article/details/106255065

版权

MySQL 专栏收录该内容

26 篇文章

订阅专栏

本文详细介绍了MySQL的多种备份工具和策略，包括mysqldump、mydumper、mysqlpump、cp/tar物理拷贝、mysqlbackup和xtrabackup。涉及逻辑备份与物理备份的区别，以及冷备份、热备份、温备份的概念。同时，文章阐述了如何进行全量+增量恢复，表DDL与数据的分离备份，并强调了备份过程中需要注意的事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL备份恢复常用工具包括：mysqldump、mysqlpump、mysqlbackup、cp/tar、xtrabackup、mysqldumper等工具。后两个工具为第三方公司研发的工具的。本文为这些工具使用讲解。

通常分为物理备份和逻辑：

物理备份数据库底层二进制文件的拷贝。有点事非常快，缺点是数据库需要停机。
物理备份工具有cp、tar、mysqlbackup、xtrabackup、还有底层虚拟机的快照。逻辑备份是从数据库逻辑结构（如SQL）进行备份，相对于物理备份它比较慢，优点是数据库不用停止。逻辑备份工具为mysqldump、mysqlpump、mysqldumper等。

从备份对业务系统的影响分为：冷备份、热备份、温备份。冷备份需要MySQL停机，例如冷备份。 热备份指数据库不需要停机且在备份期间继续对外提供服务。温备份指数据库不需要停机，但备份期间不能对外服务，数据库只读不能写。

mysqldump备份

A.备份恢复

mysqldump是逻辑备份工具。对于InnoDB存储引擎它是热备份（基于MVCC机制实现的），对于MyISAM它是温备份。

备份命令如下（备份前必须先开启MySQL的binlog）：

#确保工具版本与服务端版本一致，以避免导出的内容有乱码
# 备份指定的表（可以指定多张表）
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --routines --opt dbtest tb > tb.sql

# 备份指定的database（schema）
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --routines --opt --databases dbtest > dbtest.sql  

# 备份所有的databases（schema）
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --routines --opt --all-databases > all.sql

注意1：--master-data（新版改为--source-data）的用途是设置在导出脚本中生成“ CHANGE MASTER ”命令的形式。

注意2：导出的SQL文件中会自动加一个设置：SET @@SESSION.SQL_LOG_BIN= 0; 这样未来在利用该SQL文件恢复时，恢复动作本身不会记录binlog。

备份的结果为SQL文件，里面包括：数据库对象的DDL语句和Insert的数据语句。利用该SQL文件恢复数据库的命令为：

mysql -u root -p < dbtest.sql

mysql -u root -p < all.sql

B.全量+增量恢复

如果：通过以上mysqldump备份后，数据又发生变化，然后数据库故障。

先记下最新的binlog文件及binlog文件中最后的时间戳（或position），然后再先按以上命令，利用mysqldump备份的SQL文件恢复到数据变化前。最后再通过以下命令，利用binlog恢复后面的数据变化：

mysqlbinlog --start-datetime="2020-05-21 00:00:00" --stop-datetime="2020-05-21 14:50:50" binlog.000032  | mysql -uroot -p

C.表DDL与数据分离

备份时，还可以使用-T选项，对指定的表导出为两个文件：DDL的sql文件和数据txt文件，且数据txt文件不是Insert语句，而是文本行内容。（需要提前配置secure_file_priv）

mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --routines --opt -T /tmp --fields-terminated-by ','  dbtest tb

注：以上命令通常在MySQL服务器上执行，而不是远程执行。既同一台机器上执行， -T指定的目录需要和secure_file_priv一致。

以上导出的txt数据文件，使用mysql的 load data导入或者使用mysqlimport工具导入（mysqlimport与 load data本质上是一样的，只是一个mysql内部执行，一个在mysql外部执行）。在服务器上，登录mysql客户端，然后在客户端中执行：

mysql> LOAD DATA INFILE '/tmp/tb.txt' REPLACE INTO TABLE dbtest.tb FIELDS TERMINATED BY ',';
Query OK, 7 rows affected (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

mydumper备份

mydumper是第三方的工具逻辑备份工具（需要另行下载），是一款多线程备份工具。通过增加线程数，提高了备份速度。与mysqldump不同，mysqldumper为每一张表创建一个或多个文件。

mydumper的使用方式与mydump类似。这里不展开讲解。

mysqlpump备份

mysqlpump是MySQL官方出品的，并行dump工具，也是逻辑备份工具。选项与mysqldump类似（没有--flush-logs ，--master-data，--opt选项）

# 备份指定的表（可以指定多张表）
mysqlpump -uroot -p --single-transaction --default-character-set=utf8mb4 --default-parallelism=2 --routines dbtest tb > tb.sql

# 备份指定的database（schema）
mysqlpump  -uroot -p --single-transaction --default-character-set=utf8mb4 --default-parallelism=2  --routines --databases dbtest > dbtest.sql  

# 备份所有的databases（schema）
mysqlpump -uroot -p --single-transaction --default-character-set=utf8mb4 --default-parallelism=2 --routines --all-databases > all.sql

导出的SQL文件，同mysqldump。

注意：mysqlpump默认不会导出INFORMATION_SCHEMA， performance_schema, ndbinfo, or sys数据库库（schema）

cp/tar等物理拷贝

该方法是物理备份。需要先stop MySQL服务，然后在拷贝。拷贝的内容为整个mysql data数据库（最好拷贝全一点，不怕多拷贝，就怕少拷贝）通常还需要一并拷贝my.cnf配置文件等。

由于操作比较简单，不再演示。

mysqlbackup物理备份

mysqlbackup是Oracle官方推出Enterprise企业收费工具，物理备份工具，在MySQL社区发行版软件中不自带，需要从Oracle网站上单独下载（需要oracle账户）。 mysqlbackup的版本必须严格与MySQL服务版本完全一致。

mysqlbackup备份对InnoDB是热备份工具，其他存储引擎是温备份（会锁表），支持全量备份和增量备份。

A.全库备份

mysqlbackup典型的分为三步：backup-to-image全库备份； validate验证；

1、如果是社区版，需要先从Oracle官方下载，下载linux通用版本即可，下载后解压（必要时，可以加入到环境变量PATH中）。企业版自带，不用专门下载。

2、在MySQL数据库中创建用户和权限。官方提供的脚本如下：

CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'mysqlbackup'@'localhost'; --For mysqlbackup 8.0.19 and later
GRANT BACKUP_ADMIN ON *.* TO 'mysqlbackup'@'localhost';  -- For mysqlbackup 8.0.16 and later
GRANT SELECT ON performance_schema.variables_info TO 'mysqlbackup'@'localhost'; -- For mysqlbackup 8.0.16 to 8.0.18
GRANT SELECT ON performance_schema.log_status TO 'mysqlbackup'@'localhost'; -- For mysqlbackup  8.0.16 to 8.0.18
GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
GRANT PROCESS ON *.* TO 'mysqlbackup'@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO 'mysqlbackup'@'localhost'; -- For mysqlbackup 8.0.12 to 8.0.18

3、创建mysqlbackup需要的几个目录：

# mkdir -p /tmp/mysqlbackup/images  #存放image
# mkdir -p /tmp/mysqlbackup/backup_temp    #mysqlbackup备份时需要的临时目录
# mkdir -p /tmp/mysqlbackup/restore_temp    #mysqlbackup恢复时需要的临时目录

4、在MySQL启动的状态下，备份backup-to-image。

注意：--defaults-file表示仅从此文件获得默认选项（不从其他文件读了）。另一个选项，--defaults-extra-file在其他文件读之后，再从这个文件读取选项（以后读的为最终生效）。这两个参数在mysql其他命令中也是此含义。

# mysqlbackup  --defaults-file=/etc/my.cnf \
--host 127.0.0.1 --user mysqlbackup --password \
--backup-image=/tmp/mysqlbackup/images/fullbackup`date +%Y%m%d_%H%M%S`.mbi \
--backup-dir=/tmp/mysqlbackup/backup_temp/`date +%Y%m%d_%H%M%S`  \
backup-to-image

MySQL Enterprise Backup  Ver 8.0.20-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
 mysqlbackup --defaults-file=/etc/my.cnf --host 127.0.0.1 --user mysqlbackup 
        --password 
        --backup-image=/tmp/mysqlbackup/images/fullbackup20200521_193456.mbi 
        --backup-dir=/tmp/mysqlbackup/backup_temp/20200521_193456 
        backup-to-image 

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-to-image' run mysqlbackup
           prints "mysqlbackup completed OK!".

200521 19:34:56 MAIN    INFO: Starting to log actions.
Enter password: 
200521 19:35:00 MAIN    INFO: No SSL options specified.
200521 19:35:00 MAIN    INFO: MySQL server version is '8.0.20-commercial'
200521 19:35:00 MAIN    INFO: MySQL server compile os version is 'Linux'
200521 19:35:00 MAIN    INFO: SSL/TLS version used for connection is TLSv1.3
200521 19:35:00 MAIN    INFO: Got some server configuration information from running server.

200521 19:35:00 MAIN    INFO: Establishing connection to server for locking.
200521 19:35:00 MAIN    INFO: No SSL options specified.
200521 19:35:00 MAIN    INFO: Backup directory created: '/tmp/mysqlbackup/backup_temp/20200521_193456'
200521 19:35:00 MAIN    INFO: MySQL server version_comment is 'MySQL Enterprise Server - Commercial'
200521 19:35:00 MAIN    INFO: Mysqlbackup component not installed.
200521 19:35:00 MAIN    INFO: Server is not a community server.
200521 19:35:00 MAIN    INFO: TDE Keyring service initialized.

(中间省略)

200521 19:35:01 MAIN    INFO: Full Image Backup operation completed successfully.
200521 19:35:01 MAIN    INFO: Backup image created successfully.
200521 19:35:01 MAIN    INFO: Image Path = /tmp/mysqlbackup/images/fullbackup20200521_193456.mbi
200521 19:35:01 MAIN    INFO: MySQL binlog position: filename binlog.000007, position 156

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 17962496
   End LSN                    : 17963059
-------------------------------------------------------------

mysqlbackup completed OK!

最后一定要出现“mysqlbackup completed OK!” 才代表成功。

5、验证validate

# mysqlbackup --backup-image=/tmp/mysqlbackup/images/fullbackup20200521_193456.mbi validate
MySQL Enterprise Backup  Ver 8.0.20-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
 mysqlbackup 
        --backup-image=/tmp/mysqlbackup/images/fullbackup20200521_193456.mbi 
        validate 

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'validate' run mysqlbackup
           prints "mysqlbackup completed OK!".

200521 19:36:04 MAIN    INFO: Starting to log actions.
200521 19:36:04 MAIN    INFO: Backup Image MEB version string: 8.0.20 [2020-03-26  19:06:19]
200521 19:36:04 MAIN    INFO: MySQL server version is '8.0.20'
200521 19:36:04 MAIN    INFO: TDE Keyring service initialized.
200521 19:36:04 MAIN    INFO: Creating 14 buffers each of size 16777216.
200521 19:36:04 MAIN    INFO: Validate operation starts with following threads
                1 read-threads    6 process-threads
200521 19:36:04 MAIN    INFO: Validating image ... /tmp/mysqlbackup/images/fullbackup20200521_193456.mbi
200521 19:36:04 PCR5    INFO: Validate: [Dir]: meta
200521 19:36:04 PCR5    INFO: Validate: [Dir]: datadir/dbtest
200521 19:36:04 PCR5    INFO: Validate: [Dir]: datadir/mysql
200521 19:36:04 PCR5    INFO: Validate: [Dir]: datadir/performance_schema
200521 19:36:04 PCR5    INFO: Validate: [Dir]: datadir/sys
200521 19:36:04 MAIN    INFO: datadir/mysql.ibd validated.
200521 19:36:04 MAIN    INFO: datadir/mysql/backup_history.ibd validated.
200521 19:36:04 MAIN    INFO: datadir/undo_002 validated.
200521 19:36:04 MAIN    INFO: datadir/ibdata1 validated.
200521 19:36:04 MAIN    INFO: datadir/undo_001 validated.
200521 19:36:04 MAIN    INFO: datadir/dbtest/tba.ibd validated.
200521 19:36:04 MAIN    INFO: datadir/sys/sys_config.ibd validated.
200521 19:36:04 MAIN    INFO: datadir/mysql/backup_progress.ibd validated.
200521 19:36:04 MAIN    INFO: Validate operation completed successfully.
200521 19:36:04 MAIN    INFO: Backup Image validation successful.
200521 19:36:04 MAIN    INFO: Source Image Path = /tmp/mysqlbackup/images/fullbackup20200521_193456.mbi

mysqlbackup completed OK!

6、全库恢复copy-back-and-apply-log

先模拟数据库故障：把MySQL停止，然后删除MySQL整个的data目录下的内容，最后暴露data目录（为空目录）。

在MySQL数据库关闭情况下，执行以下命令恢复:

# mysqlbackup --defaults-file=/etc/my.cnf \
--datadir=/usr/local/mysql/data \
--backup-image=/tmp/mysqlbackup/images/fullbackup20200521_193456.mbi \
--backup-dir=/tmp/mysqlbackup/restore_temp/`date +%Y%m%d_%H%M%S`  \
copy-back-and-apply-log

MySQL Enterprise Backup  Ver 8.0.20-commercial for Linux on x86_64 (MySQL Enterprise - Commercial)
Copyright (c) 2003, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starting with following command line ...
 mysqlbackup --defaults-file=/etc/my.cnf --datadir=/usr/local/mysql/data 
        --backup-image=/tmp/mysqlbackup/images/fullbackup20200521_193456.mbi 
        --backup-dir=/tmp/mysqlbackup/restore_temp/20200521_193724 
        copy-back-and-apply-log 

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'copy-back-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

200521 19:37:24 MAIN    INFO: Starting to log actions.
200521 19:37:24 MAIN    INFO: Backup directory created: '/tmp/mysqlbackup/restore_temp/20200521_193724'
200521 19:37:24 MAIN    INFO: Backup Image MEB version string: 8.0.20 [2020-03-26  19:06:19]
200521 19:37:24 MAIN    INFO: MySQL server version is '8.0.20'
200521 19:37:24 MAIN WARNING: If you restore to a server of a different version, the innodb_data_file_path parameter might have a different default. In that case you need to add 'innodb_data_file_path=ibdata1:12M:autoextend' to the target server configuration.
200521 19:37:24 MAIN WARNING: If you restore to a server of a different version, the innodb_log_files_in_group parameter might have a different default. In that case you need to add 'innodb_log_files_in_group=2' to the target server configuration.
200521 19:37:24 MAIN WARNING: If you restore to a server of a different version, the innodb_log_file_size parameter might have a different default. In that case you need to add 'innodb_log_file_size=50331648' to the target server configuration.
200521 19:37:24 MAIN    INFO: Server is not a community server.
200521 19:37:24 MAIN    INFO: TDE Keyring service initialized.

（中间省略）

200521 19:37:25 PCR1    INFO: Last MySQL binlog file position 0 156, file name binlog.000007
200521 19:37:25 PCR1    INFO: The first data file is '/usr/local/mysql/data/ibdata1'
                              and the new created log files are at '/usr/local/mysql/data'
200521 19:37:25 MAIN    INFO: Apply-log operation completed successfully.
200521 19:37:25 MAIN    INFO: Full Backup has been restored successfully.

mysqlbackup completed OK! with 3 warnings

7、修改恢复后的目录属主：

# chown -R mysql:mysql /usr/local/mysql/data

8、启动MySQL，检查恢复的结果。

在数据库中表mysql.backup_history记录了所有备份的动作情况。

B. 增量备份

备份：

1、先全量备份（步骤见上）。

2、在已经全量备份的基础上，进行增量备份：

# mysqlbackup  --defaults-file=/etc/my.cnf  \
--host 127.0.0.1 --user mysqlbackup --password \
--incremental --incremental-base=history:last_backup \
--backup-image=/tmp/mysqlbackup/images/incremental`date +%Y%m%d_%H%M%S`.mbi \
--backup-dir=/tmp/mysqlbackup/backup_temp/`date +%Y%m%d_%H%M%S`  \
backup-to-image

其中增量的基准： history:last_backup为上一次备份（包括全量备份和增量备份）；history:last_full_backup为上一次全量备份。

增量备份的恢复：

1、在MySQL数据库停止并且data目录为空的情况下，先使用全量版本进行恢复（步骤见上）。

2、再使用一下命令，进行增量备份（可以看到增量备份的image文件大小，明显小于全量备份），全量备份可以多次：

# mysqlbackup --defaults-file=/etc/my.cnf \
--incremental \
--datadir=/usr/local/mysql/data \
--backup-image=/tmp/mysqlbackup/images/incremental20200521_184022.mbi \
--backup-dir=/tmp/mysqlbackup/restore_temp/`date +%Y%m%d_%H%M%S`  \
copy-back-and-apply-log

注：如果增量备份时是基于history:last_backup，则增量恢复必须按备份的顺序逐个恢复，不能跳跃。

3、然后修改data目录及子文件的属主：

# chown -R mysql:mysql /usr/local/mysql/data

4、启动MySQL。

xtrabackup物理备份

xtrabackup是percona公司退出的免费的物理备份工具，得到了广泛使用。xtrabackup备份是热备份工具，支持全量备份和增量备份。

......