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 "批量处理完成"