mysql 数据备份与恢复使用详解(超完整详细教程)

一、前言

对一个运行中的线上系统来说，定期对数据库进行备份是非常重要的，备份不仅可以确保数据的局部完整性，一定程度上也为数据安全性提供了保障，设想如果某种极端的场景下，比如磁盘损坏导致某个时间段数据丢失，或者误操作导致数据表数据被删等...

这种情况在现实中可以说无处不在，为了避免数据丢失或损坏带来的巨大损失，有必要对线上系统的数据定期做备份，而备份的直接好处就是，一旦数据需要做恢复的时候就可以利用这些备份数据快速恢复，从而最大程度减少损失。

二、数据备份策略

根据实际的业务需求，经验参考可以据数据规模大小，服务器磁盘容量，大致可分为下面几种：

1、全备

备份完整的数据库，全量数据就是数据库中所有的数据（或某一个库的全部数据）；

• 全量备份就是把数据库中所有的数据进行备份；
• mysqldump会取得一个时刻的一致性数据

2、增备

​ 增量数据就是指上一次全量备份数据之后到下一次全备之前数据库所更新的数据，对于mysqldump,binlog就是增量数据；

3、差异备份

• 备份自上一次完全备份后的全部改动和新文件；
• 备份速度较快，恢复速度较快，对磁盘空间有要求；
• 能够更快且简单的恢复(相比较增量)；
• 需要最近一次完全备份和最后一次差异备份就能快速恢复；

三、数据备份类型

根据数据备份时对生产系统的影响，可以做如下分类：

1、冷备

停库,停服务,备份

这些备份操作在用户不能访问数据的时候进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的行为。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

2、热备

不停库,不停服务,备份,也不会(锁表)阻止用户的写入

这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

3、温备

不停库，不停服务，备份，会(锁表) 阻止用户的写入

这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

四、前置准备

提前搭建好mysql服务（本文以mysql5.7为例进行说明），并创建一个测试使用的数据库，若干数据表；

五、mysqldump 数据备份命令使用

mysqldump是mysql自带的数据备份命令，使用该命令可以完成数据库，数据表等多种备份策略，下面针对该命令的使用做详细的说明；

1、命令格式

mysqldump [选项] 数据库名 [表名] > 脚本名
或
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
或
mysqldump [选项] --all-databases [选项] > 脚本名

关于选项部分，包含的参数是比较多的，下面列举常用的一些参数选项

参数名							缩写	含义
--host							-h		服务器IP地址
--port							-P		服务器端口号
--user							-u		MySQL 用户名
--pasword						-p		MySQL 密码
--databases								指定要备份的数据库
--all-databases							备份mysql服务器上的所有数据库
--compact								压缩模式，产生更少的输出
--comments								添加注释信息
--complete-insert						输出完成的插入语句
--lock-tables							备份前，锁定所有数据库表
--no-create-db/--no-create-info			禁止生成创建数据库语句
--force									当出现错误时仍然继续备份操作
--default-character-set					指定默认字符集
--add-locks								备份数据库表时锁定数据库表
 
--no-create-db，  ---取消创建数据库sql(默认存在)
--no-create-info，---取消创建表sql(默认存在)
--no-data         ---不导出数据(默认导出)
--add-drop-database ---增加删除数据库sql（默认不存在）
--skip-add-drop-table  ---取消每个数据表创建之前添加drop数据表语句(默认每个表之前存在drop语句)
--skip-add-locks       ---取消在每个表导出之前增加LOCK TABLES（默认存在锁）
--skip-comments        ---注释信息(默认存在)

2、案例演示

1）备份全库

mysqldump -uroot -pXXX --all-databases > /usr/local/mysql/full.sql
或者
mysqldump -uroot -pXXX -A > /usr/local/mysql/full.sql

2）备份数据库【一个或多个】

使用 -- databases 或 - B 参数，该参数后面跟数据库名，多个库间中间用空格，如果指定 databases 参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在；

mysqldump –u user –h host –p -- databases [ 数据库的名称 1 [ 数据库的名称 2...]] > 备份文件名称 .sql

mysqldump -uroot -pXXX --databases shake_date > /usr/local/mysql/shake_date_01.sql
或者
mysqldump -uroot -pXXX -B shake_date shake_flow > /usr/local/mysql/combine.sql

3）备份数据表

如果业务中不需要对全库做备份，只想备份部分表的时候，

mysqldump –u user –h host –p密码   数据库名  [ 表名 1 [ 表名 2...]] > 备份文件名称 .sql

备份shake_date 下面的t_user表

4）只备份表结构

mysqldump -u用户名 -pXXX --no-data 数据库 数据表名称 > 备份sql文件名.sql

备份shake_date下面的t_user表，只备份表结构

mysqldump -uroot -pXXX --no-data shake_date t_user > /usr/local/mysql/t_user_bk2.sql

可以打开备份的文件检查下，可以发现这里就只剩下表结构；

5）只备份表的部分数据

有时候一张表数据量很大，只需要部分数据，这时就可以使用 --where 选项了，where后面附
带需要满足的条件；

如下，备份t_user表中age大于12岁的用户

mysqldump -uroot -pXXX shake_date t_user --where="age > 12 " > t_user_bk3.sql

6）排除某些表的备份

如果备份某个库，但某些表数据量很大或与业务关联不大，这时候可以考虑排除这些表的备份，适用选项 --ignore-table ；

如下备份shake库下的表，排除t_no这个表

mysqldump -uroot -pXXX shake_date --ignore-table=shake_date.t_no > /usr/local/mysql/t_user_bk5.sql

3、其他重要参数选项补充

在实际生产中，备份数据需要考虑的因素其实更多，考虑的越细致，在后续在数据恢复的时候就越精确，下面列举一些实际生产备份中可能用得上的额外的参数选项；

-R 备份存储过程及函数
--triggers 备份触发器
-E 备份事件
-F 在备份开始时,刷新一个新binlog日志
--master-data=2  以注释的形式,保存备份开始时间点的binlog的状态信息
--single-transaction   innodb 存储引擎开启热备(快照备份)功能
--set-gtid-purged=auto

更多的参数，可以通过命令： mysqldump --help 进行查看和学习，每一项都有详细的介绍，可以结合官网一起学习；

在以上罗列的参数中，有下面三个参数选项这里做一下补充说明，也是备份过程中常常会涉及到的

• --master-data；

• --single-transaction；

• --set-gtid-purged；

1）--master-data

可选值 ： 1 ，2

通过mysqldump --help 命令可以清楚查看官方对该命令的解释，这里简单说下这个参数的作用

• 加上该选项之后，会在备份的sql中添加并记录备份时间点binlog中的偏移量，利用这个偏移的位置，结合完整的binlog日志，可以全量恢复从这个时间点之前的数据 + 这个时间点之后的数据；
• 在mysql的主从模式中可以用到；
• 该值设置为1或2，在不同的参数值设置时，备份中带来的结果不一样，通常结合--single-transaction；一起使用，可以在备份期间进行锁表，防止期间外部数据的读写造成备份数据的不一致；

以--master-data=2为例进行说明

以注释的形式,保存备份开始时间点的binlog的状态信息

• 在备份时，会自动记录，二进制日志文件名和位置号；
• 自动锁表（FTWRL）；
• 如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现快照备份；

2）--single-transaction

innodb 存储引擎开启热备(快照备份)功能

直观上理解，使用该参数，可以在数据备份期间开启类似于事务的操作，这样的话可以避免外部的DDL操作带来备份时的数据上的影响，比如有如下场景，在mysql5.6版本中可能出现的情况： 

• 100+G 有MyISAM表，做大批量DML，mysqldump备份数据库出现hang住；
• 3000w表做DDL，改数据类型，备份期间，锁表情况严重；

如果配合上面的master-data可以自动实现加锁

• 不加--single-transaction ，启动所有表的温备份，所有表都锁定；
• 加上--single-transaction , 对innodb进行快照备份, 对非innodb表可以实现自动锁表功能；

3）--set-gtid-purged

此为默认参数，即使在命令中不写，依旧生效，它的效果是在mysqldump输出的备份文件中生成  SET@@GLOBAL.GTID_PURGED语句；备份文件中的这条语句记录了GTID号， 可选值：auto , on

使用场景

• 在构建主从模式时，主库上有许多数据需要先备份出来并恢复到从库上，以此来保持两个库没有差异，然后再去配置主从，在这种场景下，需要将参数设置为 on，对于想要基于GTID实现主从复制的从库来说，从库是基于MASTER_AUTO_POSITION=1自动获取并应用GTID的，因此如果再主库导出的备份文件中没有GTID，那么从库无法自动获取并应用GTID；
• 设为off时，在mysqldump输出中不包含SET@@GLOBAL.GTID_PURGED语句;

4）--max-allowed-packet=#

适当调大该参数，可以避免在备份数据量过大时因数据落盘时数据包过大的备份失败问题

5）-R -E --triggers

备份数据时连同触发器函数等也一同进行备份

操作演示

--set-gtid-purged=OFF

mysqldump -uroot -pXXX -A --master-data=2 --single-transaction --set-gtid-purged=OFF >/usr/local/mysql/shake_user_empty.sql

--set-gtid-purged=on

 设置该参数之后，最明显的效果就是打开备份脚本文件，可以看到下面的信息，开启这个参数后在构建主从复制的时候会用上

--max-allowed-packet=#

在一些数据量比较大的备份情况下，建议适当的调大该参数

mysqldump -uroot -pXXX -B shake_date -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/usr/local/mysql/shake_full_10.sql

六、mysqldump 数据恢复

使用mysqldump可以对备份的数据进行恢复，基本语法

mysql –u root –p [dbname] < backup .sql

1、全量恢复

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称

删除shake_date数据库

执行数据恢复，再次查看发现数据就回来了；

mysql -uroot -pXXX < /usr/local/mysql/full.sql

2、全量备份中恢复单库

有整个实例的备份，但只想恢复某个数据库，这时可以从全量备份中分理出单个库的备份，可以参考下面的命令，比如将shake_date数据库从full.sql中分离出来；

sed -n '/^-- Current Database: `shake_date`/,/^-- Current Database: `/p' full.sql> /usr/local/mysql/shake_full.sql

3、从某个数据库中恢复单表数据

这也是一种比较常见的需求了，比如业务中某些表的数据非常重要，优先恢复这些表，就可以用到这个功能，操作步骤如下：

• 用shell语法分离出创建表的语句及插入数据的语句；
• 再依次导出完成数据恢复；

cat shake_full.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `t_user`/!d;q' > t_user_structure.sql

cat shake_full.sql | grep --ignore-case 'insert into `t_user`' > t_user_data.sql

首先，将shake_date库中的t_user表删除

依次执行上面的sql，即建表sql以及insert的数据sql从全库的sql备份中分离出来

可以打开一个看看

然后再执行数据的恢复

source /usr/local/mysql/t_user_structure.sql;
source /usr/local/mysql/t_user_data.sql;

4、使用dump + binlog进行数据恢复

下面说一个具体的场景，在生产环境下，假如定期对某个数据库进行全备，比如每周2的晚上进行全备，但现在发生了一件事，即在周三的时候，某位同事不小心将某张表的数据删除了，这该怎么办呢？

可以这么考虑，从周二全备那天来看，备份的数据是截止到那个时间点的全量数据，备份的时候可以将 master-data参数加进去，这样备份出来的数据中就有了那一刻binlog中的数据位置点，而在binlog日志中，记录了完整的操作记录行，那么就可以结合起来对数据进行快速恢复；

操作步骤如下：

1）创建一个数据库和数据表

给t1表随机插入几条数据

2）模拟周2对当前数据库进行全备

mysqldump -uroot -pXXX --databases mydb --master-data=2 --single-transaction --set-gtid-purged=ON >/usr/local/mysql/mydb.sql

注意：

--master-data=2 这个参数一定要开启，这个参数会记录备份那一刻的位置

 3）模拟周3又对t1表做了相关的数据操作

 4）模拟周3不小心将数据库删了

 5）执行数据恢复

恢复步骤

• 恢复截止到周1的备份数据；
• 寻找binlog的起始位置；
• 根据binlog恢复周1到周2之间的数据；

将备份的sql中的下面的信息定位到，CHANGE 开头的表示在binlog中记录的位置号为4001，这就是说，剩下的待恢复的数据从4001开始截取就可以了；

SET @@GLOBAL.GTID_PURGED='c38a5f35-67ac-11ed-aefa-525400633661:1-16';
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=4001;

定位到mysql的binlog位置，结合上述的gtid，通过binlog进行恢复，使用下面的命令检查最后删除的位置点；

show binlog events in 'mysql-bin.000002';

在上面我们定位到备份中的sql文件中的gtid是1~16，这里最后drop的时候是20这个位置，则需要截取的那部分就是17 ~ 20；

mysqlbinlog --skip-gtids --include-gtids='c38a5f35-67ac-11ed-aefa-525400633661:17-20' mysql-bin.000002 > /usr/local/mysql/mydb_extend.sql

执行恢复

检查全备恢复后的数据，这样就恢复到了周一的数据；

 接下来再恢复周2的数据；

source /usr/local/mysql/mydb_extend.sql;

七、物理备份

所谓物理备份，最直接的就是将MySQL中的数据库文件复制出来，这种方式简单粗暴，速度也快，比较省事，

比如在上面创建了一个mydb的数据库，在mysql的数据目录下就存在下面几个数据文件； 

但是这种方式并不是特别推荐，一方面在不同的操作系统下，数据目录有所不同，其次，这种备份的操作往往需要停服和锁表，以避免外部的操作给备份数据时带来的不一致的影响，另外，与mysql所使用的数据库引擎也很有关系；

八、表的导出与导入

在一些场景下，直接将数据库数据被分成sql文件可能并不是一个特别好的选择，因为导出来的文件格式不够通用，比如还有其他的数据分析平台需要使用这些数据的话，就比较麻烦了，这时候就可以考虑将数据库以文件的形式导出；

表的导出操作

1、 使用SELECT…INTO OUTFILE导出文本文件

将shake_date库下的t_user表数据导出到文件

SELECT * FROM t_user INTO OUTFILE "/var/lib/mysql-files/tuser.txt";

打开该文件检查下

2. 使用mysqldump命令导出文本文件

 使用mysqldump命令将将shake_date数据库中tuser表中的记录导出到文本文件：

 mysqldump -uroot -pXXX -T "/var/lib/mysql-files/" shake_date t_user

检查下文件

使用mysqldump将shake_date数据库中的t_user表导出到文本文件，使用FIELDS选项，要求字段之间使用逗号“，”间隔，所有字符类型字段值用双引号括起来；

mysqldump -uroot -pXXX -T "/var/lib/mysql-files/" shake_date t_user --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

可以检查下txt文件 ，这种格式的数据在很多大数据处理业务场景中是一种比较好的数据格式

3、 使用mysql命令导出文本文件

mysql -uroot -pXXX --execute="SELECT * FROM t_user;" shake_date> "/var/lib/mysql-files/tuser1.txt"

导出的结果格式如下

使用  --veritcal  参数将该条件记录分为多行显示

mysql -uroot -pXXX --vertical --execute="SELECT * FROM t_user;" shake_date > "/var/lib/mysql-files/tuser2.txt"

得到的数据格式如下

使用 --xml参数将数据导出为xml格式

mysql -uroot -pXXX --xml --execute="SELECT * FROM t_user;" shake_date>"/var/lib/mysql-files/tuser3.xml"

表的导入操作

上面演示了将表以各自类型格式的文件导入，下面再看看如何将不同格式的表的数据进行导入操作；

1. 使用LOAD DATA INFILE方式导入文本文件

使用 SELECT...INTO OUTFILE 将 shake_date 数据库中 t_user 表的记录导出到文本文件

执行下面的语句导出

SELECT * FROM shake_date.t_user INTO OUTFILE '/var/lib/mysql-files/tuser1.txt';

删除t_user表的数据

从文本文件tuser1.txt中恢复数据 

LOAD DATA INFILE '/var/lib/mysql-files/tuser1.txt' INTO TABLE shake_date.t_user;

2、将上面以逗号分割的文件导入到数据表

使用如下语句

LOAD DATA INFILE '/var/lib/mysql-files/t_user.txt' INTO TABLE shake_date.t_user FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

导入之前先清理下表的数据

3、使用mysqlimport方式导入文本文件 

仍然以上面的那个以逗号分割的文件为例进行说明

先清理数据表

执行下面的语句进行数据导入

mysqlimport -uroot -pXXX shake_date '/var/lib/mysql-files/t_user.txt' --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

执行完成后，可以看到数据又恢复了；