Mysql之binlog日志恢复操作记录

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

开启binlog日志

[root@xlc ~]# vim /etc/my.cnf
在[mysqld] 区块添加
log-bin=mysql-bin //如果已经存在去掉#号

查询是否支持binlog

如下OFF代表不支持，ON代表支持

    mysql> show variables like 'log_bin';    +---------------+-------+    | Variable_name | Value |    +---------------+-------+    | log_bin       | ON    |    +---------------+-------+    1 row in set (0.00 sec)

查看当前日志文件名称

    mysql> show master status;    +------------------+----------+--------------+------------------+-------------------+    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |    +------------------+----------+--------------+------------------+-------------------+    | mysql-bin.000005 |      120 |              |                  |                   |    +------------------+----------+--------------+------------------+-------------------+    1 row in set (0.00 sec)    #关闭当前的二进制日志文件并创建一个新文件，新的二进制日志文件的名字在当前的二进制文件的编号上加1。    mysql> flush logs;    Query OK, 0 rows affected (0.02 sec)

恢复测试

创建测试表数据

mysql>  select * from t1;+----+-------+-----+---------+| id | name  | sex | address |+----+-------+-----+---------+|  7 | daiiy | m   | aaa     ||  8 | tom   | f   | bbb     ||  9 | liany | m   | ccc     || 10 | lilu  | m   | ddd     |+----+-------+-----+---------+4 rows in set (0.00 sec)

误删除测试表数据

mysql> delete from t1;Query OK, 4 rows affected (0.00 sec)mysql>  select * from t1;Empty set (0.00 sec)

重新生成log文件

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000005 |     1609 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> flush logs;Query OK, 0 rows affected (0.01 sec)

查看指定时间段内的日志数据

[root@iZwz97cbcapiyem3o09u09Z bin]# ./mysqlbinlog --no-defaults --database=test --start-datetime='2017-08-13 23:10:00' --stop-datetime='2017-08-13 23:12:13' ../data/mysql-bin.000005 | more/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#170813 23:07:06 server id 1  end_log_pos 120 CRC32 0x02ab0045  Start: binlog v 4, server v 5.6.33-log created 170813 23:07:06BINLOG 'GmuQWQ8BAAAAdAAAAHgAAAAAAAQANS42LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUUAqwI='/*!*/;# at 120#170813 23:10:57 server id 1  end_log_pos 199 CRC32 0xb6a73a78  Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637057/*!*/;SET @@session.pseudo_thread_id=486/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1075838976/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 199#170813 23:10:57 server id 1  end_log_pos 287 CRC32 0x0ac79c04  Query   thread_id=486   exec_time=0     error_code=0use `test`/*!*/;SET TIMESTAMP=1502637057/*!*/;delete from t1/*!*/;# at 287#170813 23:10:57 server id 1  end_log_pos 318 CRC32 0xb0b4da63  Xid = 102852COMMIT/*!*/;# at 318#170813 23:11:20 server id 1  end_log_pos 397 CRC32 0xfeca9fb6  Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637080/*!*/;BEGIN/*!*/;# at 397# at 429#170813 23:11:20 server id 1  end_log_pos 429 CRC32 0x6f803988  IntvarSET INSERT_ID=7/*!*/;#170813 23:11:20 server id 1  end_log_pos 561 CRC32 0x2208be50  Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637080/*!*/;insert into t1 (name,sex,address)values('daiiy','m','aaa')/*!*/;# at 561#170813 23:11:20 server id 1  end_log_pos 592 CRC32 0x74e80a04  Xid = 102853COMMIT/*!*/;# at 592#170813 23:11:20 server id 1  end_log_pos 671 CRC32 0x75baa899  Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637080/*!*/;BEGIN/*!*/;# at 671# at 703#170813 23:11:20 server id 1  end_log_pos 703 CRC32 0x26252021  IntvarSET INSERT_ID=8/*!*/;#170813 23:11:20 server id 1  end_log_pos 833 CRC32 0x82810296  Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637080/*!*/;insert into t1 (name,sex,address)values('tom','f','bbb')/*!*/;# at 833#170813 23:11:20 server id 1  end_log_pos 864 CRC32 0x8c2790d6  Xid = 102854COMMIT/*!*/;# at 864#170813 23:11:20 server id 1  end_log_pos 943 CRC32 0x1da588b6  Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637080/*!*/;BEGIN/*!*/;# at 943# at 975#170813 23:11:20 server id 1  end_log_pos 975 CRC32 0x71dc1b65  IntvarSET INSERT_ID=9/*!*/;#170813 23:11:20 server id 1  end_log_pos 1107 CRC32 0x870f4947         Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637080/*!*/;insert into t1 (name,sex,address)values('liany','m','ccc')/*!*/;# at 1107#170813 23:11:20 server id 1  end_log_pos 1138 CRC32 0xba226ed1         Xid = 102855COMMIT/*!*/;# at 1138#170813 23:11:22 server id 1  end_log_pos 1217 CRC32 0x0dec63f4         Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637082/*!*/;BEGIN/*!*/;# at 1217# at 1249#170813 23:11:22 server id 1  end_log_pos 1249 CRC32 0xfc436a97         IntvarSET INSERT_ID=10/*!*/;#170813 23:11:22 server id 1  end_log_pos 1380 CRC32 0x6a004a4e         Query   thread_id=486   exec_time=0     error_code=0SET TIMESTAMP=1502637082/*!*/;insert into t1 (name,sex,address)values('lilu','m','ddd')/*!*/;# at 1380#170813 23:11:22 server id 1  end_log_pos 1411 CRC32 0x979f2eab         Xid = 102856COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

还原指定时间段数据

[root@iZwz97cbcapiyem3o09u09Z bin]# ./mysqlbinlog --no-defaults --database=test --start-datetime='2017-08-13 23:10:00' --stop-datetime='2017-08-13 23:12:13' ../data/mysql-bin.000005 | /usr/local/mysql/bin/mysql -uroot -p123456 testWarning: Using a password on the command line interface can be insecure.

查看恢复后数据

mysql> select * from t1;+----+-------+-----+---------+| id | name  | sex | address |+----+-------+-----+---------+|  7 | daiiy | m   | aaa     ||  8 | tom   | f   | bbb     ||  9 | liany | m   | ccc     || 10 | lilu  | m   | ddd     |+----+-------+-----+---------+4 rows in set (0.00 sec)

mysqlbinlog常见的选项有：
–start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间
–stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样
–start-position：从二进制日志中读取指定position 事件位置作为开始。
–stop-position：从二进制日志中读取指定position 事件位置作为事件截至

测试以后发现，如果想恢复delete了的数据，必须要找到这条数居insert的语句才成，例如某张表中不太确认是什么时候插入的数据，但在今天被误删除了，好像没有办法在被删除的指定时间区间来恢复数据,日志回滚只是把在某一时间段执行的语句重新执行了一次。