目录

一、完全备份、差异备份与增量备份概述三者的特点三者的区别举例二、完全备份实例2.1 冷备份与恢复2.2 mysqldump备份与恢复2.2.1单库备份与恢复2.2.2多库备份2.2.3所有库备份2.2.4对数据库表数据备份三、增量备份实例3.1增量备份3.2基于一般恢复3.3基于位置恢复3.4基于时间点恢复

一、完全备份、差异备份与增量备份概述

三者的特点

完全备份：每次对数据库进行完整的备份（包括表的结构和数据）

特点：备份与恢复操作简单，但占用大量备份空间，数据重复率高，冗余数据多，备份与恢复时间长

差异备份：以完全备份为基准，备份自从上次完全备份之后被修改过的文件

特点：占用空间小，但是安全性变差

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

特点：无冗余数据，依靠二进制日志文件进行逐次增量备份，单个文件丢失则数据不完整，安全性低

三者的区别举例

二、完全备份实例

2.1 冷备份与恢复

数据库所有的数据在这个目录里，直接整个目录打包（需要关闭数据库，基本不用）

[root@host3 data]# service mysqld stop //先关闭数据库Redirecting to /bin/systemctl stop mysqld.service[root@host3 data]# mkdir /opt/backup //创建备份目录，[root@host3 data]# tar zcvf /opt/backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data///把整个数据目录打包[root@host3 data]# cd /opt/backup[root@host3 backup]# lltotal 1384-rw-r--r-- 1 root root 1413851 Oct 13 20:03 mysql_all_-10-13.tar.gz

当数据库故障时，直接把压缩包解压，数据挪回data目录下

2.2 mysqldump备份与恢复

2.2.1单库备份与恢复

mysqldump -u 用户 -p[密码，不写则进行交互] 库名 > 保存的位置（文件以sql格式结尾）

[root@host3 mysql]# mysqldump -uroot -p school > /opt/school.sqlEnter password: [root@host3 mysql]# cd /opt[root@host3 opt]# lltotal 47700drwxr-xr-x 38 7161 314154096 Oct 14 10:22 mysql-5.7.20-rw-r--r-- 1 root root 48833145 Oct 23 mysql-boost-5.7.20.tar.gzdrwxr-xr-x. 2 root root 6 Oct 31 rh-rw-r--r-- 1 root root2144 Oct 14 14:05 school.sql

查看sql文件可知，没有备份创建数据库的语句，所以恢复的时候需要先创建数据库，再恢复

制造故障：

mysql> drop database school;Query OK, 1 row affected (0.01 sec)直接恢复mysql> source /opt/school.sqlERROR 1046 (3D000): No database selected 报错，没有数据库可选Query OK, 0 rows affected (0.00 sec)

单库正确恢复方式

mysql> create database school; 数据库名可根据需求取Query OK, 1 row affected (0.00 sec)mysql> use school;Database changed方法一：在数据库内用source语句恢复mysql> source /opt/school.sqlQuery OK, 9 rows affected (0.00 sec)Records: 9 Duplicates: 0 Warnings: 0查看，已恢复mysql> show tables;+------------------+| Tables_in_school |+------------------+| info |+------------------+1 row in set (0.00 sec)mysql> select * from info;+----+----------+-------+-------+------+| id | name| hobby | score | addr |+----+----------+-------+-------+------+| 1 | zhangsan |1 | 88 | NULL || 2 | lisi|2 | 66 | NULL || 3 | wangwu |2 | 77 | NULL || 4 | zhaoliu |1 | 80 | NULL || 5 | tianqi |3 | 50 | NULL || 6 | liyu|1 | 90 | NULL || 7 | wooo|1 | 99 | NULL || 8 | wooooo |1 | 99 | NULL || 9 | owoo|1 | 99 | NULL |+----+----------+-------+-------+------+9 rows in set (0.00 sec)方法二：用linux命令mysql进行恢复mysql> drop table info; //把表删了Query OK, 0 rows affected (0.02 sec)mysql> show tables;Empty set (0.00 sec)mysql> exit;Bye[root@host3 opt]# mysql -uroot -p school < /opt/school.sql //反向导入恢复Enter password: [root@host3 opt]# mysql -uroot -p -e 'show tables from school' //查看Enter password: +------------------+| Tables_in_school |+------------------+| info |+------------------+

2.2.2多库备份

mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名

[root@host3 opt]# mysqldump -uroot -p --databases mysql school > /opt/mysql-school.sqlEnter password: [root@host3 opt]# cd /opt[root@host3 opt]# lltotal 49684drwxr-xr-x 38 7161 314154096 Oct 14 10:22 mysql-5.7.20-rw-r--r-- 1 root root 48833145 Oct 23 mysql-boost-5.7.20.tar.gz-rw-r--r-- 1 root root 1098302 Oct 14 14:26 mysql-school.sqldrwxr-xr-x. 2 root root 6 Oct 31 rh-rw-r--r-- 1 root root2144 Oct 14 14:05 school.sql

进去文件内查看，可以看到，在多库备份的时候，备份了创建数据库的语句，所以恢复的时候可以直接恢复

2.2.3所有库备份

mysqldump [选项] --all-databases > /备份路径/备份文件名

[root@host3 opt]# mysqldump -uroot -p --all-databases > /opt/all-data.sqlEnter password: [root@host3 opt]# lsall-data.sql mysql-boost-5.7.20.tar.gz rhmysql-5.7.20 mysql-school.sql school.sql

2.2.4对数据库表数据备份

mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名

[root@host3 opt]# mysqldump -uroot -p school info > /opt/school-info.sqlEnter password:

三、增量备份实例

3.1增量备份

增量备份基于二进制日志文件备份，二进制日志在启动mysql服务器后开始记录，并在文件达到二进制日志所设置的最大值或者接受到flush logs命令后重新创建新的日志文件，生成二进制的文件序列，并及时把这些日志文件保存到安全的存储位置，即可完成一个时间段的增量备份。

先进入f文件

重启数据库刷新

接下来关于所有的数据库操作，都被记录在000001文件里。

进行一系列正确错误操作，目的是加入第十条

ysql> select * from info;+----+----------+-------+-------+------+| id | name| hobby | score | addr |+----+----------+-------+-------+------+| 1 | zhangsan |1 | 88 | NULL || 2 | lisi|2 | 66 | NULL || 3 | wangwu |2 | 77 | NULL || 4 | zhaoliu |1 | 80 | NULL || 5 | tianqi |3 | 50 | NULL || 6 | liyu|1 | 90 | NULL || 7 | wooo|1 | 99 | NULL || 8 | wooooo |1 | 99 | NULL || 9 | owoo|1 | 99 | NULL |+----+----------+-------+-------+------+9 rows in set (0.00 sec)mysql> delete from info where name='lisi';Query OK, 1 row affected (0.00 sec)mysql> insert into info (name,hobby,score) values ('zhaosi',2,66);Query OK, 1 row affected (0.00 sec)mysql> delete from info where hobby=1;Query OK, 6 rows affected (0.00 sec)mysql> select * from info;+----+--------+-------+-------+------+| id | name | hobby | score | addr |+----+--------+-------+-------+------+| 3 | wangwu |2 | 77 | NULL || 5 | tianqi |3 | 50 | NULL || 10 | zhaosi |2 | 66 | NULL |+----+--------+-------+-------+------+3 rows in set (0.00 sec)误删了很多数据

想要通过增量备份恢复正确的数据

先刷新二进制日志文件

mysqladmin -uroot -p flush-logsEnter password:

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001使用mysqlbinlog工具查看日志文件

每一次命令，都是一次事务的提交

3.2基于一般恢复

mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p

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

mysql> select * from info;当前内容+----+--------+-------+-------+------+| id | name | hobby | score | addr |+----+--------+-------+-------+------+| 3 | wangwu |2 | 77 | NULL || 5 | tianqi |3 | 50 | NULL || 10 | zhaosi |2 | 66 | NULL |+----+--------+-------+-------+------+mysql> drop table info;Query OK, 0 rows affected (0.02 sec)先把表删了，模拟故障

先进行完全备份的恢复mysql> source /opt/school-info.sqlmysql> select * from info;+----+----------+-------+-------+------+| id | name| hobby | score | addr |+----+----------+-------+-------+------+| 1 | zhangsan |1 | 88 | NULL || 2 | lisi|2 | 66 | NULL || 3 | wangwu |2 | 77 | NULL || 4 | zhaoliu |1 | 80 | NULL || 5 | tianqi |3 | 50 | NULL || 6 | liyu|1 | 90 | NULL || 7 | wooo|1 | 99 | NULL || 8 | wooooo |1 | 99 | NULL || 9 | owoo|1 | 99 | NULL |+----+----------+-------+-------+------+再进行一般恢复[root@host3 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -pEnter password: 查看，所有操作都恢复了[root@host3 data]# mysql -uroot -p -e 'select * from school.info'Enter password: +----+--------+-------+-------+------+| id | name | hobby | score | addr |+----+--------+-------+-------+------+| 3 | wangwu |2 | 77 | NULL || 5 | tianqi |3 | 50 | NULL || 10 | zhaosi |2 | 66 | NULL |+----+--------+-------+-------+------+

3.3基于位置恢复

通过查看文件，可以得出

只有第二次是正确操作，第一次和第三次都误删数据

1、恢复数据到指定位置

mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

和一般恢复一样，先进行完全备份，变成这样mysql> select * from info;+----+----------+-------+-------+------+| id | name| hobby | score | addr |+----+----------+-------+-------+------+| 1 | zhangsan |1 | 88 | NULL || 2 | lisi|2 | 66 | NULL || 3 | wangwu |2 | 77 | NULL || 4 | zhaoliu |1 | 80 | NULL || 5 | tianqi |3 | 50 | NULL || 6 | liyu|1 | 90 | NULL || 7 | wooo|1 | 99 | NULL || 8 | wooooo |1 | 99 | NULL || 9 | owoo|1 | 99 | NULL |+----+----------+-------+-------+------+只进行第一次的操作恢复mysqlbinlog --no-defaults --stop-position='434' mysql-bin.000001 |mysql -uroot -pEnter password:

2、从指定的位置开始恢复数据

mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

从第二次操作开始

[root@host3 data]# mysqlbinlog --no-defaults --start-position='499' mysql-bin.000001 |mysql -uroot -pEnter password:

3.4基于时间点恢复

删除表，恢复完全备份后

mysql> select * from info;+----+----------+-------+-------+------+| id | name| hobby | score | addr |+----+----------+-------+-------+------+| 1 | zhangsan |1 | 88 | NULL || 2 | lisi|2 | 66 | NULL || 3 | wangwu |2 | 77 | NULL || 4 | zhaoliu |1 | 80 | NULL || 5 | tianqi |3 | 50 | NULL || 6 | liyu|1 | 90 | NULL || 7 | wooo|1 | 99 | NULL || 8 | wooooo |1 | 99 | NULL || 9 | owoo|1 | 99 | NULL |+----+----------+-------+-------+------+

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

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

只进行第一次操作[root@host3 data]# mysqlbinlog --no-defaults --stop-datetime='-10-14 16:31:50' mysql-bin.000001 |mysql -uroot -pEnter password:

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

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

进行后面的操作

Enter password:

[root@host3 data]# mysqlbinlog --no-defaults --start-datetime='-10-14 16:31:50' mysql-bin.000001 |mysql -uroot -p

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

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

恢复所有正确操作，不回复误删的操作

完全备份恢复之后

直接进行第二步操作

[root@host3 data]# mysqlbinlog --no-defaults --start-datetime='-10-14 16:31:50' --stop-datetime='-10-14 16:33:38' mysql-bin.000001 |mysql -uroot -pEnter password:

恢复正确数据