Bug #101306 | relay log copy database events that not included in replicate do db filter | ||
---|---|---|---|
Submitted: | 24 Oct 2020 22:07 | Modified: | 28 Oct 2020 9:45 |
Reporter: | mohamed atef | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S1 (Critical) |
Version: | 5.7.24 | OS: | Windows |
Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[24 Oct 2020 22:07]
mohamed atef
[27 Oct 2020 22:29]
MySQL Verification Team
Hi, Using binlog-do-db is dangerous if you are not sure what you are doing. If you look at the test where my.cnf has binlog-do-db=db1 you will see that table test.test is not in binlog as you would expect, but table test.t2 is in binlog as you probably don't expect but is according to how filtering in mysql server work. All best Bogdan mysql [127.0.0.1:8022] {msandbox} ((none)) > use test; Database changed mysql [127.0.0.1:8022] {msandbox} (test) > create table test (x int); Query OK, 0 rows affected (0.00 sec) mysql [127.0.0.1:8022] {msandbox} (test) > use db1 Database changed mysql [127.0.0.1:8022] {msandbox} (db1) > create table test.t2 (x int); Query OK, 0 rows affected (0.01 sec) mysql [127.0.0.1:8022] {msandbox} (db1) > \q Bye [arhimed@localdev msb_8_0_22]$ ./stop stop /home/arhimed/sandboxes/msb_8_0_22 [arhimed@localdev msb_8_0_22]$ cd data [arhimed@localdev data]$ ~/opt/mysql/8.0.22/bin/mysqlbinlog --base64-output=never binlog.000006 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #201027 23:20:04 server id 1 end_log_pos 125 CRC32 0xeeebd5d5 Start: binlog v 4, server v 8.0.22 created 201027 23:20:04 at startup ROLLBACK/*!*/; # at 125 #201027 23:20:04 server id 1 end_log_pos 156 CRC32 0xb429f5b5 Previous-GTIDs # [empty] # at 156 #201027 23:20:11 server id 1 end_log_pos 233 CRC32 0xf5811c64 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1603837212001647 immediate_commit_timestamp=1603837212001647 transaction_length=182 # original_commit_timestamp=1603837212001647 (2020-10-27 23:20:12.001647 CET) # immediate_commit_timestamp=1603837212001647 (2020-10-27 23:20:12.001647 CET) /*!80001 SET @@session.original_commit_timestamp=1603837212001647*//*!*/; /*!80014 SET @@session.original_server_version=80022*//*!*/; /*!80014 SET @@session.immediate_server_version=80022*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 233 #201027 23:20:11 server id 1 end_log_pos 338 CRC32 0x2f7b8ab1 Query thread_id=8 exec_time=0 error_code=0 Xid = 4 SET TIMESTAMP=1603837211/*!*/; 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=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; /*!80016 SET @@session.default_table_encryption=0*//*!*/; create database db1 /*!*/; # at 338 #201027 23:20:57 server id 1 end_log_pos 415 CRC32 0x00ce1bef Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1603837257855559 immediate_commit_timestamp=1603837257855559 transaction_length=192 # original_commit_timestamp=1603837257855559 (2020-10-27 23:20:57.855559 CET) # immediate_commit_timestamp=1603837257855559 (2020-10-27 23:20:57.855559 CET) /*!80001 SET @@session.original_commit_timestamp=1603837257855559*//*!*/; /*!80014 SET @@session.original_server_version=80022*//*!*/; /*!80014 SET @@session.immediate_server_version=80022*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 415 #201027 23:20:57 server id 1 end_log_pos 530 CRC32 0x89bf09d8 Query thread_id=8 exec_time=0 error_code=0 Xid = 18 use `db1`/*!*/; SET TIMESTAMP=1603837257/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; create table test.t2 (x int) /*!*/; # at 530 #201027 23:24:48 server id 1 end_log_pos 553 CRC32 0x68368834 Stop 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*/; [arhimed@localdev data]$
[27 Oct 2020 22:44]
mohamed atef
Look i know what im doing But you can't understand In server 1 when i add binlog-do-db=db3 I add it as i want to be replicated by third server But here not the problem In server 2 i didnot want it to replicate db3 So i add only to conf file replicate-do-db=db1 Replicate-do-db=db2 Why server 2 relay log Can't skip the transactions For tables of db3 And replication halted in this transaction Especialy if the transaction was in the end of relay log file And server can't rotate to next relay log file
[27 Oct 2020 22:51]
mohamed atef
Repeat my case and check both binlog and relay log files
[28 Oct 2020 9:05]
MySQL Verification Team
Hi, That is not how do-db filtering works. kind regards Bogdan
[28 Oct 2020 9:45]
mohamed atef
Hi your reply is amazing!!!! In slave server Replication filter Said dont replicate db3 Only replicate db1,db2 Dont binlog db3 Only binlog db1,db2 Why slave server stacked at event In db3 And relay log cant skip this trabsaction The transaction is update db3.tbl1 set id=1 where id=2; Why slave server cant skip it And the transaction is the last one at relaylog slave cant rotate to next relay log Answer my question I had check replication filter documentation And find that the slave shoukd skip this transaction