mysql命令-mysqlbinlog
一、工具简介
mysqlbinlog 是 MySQL 提供的用于查看和分析二进制日志（Binary Log）文件的命令行工具。二进制日志包含了对数据库进行更改的所有事件（如数据修改、结构变更等），是 MySQL 复制和数据恢复的核心组件。
二、基本语法
mysqlbinlog [options] log_file [log_file ...]
三、主要功能特性
1. 核心功能
-
日志查看：以可读格式显示二进制日志内容
-
日志过滤：按时间、位置、数据库等条件过滤事件
-
格式转换：将二进制日志转换为 SQL 语句
-
远程访问：从远程服务器读取二进制日志
-
数据恢复：用于基于时间点或位置的恢复操作
-
复制调试：分析主从复制问题
2. 支持的日志格式
格式
说明
mysqlbinlog 支持
STATEMENT
基于 SQL 语句的日志
✅ 完全支持
ROW
基于行的日志（默认）
✅ 完全支持
MIXED
混合模式
✅ 完全支持
四、常用选项详解
1. 基本选项
选项
说明
示例
--version
显示版本信息
mysqlbinlog --version
--help 或 -?
显示帮助信息
mysqlbinlog --help
--verbose 或 -v
详细模式
mysqlbinlog -v binlog.000001
--verbose --verbose 或 -vv
更详细模式
mysqlbinlog -vv binlog.000001
--base64-output=value
控制 BASE64 输出
--base64-output=DECODE-ROWS
2. 连接选项
选项
说明
示例
--host=host_name 或 -h
MySQL 服务器主机
-h 192.168.1.100
--port=port_num 或 -P
服务器端口
-P 3307
--user=user_name 或 -u
用户名
-u repl_user
--password[=password] 或 -p
密码
-p 或 -psecret
--protocol=type
连接协议
--protocol=tcp
--socket=path 或 -S
Socket 文件路径
-S /tmp/mysql.sock
3. 过滤选项
选项
说明
示例
--database=db_name 或 -d
只显示指定数据库的事件
-d mydb
--start-datetime=datetime
从指定时间开始
--start-datetime="2024-01-15 09:00:00"
--stop-datetime=datetime
到指定时间结束
--stop-datetime="2024-01-15 18:00:00"
--start-position=pos 或 -j
从指定位置开始
--start-position=107
--stop-position=pos
到指定位置结束
--stop-position=1000
--skip-gtids
跳过 GTID 相关输出
--skip-gtids
--include-gtids=gtid_set
包含指定的 GTID 集合
--include-gtids="source_id:1-5"
--exclude-gtids=gtid_set
排除指定的 GTID 集合
--exclude-gtids="source_id:10-20"
4. 输出选项
选项
说明
示例
--result-file=name 或 -r
输出到指定文件
-r output.sql
--to-last-log 或 -t
输出到最后一个日志
-t
--read-from-remote-server 或 -R
从远程服务器读取日志
-R
--raw
以原始格式输出
--raw
--skip-row-events
跳过行事件
--skip-row-events
--short-form 或 -s
简单格式，只显示事件类型
-s
五、输出解析
1. 二进制日志事件结构
# at 107
#240115 9:00:00 server id 1 end_log_pos 194 CRC32 0x12345678
# Position: Timestamp: Server ID: End_log_pos: CRC32:
# Query thread_id=1 exec_time=0 error_code=0
# use mydb; ← 使用的数据库
SET TIMESTAMP=1705302000/*!*/; ← 时间戳
BEGIN ← 事务开始
/*!*/;
# at 194
#240115 9:00:01 server id 1 end_log_pos 259 CRC32 0x87654321
# Table_map: mydb.users mapped to number 15
# at 259
#240115 9:00:01 server id 1 end_log_pos 315 CRC32 0xabcdef12
# Update_rows: table id 15 flags: STMT_END_F
### UPDATE mydb.users
### WHERE
### @1=1
### @2='old_name'
### SET
### @1=1
### @2='new_name'
# at 315
#240115 9:00:01 server id 1 end_log_pos 346 CRC32 0x34567890
# COMMIT/*!*/; ← 事务提交
2. 关键字段解释
字段
说明
# at N
事件开始位置
server id
服务器ID，用于复制
end_log_pos
下一个事件开始位置
CRC32
校验和（如果启用）
thread_id
执行线程ID
exec_time
执行时间（秒）
error_code
错误代码
Timestamp
事件时间戳
GTID
全局事务标识符
3. 事件类型
事件类型
说明
Query
SQL查询语句
Table_map
表映射信息
Write_rows
插入行事件
Update_rows
更新行事件
Delete_rows
删除行事件
Xid
事务提交事件
Format_desc
日志格式描述
Rotate
日志轮换事件
六、高级技巧
1. 解析特定表的变更
#!/bin/bash # table_change_monitor.sh # 监控特定表的所有变更 TABLE="mydb.users" BINLOG_PATTERN="/var/lib/mysql/binlog.*"
echo "监控表 $TABLE 的变更..."
# 实时监控新日志
while true; do
for binlog in $(ls -tr $BINLOG_PATTERN 2>/dev/null | grep -v index); do
echo "检查日志: $binlog"
mysqlbinlog \
--base64-output=DECODE-ROWS -vv \
$binlog | \
awk -v table="$TABLE" '
/###.*INSERT.*/ && $0 ~ table {print "\[INSERT\] " $0; next} /###.\*UPDATE.\*/ && $0 ~ table {print "[UPDATE] " $0; next}
/###.*DELETE.*`/ && $0 ~ table {print "[DELETE] " $0; next}
'
# 记录已处理的日志
echo "$binlog" >> /tmp/processed_binlogs.txt
done
sleep 60 # 每分钟检查一次
done
2. 生成回滚SQL
#!/bin/bash # generate_rollback_sql.sh # 生成反向SQL用于回滚 BINLOG_FILE="/var/lib/mysql/binlog.000001" START_POS=107STOP_POS=1000 OUTPUT_FILE="/tmp/rollback.sql" echo "生成回滚SQL..." # 先提取正向SQL mysqlbinlog \ --start-position=$START_POS \ --stop-position=$STOP_POS \ --base64-output=DECODE-ROWS -v \ $BINLOG_FILE > /tmp/forward.sql # 转换为回滚SQL（简化示例） cat /tmp/forward.sql | \ sed -n '/###/p' | \ awk ' /### INSERT/ { gsub("### INSERT", "DELETE"); print $0 ";"; next; } /### DELETE/ { gsub("### DELETE", "INSERT"); print $0 ";"; next; } /### UPDATE/ { # 交换WHERE和SET部分 # 这里需要更复杂的解析，实际使用可能需要专门的工具 print "# 需要手动处理UPDATE: " $0; next; } ' > $OUTPUT_FILE echo "回滚SQL已生成: $OUTPUT_FILE"
3. 批量处理二进制日志
#!/bin/bash
# batch_process_binlogs.sh
# 批量处理所有二进制日志
PROCESS_DIR="/var/lib/mysql"
OUTPUT_DIR="/backup/binlog_analysis"
START_DATE="2024-01-01"
mkdir -p $OUTPUT_DIR
# 处理每个日志文件
for binlog in $(ls $PROCESS_DIR/binlog.* 2>/dev/null | grep -v index); do
BASENAME=$(basename $binlog)
echo "处理: $BASENAME"
# 转换为SQL
mysqlbinlog \
--start-datetime="$START_DATE" \
--base64-output=DECODE-ROWS -v \
$binlog > $OUTPUT_DIR/${BASENAME}.sql
# 提取元数据
mysqlbinlog --start-datetime="$START_DATE" $binlog | \
head -50 > $OUTPUT_DIR/${BASENAME}.meta
# 生成统计信息
echo "=== $BASENAME ===" > $OUTPUT_DIR/${BASENAME}.stats
grep -c "^# at " $OUTPUT_DIR/${BASENAME}.sql >> $OUTPUT_DIR/${BASENAME}.stats
grep -c "### INSERT" $OUTPUT_DIR/${BASENAME}.sql >> $OUTPUT_DIR/${BASENAME}.stats
grep -c "### UPDATE" $OUTPUT_DIR/${BASENAME}.sql >> $OUTPUT_DIR/${BASENAME}.stats
grep -c "### DELETE" $OUTPUT_DIR/${BASENAME}.sql >> $OUTPUT_DIR/${BASENAME}.stats
done
echo "批量处理完成"
发表评论
所有评论