前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >Mysql binlog 查看方法（4）

Mysql binlog 查看方法（4）

作者头像
franket
发布于 2021-10-19 11:50:18
4.4K0
发布于 2021-10-19 11:50:18
举报
文章被收录于专栏：技术杂记

SHOW RELAYLOG EVENTS

要查看 relay 日志得使用 SHOW RELAYLOG EVENTS ，如果使用 SHOW BINLOG EVENTS 会报找不到文件的错误

代码语言：javascript
复制
mysql> show binlog  events in 'relay-bin.000197'  from 4   limit 4 ;
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log
mysql> show relaylog  events in 'relay-bin.000197'  from 4   limit 4 ;
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                       |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
| relay-bin.000197 |   4 | Format_desc |       112 |         120 | Server ver: 5.6.27-75.0-log, Binlog ver: 4 |
| relay-bin.000197 | 120 | Rotate      |        15 |           0 | mysql-bin.000079;pos=4                     |
| relay-bin.000197 | 167 | Format_desc |        15 |         120 | Server ver: 5.6.27-75.0-log, Binlog ver: 4 |
| relay-bin.000197 | 283 | Query       |        15 |         196 | BEGIN                                      |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------+
4 rows in set (0.00 sec)

mysql>

因为 SHOW BINLOG EVENTS 会去 mysql-bin.index 里找，找不到自然报错

SHOW RELAYLOG EVENTS 与 SHOW BINLOG EVENTS 类似 ，详细用法可以参考 SHOW RELAYLOG EVENTS Syntax

工具的缺陷

我们看看下面的情况

代码语言：javascript
复制
mysql> show binlog events in 'mysql-bin.000001' from 4  limit 4  ;
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                         |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.27-76.0-log, Binlog ver: 4   |
| mysql-bin.000001 | 120 | Query       |         1 |         205 | BEGIN                                        |
| mysql-bin.000001 | 205 | Query       |         1 |         314 | use `testxxx`; delete from test where id=100 |
| mysql-bin.000001 | 314 | Xid         |         1 |         345 | COMMIT /* xid=15 */                          |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000001' from 120  limit 4  ;
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                         |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
| mysql-bin.000001 | 120 | Query      |         1 |         205 | BEGIN                                        |
| mysql-bin.000001 | 205 | Query      |         1 |         314 | use `testxxx`; delete from test where id=100 |
| mysql-bin.000001 | 314 | Xid        |         1 |         345 | COMMIT /* xid=15 */                          |
| mysql-bin.000001 | 345 | Query      |         1 |         428 | BEGIN                                        |
+------------------+-----+------------+-----------+-------------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000001' from 100  limit 4  ;
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
mysql>

4 和 120 正好是 Pos 点，于是可以顺利查出结果，但是如果我指定介于它们之间的 100 ，工具就会报 Wrong offset or I/O error 的错误，它并不会智能的找到之后最接近的一个位置并读出数据来，所以在查看日志内容之前一定要首先定位好，而 POS 一般都不是连续的，间距相差几十到几百，所以要反复尝试，变得很低效

mysqlbinlog

系统的 binlog 日志是由对数据进行修改的一个个事件组成

系统是以二进制的方式进行读写，mysqlbinlog 可以将它们转化为文本的形式

Tip: 由于 relay log 遵循 binlog 相同的规范，所以也可以被 mysqlbinlog 进行转化

使用 mysqlbinlog 对日志进行查看

代码语言：javascript
复制
[root@h105 mysql]# mysqlbinlog mysql-bin.000001 | head -n 40
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160612 13:56:01 server id 1  end_log_pos 120 CRC32 0x6a792fa7 	Start: binlog v 4, server v 5.6.27-76.0-log created 160612 13:56:01 at startup
ROLLBACK/*!*/;
BINLOG '
cflcVw8BAAAAdAAAAHgAAAAAAAQANS42LjI3LTc2LjAtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABx+VxXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAacv
eWo=
'/*!*/;
# at 120
#160612 13:58:27 server id 1  end_log_pos 205 CRC32 0xe1f9b47e 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1465711107/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 205
#160612 13:58:27 server id 1  end_log_pos 314 CRC32 0xec7455fc 	Query	thread_id=3	exec_time=0	error_code=0
use `testxxx`/*!*/;
SET TIMESTAMP=1465711107/*!*/;
delete from test where id=100
/*!*/;
# at 314
#160612 13:58:27 server id 1  end_log_pos 345 CRC32 0x455b6f62 	Xid = 15
COMMIT/*!*/;
# at 345
#160612 14:00:01 server id 1  end_log_pos 428 CRC32 0x227c2add 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1465711201/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
BEGIN
[root@h105 mysql]#

Tip: 默认情况下，会将指定日志的所有内容都转化为文本形式

从结果来看 mysqlbinlog 可以产生更为详尽的信息

之前删除一条数据的过程在这里

代码语言：javascript
复制
# at 120
#160612 13:58:27 server id 1  end_log_pos 205 CRC32 0xe1f9b47e 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1465711107/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 205
#160612 13:58:27 server id 1  end_log_pos 314 CRC32 0xec7455fc 	Query	thread_id=3	exec_time=0	error_code=0
use `testxxx`/*!*/;
SET TIMESTAMP=1465711107/*!*/;
delete from test where id=100
/*!*/;
# at 314
#160612 13:58:27 server id 1  end_log_pos 345 CRC32 0x455b6f62 	Xid = 15
COMMIT/*!*/;
# at 345

关于这些字段的解释可以参考：

In the first line, the number following at indicates the file offset, or starting position, of the event in the binary log file. The second line starts with a date and time indicating when the statement started on the server where the event originated. For replication, this timestamp is propagated to slave servers. server id is the server_id value of the server where the event originated. end_log_pos indicates where the next event starts (that is, it is the end position of the current event + 1). thread_id indicates which thread executed the event. exec_time is the time spent executing the event, on a master server. On a slave, it is the difference of the end execution time on the slave minus the beginning execution time on the master. The difference serves as an indicator of how much replication lags behind the master. error_code indicates the result from executing the event. Zero means that no error occurred

输出限定

各种 mysqlbinlog 的参数可以改变输出的特性，详细参数可以参考 Utility for Processing Binary Log Files

这里演示最常用的方法与参数

指定起止位置

代码语言：javascript
复制
[root@h105 mysql]# mysqlbinlog   --start-position=205    --stop-position=314     mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160612 13:56:01 server id 1  end_log_pos 120 CRC32 0x6a792fa7 	Start: binlog v 4, server v 5.6.27-76.0-log created 160612 13:56:01 at startup
ROLLBACK/*!*/;
BINLOG '
cflcVw8BAAAAdAAAAHgAAAAAAAQANS42LjI3LTc2LjAtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABx+VxXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAacv
eWo=
'/*!*/;
# at 205
#160612 13:58:27 server id 1  end_log_pos 314 CRC32 0xec7455fc 	Query	thread_id=3	exec_time=0	error_code=0
use `testxxx`/*!*/;
SET TIMESTAMP=1465711107/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
delete from test where id=100
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@h105 mysql]#

指定起止时间

代码语言：javascript
复制
[root@h105 mysql]# mysqlbinlog  --start-datetime="2016-06-12 13:58:27"  --stop-datetime="2016-06-12 14:00:01"  mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160612 13:56:01 server id 1  end_log_pos 120 CRC32 0x6a792fa7 	Start: binlog v 4, server v 5.6.27-76.0-log created 160612 13:56:01 at startup
ROLLBACK/*!*/;
BINLOG '
cflcVw8BAAAAdAAAAHgAAAAAAAQANS42LjI3LTc2LjAtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABx+VxXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAacv
eWo=
'/*!*/;
# at 120
#160612 13:58:27 server id 1  end_log_pos 205 CRC32 0xe1f9b47e 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1465711107/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 205
#160612 13:58:27 server id 1  end_log_pos 314 CRC32 0xec7455fc 	Query	thread_id=3	exec_time=0	error_code=0
use `testxxx`/*!*/;
SET TIMESTAMP=1465711107/*!*/;
delete from test where id=100
/*!*/;
# at 314
#160612 13:58:27 server id 1  end_log_pos 345 CRC32 0x455b6f62 	Xid = 15
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@h105 mysql]#

Tip: 与 SHOW BINLOG EVENTS 不一样的是，不论是 POS 还是时间点都可以不是一个与日志中精确匹配的值，mysqlbinlog 会自动判断，去定位到那个大于或等于指定值的第一条事件

mysqlbinlog 的输出是可以直接在 mysql 里执行的，结合管道可以方便将结果定向到数据库中，相当于重新执行一遍所有变更操作，利用这个特性可以进行定点恢复

The output from mysqlbinlog can be re-executed (for example, by using it as input to mysql) to redo the statements in the log. This is useful for recovery operations after a server crash

关于定点恢复的操作细节，下次专门开一篇博客，官方文档可以参考 Point-in-Time (Incremental) Recovery Using the Binary Log

命令汇总

  • grep bin /etc/my.cnf
  • mysqlbinlog mysql-bin.000001 | head -n 40
  • mysqlbinlog --start-position=205 --stop-position=314 mysql-bin.000001
  • mysqlbinlog --start-datetime="2016-06-12 13:58:27" --stop-datetime="2016-06-12 14:00:01" mysql-bin.000001

原文地址

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

linux
数据库
云数据库 SQL Server
sql
python

本文系转载，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

linux
数据库
云数据库 SQL Server
sql
python
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • SHOW RELAYLOG EVENTS
  • 工具的缺陷
  • mysqlbinlog
  • 输出限定
    • 指定起止位置
      • 指定起止时间
      • 命令汇总
      相关产品与服务
      云数据库 SQL Server
      腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
      产品介绍产品文档
      云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
      领券

      腾讯云开发者

      扫码关注腾讯云开发者

      扫码关注腾讯云开发者

      领取腾讯云代金券

      热门产品

      热门推荐

      更多推荐

      Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

      深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

      腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

      Copyright © 2013 - 2024 Tencent Cloud.

      All Rights Reserved. 腾讯云 版权所有

      登录 后参与评论