MySQL数据备份与恢复实战-优快云博客

本文链接：https://blog.youkuaiyun.com/zhen1819/article/details/111229051

本文详细介绍了MySQL的两种备份工具——mysqldump和xtrabackup，包括各自的特性和适用场景。针对mysqldump，讲解了全量备份、恢复、备份特定库和表的操作，并给出了恢复误删库的案例。对于xtrabackup，阐述了备份过程、全备份恢复、增量备份及其恢复策略，以及恢复单表的方法。

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

一、两种备份工具的特点

1.1、逻辑备份mysqldump

mysqldump是MySQL自带的备份工具，不需要下载安装；
通过mysqldump备份出来的是以文本格式保存的SQL语句，可读性高，便于备份处理；
通过mysqldump备份的数据压缩比高，节省磁盘空间；
mysqldump依赖于数据库存储引擎，需要把数据从磁盘读出，然后转换成SQL进行转储。这个过程会耗费服务器资源，数据量很大时，备份效率低；
在备份TB级别的数据时，也可能会选择mysqldump，主要考虑数据压缩比高，能节省磁盘空间。

1.2、物理备份xtrabackup(XBK)

备份时会直接拷贝数据文件，性能较高；
备份文件可读性差；
数据压缩比低，占用磁盘空间。

二、mysqldump数据备份和恢复

2.1、备份

备份常用命令：

全量备份：

# mysqldump -h 127.0.0.1 -P 3306 -u root -p -A > backup/full_bak.sql

-A选项：–all-databases，备份所有库。

备份指定库：

# mysqldump -h 127.0.0.1 -B test1 test2 > backup/test.sql

-B选项：–databases，备份指定的库，可以同时指定多个库。

备份指定表：

# mysqldump -h 127.0.0.1 test1 sbtest1 > backup/sbtest1.sql

test1：指定库名；
sbtest1：指定表名，可以同时指定库中的多个表。

只备份表结构，而不备份数据：

# mysqldump -h 127.0.0.1 -d test1 > backup/test1.sql

-d选项：–no-data，只导出表结构。

备份存储过程、函数和触发器：

# mysqldump -h 127.0.0.1 -A -R --triggers > backup/full.sql

-R选项：备份存储过程和函数；
--triggers选项：备份触发器。

备份时记录binlog的状态信息：

# mysqldump -h 127.0.0.1 -A -R --triggers --master-data=2 --single-transaction > backup/full.sql

--master-data=0：默认值；
--master-data=1：以“CHANGE MASTER TO”命令的形式记录binlog的状态信息；
--master-data=2：以注释的形式记录binlog的状态信息，指备份时刻的文件名和position号；
--single-transaction：对支持事务的表，会对该表创建一致性快照，从快照备份数据而不必锁表。

2.2、恢复

在恢复时，通过source命令导入备份的SQL文件即可。
需要注意的是：

恢复数据时，最好停止业务，避免对表中数据的破坏；
因为备份的SQL中包含了“DROP”语句，最好在测试库中恢复；
在测试库确认无误后，再将数据导入线上库；
导入到线上时，最好再将线上的正常数据做备份，避免恢复失败影响正常数据；
在执行source命令前，可以将“sql_log_bin”参数置为0，不记录恢复时产生的二进制日志。

2.3、案例

数据库在每天凌晨通过mysqldump做全备份。某天下午，某员工误操作，DROP了test1库。
模拟整个过程：

备份命令：
# mysqldump -h 127.0.0.1 -A -R --triggers --master-data=2 --single-transaction | gzip > backup/full_$(date +%F).sql.gz
模拟DROP之前的数据写入：
mysql> insert into sbtest1(k,c,pad) values(9999,1111,11111111);
mysql> insert into sbtest1(k,c,pad) values(9999,1112,11111112);
mysql> insert into sbtest1(k,c,pad) values(9999,1112,11111113);
mysql> insert into sbtest1(k,c,pad) values(9999,1113,11111113);
模拟DROP行为：
mysql> drop database test1;

如何恢复数据：

可以通过全备份文件恢复全备份之前的数据；
通过binlog恢复全备份时到删库之前的数据。

恢复过程：
1）解压最近一次的全备份数据：

# gzip -d full_2020-12-15.sql.gz
# ls
full_2020-12-15.sql

2）通过全备份文件获取二进制日志起始位置。如下命令，得出全备份时的位置是“25081620”：

# head -30 full_2020-12-15.sql | grep -i "change master to"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=25081620;

3）通过mysqlbinlog命令获取二进制日志终止位置。如下命令，通过grep找出DROP语句，得出终止位置是“25082504”：

# mysqlbinlog --base64-output=decode-rows -d test1 /usr/local/mysql/data/mysql-bin.000003 | grep -B 5 "drop database test1"
COMMIT/*!*/;
# at 25082504
#201215 20:06:09 server id 1  end_log_pos 25082599 CRC32 0x3e772714 	Query	thread_id=16	exec_time=0	error_code=0
SET TIMESTAMP=1608033969/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
drop database test1

注：–base64-output=decode-rows选项将binlog文件中的数据进行解码。
4）截取二进制日志，并保存为SQL文件：

# mysqlbinlog --start-position=25081620 --stop-position=25082504 /usr/local/mysql/data/mysql-bin.000003 > backup/incre1.sql

5）通过source语句导入SQL文件：

mysql> set sql_log_bin=0;                    #关闭当前会话的二进制日志记录
mysql> source backup/full_2020-12-15.sql;    #导入全备份的数据
mysql> use test1;                            #切换到test1库
mysql> source backup/incre1.sql;             #导入截取的二进制日志
mysql> set sql_log_bin=1;                    #恢复当前会话的二进制日志记录
mysql> select * from test1.sbtest1 order by id desc limit 4;      #数据恢复成功
+-------+------+------+----------+
| id    | k    | c    | pad      |
+-------+------+------+----------+
| 10004 | 9999 | 1113 | 11111113 |
| 10003 | 9999 | 1112 | 11111113 |
| 10002 | 9999 | 1112 | 11111112 |
| 10001 | 9999 | 1111 | 11111111 |
+-------+------+------+----------+

三、xtrabackup数据备份和恢复

3.1、xtrabackup工具安装

# yum -y install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# yum -y install percona-xtrabackup-24.x86_64

3.2、全备份

3.2.1、备份过程

对非InnoDB存储引擎的表，先锁定表，再直接拷贝表的数据文件；
对InnoDB存储引擎的表，在备份开始的时刻（假如凌晨3:00开始，3:02结束），立即触发checkpoint动作，将内存数据页刷写到磁盘，拷贝此时所有的数据文件（假如此时日志序列号LSN=1000）。数据文件拷贝完成后，在拷贝数据的2分钟过程中MySQL产生的日志文件（to_lsn=1000，last_lsn=1100，redo和undo）也进行备份。

3.2.2、备份命令

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root -S /usr/local/mysql/mysql.sock --no-timestamp backup/full

--no-timestamp的作用：不使用此选项时，将会在备份目录下产生以备份时的时间戳命名的目录。因此使用该选项用以自定义备份目录

3.2.3、相关文件的作用

xtrabackup会将MySQL数据目录下的所有文件进行拷贝，同时会生成四个文件：

xtrabackup_binlog_info：保存备份后二进制日志的起始位置；
xtrabackup_checkpoints：保存checkpoint时的相关信息。
– backup_type：备份类型。full-backuped：全备份；incremental：增量备份
– from_lsn：备份开始时的日志序列号。全备一般为0；
– to_lsn：做checkpoint时的日志序列号；
– last_lsn：备份结束时的日志序列号。
xtrabackup_info：备份时的详细信息；
xtrabackup_logfile：备份过程中产生的redo日志。

3.3、全备份恢复

3.3.1、初始化数据库

全备份恢复时，需要先删除MySQL的data目录下的数据文件。

3.3.2、恢复前的准备工作

在进行恢复前，需要先做redo前滚和undo回滚，保证数据的一致性：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log backup/full/

3.3.3、恢复

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --copy-back backup/full/

修改data目录的属主为mysql用户，然后启动MySQL：

# chown -R mysql:mysql /usr/local/mysql/data/
# systemctl start mysqld

3.4、增量备份和恢复

3.4.1、备份

增量备份是基于全备份的，所以在增量备份之前，需要先做全备份：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root -S /usr/local/mysql/mysql.sock --no-timestamp backup/full

在全备份的基础上做增量备份：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root -S /usr/local/mysql/mysql.sock --no-timestamp --incremental --incremental-basedir=backup/full backup/inc1

--incremental选项的作用是：启用增量备份；
--incremental-basedir：指向上一次备份的路径。

3.4.2、恢复前合并增量备份到全备

全备准备：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --redo-only backup/full

第一次增量备份合并到全备：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --redo-only --incremental-dir=backup/inc1 backup/full

最后一次增量备份合并到全备：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --incremental-dir=backup/inc2 backup/full

–redo-only：增量备份恢复前回滚redo日志，除了最后一次增量备份合并到全备，其它步骤都需要使用该选项。

3.4.3、全量备份再次准备

这次的准备步骤不再使用“–redo-only”选项，此时所有的增量备份都合并到了全备份中：

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log backup/full

3.4.4、恢复

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --copy-back backup/full/

恢复完成，启动MySQL之前，别忘记将data目录的属主改为mysql用户。

3.5、恢复单表

有一种特殊情况是，只是某张表的数据损坏需要恢复。此时，为了恢复单张表而恢复整个数据库不太合理。
思路是，通过alter table弃用该表的表空间文件，将使用xtrabackup备份的表空间文件（.ibd结尾的文件）覆盖MySQL的data目录原有的ibd文件，然后再重新导入该表的表空间文件即可。命令如下：

mysql> alter table table1 discard tablespace;
mysql> alter table table1 import tablespace;

3.5、案例

线上数据库每周日凌晨做一次全备份，周一至周六每天做一次增量备份。周二的下午，某员工误操作DROP了test1库。
模拟整个过程：

周日的全备份：
# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root -S /usr/local/mysql/mysql.sock --no-timestamp backup/full
周一的增量备份及备份之前的数据写入：
mysql> insert into sbtest1(k,c,pad) values(9999,111111,111111);
# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root -S /usr/local/mysql/mysql.sock --no-timestamp --incremental --incremental-basedir=backup/full backup/inc1
周二的增量备份及之前的数据写入：
mysql> insert into sbtest1(k,c,pad) values(9999,111112,111112);
# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root -S /usr/local/mysql/mysql.sock --no-timestamp --incremental --incremental-basedir=backup/inc1 backup/inc2
周二下午的删库行为及之前的数据写入：
mysql> insert into sbtest1(k,c,pad) values(9999,111113,111113);
mysql> drop database test1;

如何恢复数据：

可以通过备份文件恢复备份之前的数据；
通过binlog恢复备份时到删库之前的数据。

恢复过程：
1）停止MySQL，删除data目录下的数据文件。如果binlog文件也在data目录下，要把binlog文件备份到其它目录。
2）合并增量备份到全备份

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --redo-only backup/full
# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --redo-only --incremental-dir=backup/inc1 backup/full
# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --incremental-dir=backup/inc2 backup/full

3）全量备份再次准备

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log backup/full

4）恢复数据，并启动MySQL

# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --copy-back backup/full/
# chown -R mysql:mysql /usr/local/mysql/data
# systemctl start mysqld

进行到这里，已经恢复了周二增量备份之前的数据。周二备份之后到DROP之前的数据，需要根据二进制日志来恢复。
5）确定二进制日志起始位置

# cat backup/inc2/xtrabackup_binlog_info 
mysql-bin.000003	57265157

从最后一次增量备份生成的“xtrabackup_binlog_info”文件得知，备份时的二进制日志的位置是“57265157”
6）确定二进制日志终止位置

# mysqlbinlog --base64-output=decode-rows -vvvv /tmp/mysql-bin.000003 | grep -B 5 'drop database test1'
COMMIT/*!*/;
# at 57265378
#201215 23:22:49 server id 1  end_log_pos 57265473 CRC32 0x11ff8bae 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1608045769/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
drop database test1

通过binlog文件确定了终止位置是“57265378”。
7）截取二进制日志

# mysqlbinlog --start-position=57265157 --stop-position=57265378 /tmp/mysql-bin.000003 > backup/binlog.sql

8）导入截取的二进制日志

mysql> set sql_log_bin=0;
mysql> use test1;
mysql> source backup/binlog.sql;
mysql> set sql_log_bin=1;
mysql> select * from sbtest1 order by id desc limit 3;
+--------+------+--------+--------+
| id     | k    | c      | pad    |
+--------+------+--------+--------+
| 100003 | 9999 | 111113 | 111113 |
| 100002 | 9999 | 111112 | 111112 |
| 100001 | 9999 | 111111 | 111111 |
+--------+------+--------+--------+