MySQL完全备份、增量备份与恢复

zj2092

　　在生产环境中，数据的安全性是至关重要的，任何数据的丢失都可能产生严重的后果。所以数据库需要备份。这里以新版mysql5.7为例。

一、数据库备份分类：

• 　　从物理与逻辑的角度，备份可分为物理备份和逻辑备份。
  　　（1）物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。又可以分为冷备份和热备份。
  　　冷备份：关闭数据库时进行的备份操作
  　　热备份：在数据库运行状态中进行备份操作，这种备份方法依赖于数据库的日志文件。
  　　（2）逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份。
  
  
• 　　从数据库的备份策略角度，备份可分为完全备份、差异备份、增量备份。
  　　（1）完全备份：每次对数据库进行完整的备份。可以备份单个数据库，多个数据库，所有数据库，也可以备份数据库中的单个表，多个表。
  　　（2）差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分内容，但是存储和恢复速度快。
  　　（3）增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。
  
  

二、完全备份与恢复

• 
  备份：
  　　使用mysqldump工具可以灵活的控制备份的内容，比如某几个表或库都可以单独备份。
  
  

　　1. 对单个库进行完全备份。命令格式如下：

　　mysqldump -u用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名

　　2. 对多个库进行完全备份。命令格式如下：

　　mysqldump -u用户名 -p[密码] [选项] --databases 库名1 库名2 ... > /备份路径/备份文件名

　　3. 对所有库进行完全备份。命令格式如下：

　　mysqldump -u用户名 -p[密码] --opt --all-databases > /备份路径/备份文件名

　　4. 对表结构进行完全备份。命令格式如下：

　　mysqldump -u用户名 -p[密码] -d 数据库名 表名 > /备份路径/备份文件名

　　5. 对表进行完全备份。命令格式如下：

　　mysqldump -u用户名 -p[密码] 数据库名 表名 > /备份路径/备份文件名

　　下面演练完全备份的实验。
　　完全备份单个库：
　　

[root@localhost ~]# mysqldump -uroot -pabc123 school > /opt/school-$(date +%F).sql　　
[root@localhost ~]# ls /opt/
　　
school-2018-07-19.sql
　　

　　完全备份单个表：
　　

[root@localhost ~]# mysqldump -uroot -pabc123 school info > /opt/school_info-$(date +%F).sql　　
[root@localhost ~]# ls /opt/
　　
school_info-2018-07-19.sql
　　

• 
  恢复：
  

　　1. 登录mysql，使用source命令恢复库。命令格式如下：

　　source 库备份脚本的路径

　　2. 在不登录MySQL的情况下，使用mysql命令直接恢复整库。命令格式如下：

　　mysql -u用户名 -p[密码] < 库备份脚本的路径

　　下面演练完全备份后恢复的实验。
　　恢复单个库：
　　假设数据库损坏，删除数据库school。
　　

[root@localhost ~]# mysql -uroot -p　　
Enter password:
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　

　　
mysql> drop database school;
　　
Query OK, 1 row affected (0.02 sec)
　　

　　
mysql> show databases;
　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
| performance_schema |
　　
| sys                |
　　
+--------------------+
　　
4 rows in set (0.00 sec)
　　

　　1）使用source命令恢复。
　　注意：先创建一个同名的数据库，再进入数据库，用surce命令恢复,否则会报错。
　　

mysql> create database school;　　
Query OK, 1 row affected (0.01 sec)
　　

　　
mysql> use school;
　　
Database changed
　　
mysql> source /opt/school-2018-07-19.sql
　　
Query OK, 0 rows affected (0.01 sec)
　　
...
　　
...
　　
Query OK, 2 rows affected (0.00 sec)
　　
Records: 2  Duplicates: 0  Warnings: 0
　　
...
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　2）使用mysql命令恢复。
　　再一次删掉数据库school。
　　注意：在恢复操作之前要创建一个同名的数据库，否则会报错。
　　

mysql> create database school;　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> exit
　　
Bye
　　
[root@localhost ~]# mysql -uroot -pabc123 school < /opt/school-2018-07-19.sql
　　

　　两种方法恢复完成后查看数据库恢复成功。

　　恢复单个表：
　　假设数据损坏，删除数据库school中的表info。
　　

mysql> use school;　　
Database changed
　　
mysql> select * from info;
　　
+----------+-------+
　　
| name     | score |
　　
+----------+-------+
　　
| zhangsan | 75.00 |
　　
| lisi     | 85.00 |
　　
+----------+-------+
　　
2 rows in set (0.00 sec)
　　

　　
mysql> drop table info;
　　
Query OK, 0 rows affected (0.01 sec)
　　

　　
mysql> show tables;
　　
Empty set (0.00 sec)
　　

　　1）使用source命令恢复。
　　

mysql> use school;　　
Database changed
　　
mysql> source /opt/school_info-2018-07-19.sql
　　
Query OK, 0 rows affected (0.00 sec)
　　
...
　　
...
　　
Query OK, 2 rows affected (0.00 sec)
　　
Records: 2  Duplicates: 0  Warnings: 0
　　
...
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　2）使用mysql命令恢复。
　　再一次删除表info,然后进行恢复操作。
　　

[root@localhost ~]# mysql -uroot -pabc123 school < /opt/school_info-2018-07-19.sql 　　

　　两种方法恢复完成后查看表info恢复成功。
　　

mysql> use school;　　
Database changed
　　

三、增量备份与恢复
　　MySQL没有提供直接的增量办法，但是可以通过对MySQL的二进制日志间接实现增量备份。二进制日志保存了所有更新或者可能更新数据库的操作。
　　特点：

• 没有重复数据，备份量不大，时间短
  
• 需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要进行逐个反推恢复，操作繁琐。
  

1.实现增量备份
　　（1）要进行MySQL增量备份，首先要开启二进制日志功能。
　　在mysql的配置文件的[mysqld]选项中加入log-bin=mysql-bin，然后重启服务。
　　

vim /etc/my.cnf　　[mysqld]
　　log-bin=mysql-bin
　　

systemctl restart mysqld.service　　

　　（2）使用mysqldump完全备份school库。
　　

[root@localhost ~]# mysqldump -uroot -pabc123 school > /opt/school-$(date +%F).sql　　
[root@localhost ~]# ls /opt/
　　
school-2018-07-19.sql
　　

　　（3）使用mysqladmin的选项flush-logs生成新的二进制文件，这样在插入新的数据后，新的二进制文件对应的就是数据库的变化的内容。
　　

[root@localhost ~]# cd /usr/local/mysql/data/　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.000001
　　
mysql-bin.index
　　
[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs    //进行日志分割
　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.000001
　　
mysql-bin.000002   //产生新的二进制文件mysql-bin.000002
　　
mysql-bin.index
　　

　　（4）插入一条新的数据，以模拟数据的增加或变更。
　　

mysql> use school;　　
Database changed
　　
mysql> insert into info (name,score) values ('tom','65');
　　
Query OK, 1 row affected (0.01 sec)
　　

　　
mysql> exit
　　
Bye
　　

　　此时的数据库变化保存在编号2 的二进制文件中，使用mysqlbinlog命令可以查看二进制文件的内容，里面保存了插入数据的语句。
　　

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

　　（5）再次执行flush- logs操作生成新的二进制文件，而新的二进制文件会保存之后的数据操作。
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.000001
　　
mysql-bin.000002
　　
mysql-bin.000003  //产生新的二进制文件mysql-bin.000003
　　
mysql-bin.index
　　

　　（6）再次向数据库插入一条数据。
　　

mysql> use school;　　
Database changed
　　
mysql> insert into info (name,score) values ('lucy','70');
　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> exit
　　
Bye
　　

　　（7）最后再执行一次flush-logs操作，确保之前的二进制文件不再发生变化。
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.000001
　　
mysql-bin.000002
　　
mysql-bin.000003
　　
mysql-bin.000004  //产生新的二进制文件，保存之后的数据操作语句
　　
mysql-bin.index
　　

2.丢失完全备份之后更改的数据的恢复
　　（1）使用delete删除插入的两条数据，也就是假设完全备份后的数据丢失了。
　　

mysql> use school;　　
Database changed
　　
mysql> delete from info where name='tom';
　　
Query OK, 1 row affected (0.01 sec)
　　

　　
mysql> delete from info where name='lucy';
　　
Query OK, 1 row affected (0.00 sec)
　　

　　（2）使用二进制文件恢复时需要注意的是恢复顺序，要先恢复最先生成的二进制文件，然后依次执行。
　　

[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002|mysql -uroot -pabc123　　
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003|mysql -uroot -pabc123
　　

　　查看数据库内容，完全备份之后新插入的两条数据找回来了，说明数据恢复成功。

3. 完全备份之后丢失所有数据的恢复
　　当完全备份和增量备份之后，所有数据丢失，需要把完全备份和所有增量备份文件逐个恢复。
　　（1）执行flush-logs操作分割日志，插入一条数据。
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.000001
　　
mysql-bin.000002
　　
mysql-bin.000003
　　
mysql-bin.000004
　　
mysql-bin.000005      //产生新的二进制文件，保存之后的数据操作语句
　　
mysql-bin.index
　　

mysql> use school;　　
Database changed
　　
mysql> insert into info (name,score) values ('jack','55');
　　
Query OK, 1 row affected (0.01 sec)
　　

　　
mysql> exit
　　
Bye
　　

　　这条插入语句保存在mysql-bin.000005二进制文件中。
　　（2）再执行一次flush-logs操作，插入一条数据，之后还要再执行一次flush-logs操作，确保插入数据保存在二进制文件中，不再改变。
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.000001
　　
mysql-bin.000002
　　
mysql-bin.000003
　　
mysql-bin.000004
　　
mysql-bin.000005
　　
mysql-bin.000006  //产生新的二进制文件，保存之后的数据操作语句
　　
mysql-bin.index
　　

mysql> use school;　　
Database changed
　　
mysql> insert into info (name,score) values ('xixi','45');
　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> exit
　　
Bye
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.000001
　　
mysql-bin.000002
　　
mysql-bin.000003
　　
mysql-bin.000004
　　
mysql-bin.000005
　　
mysql-bin.000006
　　
mysql-bin.000007 //产生新的二进制文件，保存之后的数据操作语句
　　
mysql-bin.index
　　

　　这条插入语句保存在mysql-bin.000006二进制文件中。
　　（3）使用drop删除表info，也就是假设完全备份前info的数据和完全备份后的数据都丢失了。
　　

mysql> use school;　　
Database changed
　　
mysql> drop table info;
　　
Query OK, 0 rows affected (0.03 sec)
　　

　　（4）先使用mysql命令进行完全备份的恢复操作。
　　

[root@localhost data]# mysql -uroot -pabc123 school < /opt/school-2018-07-19.sql　　

　　
[root@localhost data]# mysql -uroot -pabc123
　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
mysql> use school;
　　
Database changed
　　
mysql> select * from info;
　　
+----------+-------+
　　
| name     | score |
　　
+----------+-------+
　　
| zhangsan | 75.00 |
　　
| lisi     | 85.00 |
　　
| wangwu   | 95.00 |
　　
+----------+-------+
　　
3 rows in set (0.00 sec)
　　

　　被删除的表的数据又可以查询出来
　　（5）使用二进制文件恢复时需要注意的是恢复顺序，要先恢复最先生成的二进制文件，然后依次执行。
　　

[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000005|mysql -uroot -pabc123　　
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000006|mysql -uroot -pabc123
　　

　　可以看到数据全部恢复出来了。

4. 基于时间点与位置恢复
　　利用二进制日志可实现基于时间点与位置的恢复，例如由于误操作删除了一张表，这时完全恢复时没有用的，因为日志里面还存在误操作的语句。我们需要的是恢复到误操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句。
　　

mysql> use school;　　
Database changed
　　
mysql> select * from info;
　　
+----------+-------+
　　
| name     | score |
　　
+----------+-------+
　　
| zhangsan | 75.00 |
　　
| lisi     | 85.00 |
　　
| wangwu   | 95.00 |
　　
+----------+-------+
　　
3 rows in set (0.00 sec)
　　

　　首先对数据库进行备份。
　　

[root@localhost ~]# mysqldump -uroot -pabc123 school > /opt/school-$(date +%F).sql　　
[root@localhost ~]# ls /opt/
　　
school-2018-07-20.sql
　　

（1）基于时间点恢复
　　将某个起始时间的二进制日志导入数据库中，从而跳过某个发生错误的时间点实现数据的恢复。
　　1）执行flush-logs操作生成新的二进制文件。
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.0000020
　　//产生新的二进制文件，保存之后的数据操作语句
　　

　　2）插入两条数据，但由于误操作，两条插入语句中间删除了一条数据，而这条数据是不应该删除的。为了确保数据保存在二进制文件中不改变，执行一次flush-logs操作。
　　

mysql> use school;　　
Database changed
　　
mysql> insert into info (name,score) values ('coco','65');
　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> delete from info where name='lisi';
　　
Query OK, 1 row affected (0.06 sec)
　　

　　
mysql> insert into info (name,score) values ('momo','85');
　　
Query OK, 1 row affected (0.00 sec)
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.0000021
　　//产生新的二进制文件，保存之后的数据操作语句
　　

　　3）假设数据损坏，删除表info。
　　

mysql> use school;　　
Database changed
　　
mysql> drop table info;
　　
Query OK, 0 rows affected (0.01 sec)
　　

　　4）使用mysql命令进行完全备份的恢复操作。
　　

[root@localhost data]# mysql -uroot -pabc123 school < /opt/school-2018-07-20.sql　　

　　
[root@localhost data]# mysql -uroot -pabc123
　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
mysql> use school;
　　
Database changed
　　
mysql> select * from info;
　　
+----------+-------+
　　
| name     | score |
　　
+----------+-------+
　　
| zhangsan | 75.00 |
　　
| lisi     | 85.00 |
　　
| wangwu   | 95.00 |
　　
+----------+-------+
　　
3 rows in set (0.00 sec)
　　

　　5）查看数据操作语句保存的二进制文件mysql-bin.0000020。
　　

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

　　6）根据二进制文件的数据操作语句的时间点进行完全备份后增量备份的恢复操作。
　　

[root@promote data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000020 --stop-datetime='18-07-20 0:36:18'|mysql -uroot -pabc123　　

　　
[root@promote data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000020 --start-datetime='18-07-20 0:36:41'|mysql -uroot -pabc123
　　

　　

（2）基于位置恢复
　　使用基于时间点的恢复可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，基于位置是一种更为精确的恢复方式。
　　假设数据损坏，删除表info。**
　　

mysql> use school;　　
Database changed
　　
mysql> drop table info;
　　
Query OK, 0 rows affected (0.01 sec)
　　

　　使用mysql命令进行完全备份的恢复操作。
　　

[root@localhost data]# mysql -uroot -pabc123 school < /opt/school-2018-07-20.sql　　

　　
[root@localhost data]# mysql -uroot -pabc123
　　
mysql: [Warning] Using a password on the command line interface can be insecure.
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
mysql> use school;
　　
Database changed
　　
mysql> select * from info;
　　
+----------+-------+
　　
| name     | score |
　　
+----------+-------+
　　
| zhangsan | 75.00 |
　　
| lisi     | 85.00 |
　　
| wangwu   | 95.00 |
　　
+----------+-------+
　　
3 rows in set (0.00 sec)
　　

　　1）执行flush-logs操作生成新的二进制文件。
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.0000024
　　//产生新的二进制文件，保存之后的数据操作语句
　　

　　2）插入两条数据，但由于误操作，两条插入语句中间删除了一条数据，而这条数据是不应该删除的。为了确保数据保存在二进制文件中不改变，执行一次flush-logs操作。
　　

mysql> use school;　　
Database changed
　　
mysql> insert into info (name,score) values ('lili','65');
　　
Query OK, 1 row affected (0.00 sec)
　　

　　
mysql> delete from info where name='wangwu';
　　
Query OK, 1 row affected (0.06 sec)
　　

　　
mysql> insert into info (name,score) values ('mimi','85');
　　
Query OK, 1 row affected (0.00 sec)
　　

[root@localhost data]# mysqladmin -uroot -pabc123 flush-logs　　
[root@localhost data]# ls ./ | grep mysql.bin.*
　　
mysql-bin.0000025
　　//产生新的二进制文件，保存之后的数据操作语句
　　

　　3）查看数据操作语句保存的二进制文件mysql-bin.0000024。
　　

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

　　4）根据二进制文件的数据操作语句的时间点进行完全备份后增量备份的恢复操作。
　　

[root@promote data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000024 --stop-position='615'|mysql -uroot -pabc123　　

　　
[root@promote data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000024 --start-position='661'|mysql -uroot -pabc123