超详细讲解：数据库的备份与数据恢复方法举例与说明（完全备份、差异备份、增量备份...

一、数据库的备份分类

• 从物理与逻辑的角度，备份可分为
  1、物理备份：对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
  物理备份方法
  冷备份(脱机备份)：是在关闭数据库的时候进行的
  热备份(联机备份)：数据库处于运行状态，依赖于数据库的日志文件
  温备份：数据库锁定表格(不可写入但可读)的状态下进行备份操作
  2、逻辑备份：对数据库逻辑组件(如：表等数据库对象)的备份
• 从数据库的备份策略角度，备份可分为
  1、完全备份：每次对数据库进行完整的备份
  2、差异备份：备份自从上次完全备份之后被修改过的文件
  3、增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

二、数据库的备份方法

• 物理冷备
  备份时数据库处于关闭状态，直接打包数据库文件
  备份速度快，恢复时也是最简单的
• 专业备份工具mysqldump或mysqlhotcopy
  mysqldump常用的逻辑备份工具
  mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
• 启用二进制日志进行增量备份
  进行增量备份，需要刷新二进制日志
• 第三方工具备份
  免费的MySQL热备份软件Percona XtraBackup

三、完全备份、增量备份概述与对比

完全备份：
是对整个数据库、数据库结构和文件结构的备份；
保存的是备份完成时刻的数据库；
是差异备份与增量备份的基础；
每次对数据进行完整的备份。

优点： 备份与恢复操作简单方便缺点：
数据存在大量的重复；
占用大量的备份空间；
备份与恢复时间长。

增量备份：

MySQL增量备份是自上一次备份后增加/变化的文件或者内容特点
没有重复数据，备份量不大，时间短；
依靠二进制日志文件进行逐次增量备份，单个文件丢失则数据不完整，安全性低。

MySQL二进制日志对增量备份有重要的作用

• 二进制日志保存了所有更新或者可能更新数据库的操作；
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件；
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

四、完全备份的实例

4.1 冷备份与数据恢复

需要先停止数据库服务，再直接打包压缩数据库文件
数据库所有文件目录：/usr/local/mysql/data

登录后复制

数据库已有的库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
#进行备份
[root@server1 ~]# systemctl stop mysqld
[root@server1 ~]# mkdir /opt/backup
[root@server1 ~]# tar zcf /opt/backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/
data/   
#对备份的文件加入备份时间点的命名，获取当时的日期
#查看备份文件
[root@server1 ~]# cd /opt/backup/
[root@server1 backup]# ll
总用量 1356
-rw-r--r--. 1 root root 1386275 11月  2 10:54 mysql_all-2020-11-02.tar.gz

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.

故障时，停止服务，将故障的数据库文件移走到备份文件夹中，解压刚才备份数据库包到/restore目录下，再移动到/usr/local/mysql/data下，再重启服务

登录后复制

mysql> drop database student; #误删除库
Query OK, 3 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
#恢复
[root@server1 ~]# systemctl stop mysqld
[root@server1 ~]# mkdir /badbak   #建立损坏数据库的备份
[root@server1 ~]# mv /usr/local/mysql/data/ /badbak 
[root@server1 ~]# mkdir /restore   
[root@server1 ~]# tar zxf /opt/backup/mysql_all-2020-11-02.tar.gz -C /restore/
[root@server1 ~]# mv /restore//usr/local/mysql/data/ /usr/local/mysql/data
[root@server1 ~]# systemctl start mysqld
#查看恢复结果
[root@server1 ~]# mysql -uroot -pxzf729
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
恢复成功

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.

4.2 mysqldump备份与恢复

备份方法：

登录后复制

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql
 ###备份所有数据库到当前目录下的all-data-$(date +%F).sql 文件
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库
mysqldump -u root -p auth > auth-$(date +%F).sql ###备份auth数据库
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

实例1：
备份单独一个student库

登录后复制

[root@server1 ~]# mysqldump -u root -p student > student-$(date +%F).sql
Enter password:

• 1.
• 2.

恢复：
误删除单独一个库，恢复时，需要再建一下这个库

登录后复制

mysql> drop database student;
Query OK, 3 rows affected (0.02 sec)

mysql> create database test;#命名可根据需要
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye

方法一：在数据库外导入
[root@server1 ~]# mysql -u root -p test < student-2020-11-02.sql #将刚刚备份的导入test
Enter password: 
方法二：在数据库内用source
#这里必须要use 数据库，否则无法有对应的数据库可以导入
mysql> use test;
mysql> source /student-2020-11-02.sql;

查看恢复结果
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| info              |
| test              |
| zf                |
+-------------------+
3 rows in set (0.00 sec)
mysql> select * from info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.

实例2：
备份多个数据库

登录后复制

[root@server1 ~]# mysqldump -uroot -pxzf729 --databases mysql student > mysql-student-bak.sql
#备份数据库mysql和student到当前目录下的mysql-student.sql，可查看到，也可加绝对路径保存
[root@server1 ~]# ll
总用量 48784
-rw-------.  1 root root      1878 8月  11 04:02 anaconda-ks.cfg
-rw-r--r--.  1 root root      1926 8月  11 04:49 initial-setup-ks.cfg
drwxr-xr-x. 38 7161 31415     4096 10月 22 11:11 mysql-5.7.20
-rw-r--r--.  1 root root  48833145 10月 22 10:31 mysql-boost-5.7.20.tar.gz   ####
-rw-r--r--.  1 root root   1101429 11月  2 12:14 mysql-student-bak.sql
......

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

误删除两个库

登录后复制

mysql> drop database mysql;
Query OK, 32 rows affected, 2 warnings (0.04 sec)
mysql> drop database student;
Query OK, 3 rows affected, 2 warnings (0.00 sec)

• 1.
• 2.
• 3.
• 4.

恢复：无需再创建数据库，可直接恢复

登录后复制

mysql> source /root/mysql-student-bak.sql
查看恢复结果
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
3 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.

五、增量备份的实例(基于完全备份)

增量备份分类：

• 一般恢复
  将所有备份的二进制日志内容全部恢复
• 断点恢复基于位置恢复：
  数据库在某一时间点可能既有错误的操作也有正确的操作
  可以基于精准的位置跳过错误的操作基于时间点恢复：
  跳过某个发生错误的时间点实现数据恢复

MySQL二进制日志对增量备份有重要的作用

• 二进制日志保存了所有更新或者可能更新数据库的操作；
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件；
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

修改配置文件，开启二进制日志功能

登录后复制

vi /etc/my.cnf
.....
[root@server1 ~]# [mysqld]
末尾加
log-bin=mysql-bin   前面是功能名称，后面是二进制日志文件名称
#重启数据库
[root@server1 ~]# systemctl restart mysqld
[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# ll
总用量 122924
-rw-r-----. 1 mysql mysql       56 10月 22 11:29 auto.cnf
-rw-r-----. 1 mysql mysql      917 11月  2 15:45 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibdata1
-rw-r-----. 1 mysql mysql 50331648 11月  2 15:45 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibtmp1
drwxr-x---. 2 mysql mysql     4096 11月  2 12:51 mysql
-rw-r-----. 1 mysql mysql      154 11月  2 15:45 mysql-bin.000001 #生成二进制日志文件
-rw-r-----. 1 mysql mysql       19 11月  2 15:45 mysql-bin.index

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.

以下所有的数据库操作(新增与误删除)，都被记录在000001文件里

登录后复制

#原有的记录，已做了完全备份
ysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
3 rows in set (0.00 sec)

#先做一些数据库操作
mysql> insert into student.info values(4,'yangli','南京');
Query OK, 1 row affected (0.02 sec)

#误删除了一条记录
mysql> delete from student.info where name='lisi';
Query OK, 1 row affected (0.01 sec)

#又插入了一条数据
mysql> insert into student.info values(5,'lili','南京');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
4 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.

恢复：

首先需要刷新二进制文件，000002里会存储后续的数据库操作，而不会存在000001里面了。

登录后复制

[root@server1 ~]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@server1 ~]# ll /usr/local/mysql/data/
总用量 122928
-rw-r-----. 1 mysql mysql       56 10月 22 11:29 auto.cnf
-rw-r-----. 1 mysql mysql      917 11月  2 15:45 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 11月  2 16:49 ibdata1
-rw-r-----. 1 mysql mysql 50331648 11月  2 16:49 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 10月 22 11:29 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 11月  2 15:45 ibtmp1
drwxr-x---. 2 mysql mysql     4096 11月  2 12:51 mysql
-rw-r-----. 1 mysql mysql     1019 11月  2 16:57 mysql-bin.000001##刷新之前的操作存储在这里
-rw-r-----. 1 mysql mysql      154 11月  2 16:57 mysql-bin.000002##新增的二进制文件
-rw-r-----. 1 mysql mysql       38 11月  2 16:57 mysql-bin.index

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.

查看二进制文件：

登录后复制

[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
使用mysqlbinlog工具查看日志文件

• 1.
• 2.
• 3.

每一个数据库操作，在二进制文件中都是一个事务，以begin开头，commit结尾，以之前插入yangli的数据操作为例，可查看到：

5.1 一般恢复

直接把整个二进制文件的内容进行恢复。

当前的状态：

登录后复制

mysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
4 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

误删除表之后，先用完全备份恢复，再用二进制文件恢复表至上述状态

登录后复制

mysql> drop database student;
Query OK, 0 rows affected (0.01 sec)
#完全备份
mysql> source /root/mysql-student-bak.sql;
mysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
+----+--------+---------+
#一般备份
[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -pxzf729
#查看恢复结果
mysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
4 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.

5.2 时间点恢复

从日志开头截止到某个时间点的恢复

登录后复制

mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

• 1.

从某个时间点到日志结尾的恢复

登录后复制

mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

• 1.

从某个时间点到某个时间点的恢复

登录后复制

mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

• 1.

想要恢复删除的lisi

查看二进制日志文件：000001

登录后复制

[root@server1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001

• 1.

记录两个时间点：

误操作之前的时间点：201102 16：48：37

正确操作开始的时间点：101102 16：49：18

跳过了误操作。

登录后复制

#删除坏表，完全备份恢复表
#首先从日志开头截止到某个时间点的恢复，即删除lisi操作之前的时间点
[root@server1 data]# mysqlbinlog --no-defaults --stop-datetime='2020-11-02 16:48:37' mysql-bin.000001 |mysql -uroot -pxzf729
#从某个时间点到日志结尾的恢复，即正确操作之后的日志恢复
[root@server1 data]# mysqlbinlog --no-defaults --start-datetime='2020-11-02 16:49:18' mysql-bin.000001 |mysql -uroot -pxzf729
#查看恢复结果
mysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
5 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.

5.3 位置点恢复

想要恢复删除的lisi
查看二进制文件：

记录两个位置点：

误操作之前的位置点 493

正确操作开始的位置点 700

登录后复制

#删除坏表，完全备份恢复表
#首先从日志开头截止到某个位置点的恢复，即删除lisi操作之前的位置点
#停止错误操作
[root@server1 data]# mysqlbinlog --no-defaults --stop-position='493' mysql-bin.000001 |mysql -uroot -pxzf729
#从某个位置点到日志结尾的恢复，即正确操作之后的日志恢复
#开始正确操作
[root@server1 data]# mysqlbinlog --no-defaults --start-position='700' mysql-bin.000001 |mysql -uroot -pxzf729
#查看恢复结果
mysql> select * from student.info;
+----+--------+---------+
| id | name   | address |
+----+--------+---------+
|  1 | lisi   | 苏州    |
|  2 | liqi   | 杭州    |
|  3 | wangwu | 北京    |
|  4 | yangli | 南京    |
|  5 | lili   | 南京    |
+----+--------+---------+
5 rows in set (0.00 sec)

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.