show binlog events

最新推荐文章于 2024-12-03 10:00:00 发布

Miles_sys

最新推荐文章于 2024-12-03 10:00:00 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/Maikilee_sys/article/details/51981150

mysql 专栏收录该内容

56 篇文章

订阅专栏

本文介绍了MySQL中SHOW BINLOG EVENTS命令的使用方法及其参数解释，通过实例展示了如何查看二进制日志中的具体事件，包括创建表、插入数据等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

用于在二进制日志中显示事件。如果您不指定’log_name’，则显示第一个二进制日志。
LIMIT子句和SELECT语句具有相同的语法。

实验：

mysql> create table tbl (test TEXT);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tbl values ("hello,Miles");
Query OK, 1 row affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

默认显示的信息显然不符合我们的要求

mysql> show binlog events\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.49-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 107
 Event_type: Query
  Server_id: 1
End_log_pos: 276
       Info: grant replication slave,replication client on *.* 
to repl@'192.168.%' identified by 'beijing'
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 276
 Event_type: Rotate
  Server_id: 1
End_log_pos: 319
       Info: mysql-bin.000002;pos=4
3 rows in set (0.00 sec)

找到当前binlog，因为之前的语句中有flush logs，所以执行的命令记录在上一个日志中

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000006'\G;
......
   Log_name: mysql-bin.000006
        Pos: 623
 Event_type: Query
  Server_id: 1
End_log_pos: 708
       Info: create database miles
*************************** 7. row ***************************
   Log_name: mysql-bin.000006
        Pos: 708
 Event_type: Query
  Server_id: 1
End_log_pos: 800
       Info: use `miles`; create table tbl (test TEXT)
*************************** 8. row ***************************
   Log_name: mysql-bin.000006
        Pos: 800
 Event_type: Query
  Server_id: 1
End_log_pos: 869
       Info: BEGIN
*************************** 9. row ***************************
   Log_name: mysql-bin.000006
        Pos: 869
 Event_type: Query
  Server_id: 1
End_log_pos: 971
       Info: use `miles`; insert into tbl values ("hello,Miles")
*************************** 10. row ***************************
   Log_name: mysql-bin.000006
        Pos: 971
 Event_type: Xid
  Server_id: 1
End_log_pos: 998
       Info: COMMIT /* xid=1992 */
*************************** 11. row ***************************
   Log_name: mysql-bin.000006
        Pos: 998
 Event_type: Rotate
  Server_id: 1
End_log_pos: 1041
       Info: mysql-bin.000007;pos=4
11 rows in set (0.00 sec)