binlog信息查询
查看binlog配置，是否开启，binlog位置：
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------+
| log_bin | ON |
| log_bin_basename | /home/q/mysql/multi/3306/binlog/mysql-bin |
| log_bin_index | /home/q/mysql/multi/3306/binlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------------------------------------------------+
6 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000004 | 1659524 |
| mysql-bin.000005 | 8161208 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000004';
+------------------+---------+-------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+---------+-------------+-----------+-------------+--------------------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 23625 | 120 | Server ver: 5.6.39-83.1-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query | 23625 | 214 | BEGIN |
| mysql-bin.000004 | 214 | Table_map | 23625 | 299 | table_id: 72 (qta_promotion_activity.activity_template) |
| mysql-bin.000004 | 299 | Write_rows | 23625 | 43746 | table_id: 72 flags: STMT_END_F |
| mysql-bin.000004 | 43746 | Xid | 23625 | 43773 | COMMIT /* xid=1 */ |
| mysql-bin.000004 | 43773 | Query | 23625 | 43867 | BEGIN |
| mysql-bin.000004 | 43867 | Table_map | 23625 | 43958 | table_id: 71 (qta_promotion_activity.activity_instance) |
| mysql-bin.000004 | 43958 | Write_rows | 23625 | 44049 | table_id: 71 flags: STMT_END_F |
| mysql-bin.000004 | 44049 | Xid | 23625 | 44076 | COMMIT /* xid=2 */ |
去相应的路径可以看到binlog的文件
[sage.wang@machine /home/q/mysql/multi/3306]$ sudo ls -alh binlog/
total 9.4M
drwx------ 2 mysql mysql 4.0K Aug 10 15:13 .
drwxr-xr-x 11 mysql mysql 4.0K Aug 10 15:13 ..
-rw-rw---- 1 mysql mysql 1.6M Aug 10 15:12 mysql-bin.000004
-rw-rw---- 1 mysql mysql 7.8M Aug 10 20:12 mysql-bin.000005
-rw-rw---- 1 mysql mysql 98 Aug 10 15:13 mysql-bin.index
mysql-bin.000004和mysql-bin.000005就是binlog的文件。
使用mysqlbinlog命令对binlog文件进行操作
查看文件内容
sudo /home/q/mysql/bin/mysqlbinlog --stop-datetime='2018-08-10 11:25:56' /home/q/mysql/multi/3306/binlog/mysql-bin.000004
可以查看到类似如下的很多日志信息
BEGIN
/*!*/;
# at 1657217
#180810 10:48:20 server id 23625 end_log_pos 1657329 Table_map: `qta_promotion_coupon`.`coupon_00` mapped to number 129
# at 1657329
#180810 10:48:20 server id 23625 end_log_pos 1657890 Write_rows: table id 129 flags: STMT_END_F
BINLOG '
9PxsWxNJXAAAcAAAAPFJGQAAAIEAAAAAAAEAFHF0YV9wcm9tb3Rpb25fY291cG9uAAljb3Vwb25f
MDAAGAgIDwgPCA8PDwH29gEI/Pz8DxEREREDAReAAIAAgAAAAgACCgIKAgICAgACAAAAAADAAQ==
9PxsWx5JXAAAMQIAACJMGQAAAIEAAAAAAAEAAgAY////AAAAJgAAAAAAAADgcxtWAAAAAAEwnAIA
AAAAAAAUMjAxNjAxMTkxMjAwMjgwNDExNTOrTKbtNQAAAAwyMzE2MjAzNjU0ODMAAA8A5paw5a6i
5LqU5oqY5Yi4FYAAADwAgAAAPAABAAAAAAAAAAAxAHsicHJvcG9ydGlvblNlZ21lbnQiOiJ0cnVl
IiwiZGlzY291bnRMZXZlbCI6IjUwIn0AADMBeyJzZWdtZW50TW9uZXlDb25kaXRpb25zIjpbeyJt
b25leU9wZXJhdGlvbiI6IkdFIiwic2VnbWVudE1vbmV5QmFzZSI6eyJhbW91bnQiOjAuMDAsImN1
cnJlbmN5IjoiQ05ZIn0sInNlZ21lbnRNb25leUJvbnVzIjp7ImFtb3VudCI6NjAuMDAsImN1cnJl
bmN5IjoiQ05ZIn19XSwiaG90ZWxMZXZlbCI6MCwib3BlcmF0aW9uQ29kZSI6MiwiY291cG9uQ29u
ZGl0aW9uRGVzY0JvZHlMaXN0IjpbeyJ0aXRsZSI6IuS8mOaDoOivtOaYjiIsImRlc2MiOiLorqLl
jZXkuK3miL/otLnmgLvpop3mu6Hvv6Uw5Y+v55So77yM6ZmQ55So5LiA5qyhIn1dfSEAY2F0YWx5
c2lzLWRpc2NfdGVzdC1ORkROXzEtTkZETl8xW2z89Ft28P9bbPz0W2z89AEAAAAA
'/*!*/;
# at 1657890
#180810 10:48:20 server id 23625 end_log_pos 1657917 Xid = 175
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
server id是数据库主机的服务号
end_log_pos 是位置点
thread_id 是线程号
数据恢复
要恢复数据的话，可以从位置点恢复，默认就会执行从开始到位置点的语句。也可以通过结束时间恢复。
基于时间点的恢复
恢复2018-08-10 11:25:56时间点以前的数据
sudo /home/q/mysql/bin/mysqlbinlog --stop-datetime='2018-08-10 11:25:56' /home/q/mysql/multi/3306/binlog/mysql-bin.000004 |mysql -h127.0.0.1 -uqta_noah_rw -p密码
基于位置点恢复
恢复位置点1656329之前的数据
sudo /home/q/mysql/bin/mysqlbinlog --stop-position='1656329' /home/q/mysql/multi/3306/binlog/mysql-bin.000004 |mysql -h127.0.0.1 -uqta_noah_rw -p密码
相应的，使用mysqlbinlog可以指定开始时间、开始位置点、结束时间、结束位置点的方式查询、导出、恢复数据。
详细参考文章：
http://blog.51cto.com/lvnian/1699627
https://www.cnblogs.com/martinzhang/p/3454358.html
