MySQL数据备份及恢复

一、数据备份

（一）mysqldump

1、命令格式

MySQL自带了一个很好使用的数据库备份命令，就是mysqldump，语法如下：

mysqldump -u 用户名 -p 数据库名 > 备份文件名

 2、实例说明

-- 查看备份前数据
[root@hadoop-slave1 temp]# mysql -uroot -p123456 -e "use crm_db;show tables;select *from userinfo;"
+------------------+
| Tables_in_crm_db |
+------------------+
| userinfo         |
+------------------+
+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | zhangsan |  24 |
+----+----------+-----+

-- 执行备份命令
[root@hadoop-slave1 temp]# mysqldump -uroot -p123456 crm_db > /temp/crm_db_bak.sql

-- 查看备份结果
[root@hadoop-slave1 temp]# egrep -v "#|*|--|^$" /temp/crm_db_bak.sql 
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_uni_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
LOCK TABLES `userinfo` WRITE;
INSERT INTO `userinfo` VALUES (1,'zhangsan',24);
UNLOCK TABLES;

（二）参数说明

上面使用mysqldump简单的方式进行备份，当然mysqldump还有很多参数。

1、--default-character-set

该参数指定了备份的字符集，可解决备份乱码的问题。

[root@hadoop-slave1 temp]# mysqldump -uroot -p123456 --default-character-set=utf8 crm_db > /temp/crm_db_bak.sql

2、-A

-A参数的作用是备份所有的库，例如：

-- 备份命令
[root@hadoop-slave1 /]# mysqldump -uroot -p123456 -A --events  > /temp/all.sql

3、-B

-B参数的作用是在备份中：

• 增加了创建数据库
• 连接数据库（use database）

-- 进行备份
[root@hadoop-slave1 temp]# mysqldump -uroot -p123456 -B crm_db > /temp/crm_db_bak.sql


-- 查看内容
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crm_db` 
USE `crm_db`;

DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_uni_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

LOCK TABLES `userinfo` WRITE;
INSERT INTO `userinfo` VALUES (1,'zhangsan',24);
UNLOCK TABLES;

可以看到这与之前的不同在于多了以下两条语句：

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crm_db`  -- 创建数据库
USE `crm_db`; -- 连接数据库

4、--compact

该参数可以优化备份文件大小，减少输出注释。该参数适合测试时使用。

-- 备份命令
[root@hadoop-slave1 temp]# mysqldump -uroot -p123456 --compact crm_db > /temp/crm_db_compat_bak_.sql

-- 备份文件的完整内容
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userinfo` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_uni_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `userinfo` VALUES (1,'zhangsan',24);

5、gzip

gzip可以对MySQL备份文件的内容进行压缩，压缩效率提升3倍左右。

-- 命令
[root@hadoop-slave1 temp]# mysqldump -uroot -p123456 -B crm_db|gzip > /temp/crm_db_bak_gzip.sql.gz

可以查看普通备份和压缩后的文件大小比较：

[root@hadoop-slave1 temp]# ll /temp/
total 8
-rw-r--r--. 1 root root  804 Aug 29 16:58 crm_db_bak_gzip.sql.gz
-rw-r--r--. 1 root root 2141 Aug 29 16:58 crm_db_bak.sql

6、-F

-F参数用于刷新 binlog参数，在/etc/my.cnf配置文件中如果启用了log-bin就会在安装的数据目录中生成binlog日志信息。

在没有进行备份时，和binlog相关的文件有：

[root@hadoop-slave1 data]# ll
...
-rw-rw----. 1 mysql mysql      107 Aug 29 22:50 mysql-bin.000001
-rw-rw----. 1 mysql mysql       19 Aug 29 22:50 mysql-bin.index
...

使用-F参数进行备份：

-- 进行备份
[root@hadoop-slave1 data]# mysqldump -uroot -p123456 -A -B -F --events  > /temp/all.sql

-- 查看binlog相关文件
[root@hadoop-slave1 data]# ll
...
-rw-rw----. 1 mysql mysql      150 Aug 29 23:14 mysql-bin.000001
-rw-rw----. 1 mysql mysql      150 Aug 29 23:14 mysql-bin.000002
-rw-rw----. 1 mysql mysql      150 Aug 29 23:14 mysql-bin.000003
-rw-rw----. 1 mysql mysql      107 Aug 29 23:14 mysql-bin.000004
-rw-rw----. 1 mysql mysql       76 Aug 29 23:14 mysql-bin.index
...

　　binlog文件被刷新了，也就意味着如果进行数据恢复，只需要从mysql-bin.000004之后进行增量恢复，这个文件之前的已经进行了全量备份了（在all.sql文件中）。这个参数主要就是切割binlog日志，记录备份的位置。防止恢复数据进行全量恢复而出错。

7、--master-data

这个参数也是记录binlog的位置，找到恢复的点，例如：

-- --master-data使用
[root@hadoop-slave1 data]# mysqldump -uroot -p123456 --compact --master-data -B crm_db  > /temp/crm_db.sql

-- 备份的文件内容
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `crm_db` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `crm_db`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `userinfo` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_uni_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `userinfo` VALUES (1,'zhangsan',24);

可以看到备份的文件内容中，多了一行：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107;

这是记录了binlog日志的信息，也就是说在这个位置之前的已经全部备份了，只需要通过binlog恢复这个点之后的内容即可。

对于查看mysql-bin.000004文件的内容，通过mysqlbinlog命令：

root@hadoop-slave1 data]# mysqlbinlog mysql-bin.000004

（三）备份库、表练习

1、多库备份

假设现在数据库中有多个库，上述使用的一直是一个库crm_db为例：现在新建一个库crm_db_1，然后同时进行备份。

-- 显示库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm_db             |
| crm_db_1           |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

-- 同时备份crm_db、crm_db_1库
[root@hadoop-slave1 temp]# mysqldump -uroot -p123456 -B crm_db crm_db_1|gzip > /temp/crm_db_all_bak.sql.gz

上面的-B参数在生产环境常用，并且有以下的功能：

• 后面可以接多个库进行备份
• 备份文件中会增加创建数据库的信息（create database dbname...）
• 备份文件中会增加连接数据库的信息（use dbname）

2、分库备份

　　上面是对多个库进行备份，并且备份到了一个备份文件中了，那么如何将每一个库都备份一个自己独有的备份文件呢？分库备份就是一个备份语句备份一个库，如果有多个库就执行多条相同的备份单个库的备份语句。注意的是每一个库的备份文件名可以用其库名代替，后面.sql即可。

...
mysqldump -uroot -p123456 -B crm_db|gzip > /temp/crm_db_bak_gzip.sql.gz
mysqldump -uroot -p123456 -B crm_db_1|gzip > /temp/crm_db_1_bak_gzip.sql.gz
...

上面就是一条一条的写，但假如有1000个库怎么办，下面有两种方式可以进行分库备份：

• 法一

-- 备份命令
[root@hadoop-slave1 temp]# mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r  
's#^([a-z].*$)#mysqldump -uroot -p123456 --events -B 1|gzip>/temp/1.sql.gz#g'|bash

-- 备份结果
[root@hadoop-slave1 temp]# ll /temp/
total 152
-rw-r--r--. 1 root root    530 Aug 29 19:18 crm_db_1.sql.gz
-rw-r--r--. 1 root root    820 Aug 29 19:18 crm_db.sql.gz
-rw-r--r--. 1 root root 144380 Aug 29 19:18 mysql.sql.gz

• 法二

首先创建一个脚本gen_sql.sh：

for dbname in `mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|infor|perfor"`
do
  mysqldump -uroot -p123456 --events -B $dbname|gzip>/temp/${dbname}_bak.sql.gz
done

然后执行：

[root@hadoop-slave1 scripts]# sh gen_sql.sh 

这样与第一种方式产生的结果一样。

分库的意义就是，如果某一个库出现问题时只需要备份这一个库就可以了，而将所有的库备份在一个备份文件中，这样恢复起来比较麻烦。

3、单表备份

语法：

mysqldump -uroot 用户名 -p 数据库名 表名 > 备份文件名

例如：

[root@hadoop-slave1 /]# mysqldump -uroot -p123456 crm_db userinfo > /temp/t1.sql

注意：不要加-B参数，因为库名后面就是表名。

4、多表备份

语法：

mysqldump -uroot 用户名 -p 数据库名 表名1 表名2 > 备份文件名

例如：

-- 备份命令
[root@hadoop-slave1 /]# mysqldump -uroot -p123456 crm_db userinfo > /temp/t1.sql

-- 备份内容
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_uni_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
LOCK TABLES `userinfo` WRITE;
INSERT INTO `userinfo` VALUES (1,'zhangsan',24);
UNLOCK TABLES;

　　如果库名后紧跟多个表的时候会将多个表写入同一个备份文件，这样的话如果向恢复某一个表时很难将备份文件拆开，那么这也就意味着我们需要像分库一样进行分表备份。那么又该如何进行分表备份呢？其实只需要每条语句备份一个表即可：

...
mysqldump -uroot -p123456 crm_db userinfo > /temp/t1.sql
mysqldump -uroot -p123456 crm_db product> /temp/t2.sql
...

　　当然，你可以像分库备份一样写一个脚本文件来进行备份。

5、备份数据库表结构

 有时候我们只需要备份数据表结构而不需要数据，这是需要使用到-d参数。

-- 备份表结构
[root@hadoop-slave1 /]# mysqldump -uroot -p123456 -B -d crm_db > /temp/crm_db__bak.sql

-- 查看备份
[root@hadoop-slave1 /]# egrep -v "#|*|--|^$" /temp/crm_db__bak.sql 
USE `crm_db`;
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_uni_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

6、备份数据表数据

上面是只备份数据表结构，那么如果只想备份数据表的数据，就使用-t参数：

-- 备份命令
[root@hadoop-slave1 /]# mysqldump -uroot -p123456 -t crm_db > /temp/crm_db_bak.sql

-- 备份内容
LOCK TABLES `userinfo` WRITE;INSERT INTO `userinfo` VALUES (1,'zhangsan',24);
UNLOCK TABLES;

（四）mysqldump关键参数总结

-- 参数查看通过
[root@hadoop-slave1 data]# mysqldump --help

-- 以下关键参数说明
1、-A备份所有的库
2、-B指定多个库，增加建库和use语句
3、--compact 去掉注释，适合调试，不用于生产
4、-F刷新binlog日志
5、--master-data 增加binlog日志文件名和对应的位置点
6、-d只备份表结构
7、t 只备份表数据
8、--single-transaction 适合innodb事务数据库备份，保证数据在备份时的一致性，
    设定本次会话的隔离级别为REPEATABLE READ，确保本次会话dump是，不会看到其它会话已经提交的数据
9、-l,--local-table Locak all tables for read
10、-x,--local-all-tables Local all tables across all databases

-- 推荐使用的innodb备份命令
mysqldump -uroot -p123456 -A -B -F --master-data=2 --events --single-transaction|gzip>/temp/all.sql.gz
-- myisam备份命令
mysqldump -uroot -p123456 -A -B -F --master-data=2 -x --events |gzip>/temp/all.sql.gz

二、数据恢复

（一）source命令恢复

先进行备份，然后通过source命令恢复：

-- 备份
[root@hadoop-slave1 /]# mysqldump -uroot -p123456 -B crm_db > /temp/crm_db_bak.sql
-- 查看备份结果
[root@hadoop-slave1 /]# ll /temp/*
-rw-r--r--. 1 root root 2145 Aug 30 07:40 /temp/crm_db_bak.sql

-- 删除已经备份的库
[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'drop database crm_db;show databases;' 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm_db_1           |
| mysql              |
| performance_schema |
+--------------------+

-- 恢复备份，需要进入MySQL命令行中
[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'source /temp/crm_db_bak.sql'

（二）mysql命令恢复（标准）

1、备份时带-B参数

-B参数在备份时有连接数据库和创建数据库的作用，所以如果在备份时指定了该参数，那么在恢复时就无需指定具体的恢复的数据库：

-- 恢复备份
[root@hadoop-slave1 /]# mysql -uroot -p123456 < /temp/crm_db_bak.sql 

-- 查看结果
[root@hadoop-slave1 /]# mysql -uroot -p123456 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm_db             |
| crm_db_1           |
| mysql              |
| performance_schema |
+--------------------+

2、备份时未带-B参数

这也就意味着在恢复数据库数据时必须指定恢复到那个数据库，并且这个数据库已经是存在的。

-- 恢复crm_db1中的数据，crm_db1已经存在
[root@hadoop-slave1 /]# mysql -uroot -p123456 crm_db1 < /temp/crm_db1_bak.sql 

（三）分库、分表备份数据恢复

对于分库、分表的数据，通过脚本读取指定的库、表，使用mysql命令恢复。

-- 生成crm_db、crm_db1的备份文件
[root@hadoop-slave1 /]# mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|infor|perfor|mysql"|sed -r  
> 's#^([a-z].*$)#mysqldump -uroot -p123456 --events -B 1|gzip>/temp/1.sql.gz#g'|bash

-- 查看备份文件
[root@hadoop-slave1 /]# ls /temp/
crm_db_1.sql.gz  crm_db.sql.gz

-- 进入到备份文件所在的目录，然后对其解压
[root@hadoop-slave1 temp]# ls
crm_db_1.sql.gz  crm_db.sql.gz
[root@hadoop-slave1 temp]# gzip -d *
[root@hadoop-slave1 temp]# ls
crm_db_1.sql  crm_db.sql

-- 通过脚本进行恢复，可先删除原先的库
[root@hadoop-slave1 temp]# for dbname in `ls *.sql|sed 's#.sql##g'`;do mysql -uroot -p123456 <${dbname}.sql;done

-- 查看所有的库
[root@hadoop-slave1 temp]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| crm_db             |
| crm_db_1           |
| mysql              |
| performance_schema |
+--------------------+

（四）binlog日志恢复

MySQL的binlog日志用于记录MySQL中的增、删、该操作，当你没有备份时就只能依靠它来进行数据的恢复了。

1、恢复的种类

• 全库

root@hadoop-slave1 data]# mysqlbinlog mysql-bin.000002 > all.sql

• 分库

比如从binlog日志中抽离出crm_db的sql操作信息：

root@hadoop-slave1 data]# mysqlbinlog -d crm_db mysql-bin.000002 > crm_db.sql

2、增量恢复的方式

• 基于位置点的增量恢复

 mysqlbinlog  mysql-bin.000002 --start-position=107 --stop-position=208 -r db.sql

 注意：结尾的日志比较特殊，不会被包含，即输出208以前的日志。

 如果开始位置到结束，则无需制定--stop-position参数：

 mysqlbinlog  mysql-bin.000002 --start-position=107 -r db.sql

如果没有指定--start-position参数，则是开始到指定的结束位置：

 mysqlbinlog  mysql-bin.000002 --stop-position=208 -r db.sql

• 基于时间的增量恢复

 mysqlbinlog  mysql-bin.000002 --start-datetime='2020-8-30 11:10:20' 
--stop-datetime='2020-8-30 14:30:20' -r db.sql

（五）数据备份、恢复实战

现在假如有这样的场景，数据库crm_db中的数据在某个时间点操作失误，想恢复数据。

1、查看crm_db数据库相关信息

[root@hadoop-slave1 /]# mysql -uroot -p123456 -e 'use crm_db;show tables;select * from userinfo;' 
+------------------+
| Tables_in_crm_db |
+------------------+
| userinfo         |
+------------------+
+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | zhangsan |  24 |
|  2 | lisi     |  32 |
|  3 | wangwu   |  45 |
+----+----------+-----+

2、对crm_db进行备份

[root@hadoop-slave1 /]# mysqldump -uroot -p123456 -B crm_db > /temp/crm_db.sql 
[root@hadoop-slave1 /]# ll /temp/
total 4
-rw-r--r--. 1 root root 2175 Aug 30 09:46 crm_db.sql

3、异常产生

在这一步由于更改数据导致数据异常：

mysql> update userinfo set username='zhaoliu';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from userinfo;
+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | zhaoliu  |  24 |
|  2 | zhaoliu  |  32 |
|  3 | zhaoliu  |  45 |
+----+----------+-----+
3 rows in set (0.00 sec)

由于update使用不当，导致错误产生，此时需要进行恢复，但是在恢复的过程中仍旧会有新的数据不断插入，所以需要将binlog日志先进行分割。

4、binlog日志分割

在数据库不停止服务时，进行日志分割：

-- 分割前文件
[root@hadoop-slave1 /]# ll /application/mysql-5.5.32/data/mysql-bin*
-rw-rw----. 1 mysql mysql 626 Aug 30 09:57 /application/mysql-5.5.32/data/mysql-bin.000001
-rw-rw----. 1 mysql mysql  19 Aug 30 09:51 /application/mysql-5.5.32/data/mysql-bin.index

--分割
[root@hadoop-slave1 /]# mysqladmin -uroot -p123456 flush-log

-- 分割后文件
[root@hadoop-slave1 /]# ll /application/mysql-5.5.32/data/mysql-bin*
-rw-rw----. 1 mysql mysql 669 Aug 30 10:05 /application/mysql-5.5.32/data/mysql-bin.000001
-rw-rw----. 1 mysql mysql 107 Aug 30 10:05 /application/mysql-5.5.32/data/mysql-bin.000002
-rw-rw----. 1 mysql mysql  38 Aug 30 10:05 /application/mysql-5.5.32/data/mysql-bin.index

分割后多了一个mysql-bin.000002文件，也就是说后面写的内容都会写在这个文件中，现在需要处理的就是mysql-bin.000001文件。

5、备份恢复

首先，我们将还未出错前的数据通过备份文件crm_db.sql进行恢复：

-- 通过备份恢复
[root@hadoop-slave1 temp]# mysql -uroot -p123456 < /temp/crm_db.sql 

-- 查看数据
[root@hadoop-slave1 temp]# mysql -uroot -p123456 -e 'use crm_db;select * from userinfo;' 
+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | zhangsan |  24 |
|  2 | lisi     |  32 |
|  3 | wangwu   |  45 |
+----+----------+-----+

现在恢复的是备份前的数据，缺少的是备份的时间点到出错这段时间的数据，那么这段时间只能通过binlog日志来获取了。

6、binlog恢复数据

-- binlog日志生成sql
[root@hadoop-slave1 data]# mysqlbinlog -d crm_db mysql-bin.000001 > bin.sql

-- 删除bin.sql中update更新的语句，然后恢复
[root@hadoop-slave1 data]# mysql -uroot -p123456 crm_db < bin.sql 

-- 查看数据
[root@hadoop-slave1 data]# mysql -uroot -p123456 -e 'use crm_db;select * from userinfo;' 
+----+----------+-----+
| id | username | age |
+----+----------+-----+
|  1 | zhangsan |  24 |
|  2 | lisi     |  32 |
|  3 | wangwu   |  45 |
+----+----------+-----+

上面恢复的数据时备份时间点到出错这段时间的数据，那么出错之后的数据因为进行了日志分割，所以它在第二个binlog日志文件中，只需要再将其恢复即可：

-- 恢复mysql-bin.000002中的数据
[root@hadoop-slave1 data]# mysqlbinlog -d crm_db mysql-bin.000002 > bin2.sql
[root@hadoop-slave1 data]# mysql -uroot -p123456 crm_db < bin2.sql