• MySQL二进制日志分析


     MySQL二进制日志的主要作用有三个:数据恢复、主从服务器日志复制即同步、审计判断是否有对数据库进行注入的攻击。二进制日志文件无法直接打开,需要工具进行分析。

    1、首先,启动二进制日志。参数是 log_bin,动态参数,所以需要在配置文件中设置如下。也可以直接”log_bin=“参数后面设置文件路径。查看路径的参数为datadir。

    [root@localhost mysql]# cat /etc/my.cnf
    # Remove leading # to turn on a very important data integrity option: logging

    # changes to the binary log between backups.
    log_bin

    ...

    mysql> show variables like 'datadir';
    +---------------+-----------------+
    | Variable_name | Value |
    +---------------+-----------------+
    | datadir | /var/lib/mysql/ |
    +---------------+-----------------+
    1 row in set (0.00 sec)

    mysql>

    2、二进制日志文件列表如下,

    [root@localhost mysql]# ll

    -rw-rw---- 1 mysql mysql 215978 2月 28 15:30 localhost-bin.000001
    -rw-rw---- 1 mysql mysql 120 2月 28 15:30 localhost-bin.000002

    -rw-rw---- 1 mysql mysql 115 3月 2 13:33 localhost-bin.index

    3、二进制日志的分析工具必须通过MySQL 自带的mysqlbinlog 工具。

    例1、从最新的日志文件000002的开始看,这里没有任何的记录,只是记录了文件头信息。
    [root@localhost mysql]# mysqlbinlog --start-position=0 localhost-bin.000002
    Warning: option 'start-position': unsigned value 0 adjusted to 4
    /*!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
    #200228 15:30:30 server id 1 end_log_pos 120 CRC32 0xda43dea1 Start: binlog v 4, server v 5.6.27-log created 200228 15:30:30 at startup
    # Warning: this binlog is either in use or was not closed properly.
    ROLLBACK/*!*/;
    BINLOG '
    lsFYXg8BAAAAdAAAAHgAAAABAAQANS42LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAACWwVheEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaHe
    Q9o=
    '/*!*/;
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    [root@localhost mysql]#

    例2、最新的日志文件000002的Position 为120,而该文件长度刚刚120Bytes。
    [root@localhost mysql]# mysql -uroot -p
    ...
    mysql> show master status;
    +----------------------+----------+--------------+------------------+-------------------+
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +----------------------+----------+--------------+------------------+-------------------+
    | localhost-bin.000002 | 120 | | | |
    +----------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.01 sec)

    mysql>
    ...

     例3、历史日志文件000001的Position 为215486开始 的信息如下。

    [root@localhost mysql]# mysqlbinlog --start-position=215486 localhost-bin.000001
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!40019 SET @@session.max_insert_delayed_threads=0*/;
    ...
    # at 215486
    #200228 15:21:48 server id 1 end_log_pos 215585 CRC32 0x20df1851 Query thread_id=1 exec_time=0 error_code=0
    SET TIMESTAMP=1582874508/*!*/;
    SET @@session.pseudo_thread_id=1/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1075838976/*!*/;
    SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
    /*!C latin1 *//*!*/;
    SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    BEGIN
    /*!*/;
    # at 215585
    ...
    # at 215955
    #200228 15:30:27 server id 1 end_log_pos 215978 CRC32 0x8374e4e4 Stop
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    [root@localhost mysql]#

    例4、将上述历史日志文件000001的Position +1变成215487开始得到的信息如下。
    [root@localhost mysql]# mysqlbinlog --start-position=215487 localhost-bin.000001
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!40019 SET @@session.max_insert_delayed_threads=0*/;
    ...
    ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 553648128, event_type: 1
    ERROR: Could not read entry at offset 215487: Error in log format or read error.
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    [root@localhost mysql]#

    这里看到错误提示,因为215487不是一条记录的起始位置,所以start-position表示的是一条记录的在二进制文件中的物理起始位置。

  • 相关阅读:
    python执行命令行调试工具pdb
    py常用标准库
    Python的 垃圾回收机制
    私有化 : _x: 单前置下划线,私有化属性或方法;__xx:双前置下划线;__xx__:双前后下划线;属性property
    python 解析docx文档将文档名修改为docx里的合同编号
    LINUX7 HA(mysql)
    datax oracle到mysql数据抽取
    SQL优化案例(谓词越界)
    How to Find which Session is Holding a Particular Library Cache Lock (Doc ID 122793.1)
    窗口函数
  • 原文地址:https://www.cnblogs.com/orange-CC/p/12378142.html
Copyright © 2020-2023  润新知