MySQL数据库的备份与恢复

数据备份的重要性

• 在生产环境中，数据的安全性至关重要
• 任何数据的丢失都可能产生严重的后果
• 造成数据丢失的原因
  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难（如火灾、地震）和盗窃

数据库备份的分类

从物理与逻辑的角度，备份可分为

物理备份

对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。

物理备份方法

• 冷备份（脱机备份）：是在关闭数据库的时候进行的
• 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

逻辑备份

对数据库逻辑组件（如：表等数据库对象）的备份。

从数据库的备份策略角度，备份可分为

完全备份

每次对数据库进行完整的备份。

差异备份

备份自从上一次完全备份之后被修改过的文件

增量备份

只有在上一次完全备份或者增量备份后被修改的文件才会被备份

注解图：

归纳： 增量备份只会备份，完全备份或者在上一次增量备份做修改的内容，是依赖于之前的备份内容，前面丢失一个备份，那么备份就不完全了
差异备份不认增量备份只认完全备份，会把完全备份之后增加的内容统一进行备份。
增量备份和差异备份两者是同时进行的，两者相辅相成。

常见的备份方法

物理冷备

• 备份时数据库处于关闭状态，直接打包数据库文件
• 备份速度快，恢复时也是最简单的

专用备份工具mydump或mysqlhotcopy

• mysqldump常用的逻辑备份工具
• mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

启用二进制日志进行增量备份

• 进行增量备份，需要刷新二进制日志

第三方工具备份

• 免费的MySQL热备份软件Percona XtraBackup

MySQL完全备份

• 是对整个数据库、数据库结构和文件结构的备份
• 保存的是备份完成时刻的数据库
• 是差异备份与增量备份的基础
• 优点
  • 备份与恢复操作简单方便
• 缺点
  • 数据存在大量的重复
  • 占用大量的备份空间
  • 备份与恢复时间长

数据库完全备份分类

物理冷备份与恢复

• 关闭MySQL数据库
• 使用tai命令直接1打包数据库文件夹
• 直接替换现有MySQL目录即可

mysqldump备份与恢复

• MySQL自带的备份工具
• 可以将指定的库、表导出SQL脚本
• 使用命令mysql导入备份的数据

操作

MySQL物理冷备份（一）

[root@localhost ~]# mkdir /opt/bak   		#创建个存放文件夹
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# tar zvcf /opt/bak/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/
/opt/bak/mysql_all_$(date +%F).tar.gz 		  #存放点
/usr/local/mysql/data/  		  #需备份的文件夹
[root@localhost ~]# ls /opt/bak/ 		  #检验是否备份
mysql_all_2020-10-15.tar.gz

mysqldump备份数据库（二）

• mysqldump命令对单个库进行完全备份
  • mysqldump -u 用户名 -p [数据库名] > /备份路径/备份文件名
    单库备份的示例

[root@localhost ~]# mysqldump -uroot -p school > /opt/school.sql
[root@localhost ~]# cd /opt
[root@localhost opt]# ll
-rw-r--r--   1 root root      1926 Oct 15 00:47 school.sql

[root@localhost opt]# vi school.sql
...
DROP TABLE IF EXISTS `info`;    	#如果 info 表存在就删除该表，下面重新导入数据。（初始化数据库）
...
LOCK TABLES `info` WRITE;   	 #锁表
INSERT INTO `info` VALUES (1,'zhangsan',88.00),(2,'lisi',90.00); 	#插入数据
UNLOCK TABLES;     	 #解表

附加： 脚本中没有添加数据库，如要还原数据，需先创建库。

mysqldump备份数据库（三）

• mysqldump命令对多个库进行完全备份
  • mysqldump -u 用户名 -p --databases 库名1 库名2… > /备份路径/备份文件名
    多库备份的示例

[root@localhost ~]# mysqldump -uroot -p --databases school bank> /opt/school_bank.sql
[root@localhost opt]# vi school_bank.sql 
...
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `school` /*!40100 DEFAULT CHARACTER SET utf8 */; 	   #创建数据库
USE `school`;  	 #进入库
...
DROP TABLE IF EXISTS `info`;

CREATE TABLE `info` (    	#创建表结构
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

LOCK TABLES `info` WRITE; 	  #锁表
INSERT INTO `info` VALUES (1,'zhangsan',88.00),(2,'lisi',90.00);  	#插入数据
UNLOCK TABLES;  	  #解表

另一个表过程一样 ：
（建库——>入库——>初始化——>创建表结构——>锁表——>插入数据——>解表）

mysqldump备份数据库（四）

• 对所有库进行完全备份
  • mysqldump -u 用户名 -p --all-databases > /备份路径/备份文件名
    所有库备份的示例

[root@localhost opt]# mysqldump -uroot -p --all-databases > /opt/all-data.sql
与多表备份过程一样，就是对所有库进行备份

mysqldump备份数据表

• mysqldump可针对库内特定的表进行备份
• 使用mysqldump备份表的操作
  • mysqldump -u 用户名 -p 数据库名 表名 > /备份路径/备份文件名
    备份表的示例

[root@localhost opt]# mysqldump -uroot -p school info > /opt/info.sql
与单表过程一样

恢复数据库（一）

• 使用mysqldump导出的脚本，可使用导入的方法
  • source命令
  • mysql命令
• 使用source恢复数据库的步骤
  • 登录到MySQL数据库
  • 执行source 备份sql脚本的路径
• source 恢复的示例

mysql> drop database school;   #删除数据库
mysql> create database stu;    #创建新的数据库
mysql> use stu;     #进入数据库
mysql> source /opt/school.sql;   #恢复数据库（school.sql之前备份过的）
mysql> show tables;    #查看是否恢复
+---------------+
| Tables_in_stu |
+---------------+
| info          |
+---------------+
1 row in set (0.00 sec)
mysql> select * from info;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | zhangsan | 88.00 |
|  2 | lisi     | 90.00 |
+----+----------+-------+
2 rows in set (0.00 sec)

恢复数据库（二）

• 使用mysql命令恢复数据
  • mysql -u 用户名 -p < 库备份脚本的路径
• 使用mysql命令恢复的示例

mysql -u root -p < /opt/all-data.sql

恢复表的操作

• 恢复表时同样可以使用source或者mysql命令
• source恢复表的操作与恢复库的操作相同
• 当备份文件中只包含表的备份，而不包括创建库的语句时，必须指定库名，且目标库必须存在
  • mysql -u 用户名 -p 数据库< 表备份脚本的路径
  • mysql -u root -p stu< /opt/info.sql
• 在生产环境中，可以使用shell脚本自动实现定时备份

MySQL增量备份

• 使用mysqldump进行完全备份存在的问题
  • 备份数据中有重复数据
  • 备份时间与恢复时间过长
• 是自上一次备份后增加/变化的文件或者内容
• 特点
  • 没有重复数据，备份量不大，时间短
  • 恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
• MySQL没有提供直接的增量备份方法
• 可通过MySQL提供的二进制日志间接实现增量备份
• MySQL二进制日志对备份的意义
  • 二进制日志保存了所有更新数据库的操作
  • 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
  • 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

MySQL数据库增量恢复

• 一般恢复
  • 将所有备份的二进制日志内容全部恢复
• 基于位置恢复
  • 数据库在某一时间点可能既有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作
• 基于时间点恢复
  • 跳过某个发生错误的时间点实现数据恢复
• 增量恢复的方法
  • 一般恢复
    • mysqlbinlog --no-defaults 增量备份文件 | mysql -u 用户名 -p
  • 基于位置的恢复
    • 恢复数据到指定位置
      • mysqlbinlog --no-defaults --stop-position=‘截止位置’ 二进制日志 | mysql -u 用户名 -p 密码
    • 从指定的位置开始恢复数据
      • mysqlbinlog --no-defaults --start-position=‘开始位置’ 二进制日志 | mysql -u 用户名 -p 密码
• 基于时间点的恢复
  • 从日志开头截止到某个时间点的恢复
    • mysqlbinlog --no-defaults --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
  • 从某个时间点到日志结尾的恢复
    • mysqlbinlog --no-defaults --start-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
  • 从某个时间点到某个时间点的恢复
    • mysqlbinlog --no-defaults --stop-datetime=‘年-月-日 小时:分钟:秒’ --stop-datetime=‘年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

生成二进制日志：

[root@localhost ~]# vi /etc/my.cnf
log-bin = 1-bin     #1可自定义
[root@localhost data]# systemctl restart mysqld
[root@localhost data]# ls
1-bin.000001 1-bin.index
[root@localhost data]# mysqlbinlog --no-defaults 1-bin.000001  #检查二进制日志（加密的）

附加： 在数据库中进行操作后退出刷新，会在1-bin.000001产生操作日志。

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs;   #刷新（在Linux环境下）生成新的二进制文件
[root@localhost data]# ls
1-bin.000001 1-bin.000002
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v 1-bin.000001  

附加：
–base64 解码
-output 输出
=decode-rows 按正常编码的方式逐行输出
-v 自动换行

刷新结束，进入数据库再次操作，如果操作中途出现错误无法修复可以采用一般恢复恢复到日志

操作过程：
生成日志——>对数据库进行操作（中途有误操作）——> 退出数据库刷新下产生新日志 ——>对日志解码查看，记录误操作的前、后和误操作的开始位置和时间节点（误操作前还需要记录下结束的位置）
——>进入数据库删除错误的整个数据库——> 再用source命令恢复之前备份的数据

[root@localhost data]# mysqlbinlog --no-defaults --stop-position='423' 1-bin.000001 | mysql -uroot -p  #恢复到指定位置
[root@localhost data]# mysqlbinlog --no-defaults --start-position='830' 1-bin.000001 | mysql -uroot -p  #从指定位置开始恢复

时间节点恢复跟位置恢复类似