使用mysqldump进行数据的备份与还原

备份数据的最终目的是为了在出现一些意外情况时，能够通过备份将数据还原，所以单单的备 份数据往往是无法满足还原时的需求的，所以在备份数据库时，除了要备份数据本身，还要备

份相关的数据库环境，如配置文件，定时任务，sudo权限等等相关的信息

一、为什么要备份

1、灾难恢复：硬件故障，软件故障，自然灾害，黑客攻击，误操作

​ 2、测试

二、备份要注意的要点

1、备份需要多少时间（备份过程的时长）

2、能容忍最多丢失多少数据

​ 3、恢复数据需要在多场时间内完成（恢复过程的时长）

​ 4、需要恢复哪些数据

​ （1）做还原测试，用于测试备份的可用性

（2）还原演练

5、备份负载

三、备份内容

1、数据

2、二进制日志，innodb的事务日志  　　

​ 3、代码（存储过程、存储函数、触发器、时间调度器）

4、服务器的配置文件

四、备份相关术语

1、完全备份（全量备份）（Full Backup）

对某个时间点的所有数据进行一个完全的备份，对应时间点的所有数据都被包含在完全备 份中。（备份整个数据集）

2、部分备份

只备份数据子集

3、增量备份

仅备份最近一次完全备份或增量备份（如果有增量备份）以来变化的数据

4、**差异备份**  

	仅备份最近一次完全备份以来变化的数据；  

​ 5、热备

在数据库正常运行的情况下进行备份，读写操作均可执行（往往依赖于事务日志）（难度 最大）（myisam存储引擎不支持热备，Innodb存储引擎支持热备）

6、冷备

读写操作均不能执行（停数据库后进行文件拷贝即可）

7、物理备份

直接备份数据库所对应的数据文件，与存储引擎无关（cp），相对于逻辑备份来说，性能

更强

8、逻辑备份

从数据库中“导出”数据另存而进行备份

五、设计备份方案

1、数据集：完全+增量+二进制日志|完全+差异+二进制日志

2、备份手段：物理，逻辑（物理备份恢复较快，逻辑备份恢复较慢）

对于备份较大的数据建议物理备份，对于较小的数据建议用逻辑备份。

六、备份工具的选择

1、cp命令或tar命令

在Linux中直接对数据文件进行备份，这种方式只适用于冷备的方式 　　2、通过select语句进行部分备份

通过select语句将表中的数据导出到指定文件中。 在使用select语句备份的同时，最好将表架构也备份一份，因为还原的时候可以用到。

3、mysqldump+复制binlog：

mysqldump：完全备份（部分备份）（逻辑备份工具） 　　复制binlog中指定时间范围内的event：增量备份

mysqldump是mysql自带的备份工具，它是一种逻辑备份工具，也就是说，它会将数据 从数据库中读出，转化为对应的sql语句。

mysqldump能够实现完全备份或部分备份 　　使用innodb表类型的表能够使用mysqldump进行热备 　　使用myisam表类型的表只能够使用mysqldump进行温备 　　如果数据量较小，可以选择使用mysqldump。 　　原理：

通过协议连接到mysql数据库，将需要备份的数据查询出来，将查询出的数据转换成对应 的insert语句，还原这些数据时，只要执行这些insert语句即可将对应的数据还原。

优点：

可以直接使用文本处理工具处理对应的备份数据，因为备份数据已经被mysqldump转换 成了对应的insert语句，所以，我们可以借助文件系统中的文本处理工具对备份数据进行直接 处理。

缺点：

当数据为浮点类型时，会出现精度丢失

它的备份过程属于逻辑备份，其备份速度、恢复速度与物理备份工具相比较慢，而且 mysqldump备份的过程是串行化的（mydumper可并行备份），不会并行的进行备份；当数 据量较大时，其效率较低

4、lvm2快照+复制binlog：

lvm2快照：（做快照的时候不能有任何的数据写入）（几乎热备（备份速度快）） 　　lvm2快照：适用cp或者tar等做物理备份：完全备份 　　复制binlog中指定时间范围内的event：增量备份

5、xtrabackup|mariabackup：

由Percona提供的支持对InnoDB做热备（物理备份）工具（开源免费的）

支持完全备份、差异备份、增量备份、部分备份等功能

七、备份恢复补充知识

1、Linux上查看及测试配置文件顺序

	[root@mysql ~]# mysql --help | grep 'my\.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
	/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

​ 注意：~/.my.cnf mysql家目录下的.my.cnf 优先级最高。

​ windows上查看：mysql --help | findstr my.cnf

2、 binlog的正确删除方法:

	1> reset master;删除所有binlog日志文件（除mysql-bin.index文件）
	2> purge master logs to mysql-bin.****** 将******编号之前的binlog日志文件删除
	3> purge master logs before 'yyyy-mm-dd hh24:mi:ss'删除 yyyy-mm-dd hh24:mi:ss日期之前产生的所有日志
	4> 在my.cnf配置文件中设置参数 expire_logs_days=# 即binlog日志的过期天数 过了指定日期之后日志将会被自动删除.

3、mysqlbinlog常用参数

	--database 仅仅列出配置的数据库信息
	--no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令
	--offset 跳过 log 中 N 个条目
	--verbose 将日志信息重建为原始的 SQL 陈述。
	-v 仅仅解释行信息
	-vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来
	--start-datetime 显示从指定的时间或之后的时间的事件。
		接收 DATETIME 或者 TIMESTRAMP 格式。
	--base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽。
		AUTO 默认参数，自动显示 BINLOG 中的必要的语句
		NEVER 不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。
		DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据。
	-r, --result-file=name 结果文件，相当于> filename

​ 注意：
​ MySQL 5.7 中无论是否打开 GTID 的配置，在每次事务开启时，都首先会出 GTID 的一个事务，用于并行复制。所以在确定导出开始事务位置时，要算上这个事件。
​ 在使用 --stop-position 导出时，会导出在指定位置的前一个事件，所以这里要推后一个事务。
​ 对于 DML 的语句，主要结束位置要算上 COMMIT 的位置。

4、mysqlbinlog恢复数据

​ 在执行数据恢复前，如果操作的是生产环境，会有如下的建议：
​ 使用 flush logs 命令，替换当前主库中正在使用的 binlog 文件，好处如下：
​ 1> 可将误删操作，定位在一个 BINLOG 文件中，便于之后的数据分析和恢复。
​ 2> 避免操作正在被使用的 BINLOG 文件，防止发生意外情况。
​ 3> 数据的恢复不要在生产库中执行，先在临时库恢复，确认无误后，再倒回生产库。防止对数据的二次伤害。

八、备份恢复的策略-mysqldump

恢复主要有两个步骤：

​ 1.在临时库中，恢复定期执行的全量备份数据。
​ 2.然后基于全量备份的数据点，通过 BINLOG 来恢复误操作和正常的数据。

（完全备份+增加备份，速度相对较慢，适合中小型数据库）

​ mysqldump 常用参数

-A, --all-databases 备份所有数据库
-B, --databases  备份时多了create database dbname和 use dbname
-F, --flush-logs 刷新日志
-x, --lock-all-tables  锁表
-d, --no-data 不备份数据

​ 1、备份db1数据库并压缩

# mysqldump -uroot -pABCabc123! -B db1 | gzip > db1.sql.gz

​ 2、只备份db1数据库结构

# mysqldump -uroot -pABCabc123! -d -B db1 | gzip > db1_2.sql.gz

​ 3、备份数据库db1的Authors和Books 表

mysqldump -uroot -pABCabc123! db1 Authors Books -r  db1_table1.sql

1)还原数据库：

​ 方法1：mysql -uroot -p密码 db1 < db1_table1.sql
​ 还原db1数据库：

zcat db1.sql.gz | mysql -uroot -pABCabc123!

​ 还原db1的Authors和Books 表：

mysql -uroot -pABCabc123! db1 < db1_table1.sql 

​ 方法2：mysql命令行使用source

mysql -uroot -pABCabc123! -e 'use db1;source db1_table1.sql;'

2)增量备份：binlog

第一个前提：

​ 1）my.cnf，是要开启MySQL log-bin日志功能，重启MySQL
​ 2）存在一个完全备份，生产环境一般凌晨某个时刻进行全备

创建示例数据库：

mysql> create database db3;

创建表

mysql> use db3
​Database changed
​mysql> CREATE TABLE `Student` (
​->   `Sno` int(10) NOT NULL COMMENT '学号',  `Sname` varchar(16) NOT NULL COMMENT '姓名',
​->   `Ssex` char(2) NOT NULL COMMENT '性别',  `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
​->   `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别',  PRIMARY KEY (`Sno`)
​-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
​

Query OK, 0 rows affected (0.00 sec)

​ 插入测试数据：

mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
​
Query OK, 4 rows affected (0.02 sec)
​Records: 4  Duplicates: 0  Warnings: 0
​		
mysql> select * from Student;
​		
+-----+--------+------+------+-----------------+
​		
| Sno | Sname  | Ssex | Sage | Sdept           |
​		
+-----+--------+------+------+-----------------+
​		
|   1 | 陆亚   | 男   |   24 | 计算机网络      |
​		
|   2 | tom    | 男   |   26 | 英语            |
​		
|   3 | 张阳   | 男   |   21 | 物流管理        |
​		
|   4 | alex   | 女   |   22 | 电子商务        |
​		
+-----+--------+------+------+-----------------+
​		
4 rows in set (0.00 sec)

​第二个前提条件：完全备份

[root@node5 ~]# mysqldump -uroot -pABCabc123! -B db3 | xz > db3.$(date +%F).sql.xz

继续插入数据：

mysql> INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');
​
Query OK, 2 rows affected (0.00 sec)
​Records: 2  Duplicates: 0  Warnings: 0

​mysql> select * from Student;
​+-----+----------+------+------+-----------------+
​| Sno | Sname    | Ssex | Sage | Sdept           |
​+-----+----------+------+------+-----------------+
​|   1 | 陆亚     | 男   |   24 | 计算机网络      |
​|   2 | tom      | 男   |   26 | 英语            |
​|   3 | 张阳     | 男   |   21 | 物流管理        |
​|   4 | alex     | 女   |   22 | 电子商务        |
​|   5 | xumubin  | 男   |   29 | 中文专业        |
​|   6 | wangzhao | 男   |   21 | 导弹专业        |
​+-----+----------+------+------+-----------------+
​
6 rows in set (0.00 sec)

​ 误操作删除了表Student

mysql> drop table Student;

恢复数据：

​ 1、刷新日志

mysql> flush logs;
​		
​mysql> show master status;
​+------------------+----------+--------------+------------------+-------------------+
​| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
​+------------------+----------+--------------+------------------+-------------------+
​| node5-bin.000003 |      154 |              |                  |                   |
​+------------------+----------+--------------+------------------+-------------------+
​
1 row in set (0.00 sec)

​ 2、还原完全备份

[root@node5 ~]# xzcat db3.2020-07-10.sql.xz | mysql -uroot -pABCabc123!

​ 恢复了 前4条数据

mysql> select * from Student;
​+-----+--------+------+------+-----------------+
​| Sno | Sname  | Ssex | Sage | Sdept           |
​+-----+--------+------+------+-----------------+
​|   1 | 陆亚   | 男   |   24 | 计算机网络      |
​|   2 | tom    | 男   |   26 | 英语            |
​|   3 | 张阳   | 男   |   21 | 物流管理        |
​|   4 | alex   | 女   |   22 | 电子商务        |
​+-----+--------+------+------+-----------------+
​
4 rows in set (0.00 sec)

​ 3、使用binlog日志恢复全备之后的数据
​
​ 查看binlog日志

[root@node5 ~]# mysqlbinlog --base64-output=decode-rows -v /usr/local/mysql/data/node5-bin.000002

​ 使用结束时间

mysqlbinlog /usr/local/mysql/data/node5-bin.000002 --start-datetime='2020-07-10 15:57:48' --stop-datetime='2020-07-10 15:58:40' -r time1.sql

​ 使用结束位置

mysqlbinlog /usr/local/mysql/data/node5-bin.000002 --start-position=1185 --stop-position=1509 > pos1.sql

​ 还原增量数据：

mysql -uroot -pABCabc123! db3  < time1.sql