MySQL Binlog日志解析方法
一、Show binlog events命令解析Binlog
1.1 语法格式
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 选项解析： IN 'log_name' # 指定要查询的binlog文件名(不指定就是第一个binlog文件) FROM pos # 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算) LIMIT [offset,] # 偏移量(不指定就是0) row_count # 查询总条数(不指定就是所有行)
1.2 查询日志输出简介
（1）主从同步场景，查看从库复制状态
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.1 Master_User: ucloudbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001482 Read_Master_Log_Pos: 625276093 # 目前读取到主库binlog的pos Relay_Log_File: mysql-relay.003264 Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.001482 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1146 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '06eadfce-e056-11eb-89c4-525400aed637:52049821' at master log mysql-bin.001482, end_log_pos 625058096. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Master_Log_Pos: 625057883 # 目前已经执行主库binlog的pos点 Relay_Log_Space: 625276809 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '06eadfce-e056-11eb-89c4-525400aed637:52049821' at master log mysql-bin.001482, end_log_pos 625058096. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Master_Server_Id: 3232262287 Master_UUID: 06eadfce-e056-11eb-89c4-525400aed637 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 210721 10:36:18 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 06eadfce-e056-11eb-89c4-525400aed637:4840456-52050433 Executed_Gtid_Set: 06eadfce-e056-11eb-89c4-525400aed637:1-52049820 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
（2）主库查看异常pos点执行的SQL情况
# 查询当前读取的pos点情况
mysql> show binlog events in 'mysql-bin.001482' from 625058096 limit 2\G;
*************************** 1. row ***************************
Log_name: mysql-bin.001482
Pos: 625058096
Event_type: Gtid
Server_id: 3232262287
End_log_pos: 625058161
Info: SET @@SESSION.GTID_NEXT= '06eadfce-e056-11eb-89c4-525400aed637:52049822'
*************************** 2. row ***************************
Log_name: mysql-bin.001482 # 查询的binlog日志文件名
Pos: 625058161 # pos起始点
Event_type: Query # 事件类型：Query
Server_id: 3232262287 # 标识是由哪台服务器执行的
End_log_pos: 625058309 # pos结束点:625058309(即：下行的pos起始点)
Info: use `admin`; CREATE TABLE IF NOT EXISTS `2021_log` LIKE `2020_log` # 执行的sql语句
# 查询当前执行的pos点情况
mysql> show binlog events in 'mysql-bin.001482' from 625276093 limit 3\G;
*************************** 1. row ***************************
Log_name: mysql-bin.001482
Pos: 625276093
Event_type: Gtid
Server_id: 3232262287
End_log_pos: 625276158
Info: SET @@SESSION.GTID_NEXT= '06eadfce-e056-11eb-89c4-525400aed637:52050434'
*************************** 2. row ***************************
Log_name: mysql-bin.001482
Pos: 625276158
Event_type: Query
Server_id: 3232262287
End_log_pos: 625276240
Info: BEGIN
*************************** 3. row ***************************
Log_name: mysql-bin.001482
Pos: 625276240
Event_type: Rows_query
Server_id: 3232262287
End_log_pos: 625276465
Info: # INSERT INTO 2021_log(connect_time, devid, succ) VALUES ('2021-07-21 10:36:21', 'A14096C4-F7E2-4551-AED4-B41B2AB5A991', 1), ('2021-07-21 10:36:20', '1FEDA2A7-3F0D-4244-90E9-4F3ED8604977', 1)# 更直观查看方式 mysql> show binlog events in 'mysql-bin.000015' from 636139847 limit 10; +------------------+-----------+-------------+-----------+-------------+-------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----------+-------------+-----------+-------------+-------------------------------------------------------------------------+ | mysql-bin.000015 | 636139847 | Xid | 171246918 | 636139878 | COMMIT /* xid=85303342 */ | | mysql-bin.000015 | 636139878 | Gtid | 171246918 | 636139957 | SET @@SESSION.GTID_NEXT= 'a33c34d6-7829-11ec-a6dc-525400d8e0fa:1984512' | | mysql-bin.000015 | 636139957 | Query | 171246918 | 636140045 | BEGIN | | mysql-bin.000015 | 636140045 | Table_map | 171246918 | 636140248 | table_id: 809 (8daymoni.vip_user) | | mysql-bin.000015 | 636140248 | Update_rows | 171246918 | 636141192 | table_id: 809 flags: STMT_END_F | | mysql-bin.000015 | 636141192 | Xid | 171246918 | 636141223 | COMMIT /* xid=85303325 */ | | mysql-bin.000015 | 636141223 | Gtid | 171246918 | 636141302 | SET @@SESSION.GTID_NEXT= 'a33c34d6-7829-11ec-a6dc-525400d8e0fa:1984513' | | mysql-bin.000015 | 636141302 | Query | 171246918 | 636141390 | BEGIN | | mysql-bin.000015 | 636141390 | Table_map | 171246918 | 636141593 | table_id: 810 (888bmoni.vip_user) | | mysql-bin.000015 | 636141593 | Update_rows | 171246918 | 636142585 | table_id: 810 flags: STMT_END_F | +------------------+-----------+-------------+-----------+-------------+-------------------------------------------------------------------------+ 10 rows in set (0.00 sec)
（3）表格式查看方法
mysql> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000033 | 21254686 | | binlog.000034 | 10537610 | +---------------+-----------+ 2 rows in set (0.00 sec) mysql> show binlog events in 'binlog.000034' limit 10; +---------------+------+----------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+------------------------------------------------+ | binlog.000034 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | binlog.000034 | 123 | Previous_gtids | 1 | 154 | | | binlog.000034 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000034 | 219 | Query | 1 | 294 | BEGIN | | binlog.000034 | 294 | Table_map | 1 | 376 | table_id: 113 (eyoucms.ey_arcmulti) | | binlog.000034 | 376 | Update_rows | 1 | 1362 | table_id: 113 flags: STMT_END_F | | binlog.000034 | 1362 | Query | 1 | 1438 | COMMIT | | binlog.000034 | 1438 | Anonymous_Gtid | 1 | 1503 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000034 | 1503 | Query | 1 | 1578 | BEGIN | | binlog.000034 | 1578 | Table_map | 1 | 1651 | table_id: 118 (eyoucms.ey_weapp_minicount_log) | +---------------+------+----------------+-----------+-------------+------------------------------------------------+ 10 rows in set (0.01 sec)
二、mysqlbinlog工具解析binlog
2.1 语法格式
mysqlbinlog [options] logfile1 logfile2 ... 选项解析： -d, --database=name # 仅显示指定数据库的转储内容 -o, --offset=# # 跳过前N行的日志条目 -r, --result-file=name # 将输入的文本格式的文件转储到指定的文件 -s, --short-form # 使用简单格式 --set-charset=name # 在转储文件的开头增加'SET NAMES character_set'语句 --start-datetime=name # 转储日志的起始时间 --stop-datetime=name # 转储日志的截止时间 -j, --start-position=# # 转储日志的起始位置 --stop-position=# # 转储日志的截止位置
2.2 工具解析技巧
[root@ansible ~]# mysqlbinlog -v --base64-output=decode-row mysql-bin.029732 --start-position=802859893 > /tmp/1.sql [root@ansible ~]# mysqlbinlog -v --base64-output=decode-row mysql-bin.000053 --start-datetime="2021-03-04 14:00:00" --stop-datetime="2021-03-04 15:00:00"
（1）输出事务开始行号
[root@ansible ~]# grep -n -i "BEGIN" /tmp/1.sql [root@ansible ~]# tail -n 10 /tmp/2.txt 25:BEGIN #第一个事务所在行号 143831106:BEGIN #第二个事务所在行号 143831151:BEGIN 143831223:BEGIN 143831295:BEGIN 143831367:BEGIN 143831439:BEGIN 143831511:BEGIN 143831583:BEGIN 143831655:BEGIN [root@ansible ~]# cat /tmp/2.txt | wc -l #统计事务数量
【注】判断是否为大事务，可以通过查看2个事务之间相差多少行即可。如上：第一个事务在25行，第二个事务在143831106行。由此可判断这个事务很大，执行时间很长~
（2）输出解析出的binlog某些行
[root@ansible ~]# sed -n '1,10'p /tmp/2.txt >> 3.txt # 将文件的1~10行输出到3.txt文件中
（3）切换到指定行
命令行模式下输入（n为指定的行号）： ngg / nG # vim打开文件后，直接输入要切换到的行号，如 16gg或16G :n # vim打开文件后，shift+:然后跟行号，如 17 vim +n filename #（注意这里要输入 + 号）如 vim +100 2.txt
（4）检索binlog中对某张表的操作
cat /tmp/1.sql | grep -i "table_name" | more cat /tmp/1.sql | grep -i "table_name" | grep -i "delete" | more
作者：UStarGao
链接：https://www.starcto.com/mysql/190.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-01-23K8S Context和Namespace管理工具kubectx/kubens
- 2021-11-19Linux本地SSD磁盘IO性能压测教程-fio
- 2021-10-28PostgreSQL日志轮滚配置教程
- 2021-02-12MySQL修改非事务引擎
- 2022-06-24一文搞懂MySQL日志区别-binlog/redo log/undo log
