mysql备份与恢复

一、mysqldump备份结合binlog日志恢复
mysql备份一般采用全库加日志备份的方式，例如每天在执行一次全备份，每小时执行一次二进制日志备份，这样在mysql故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。
1、binlog介绍
mysql的二进制日志记录着该数据库的所有增删改的操作日志（但是前提一定要在自己的服务器上开启binlog），它还包括了这些操作的执行时间，为了显示这些二进制内容，我们可以使用mysqlbinlog这条命令来查看。
binlog的用途
1）：主从同步，也就是咱们常用的mysql主从复制，这想必大家应该都知道的。
2）：恢复数据库功能。

开启binarylog功能

我们可以通过编辑/etc/my.cnf主配置文件中的log-bin选项就可以开启二进制日志，如下：

vim /etc/my.conf

mysql备份与恢复

注意：在我开启了二进制后，重启mysql的时候会出错，这个问题是server-id导致的，所以我们也需要开启server-id后边的数字可以随便写！

在开启了二进制后我们看可以看到000001,000002这样的二进制文件，在我们每次重启mysql服务，或者执行flush log；它都会产生新的二进制文件，同时还会生成一个filename.index的文件，这个文件中存储着所有二进制文件的清单，又称为二进制文件的索引，如下：

mysql备份与恢复

配置重启mysql后我们可以通过show variables like “log_bin”来查看bin-log是否开启 如下：

mysql>show variables like "log-bin"

mysql备份与恢复

1、接下来我们对数据库进行增删改的操作，否则log里面的数据有点空（以下操作不进入数据库使用-e参数)

[root@master ~]# mysql -uroot -ppwd123 -e "reset master"  
[root@master ~]# mysql -uroot -ppwd123 -e "create database test"  
[root@master ~]# mysql -uroot -ppwd123 -e "use test;create table tb1(id int primary key auto_increment,name varchar(20))"  
[root@master ~]# mysql -uroot -ppwd123 -e "insert into test.tb1(name) values('lisi')"  
[root@master ~]# mysql -uroot -ppwd123 -e "insert into test.tb1(name) values('zhangsan')"

解释如下：

1：创建一个新的日志文件 起始值从000001 开始

2：创建一个库，库名称为test

3：进入test库，创建了一个tb1的表

4：在tb1表中插入了名字为lisi的数据 

5：在tb1表中插入了名字为zhangsan的数据 

mysql备份与恢复

2、重新开始一个新的日志文件

[root@master ~]# mysql -uroot -ppwd123 -e "flush logs"  
[root@master ~]# mysql -uroot -ppwd123 -e "delete from test.tb1 where id=2"  
[root@master ~]# mysql -uroot -ppwd123 -e "insert into test.tb1(name) values('tom')"  
[root@master ~]# mysql -uroot -ppwd123 -e "select * from test.tb1"

解释如下：

1：开启新的二进制文件，也就是说以上操作都会记录到0.00002中。

2：删除tb1表中的id为2的数据，也就是删除了zhangsan

3：插入一条数据到tb1表中，名字为tom

4：查看tb1表中的数据

mysql备份与恢复

3、查看mysql服务上的二进制日志

mysql> show binary logs;

mysql备份与恢复

查看二进制日志中的事件，如下：

mysql> show binlog events;

mysql备份与恢复

在输入show binlog events；的时候我们发现它默认查看的是000001的事件，以上图中从4开始记录，在219-313的时候执行了一条create database test；在378-520的时候执行了创建tb1的表，在最后1087的时候刷新了bin-log日志开启了新的日志也就是000002

这时候我们在查看000002的事件，在上面我们刷新了日志后执行了删除库的操作，按道理里来说，在000002中那么就肯定能够看到删除库的操作了，查询命令如下：

mysql> show binlog events in 'mysql-bin.000002';

mysql备份与恢复

在以上000002事件中我们发现，并没有很清楚的可以看到delete等操作

这时候我们可以切换到binlog所在的目录下查看

mysqlbinlog -v mysql-bin.000001

mysqlbinlog -v mysql-bin.000002

这样我们会更加详细的看到000001&&000002的事件，例如在000001中我们在219的时候创建了一个库如下：

mysql备份与恢复

接下来我们看000002的事件

mysql备份与恢复

上述描述了执行删除的开始为287，在416结束，其中删除了tb1表中的数据 zhangsan，那么知道这些信息后，我们就可以恢复了

恢复zhangsan用户：

由于之前没有做过全库的备份，所以要使用所有的binlog日志恢复，所以在生产环境中需要很长的时间恢复，导出相关的binlog文件

[root@master ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin001.sql  
[root@master ~]# mysqlbinlog --stop-position=287 /usr/local/mysql/data/mysql-bin.000002 > /opt/287.sql  
[root@master ~]# mysqlbinlog --start-position=416 /usr/local/mysql/data/mysql-bin.000002 > /opt/416.sql

解释：在以上第一行就是把000001备份到/opt下，我们都知道000001执行的操作有 创建库创建表插入数据

   在第二行执行的操作是在287之前所有的操作都备份 从416以后的操作数据也都备份，因为在287-416的中间执行了删除zhangsan的操作，所以不备份287-416中间操作的数据，直接把删除命令跳过。

备份完成后我们删除test库，然后进行还原.

mysql> show databases;  
+--------------------+  
| Database           |  
+--------------------+  
| information_schema |  
| mysql              |  
| performance_schema |  
| sys                |  
| test               |  
+--------------------+  
5 rows in set (0.36 sec)  

mysql> drop database test;  
Query OK, 1 row affected (0.01 sec)

[root@master ~]# mysql -uroot -ppwd123 < /opt/mysql-bin001.sql

[root@master ~]# mysql -uroot -ppwd123 < /opt/287.sql

[root@master ~]# mysql -uroot -ppwd123 < /opt/416.sql

恢复完成后查看是否成功

mysql备份与恢复