MySQL数据库的备份与恢复

一、MySQL数据库的备份

尽管采取了一些管理措施来保证数据库的安全，但是在不确定的意外情况下，总是有可能造成数据的损失。例如，意外的停电，不小心的操作失误等都可能造成数据的丢失。所以为了保证数据的安全，我们需要定期对数据进行备份。如果数据库中的数据出现了错误，就需要使用备份好的数据进行数据还原，这样可以将损失降至最低。

【1】数据的备份认识

数据备份是容灾的基础，是指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或阵列复制到其它的存储介质的过程。传统的数据备份主要是采用内置或外置的磁带机进行冷备份。但是这种方式只能防止操作失误等人为故障，而且其恢复时间也很长。随着技术的不断发展，数据的海量增加，不少的企业开始采用网络备份。网络备份一般通过专业的数据存储管理软件结合相应的硬件和存储设备来实现。

（1）数据备份之双机热备简介

双机热备只限定了高可用中的两台服务器。热备软件是用来解决一种不可避免的计划和非计划系统宕机问题的软件解决方案，当然也有硬件的。是构筑高可用集群系统的基础软件，对于任何导致系统宕机或服务中断的故障，都会触发软件流程来进行错误判定、故障隔离、以及通地联机恢复来继续执行被中断的服务。在这个过程中，用户只需要经受一定程度可接受的时延，而能够在最短的时间内恢复服务。
从狭义上讲，双机热备特指基于高可用系统中的两台服务器的热备（或高可用），因两机高可用在国内使用较多，故得名双机热备，双机高可用按工作中的切换方式分为：主-备方式（Active-Standby方式）和双主机方式（Active-Active方式），主-备方式即指的是一台服务器处于某种业务的激活状态（即Active状态），另一台服务器处于该业务的备用状态（即Standby状态)。而双主机方式即指两种不同业务分别在两台服务器上互为主备状态（即Active-Standby和Standby-Active状态）。 注：Active-Standby的状态指的是某种应用或业务的状态，并非指的是服务器状态。

（2）数据备份之双机热备方案方式

1】基于共享存储（磁盘阵列）的方式

共享存储方式主要通过磁盘阵列提供切换后，对数据完整性和连续性的保障。用户数据一般会放在磁盘阵列上，当主机宕机后，备机继续从磁盘阵列上取得原有数据。如下图所示。 这种方式因为使用一台存储设备，往往被业内人士称为磁盘单点故障。但一般来讲存储的安全性较高。所以如果忽略存储设备故障的情况下，这种方式也是业内采用最多的热备方式。

2】基于数据复制的方式

这种方式主要利用数据的同步方式，保证主备服务器的数据一致性。数据同步方式 基本于数据复制的方式有多种方法，其性能和安全也不尽相同，其主要方法有以下几种： A、单纯的文件方式的考贝不适用于数据库等应用，因为打开的文件是不能被复制的，如果要复制必须将数据库关闭，这显然是不可以的。以文件方式的复制主要适用于WEB页的更新，FTP上传应用，对主备机数据完整性，连续性要求不高的情况下使用。 B、利用数据库所带有复制功能。

【2】数据备份的策略

（1）完整备份（full backup）

把所有的数据完全的备份下来。每天对自己的系统进行完全备份。例如，星期一用一盘磁带对整个系统进行备份，星期二再用另一盘磁带对整个系统进行备份，依此类推。这种备份策略的好处是：当发生数据丢失的灾难时，只要用一盘磁带（即灾难发生前一天的备份磁带），就可以恢复丢失的数据。然而它亦有不足之处，首先，由于每天都对整个系统进行完全备份，造成备份的数据大量重复。这些重复的数据占用了大量的磁带空间，这对用户来说就意味着增加成本。其次，由于需要备份的数据量较大，因此备份所需的时间也就较长。对于那些业务繁忙、备份时间有限的单位来说，选择这种备份策略是不明智的。

（2）增量备份(incremental backup)

以完整备份或上一次增量备份的基础上只对增加的数据进行备份。星期天进行一次完全备份，然后在接下来的六天里只对当天新的或被修改过的数据进行备份。这种备份策略的优点是节省了磁带空间，缩短了备份时间。但它的缺点在于，当灾难发生时，数据的恢复比较麻烦。例如，系统在星期三的早晨发生故障，丢失了大量的数据，那么现在就要将系统恢复到星期二晚上时的状态。这时系统管理员就要首先找出星期天的那盘完全备份磁带进行系统恢复，然后再找出星期一的磁带来恢复星期一的数据，然后找出星期二的磁带来恢复星期二的数据。很明显，这种方式很繁琐。另外，这种备份的可靠性也很差。在这种备份方式下，各盘磁带间的关系就象链子一样，一环套一环，其中任何一盘磁带出了问题都会导致整条链子脱节。比如在上例中，若星期二的磁带出了故障，那么管理员最多只能将系统恢复到星期一晚上时的状态。

（3）差异备份(differential backup)

以完整备份为参照和现在的数据进行比较，把与完整备份不同的数据进行备份。管理员先在星期天进行一次系统完全备份，然后在接下来的几天里，管理员再将当天所有与星期天不同的数据（新的或修改过的）备份到磁带上。差分备份策略在避免了以上两种策略的缺陷的同时，又具有了它们的所有优点。首先，它无需每天都对系统做完全备份，因此备份所需时间短，并节省了磁带空间，其次，它的灾难恢复也很方便。系统管理员只需两盘磁带，即星期天的磁带与灾难发生前一天的磁带，就可以将系统恢复。

【3】数据备份的配置方法

（1）物理冷备

备份时数据库处于关闭状态，直接打包数据库文件，备份速度快。
备份：

[root@localhost ~]# systemctl stop mysqld.service 
[root@localhost ~]# mkdir -p /opt/backup
[root@localhost opt]# cd /usr/local/mysql/
[root@localhost opt]# tar zcvf /opt/backup/stu_sql-$(date +%F).tar.gz data

恢复：

[root@localhost ~]#tar zxvf  /opt/backup/stu_sql-2020-08-19.tar.gz -C /usr/local/mysql/

（2）MySQL完整备份与恢复

使用专用备份逻辑工具mysqldump，是对整个数据库、数据库结构和数据的备份，保存的是完整时刻的数据库，是差异备份与增量备份的基础。但是数据存在大量的重复，占用大量的备份空间，而且备份时间和恢复时间长。

1】备份整个数据库：

[root@localhost ~]# mysqldump -uroot -p school > /opt/school.sqlbak
//school是数据库
Enter password: （数据库密码）

恢复整个数据库方法一：

mysql> create database school;
mysql> use school;
mysql> source /opt/school.sqlbak;

恢复整个数据库方法二：

[root@localhost opt]#mysql -uroot -p </opt/school.sqlbak;
Enter password:

2】备份多个数据库：

[root@localhost opt]# mysqldump -uroot -p --databases school comany >/opt/school_comany.sqlbak
//school和comany都是数据库
Enter password:（数据库密码）

恢复多个数据库方法一：

mysql> create database school;
mysql> use school;
mysql> source /opt/school_comany.sqlbak;

恢复方法二：

[root@localhost opt]#mysql -uroot -p </opt/school_comany.sqlbak;
Enter password:

3】备份表

[root@localhost opt]# mysqldump -uroot -p school teacher > /opt/rowteach.sqlbak;
//school是数据库teacher是表
Enter password:

恢复表方法一：

mysql> use school;
mysql> source  /opt/rowteach.sqlbak;

恢复方法二：

[root@localhost opt]#mysql -uroot -p </opt/rowteach.sqlbak
Enter password:

4】对所有库进行备份

[root@localhost opt]# mysqldump -uroot -p --all-databases >/opt/all.sqlbak
Enter password:

恢复方法一：

mysql> source  /opt/all.sqlbak;

恢复方法二：

[root@localhost opt]#mysql -uroot -p </opt/all.sqlbak
Enter password:

（3）MySQL增量备份与恢复

MySQL没有提供直接的增量备份的方法，但可以通过MySQL提供的二进制日志间接实现增量备份。而MySQL二进制日志对备份的意义有：
二进制日志保存了所有更新或者可能更新数据库的操作；二进制日志启动MySQL服务器开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件；只需要定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。下面两种方式是根据断点来执行恢复，跳过错误的操作。

1】基于位置恢复

1）完整备份

mysql> select * from student;
+----+----------+-------+
| id | name     | socre |
+----+----------+-------+
|  2 | lisi     | 99.00 |
|  1 | zhangsan | 88.00 |
+----+----------+-------+
2 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -uroot -p school > /opt/school.sqlbak
//school是数据库
Enter password: （数据库密码）
[root@localhost data]# cd /opt
[root@localhost opt]# ls
school.sqlbak

2）开启MySQL数据库日志

[root@localhost opt]# vim /etc/my.cnf
[mysqld]
...........................
log-bin=mysql-bin
.......................
[root@localhost opt]# systemctl restart mysqld.service

[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
mysql-bin.000001

3）添加数据，模拟误操作

mysql> select * from student;
+----+----------+-------+
| id | name     | socre |
+----+----------+-------+
|  2 | lisi     | 99.00 |
|  1 | zhangsan | 88.00 |
+----+----------+-------+
2 rows in set (0.01 sec)

mysql> insert into student values(05,'tianqi',77);
Query OK, 1 row affected (0.00 sec)

mysql> delete from student where name='zhangsan';
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(06,'qianba',78);
Query OK, 1 row affected (0.00 sec)

4）刷新MySQL数据库

[root@localhost data]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@localhost data]# ls
mysql-bin.000002 mysql-bin.000001

5）保存日志文件并转化成64位简码

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 > /opt/baksql.01
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/baksql.02
[root@localhost data]# cd /opt
[root@localhost opt]# ls
baksql.02     school.sqlbak   baksql.01

6）删除student表

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

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| teacher          |
+------------------+
1 row in set (0.00 sec)

7）查看简码日志利用at位置点恢复

mysql> source /opt/school.sqlbak
[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='571' /usr/local/mysql/data/mysql-bin.000001 |mysql -uroot -p
Enter password: 
[root@localhost opt]# mysqlbinlog --no-defaults --start-position='681' /usr/local/mysql/data/mysql-bin.000001 |mysql -uroot -p
Enter password: 

mysql> select * from student;
+----+----------+-------+
| id | name     | socre |
+----+----------+-------+
|  2 | lisi     | 99.00 |
|  1 | zhangsan | 88.00 |
|  5 | tianqi   | 77.00 |
|  6 | qianba   | 78.00 |
+----+----------+-------+
4 rows in set (0.00 sec)

2】基于时间点恢复

1）完整备份

mysql> select * from student;
+----+----------+-------+
| id | name     | socre |
+----+----------+-------+
|  2 | lisi     | 99.00 |
|  1 | zhangsan | 88.00 |
+----+----------+-------+
2 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -uroot -p school > /opt/school.sqlbak
//school是数据库
Enter password: （数据库密码）
[root@localhost data]# cd /opt
[root@localhost opt]# ls
school.sqlbak

2）开启MySQL数据库日志

[root@localhost opt]# vim /etc/my.cnf
[mysqld]
...........................
log-bin=mysql-bin
.......................
[root@localhost opt]# systemctl restart mysqld.service

[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
mysql-bin.000001

3）添加数据，模拟误操作

mysql> select * from student;
+----+----------+-------+
| id | name     | socre |
+----+----------+-------+
|  2 | lisi     | 99.00 |
|  1 | zhangsan | 88.00 |
+----+----------+-------+
2 rows in set (0.01 sec)

mysql> insert into student values(05,'tianqi',77);
Query OK, 1 row affected (0.00 sec)

mysql> delete from student where name='zhangsan';
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(06,'qianba',78);
Query OK, 1 row affected (0.00 sec)

4）刷新MySQL数据库二进制文件

[root@localhost data]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@localhost data]# ls
mysql-bin.000002 mysql-bin.000001

5）保存日志文件并转化成64位简码

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 > /opt/baksql.01
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/baksql.02
[root@localhost data]# cd /opt
[root@localhost opt]# ls
baksql.02     school.sqlbak   baksql.01

6）删除student表

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

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| teacher          |
+------------------+
1 row in set (0.00 sec)

7）查看简码日志利用时间点恢复（注意时间表示格式）

mysql> source /opt/school.sqlbak
[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2020-08-23 8:52:03' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p
Enter password: 
[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2020-08-23 8:52:27' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p
Enter password: 

mysql> select * from student;
+----+----------+-------+
| id | name     | socre |
+----+----------+-------+
|  2 | lisi     | 99.00 |
|  1 | zhangsan | 88.00 |
|  5 | tianqi   | 77.00 |
|  6 | qianba   | 78.00 |
+----+----------+-------+
4 rows in set (0.00 sec)