mysql 基于binlog恢复

      在日常操作MySQL的过程中可能会遇到因为操作失误导致数据丢失，由于操作之前没有进行备份，而最近备份的文件时间又早，很可能导致备份之后到现在这段时间数据的丢失，那么如何应对这种突发状况？其实mysql已经给我们提供了应对这种情况的功能，只不过这项功能默认没有开启，平时又用不到，因此没有对它进行了解，下面我们就来认识一下它吧。    

 什么是binlog？

    binlog，也称为二进制日志，记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中，可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql的复制（主主数据库的复制、主从数据库的复制）。

如何开启binlog？

    首先我们可以进入mysql输入命令

我们可以通过这个命令来查询关于binlog相关的设置，其中有一个log_bin选项，如果为off，那么证明我们的binlog没有开启，如果为on证明我们的binlog已经开启。

开启binlog的方法很简单，只需要打开mysql的配置文件my.ini（也可能是my.cnf），找到log-bin，去掉前面的#号，如果没有该选项，则可以手动添加。值得注意的是开启log-bin，记得添加server-id，不然mysql服务起不来。

其中mysql-bin就是日志文件的名称了，日志文件的名称和路径都可以自定义，如果不配置路径和名称，那么该文件会出现在mysql/data目录下，名称为mysql-bin.xxxxxx。

    添加完成后重启mysql，我们就会在mysql/data目录下找到binlog日志文件了，首次使用binlog的时候会出现两个文件，一个是mysql-bin.000001，一个是mysql-bin.index，其中，000001结尾的文件就是我们需要的日志文件，它包含了我们数据库的所有增，改，删操作（查询操作不做记录），以index结尾的文件是索引文件，包含了所有的以000xxx结尾的日志文件。

如何使用mysqlbinlog查询操作记录？

         开启binlog后mysql会自动为了记录以后增，改，删操作，关于备份操作无需我们手动操作，我们只要在需要恢复数据的时候查找对应的数据即可，由于binlog存储的格式为二进制，因此我们无法直接使用，需要借助mysql提供的工具mysqlbinlog（mysqlbinlog在mysql安装目录下的bin目录下）。初次接触一个命令不知道如何使用的时候，我们可以通过帮助命令查看它如何使用

1. mysqlbinlog --help  

   1.读取所有数据库的操作

[sql] view plain copy

1. mysqlbinlog /data/mysql-bin.000001 
2. mysqlbinlog /data/mysql-bin.000001   -vv 显示详细
3. mysqlbinlog /data/mysql-bin.000001   -vv 显示详细并去掉注释
4. 
   

通过该命令，我们可以看到该日志文件中记录的所有数据库的增，该，删操作。

    2.查询指定数据库的操作

[sql] view plain copy

1. mysqlbinlog --database=test   /data/mysql-bin.000001  

通过该命令，我们可以查询数据库名称为test的增，该，删操作

    3.查询指定位置的操作

    binlog每次进行记录的时候都会为其标注一个position，用于标识该操作所在的位置，与之相关的参数为--start-position（开始位置）和--stop-position（结束位置），我们可以通过position进行指定操作的查询。

如何获取position的值？

只需要在mysql中使用show binlog events即可，每一行都记录了一条操作，其中Pos就是该操作的start-position，End_log_pos就是stop-position。我们如果需要查询上述图片中的操作，可以使用以下语句：

  mysqlbinlog --start-position=293 --stop-position=348 /data/mysql-bin.000001 

   4.查询指定时间的操作

    除了有位置标识外，binlog还有时间标识，参数为--start-datetime（开始时间）和--stop-datetime（结束时间），如果想要查询某个时间段的操作，可以使用该参数。

1. mysqlbinlog --start-datetime="2015-08-08 10:00:00" --stop-datetime="2015-08-08 12:00:00"   /data/mysql-bin.000001  

   常用的查询操作也就这么多了，查询操作不是我们的目的，恢复记录才是我们的目的，一切的查询都是为了恢复。

如何恢复删除记录？

    如果你对于查询操作了如指掌了，那么恢复操作就简单的多了，因为恢复数据就是在查询的基础上，恢复的方法大致分为两种：

 1.直接使用mysqlbinlog进行查询带恢复

mysqlbinlog --start-position=4 --stop-position=98  /data/mysql-bin.000001 | mysql -u root -p 

    上述命令跟查询操作不同的地方在于尾部添加了“| mysql -u root -p”，该命令是用于连接数据库，整条命令连接起来就是恢复开始位置为4，结束位置为98的所有操作（不限于单行记录的恢复，如果想要用于连续的多行操作，只需要把最后的结束位置设置为最后一个需要进行恢复的行的End_log_pos即可）

2.先导出查询记录，然后通过mysql source操作进行数据恢复

mysqlbinlog --start-position=4 --stop-position=98 ../data/mysql-bin.000001 > test.sql

mysql>source test.sql  

  恢复数据实例

    首先开启binlog，然后新建一个logtest数据库，然后新建一个test数据表，插入几条测试数据，然后删除，此时我们使用binlog来恢复数据。

1、查看   mysql -uroot -p -S /data/mysql3309/mysql3309.sock -e 'show binlog events\G;'  

*************************** 11. row ***************************
   Log_name: mysql-bin.000001
        Pos: 617
 Event_type: Delete_rows
  Server_id: 9
End_log_pos: 661
       Info: table_id: 118 flags: STMT_END_F

------------------------------------------------------------------------------

Log_name:此条log存在那个文件中，从上面可以看出这2条log皆存在与mysql_bin.000001文件中。 
Pos:log在bin-log中的开始位置 
Event_type:log的类型信息 
Server_id:可以查看配置中的server_id,表示log是那个服务器产生 
End_log_pos：log在bin-log中的结束位置 
Info:log的一些备注信息，可以直观的看出进行了什么操作 

----------------------------------------------------------------------------------------------------------

2、恢复 mysqlbinlog  --stop-position=617  /data/mysql3309/data/mysql-bin.000001  | mysql -uroot -p123456  -S /data/mysql3309/mysql3309.sock

 当然使用binlog恢复的时候一定要注意几个问题：

    问题一：为什么使用mysqlbinlog --database=logtest ../data/mysql-bin.000001 | mysql -u root -p恢复数据不管用？

    其实并非该命令出错，而是日志中同时记录了最后一个drop操作，在没有选在position和datetime的情况下，binlog会读取有关该数据库的所有记录，从create到drop，使用该命令的情况下会重新把create到drop的语句再执行一遍，而在我们看来跟什么都没有做差不多，但是你再次使用mysqlbinlog查看该数据库的相关操作记录的时候就会发现，从create到drop的操作出现了两次，证明该命令的确被执行过，不过由于最后有drop语句，因此被恢复的数据库信息又被删除，回到没有恢复的状态。因此该命令不适用于数据的恢复，想要恢复数据，还要根据情况选择指定的时间或者位置。

    问题二：为什么使用position参数的时候数据恢复不完全？

     选择position的时候也是非常有讲究的，就拿上图来说，如果执行命令语句的时候，只是执行到492

[sql] view plain copy

1. mysqlbinlog --database=logtest --start-position=98 --stop-position=492 ../data/mysql-bin.000001 | mysql -u root -p  

那么进行数据恢复的时候只能恢复logtest数据库和test数据表，而不能恢复数据表中的数据，Pos是一条记录的起始位置，End_log_pos是一条记录的结束 ，如果想要恢复某连续行的数据的时候，不要忘了把最后一行需要恢复的数据的End_log_pos作为stop-position，而不是把Pos作为stop-position。

注意事项

    1.每当重启mysql的时候，都会自动生成一个新的binlog文件，恢复数据的时候首先确定需要恢复的数据在哪个日志文件中，然后查找对应binlog文件进行数据恢复。

    2.binlog分别记录了一个操作的起始位置Pos和结束位置End_log_pos，当起始位置和终止位置都选择正确的时候，恢复的数据才会正确，尤其是当进行连续的多行记录进行恢复的时候，对于stop-position的选择一定要注意，最后一行的End_log_pos才是我们需要的。

    3.使用show binlog events的时候默认指定的是第一个二进制文件，如果想要查看其它的二进制文件，可以使用show binlog events in 'logname'，其中logname是个字符串，不要忘记带上引号，否则会出错。