• 技术文章 >数据库 >MySQL

    mysql二进制日志的保存方式

    小妮浅浅小妮浅浅2021-07-22 16:25:48原创76

    说明

    1、statement基于SQL语句,每个涉及修改的sql都会记录在binlog中。

    缺点:日志量过大,如sleep()函数、last_insert_id()>、user-definedfuctions(udf)、主从复制等。

    2、ROW基于行,只记录变化的记录,不记录sql的上下文环境。

    缺点:如果遇到update..set..wheretrue,binlog的数据量会增加。

    3、建议使用MIXED,语句采用statement,函数采用ROW存储。

    实例

    # lee @ acer in /var/log/mysql [14:45:44]
    $ mysqlbinlog -vv mysql-bin.000076
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
    /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
    DELIMITER /*!*/;
    # at 4
    #170713 14:35:53 server id 1  end_log_pos 123 CRC32 0xbf52e415  Start: binlog v 4, server v 5.7.18-0ubuntu0.16.04.1-log created 170713 14:35:53
    # Warning: this binlog is either in use or was not closed properly.
    BINLOG '
    yRRnWQ8BAAAAdwAAAHsAAAABAAQANS43LjE4LTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
    AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
    ARXkUr8=
    '/*!*/;
    # at 123
    #170713 14:35:53 server id 1  end_log_pos 154 CRC32 0xafecf53d  Previous-GTIDs
    # [empty]
    # at 154
    #170713 14:43:03 server id 1  end_log_pos 219 CRC32 0x491b5a7e  Anonymous_GTID  last_committed=0    sequence_number=1
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 219
    #170713 14:43:03 server id 1  end_log_pos 318 CRC32 0x4acf1cd1  Query   thread_id=8 exec_time=0 error_code=0
    use `test`/*!*/;
    SET TIMESTAMP=1499928183/*!*/;
    SET @@session.pseudo_thread_id=8/*!*/;
    SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
    SET @@session.sql_mode=1436549152/*!*/;
    SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
    /*!\C utf8 *//*!*/;
    SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
    SET @@session.lc_time_names=0/*!*/;
    SET @@session.collation_database=DEFAULT/*!*/;
    alter table t add c2 text
    /*!*/;
    # at 318
    #170713 14:44:02 server id 1  end_log_pos 383 CRC32 0x8437eaf9  Anonymous_GTID  last_committed=1    sequence_number=2
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 383
    #170713 14:44:02 server id 1  end_log_pos 455 CRC32 0x57adaf15  Query   thread_id=8 exec_time=0 error_code=0
    SET TIMESTAMP=1499928242/*!*/;
    BEGIN
    /*!*/;
    # at 455
    #170713 14:44:02 server id 1  end_log_pos 504 CRC32 0xdba65133  Table_map: `test`.`t` mapped to number 498
    # at 504
    #170713 14:44:02 server id 1  end_log_pos 556 CRC32 0x19769a28  Write_rows: table id 498 flags: STMT_END_F
     
    BINLOG '
    shZnWRMBAAAAMQAAAPgBAAAAAPIBAAAAAAEABHRlc3QAAXQAAwMP/AMeAAIHM1Gm2w==
    shZnWR4BAAAANAAAACwCAAAAAPIBAAAAAAEAAgAD//gDAAAABGhhaGEFAGxsYWxhKJp2GQ==
    '/*!*/;
    ### INSERT INTO `test`.`t`
    ### SET
    ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='haha' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
    ###   @3='llala' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
    # at 556
    #170713 14:44:02 server id 1  end_log_pos 587 CRC32 0x6184a9a1  Xid = 47
    COMMIT/*!*/;
    # at 587
    #170713 14:44:16 server id 1  end_log_pos 652 CRC32 0xc585d273  Anonymous_GTID  last_committed=2    sequence_number=3
    SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
    # at 652
    #170713 14:44:16 server id 1  end_log_pos 724 CRC32 0x63005380  Query   thread_id=8 exec_time=0 error_code=0
    SET TIMESTAMP=1499928256/*!*/;
    BEGIN
    /*!*/;
    # at 724
    #170713 14:44:16 server id 1  end_log_pos 773 CRC32 0x2c52eaa1  Table_map: `test`.`t` mapped to number 498
    # at 773
    #170713 14:44:16 server id 1  end_log_pos 816 CRC32 0xa80bd454  Delete_rows: table id 498 flags: STMT_END_F
     
    BINLOG '
    wBZnWRMBAAAAMQAAAAUDAAAAAPIBAAAAAAEABHRlc3QAAXQAAwMP/AMeAAIHoepSLA==
    wBZnWSABAAAAKwAAADADAAAAAPIBAAAAAAEAAgAD//wBAAAAAmRkVNQLqA==
    '/*!*/;
    ### DELETE FROM `test`.`t`
    ### WHERE
    ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
    ###   @2='dd' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
    ###   @3=NULL /* BLOB/TEXT meta=2 nullable=1 is_null=1 */
    # at 816
    #170713 14:44:16 server id 1  end_log_pos 847 CRC32 0x9d03bcd0  Xid = 48
    COMMIT/*!*/;
    SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    DELIMITER ;
    # End of log file
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    以上就是mysql二进制日志的保存方式,希望对大家有所帮助。更多mysql学习指路:MySQL

    专题推荐:mysql二进制日志
    品易云
    上一篇:mysql读写分离的两种实现 下一篇:mysql主从复制策略的介绍

    相关文章推荐

    • mysql中set类型如何理解• 如何使用PHP删除MySQL数据库• mysql instr条件查询的实现• mysql模块如何使用• mysql服务器端的组件• mysql逻辑备份有什么特点• mysql常见的优化类型• PHP7原生MySQL数据库的操作• mysql在win10配置环境变量的方法• mysql内连接是什么• mysql内连接查询的两种类型• mysql外连接查询如何理解• mysql外连接有哪些类型• mysql中limit的使用方法• mysql读写分离的两种实现

    全部评论我要评论

  • 取消发布评论
  • 

    Python学习网