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:06
BINLOG '
GmuQWQ8BAAAAdAAAAHgAAAAAAAQANS42LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAUUA
qwI=
'/*!*/;
# at 120
#170813 23:10:57 server id 1 end_log_pos 199 CRC32 0xb6a73a78 Query thread_id=486 exec_time=0 error_code=0
SET 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=0
use `test`/*!*/;
SET TIMESTAMP=1502637057/*!*/;
delete from t1
/*!*/;
# at 287
#170813 23:10:57 server id 1 end_log_pos 318 CRC32 0xb0b4da63 Xid = 102852
COMMIT/*!*/;
# at 318
#170813 23:11:20 server id 1 end_log_pos 397 CRC32 0xfeca9fb6 Query thread_id=486 exec_time=0 error_code=0
SET TIMESTAMP=1502637080/*!*/;
BEGIN
/*!*/;
# at 397
# at 429
#170813 23:11:20 server id 1 end_log_pos 429 CRC32 0x6f803988 Intvar
SET 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=0
SET 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 = 102853
COMMIT/*!*/;
# at 592
#170813 23:11:20 server id 1 end_log_pos 671 CRC32 0x75baa899 Query thread_id=486 exec_time=0 error_code=0
SET TIMESTAMP=1502637080/*!*/;
BEGIN
/*!*/;
# at 671
# at 703
#170813 23:11:20 server id 1 end_log_pos 703 CRC32 0x26252021 Intvar
SET 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=0
SET 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 = 102854
COMMIT/*!*/;
# at 864
#170813 23:11:20 server id 1 end_log_pos 943 CRC32 0x1da588b6 Query thread_id=486 exec_time=0 error_code=0
SET TIMESTAMP=1502637080/*!*/;
BEGIN
/*!*/;
# at 943
# at 975
#170813 23:11:20 server id 1 end_log_pos 975 CRC32 0x71dc1b65 Intvar
SET 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=0
SET 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 = 102855
COMMIT/*!*/;
# at 1138
#170813 23:11:22 server id 1 end_log_pos 1217 CRC32 0x0dec63f4 Query thread_id=486 exec_time=0 error_code=0
SET TIMESTAMP=1502637082/*!*/;
BEGIN
/*!*/;
# at 1217
# at 1249
#170813 23:11:22 server id 1 end_log_pos 1249 CRC32 0xfc436a97 Intvar
SET 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=0
SET 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 = 102856
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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 test
Warning: 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的语句才成，例如某张表中不太确认是什么时候插入的数据，但在今天被误删除了，好像没有办法在被删除的指定时间区间来恢复数据,日志回滚只是把在某一时间段执行的语句重新执行了一次。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
