mysql常用的binlog日志操作命令

admin

1）查看所有binlog日志列表

1. mysql> show master logs;
   
2. +------------------+-----------+
   
3. | Log_name | File_size |
   
4. +------------------+-----------+
   
5. | mysql-bin.000001 | 149 |
   
6. | mysql-bin.000002 | 4102 |
   
7. +------------------+-----------+
   
8. 2 rows in set (0.00 sec)
   

复制代码

2）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

1. mysql> show master status;
   
2. +------------------+----------+--------------+------------------+
   
3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   
4. +------------------+----------+--------------+------------------+
   
5. | mysql-bin.000002 | 4102 | | |
   
6. +------------------+----------+--------------+------------------+
   
7. 1 row in set (0.00 sec)

复制代码

3）flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件

1. mysql> flush logs;
   
2. Query OK, 0 rows affected (0.13 sec)
   
3. 
   
4. mysql> show master logs;
   
5. +------------------+-----------+
   
6. | Log_name | File_size |
   
7. +------------------+-----------+
   
8. | mysql-bin.000001 | 149 |
   
9. | mysql-bin.000002 | 4145 |
   
10. | mysql-bin.000003 | 106 |
    
11. +------------------+-----------+
    
12. 3 rows in set (0.00 sec)

复制代码

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4）重置(清空)所有binlog日志

1. mysql> reset master;
   
2. Query OK, 0 rows affected (0.12 sec)
   
3. 
   
4. mysql> show master logs;
   
5. +------------------+-----------+
   
6. | Log_name | File_size |
   
7. +------------------+-----------+
   
8. | mysql-bin.000001 | 106 |
   
9. +------------------+-----------+
   
10. 1 row in set (0.00 sec)

复制代码

5）查看指定binlog文件的内容

1. 只查看第一个binlog文件的内容
   
2. mysql> show binlog events;
   
3. 
   
4. 
   
5. 查看指定binlog文件的内容
   
6. mysql> show binlog events in 'mysql-bin.000002';

复制代码

admin

这里详解一下

1. SHOW BINLOG EVENTS
   
2. [IN 'log_name']
   
3. [FROM pos]
   
4. [LIMIT [offset,] row_count]

复制代码

Shows the events in the binary log. If you do not specify ‘log_name’, the first binary log is displayed.


各个参数意思如下：

Log_name:The name of the file that is being listed.
Pos：The position at which the event occurs.
Event_type：An identifier that describes the event type.
Server_id：The server ID of the server on which the event originated.
End_log_pos：The position at which the next event begins, which is equal to Pos plus the size of the event.
Info：More detailed information about the event type. The format of this information depends on the event type.