Description of mysql replication filter parameters and mysql filter parameters
Reference documents: http://www.ywnds.com /? P = 6945 https://stackoverflow.com/questions/23191160/whats-the-difference-in-replicate-wild-do-table-and-replicate-do-tablehttp://80888888.blog.51cto.com/2741630/1333249http://keithlan.github.io/2015/11/02/mysql_replicate_rule/ has a total of 6 filter rules: 1 -- replicate-do-db
2 -- replicate-ignore-db
3 -- replicate-do-table
4 -- replicate-wild-do-table
5 -- replicate-ignore-table
6 -- replicate-wild-ignore-table
The first two of the above six rules are database-Level Rules, and the last four are table-Level Rules. Note: 1: database-Level Rules are only applicable to binlog_format = 'Statement 'or 'mixed '.
2: binlog_format = 'row' is not restricted by database-Level Rules, but only by table-Level Rules.
3: Table-Level Rules for all modes. 4: When binlog_format = 'Statement 'or 'mixed', record the use STATEMENT. If -- replicate-do-db is used, cross-database modification is not supported. For example, use test; update db1 set ...... The test database after use is determined here, so the updte statement is not executed. -- Replicate-wild-do-table is recommended.
5: When binlog_format = 'row', the use statement is not recorded. It is only affected by table-Level Rules. Only the following four rules can be configured. Test binlog records in different modes: run SQL: set global binlog_format = 'row'; use test; update db1.t2 set name = 'a200' where id = 105; use db1; the recorded binlog is as follows:
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 2 /*!40019 SET @@session.max_insert_delayed_threads=0*/; 3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 4 DELIMITER /*!*/; 5 # at 4 6 #170808 0:49:08 server id 1882073306 end_log_pos 120 CRC32 0xbddf73ce Start: binlog v 4, server v 5.6.23-72.1-log created 170808 0:49:08 7 # Warning: this binlog is either in use or was not closed properly. 8 # at 120 9 #170808 0:50:16 server id 1882073306 end_log_pos 191 CRC32 0xb2586cef Query thread_id=73 exec_time=0 error_code=010 SET TIMESTAMP=1502124616/*!*/;11 SET @@session.pseudo_thread_id=73/*!*/;12 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;13 SET @@session.sql_mode=1073741824/*!*/;14 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;15 /*!\C utf8 *//*!*/;16 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;17 SET @@session.lc_time_names=0/*!*/;18 SET @@session.collation_database=DEFAULT/*!*/;19 BEGIN20 /*!*/;21 # at 19122 #170808 0:50:16 server id 1882073306 end_log_pos 245 CRC32 0xe7585ab8 Table_map: `db1`.`t2` mapped to number 39523 # at 24524 #170808 0:50:16 server id 1882073306 end_log_pos 365 CRC32 0x86dbbb24 Update_rows: table id 395 flags: STMT_END_F25 ### UPDATE `db1`.`t2`26 ### WHERE27 ### @1=105 /* INT meta=0 nullable=0 is_null=0 */28 ### @2='a100' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */29 ### @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */30 ### @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */31 ### @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */32 ### SET33 ### @1=105 /* INT meta=0 nullable=0 is_null=0 */34 ### @2='a200' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */35 ### @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */36 ### @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */37 ### @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */38 # at 36539 #170808 0:50:16 server id 1882073306 end_log_pos 396 CRC32 0x99f0e5d3 Xid = 3221240 COMMIT/*!*/;
Run the SQL statement: set global binlog_format = 'mixed'; use test; update db1.t2 set name = 'a300' where id = 105; use db1; the recorded binlog is as follows:
1 # at 396 2 #170808 0:53:14 server id 1882073306 end_log_pos 474 CRC32 0x46f75d21 Query thread_id=74 exec_time=0 error_code=0 3 SET TIMESTAMP=1502124794/*!*/; 4 BEGIN 5 /*!*/; 6 # at 474 7 #170808 0:53:14 server id 1882073306 end_log_pos 589 CRC32 0x7193484c Query thread_id=74 exec_time=0 error_code=0 8 use `test`/*!*/; 9 SET TIMESTAMP=1502124794/*!*/;10 update db1.t2 set name='a300' where id=10511 /*!*/;12 # at 58913 #170808 0:53:14 server id 1882073306 end_log_pos 620 CRC32 0xc3e0c9e3 Xid = 3232814 COMMIT/*!*/;15 DELIMITER ;16 # End of log file17 ROLLBACK /* added by mysqlbinlog */;18 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;19 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;