美文网首页
Mysql主从复制异常,定位具体的SQL

Mysql主从复制异常,定位具体的SQL

作者: Jackzzg | 来源:发表于2016-07-27 15:42 被阅读25次

    show binlog events 找到对应的position:

    Mysql bin_log 日志row格式,如何转换为SQL:

    show binlog events in 'mysql-bin.007064' from 0 limit 165000, 100;

    可以看到一个语句跨越了四段position:

    | mysql-bin.007064 | 309483688 | Query | 1 | 309483760 | BEGIN |
    | mysql-bin.007064 | 309483760 | Table_map | 1 | 309483828 | table_id: 71 (guba.guba_threads_all) |
    | mysql-bin.007064 | 309483828 | Delete_rows | 1 | 309483892 | table_id: 71 flags: STMT_END_F |
    | mysql-bin.007064 | 309483892 | Xid | 1 | 309483923 | COMMIT /* xid=2760796538 */

    通过mysqlbinlog 来分析下具体执行的SQL语句内容:

    /usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=309483688 --stop-position=309483923 mysql-bin.007064

    DELETE FROM guba.guba_threads_all WHERE
    @1=16923125 /* INT meta=0 nullable=0 is_null=0 /
    @2='zp' /
    VARSTRING(90) meta=90 nullable=0 is_null=0 /
    @3=1469426431 /
    INT meta=0 nullable=0 is_null=0 /
    @4=1469426431 /
    INT meta=0 nullable=0 is_null=0 /
    @5=1617121 /
    INT meta=0 nullable=0 is_null=0 /
    @6=0 /
    INT meta=0 nullable=1 is_null=0 /
    @7=5 /
    INT meta=0 nullable=1 is_null=0 /
    @8=2 /
    TINYINT meta=0 nullable=0 is_null=0 */

    相关文章

      网友评论

          本文标题:Mysql主从复制异常,定位具体的SQL

          本文链接:https://www.haomeiwen.com/subject/yuvojttx.html