使用mysql的binlog恢复误操作(update|delete)的数据

binlog格式介绍

• MYSQL binlog复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

  ① STATEMENT模式（SBR）

  每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

  ② ROW模式（RBR）

  不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

  ③ MIXED模式（MBR）

  以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

  具体可参考链接http://www.cnblogs.com/langtianya/p/5504774.html

binlog复制配置

① 在mysql的配置文件my.cnf中，可以通过一下选项配置binglog相关


    binlog_format   = ROW //binlog日志格式，默认为STATEMENT
    log-bin = /data/mysql/mysql-bin.log//binlog日志文件
    expire_logs_days= 7   //binlog过期清理时间
    max_binlog_size = 100m   //binlog每个日志文件大小
    binlog_cache_size   = 4m//binlog缓存大小
    max_binlog_cache_size   = 512m //最大binlog缓存大小


②通过mysql语句查询 binlog格式

    mysql> show VARIABLES like 'binlog_format'； // 查询当前mysql的binlog格式
    mysql> show VARIABLES like 'datadir';       // 查询数据库binlog和库，表的目录 /var/mysql/data/

③ 查看是否开启binlog
```

mysql> show VARIABLES like ‘binlog_format’； // 查询当前mysql的binlog是否开启
“`

如何通过binlog恢复误操作的数据

• 步骤一：场景准备

  ① 创建测试用的数据表

  mysql> create table t1 (
      -> id int unsigned not null auto_increment,
      -> name char(20) not null,
      -> sex enum('f','m') not null default 'm',
      -> address varchar(30) not null,
      -> primary key(id)
      -> );
      Query OK, 0 rows affected (0.31 sec)
  mysql>
  

  ② 插入测试数据

  mysql> insert into t1 (name,sex,address)values('daiiy','m','guangzhou');
      Query OK, 1 row affected (0.01 sec)
  
  mysql> insert into t1 (name,sex,address)values('tom','f','shanghai');   
      Query OK, 1 row affected (0.00 sec)
  

  ③ 删除数据

  mysql> delete from t1;
  Query OK, 2 rows affected (0.03 sec)
  

• 步骤二： 查询当前mysql的binlog文件

  ① 查询mysql所有的binlog文件

  mysql>  show master logs;  // 该结果返回的即为mysql记录的所有binlog文件
  

  Log_name	File_size
  mysql81-bin.000112	1654366
  mysql81-bin.000113	100131397

  ② 查询当前mysql已经记录到哪个binlog子文件中

  mysql> show master status;  // 该结果返回的即为当前mysql的操作所记录的binlog子文件
  

  File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
  mysql81-bin.000113	100131397			8df3aac9-262a-11e5

• 步骤三： 通过binlog文件恢复被删除的数据

  ①：在mysql的安装bin目录下(cd /usr/local/mysql/bin)找到mysqlbinlog命令执行文件

  ②：找到当前mysql的binlog文件(通过上面show master status命令可以知道最新的binlog文件为mysql81-bin.000113)： /var/mysql/data/mysql81-bin.000113

  ③：在usr/local/mysql/bin目录下：在mysql81-bin.000113文件中查找”删除”操作记录

  :/usr/local/mysql/bin>$ ./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113 | grep -B 15 -A 15 'DELETE FROM'
  

  ④：拷贝mysql81-bin.000113文件中的删除记录到指定文件中/var/mysql/data/delete.txt：

  :/usr/local/mysql/bin>$./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113| sed -n '/### DELETE FROM 'pluto_core
  _1'.'t1’/,/COMMIT/p' > /var/mysql/data/delete.txt

  注意：
  1） 库名|表名需要被 “括起来，格式必需与第③步查找出来的格式一致
  2） 指定/var/mysql/data/目录权限必需是可写的 (sudo chmod 755 data)

  ⑤：打开/var/mysql/data/delete.txt文件，这时应该可以看到该文件中已经有之前的删除操作记录了

  //delete.txt文件
  ### DELETE FROM `pluto_core_1`.`t1`
  ### WHERE
  ###   @1=9 /* INT meta=0 nullable=0 is_null=0 */
  ###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
  ###   @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
  ### DELETE FROM `pluto_core_1`.`t1`
  ### WHERE
  ###   @1=10 /* INT meta=0 nullable=0 is_null=0 */
  ###   @2='呵呵' /* STRING(60) meta=65084 nullable=0 is_null=0 */
  ###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
  ###   @4='上海' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
  # at 10958544
  #170908 14:59:23 server id 181  end_log_pos 10958575 CRC32 0x5c83521d   Xid = 221304
  COMMIT/*!*/;
  

  ⑥： 将delete.txt中的记录转换为SQL语句

  cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > source.sql

  // source.sql文件
  INSERT INTO `pluto_core_1`.`t1`
  SELECT
   9 ,
   'daiiy' ,
  2 ,
  'guangzhou' ;
  INSERT INTO `pluto_core_1`.`t1`
  SELECT
   10 ,
  '呵呵' ,
  2 ,
   '上海' ;
  

  ⑦： 在source.sql文件所在目录下登录mysql导入source.sql

  `:/var/mysql/data>$mysql -h99.48.xxx.xxx -P3306 -uaaa -pbbbbb
  mysql>source source.sql
  

  ⑧： 查看数据库pluto_core_1.t1,查看已删除的数据是否已经恢复了

• 步骤四： 通过binlog文件恢复被更新的数据

  ①：在usr/local/mysql/bin目录下：在mysql81-bin.000113文件中查找”更新”操作记录

  :/usr/local/mysql/bin>$ ./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113 |  grep -B 15 'zhuhai'
  

  ②： 拷贝mysql81-bin.000113文件中的删除记录到指定文件中/var/mysql/data/delete.txt：

  :/usr/local/mysql/bin>$./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113| sed -n '/# at 1679/,/COMMIT/p' > /var/mysql/data/delete.txt

  ③：将delete.txt中的记录转换为SQL语句

  sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' t1.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > recover.sql

  转载至：MySQL误操作后数据恢复(update,delete忘记加where)