在实际的工作过程中,作为DBA,经常会遇到主从复制的问题,主从复制延迟也好,主从复制断开也好,这种情况下经常需要去查主库的binlog日志文件,而binlog本身比较大,解析起来比较费劲,如果可以直观的看到binlog的内容,那对于DBA来讲绝对是福音。
在MySQL中,提供了这样的方法,就是使用show binlog events的方法,当然,在介绍这个方法之前,我们先看看如何查看binlog,如下:
1、查看当前库的所有binlog文件
首先我们查看当前库的所有binlog文件,这里有两个方法:
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysqlbin. | 217555859 |
| mysqlbin.000006 | |
+-----------------+-----------+
2 rows in set (0.02 sec)
mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysqlbin.000005 | |
| mysqlbin. | 53729453 |
+-----------------+-----------+
rows in set (. sec)
我们可以看到当前的binlog序号是5和6。
2、生成新的binlog文件
如果我们想重新生成一个binlog,方便我们查看结果,可以使用flush log的方法来进行,如下:
mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysqlbin. | 217555859 |
| mysqlbin.000006 | |
+-----------------+-----------+
2 rows in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)
mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysqlbin.000005 | |
| mysqlbin. | 53729495 |
| mysqlbin.000007 | |
+-----------------+-----------+
3 rows in set (0.00 sec)
这里,我们看到,新生成了编号为7的日志,此时我们对测试表a进行插入操作,如下:
mysql> desc a;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar() | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into a values (1,'aaa'),(2,'bbb'),(3,'ccc');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
紧接着我们进行刷新binlog的操作,让生成binlog文件8:
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mysqlbin.000005 | 217555859 |
| mysqlbin.000006 | 53729495 |
| mysqlbin.000007 | 399 |
| mysqlbin.000008 | 107 |
+-----------------+-----------+
4 rows in set (0.00 sec)
此时我们知道mysqlbin.000007的内容就是我们执行的insert操作,那么如何查看这个binlog中的内容呢?
3、查看binlog中的事件内容
可以使用下面的方法:
mysql> show binlog events in 'mysqlbin.000007';
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
| mysqlbin.000007 | 4 | Format_desc | 22250 | 107 | Server ver: 5.5.19-log, Binlog ver: 4 |
| mysqlbin.000007 | 107 | Query | 22250 | 175 | BEGIN |
| mysqlbin.000007 | | Query | | | use `yeyz`; insert into a values (,'aaa'),(,'bbb'),(,'ccc') |
| mysqlbin.000007 | | Query | | | COMMIT |
| mysqlbin.000007 | | Rotate | | | mysqlbin.000008;pos=4 |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
5 rows in set (0.01 sec)
此时我们可以看到,刚才进行插入的内容(1,'aaa'),(2,'bbb'),(3,'ccc'),一样的道理,当我们发现主从复制断开的时候,我们也可以这样去查看断开的binlog位置处主库的binlog内容,断开的binlog位置可以通过show slave status在从库上查看,如下:
mysql >> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
其中,exec_master_log_pos即为执行的master_log_pos位置。
4、查看指定位置处的binlog内容。
如果此时我们想查看当前的位置处的binlog event内容,可以在上面的语句基础上添加from语法进行查看,如下:
mysql> show binlog events in 'mysqlbin.000007';
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
| mysqlbin.000007 | 4 | Format_desc | 22250 | 107 | Server ver: 5.5.19-log, Binlog ver: 4 |
| mysqlbin.000007 | 107 | Query | 22250 | 175 | BEGIN |
| mysqlbin.000007 | | Query | | | use `yeyz`; insert into a values (,'aaa'),(,'bbb'),(,'ccc') |
| mysqlbin.000007 | | Query | | | COMMIT |
| mysqlbin.000007 | | Rotate | | | mysqlbin.000008;pos=4 |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------------+
5 rows in set (0.01 sec)
mysql> show binlog events in 'mysqlbin.000007' from ;
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------------------+
| mysqlbin.000007 | 175 | Query | 22250 | 288 | use `yeyz`; insert into a values (,'aaa'),(,'bbb'),(,'ccc') |
| mysqlbin.000007 | | Query | | | COMMIT |
| mysqlbin.000007 | | Rotate | | | mysqlbin.000008;pos=4 |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show binlog events in 'mysqlbin.000007' from limit ;
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------------------+
| mysqlbin.000007 | 175 | Query | 22250 | 288 | use `yeyz`; insert into a values (,'aaa'),(,'bbb'),(,'ccc') |
+-----------------+-----+------------+-----------+-------------+----------------------------------------------------------------+
row in set (0.00 sec)
mysql> show binlog events in 'mysqlbin.000007' from ;
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
可以看到,当我们添加了from子句之后,就可以直接从某一个binlog的指定位置开始读取binlog的事件,如果binlog中的事件太多,还可以指定limit关键字,这样,就可以读取出来指定条数的binlog事件了。
还有一点需要注意,这个from后面跟的值,必须是binlog的指定位置,如果binlog中没有这个指定位置,那么上面的语法是不成立的,会给出错误提示,例如上面的174并不是Pos中的位置,那么就报错,提示偏移量错误。
除此之外,该语句需要replication slave权限。否则是无法进行查看的。
有帮助的话还希望点下再看哈