以myslq 5.7为例
开启bin-log log-bin = mysql-binserver-id = 1
1.查看状态
mysql> show variables like 'log_%'; Variable_name | Value | log_bin | ON | log_bin_basename | /var/lib/mysql/mysql-bin | log_bin_index | /var/lib/mysql/mysql-bin.index | log_bin_trust_function_creators | OFF | log_bin_use_v1_row_events | OFF | log_builtin_as_identified_by_password | OFF | log_error | /var/log/mysqld.log | log_error_verbosity | 3 | log_output | FILE | log_queries_not_using_indexes | OFF | log_slave_updates | OFF | log_slow_admin_statements | OFF | log_slow_slave_statements | OFF | log_statements_unsafe_for_binlog | ON | log_syslog | OFF | log_syslog_facility | daemon | log_syslog_include_pid | ON | log_syslog_tag | | log_throttle_queries_not_using_indexes | 0 | log_timestamps | UTC | log_warnings | 2 |
binlog 操作 1.创建一个数据库
mysql > create database wordpress;Query OK, 1 row affected (0 .01 sec)
2.查看binlog 日志
[root@centos ~]
171225 15 :29 :14 server id 1 end_log_pos 328 CRC32 0x9be9c989 Query thread_id= 7 exec_time= 0 error_code= 0 @@session .character_set_client= 33 ,@@session .collation_connection= 33 ,@@session .collation_server= 8 ;SET @@session .lc_time_names= 0 ;SET @@session .collation_database= DEFAULT ;create database wordpressSET @@SESSION .GTID_NEXT= 'AUTOMATIC' ;End of log file
binlog是二进制文件，普通文件查看器cat more vi等都无法打开，必须使用自带的 mysqlbinlog 命令查看
binlog日志与数据库文件在同目录中
注: server id 1 数据库主机的服务号；
end_log_pos 328 pos点thread_id =7 线程号
3.上面这种办法读取出binlog日志的全文内容较多，不容易分辨查看pos点信息，这里介绍一种更为方便的查询命令：
mysql> show binlog events [IN 'log_name' ] [FROM pos] [LIMIT [offset ,] row_count ];
选项解析：
IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)pos 指定从哪个pos 起始点开始查起(不指定就是从整个文件首个pos 点开始算)LIMIT [offset,] 偏移量(不指定就是0 )
mysql> show binlog events IN 'mysql-bin.000001' Log_name | Pos | Event_type | Server_id | End_log_pos | Info | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | mysql-bin.000001 | 219 | Query | 1 | 328 | create database wordpress |
mysql> show binlog events IN 'mysql-bin.000001 '\G;* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_descServer_id: 1 End_log_pos: 123 Info: Server ver: 5.7 .2 0-log, Binlog ver: 4 * * * * * * * * * * * * * * * * * * * * * * * * * * * 2 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 123 Event_type: Previous_gtidsServer_id: 1 End_log_pos: 154 Info: * * * * * * * * * * * * * * * * * * * * * * * * * * * 3 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 154 Event_type: Anonymous_GtidServer_id: 1 End_log_pos: 219 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'* * * * * * * * * * * * * * * * * * * * * * * * * * * 4 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 219 Event_type: QueryServer_id: 1 End_log_pos: 328 Info: create database wordpress4 rows in set (0.00 sec)ERROR:
查询第一个（最早）的binlog 日志
mysql> show binlog events\G;* * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_descServer_id: 1 End_log_pos: 123 Info: Server ver: 5.7 .2 0-log, Binlog ver: 4 * * * * * * * * * * * * * * * * * * * * * * * * * * * 2 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 123 Event_type: Previous_gtidsServer_id: 1 End_log_pos: 154 Info: * * * * * * * * * * * * * * * * * * * * * * * * * * * 3 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 154 Event_type: Anonymous_GtidServer_id: 1 End_log_pos: 219 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'* * * * * * * * * * * * * * * * * * * * * * * * * * * 4 . row * * * * * * * * * * * * * * * * * * * * * * * * * * * Log_name: mysql-bin.000001 Pos: 219 Event_type: QueryServer_id: 1 End_log_pos: 328 Info: create database wordpress4 rows in set (0.00 sec)ERROR:
指定pos点查询
mysql> show binlog events in 'mysql-bin.000001' from 219 -> ; +------------------+-----+------------+-----------+-------------+---------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+------------+-----------+-------------+---------------------------+ | mysql-bin.000001 | 219 | Query | 1 | 328 | create database wordpress | +------------------+-----+------------+-----------+-------------+---------------------------+
从指定pos 查询10条
mysql> show binlog events in 'mysql-bin.000001' from 219 limit 10\G;**** **** **** **** **** **** *** 1. row ** **** **** **** **** **** **** *_name: mysql-bin.000001 Pos: 219 Event_ type: Query_id: 1 End_ log_pos: 328 Info: create database wordpress 1 row in set (0.00 sec) ERROR: No query specified
恢复相关命令 mysqlbinlog --stop-position =154 --database =wordpress mysql-bin.000002 | mysql -uroot -ppasswd -v wordpress
常用选项：
--start-position =953 起始pos点--stop-position =1437 结束pos点--start-datetime ="2013-11-29 13:18:54" 起始时间点--stop-datetime ="2013-11-29 13:21:53" 结束时间点--database =zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
不常用选项：
-u --user =name Connect to the remote server as username.连接到远程主机的用户名to connect to remote server.连接到远程主机的密码--host =name Get the binlog from server.从远程主机上获取binlog日志from a MySQL server.从某个MySQL服务器上读取binlog日志
文章作者： 阿文
版权声明： 本博客所有文章除特别声明外，均采用
CC BY-NC-SA 4.0 许可协议。转载请注明来自
阿文的博客 ！
