• MySQL參数binlog-do-db对binlogs写入的影响


    1. 环境描写叙述

    目的:当数据库中设置了binlog-do-db时。在不同的binlog_format=statement | row | mixed 下对binlog的写入影响,这个在主从复制中会有一些坑。因为binlog的写入不全然。极有可能会导致主从不一致的情况的。


    blog地址:http://blog.csdn.net/hw_libo/article/details/40476577

    SuSE 11 sp1 x86_64  +  MySQL 5.5.37

    參数设置:

    binlog-do-db = bosco1

    測试例子1:

    use bosco2;
    create table bosco1.bosco1_tb01(id int);
    create table bosco2.bosco2_tb01(id int);
    insert into bosco1.bosco1_tb01(id) values(1);
    insert into bosco2.bosco2_tb01(id) values(1);
    測试例子2:

    use bosco1;
    create table bosco1.bosco1_tb01(id int);
    create table bosco2.bosco2_tb01(id int);
    insert into bosco1.bosco1_tb01(id) values(1);
    insert into bosco2.bosco2_tb01(id) values(1);

    2. 測试1:use bosco2及SBR/RBR/MBR下

    binlog-do-db=bosco1;
    
    MySQL [(none)]> use bosco2;
    Database changed
    
    MySQL [bosco2]> select @@tx_isolation,@@binlog_format;
    +-----------------+-----------------+
    | @@tx_isolation  | @@binlog_format |
    +-----------------+-----------------+
    | REPEATABLE-READ | STATEMENT       |
    +-----------------+-----------------+
    1 row in set (0.00 sec)
    
    MySQL [bosco1]> flush logs;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [bosco1]> create table bosco1.bosco1_tb01(id int);
    Query OK, 0 rows affected (0.01 sec)
    
    MySQL [bosco1]> create table bosco2.bosco2_tb01(id int);
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [bosco1]> insert into bosco1.bosco1_tb01(id) values(1);
    Query OK, 1 row affected (0.01 sec)
    
    MySQL [bosco1]> insert into bosco2.bosco2_tb01(id) values(1);
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [bosco1]> flush logs;
    Query OK, 0 rows affected (0.01 sec)
    

    那么来查看一下上面的操作有没有写入binlog中:

    # mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000013
    /*!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
    #141026  1:41:09 server id 1303308  end_log_pos 107 	Start: binlog v 4, server v 5.5.37-log created 141026  1:41:09
    # at 107
    #141026  1:43:02 server id 1303308  end_log_pos 150 	Rotate to mysql-bin.000014  pos: 4
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    
    可见。指定了binlog-do-db=bosco1,事务隔离级别RR + binlog_format=statement或是row,在使用其它database(非bosco1数据库)下的全部操作都不会记录到binlogs中。即使是操作binlog-do-db=bosco1下的表;并且DDL也不会被记录。



    3. 測试2:use bosco1及RBR下

    binlog-do-db=bosco1;
    
    MySQL [bosco2]> use bosco1;
    
    MySQL [bosco1]> select @@tx_isolation,@@binlog_format;
    +-----------------+-----------------+
    | @@tx_isolation  | @@binlog_format |
    +-----------------+-----------------+
    | REPEATABLE-READ | ROW             |
    +-----------------+-----------------+
    1 row in set (0.00 sec)
    
    MySQL [bosco1]> flush logs;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [bosco1]> create table bosco1.bosco1_tb01(id int);
    Query OK, 0 rows affected (0.01 sec)
    
    MySQL [bosco1]> create table bosco2.bosco2_tb01(id int);
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [bosco1]> insert into bosco1.bosco1_tb01(id) values(1);
    Query OK, 1 row affected (0.01 sec)
    
    MySQL [bosco1]> insert into bosco2.bosco2_tb01(id) values(1);
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [bosco1]> flush logs;
    Query OK, 0 rows affected (0.01 sec)
    

    那么来查看一下上面的操作有没有写入binlog中:

    # mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000006
    ……
    SET @@session.collation_database=DEFAULT/*!*/;
    create table bosco1.bosco1_tb01(id int)
    /*!*/;
    # at 211
    #141026  1:37:44 server id 1303308  end_log_pos 315 	Query	thread_id=14	exec_time=0	error_code=0
    SET TIMESTAMP=1414258664/*!*/;
    create table bosco2.bosco2_tb01(id int)
    /*!*/;
    # at 315
    #141026  1:37:44 server id 1303308  end_log_pos 385 	Query	thread_id=14	exec_time=0	error_code=0
    SET TIMESTAMP=1414258664/*!*/;
    BEGIN
    /*!*/;
    # at 385
    # at 437
    #141026  1:37:44 server id 1303308  end_log_pos 437 	Table_map: `bosco1`.`bosco1_tb01` mapped to number 49
    #141026  1:37:44 server id 1303308  end_log_pos 471 	Write_rows: table id 49 flags: STMT_END_F
    ### INSERT INTO `bosco1`.`bosco1_tb01`
    ### SET
    ###   @1=1
    # at 471
    #141026  1:37:44 server id 1303308  end_log_pos 498 	Xid = 200
    COMMIT/*!*/;
    # at 498
    #141026  1:37:49 server id 1303308  end_log_pos 541 	Rotate to mysql-bin.000011  pos: 4
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    
    可见,指定了binlog-do-db=bosco1,事务隔离级别RR + binlog_format=row:
    在使用指定的database(bosco1数据库)下操作本身库中的表全部DDL/DML操作都会记录到binlogs中。而操作其它库中的表时,仅仅有DDL操作被记录下来,DML操作都不会记录。


    4. 測试3:use bosco1及SBR/MBR下

    binlog-do-db=bosco1;
    
    MySQL [bosco2]> use bosco1;
    
    MySQL [bosco1]> select @@tx_isolation,@@binlog_format;
    +-----------------+-----------------+
    | @@tx_isolation  | @@binlog_format |
    +-----------------+-----------------+
    | REPEATABLE-READ | STATEMENT       |
    +-----------------+-----------------+
    1 row in set (0.00 sec)
    
    MySQL [bosco1]> flush logs;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [bosco1]> create table bosco1.bosco1_tb01(id int);
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [bosco1]> create table bosco2.bosco2_tb01(id int);
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [bosco1]> insert into bosco1.bosco1_tb01(id) values(1);
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [bosco1]> insert into bosco2.bosco2_tb01(id) values(1);
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [bosco1]> flush logs;
    Query OK, 0 rows affected (0.00 sec)
    
    那么来查看一下上面的操作有没有写入binlog中:
    # mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000008
    ……
    SET @@session.collation_database=DEFAULT/*!*/;
    create table bosco1.bosco1_tb01(id int)
    /*!*/;
    # at 211
    #141026  1:33:43 server id 1303308  end_log_pos 315 	Query	thread_id=14	exec_time=0	error_code=0
    SET TIMESTAMP=1414258423/*!*/;
    create table bosco2.bosco2_tb01(id int)
    /*!*/;
    # at 315
    #141026  1:33:48 server id 1303308  end_log_pos 385 	Query	thread_id=14	exec_time=0	error_code=0
    SET TIMESTAMP=1414258428/*!*/;
    BEGIN
    /*!*/;
    # at 385
    #141026  1:33:48 server id 1303308  end_log_pos 494 	Query	thread_id=14	exec_time=0	error_code=0
    SET TIMESTAMP=1414258428/*!*/;
    insert into bosco1.bosco1_tb01(id) values(1)
    /*!*/;
    # at 494
    #141026  1:33:48 server id 1303308  end_log_pos 521 	Xid = 188
    COMMIT/*!*/;
    # at 521
    #141026  1:33:50 server id 1303308  end_log_pos 591 	Query	thread_id=14	exec_time=0	error_code=0
    SET TIMESTAMP=1414258430/*!*/;
    BEGIN
    /*!*/;
    # at 591
    #141026  1:33:50 server id 1303308  end_log_pos 700 	Query	thread_id=14	exec_time=0	error_code=0
    SET TIMESTAMP=1414258430/*!*/;
    insert into bosco2.bosco2_tb01(id) values(1)
    /*!*/;
    # at 700
    #141026  1:33:50 server id 1303308  end_log_pos 727 	Xid = 189
    COMMIT/*!*/;
    # at 727
    #141026  1:33:58 server id 1303308  end_log_pos 770 	Rotate to mysql-bin.000009  pos: 4
    DELIMITER ;
    # End of log file
    ROLLBACK /* added by mysqlbinlog */;
    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
    
    可见,指定了binlog-do-db=bosco1,事务隔离级别RR + binlog_format=statement。在使用指定的database(bosco1数据库)下操作全部数据库下的表中的全部操作DML都会记录到binlogs中,即使是操作非binlog-do-db=bosco1指定数据库下的表。并且DDL也会被记录。另外在binlog_format=mixed下也是一样的结果。

    有兴趣的朋友。也能够測试下binlog-ignore-db,相信也会大吃一惊的。

    blog地址:http://blog.csdn.net/hw_libo/article/details/40476577

    -- Bosco  QQ:375612082

    ---- END ----
    -------------------------------------------------------------------------------------------------------
    版权全部,文章同意转载。但必须以链接方式注明源地址。否则追究法律责任!

  • 相关阅读:
    上传文件
    Python中@staticmethod和@classmethod的作用和区别
    面向对象 类
    成员修饰符
    class 类 笔记
    模块&包
    java 文件上传下载
    redis 基本操作命令
    redis 和 mysql 的主从复制
    mysql的日志文件及其作用
  • 原文地址:https://www.cnblogs.com/yjbjingcha/p/7000477.html
Copyright © 2020-2023  润新知