Shulou(Shulou.com)06/01 Report--

Introduction

Introduction to related parameters in MySQL5.7 official documentation: https://dev.mysql.com/doc/refman/5.7/en/change-replication-filter.html

Dynamic modification is supported in version 5.7, and the previous version needs to restart the database:

STOP SLAVE SQL_THREAD;CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB= (demo); START SLAVE SQL_THREAD; problem description

Execute statements in the main library, if you do not use use db

The statement to create the new table will not be executed in the slave library, resulting in a master-slave error

Scene reproduction

From the library configuration:

Replicate-ignore-db = testreplicate-do-db = abc

Main library operation:

22:29:04test > use test;Database changed22:30:26test > create table abc.t0417 (id int,name varchar (20)); Query OK, 0 rows affected (0.08 sec) 22:31:37abc > use test;Database changed22:32:05test > insert into abc.t0417 values; Query OK, 1 row affected (0.02 sec)

From the library state, the SQL thread reported an error:

22:31:07abc > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.136.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000015 Read_Master_Log_Pos: 8478 Relay_Log_File: cptest-relay-bin.000007 Relay_Log_Pos: 1292 Relay_Master_Log_File: mysql-bin.000015 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: abc Replicate_Ignore_DB: test Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1146 Last_Error: Error executing row event: 'Table' abc.t0417' doesn't exist' Skip_Counter: 0 Exec_Master_Log_Pos: 8235 Relay_Log_Space: 1709 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1146 Last_SQL_Error: Error executing row event: 'Table' abc .t0417 'doesn't exist' Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4ac80f6d-8063-11e7-9d63-000c291d913c Master_Info_File: / data/mysql/mysql3309/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_ Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 180416 22:32:20 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4ac80f6d-8063-11e7-9d63-000c291d913c:3609-3630 Executed_Gtid_Set: 4ac80f6d-8063-11e7-9d63-000c291d913c:1:3609-3629 A1c747e9-4170-11e8-883f-000c29c6b279:1 Auto_Position: 01 row in set (0.00 sec) to solve the problem

Change the slave configuration:

# replicate-ignore-db = test#replicate-do-db = abcreplicate_wild_do_table = abc.%replicate_wild_ignore_table = test.%

Main library operation:

Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 65Server version: 5.6.35-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.caopeng@192.168.136.128 00:39:16 (none) > use test;Database changedcaopeng@192.168.136.128 00:39:32test > create table abc.test0417 (id int,name varchar (20)); Query OK, 0 rows affected (0.17 sec) caopeng@192.168.136.128 00:40:25test > insert into abc.test0417 values (1 row affected (0.11 sec))

From the library status:

Root@localhost 00:39:06 (none) > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.136.128 Master _ User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000015 Read_Master_Log_Pos: 9644 Relay_Log_File: cptest-relay-bin.000010 Relay_Log_Pos: 771 Relay_Master_Log_File: mysql-bin.000015 Slave_IO_Running : Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: abc.% Replicate_Wild_Ignore_Table: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 9644 Relay_Log_Space: 945 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL _ Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4ac80f6d-8063-11e7-9d63-000c291d913c Master_Info_File: / data/mysql/mysql3309/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for the slave thread to update it Master_Retry_Count O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 4ac80f6d-8063-11e7-9d63-000c291d913c:3609-3635 Executed_Gtid_Set: 4ac80f6d -8063-11e7-9d63-000c291d913c:1:3609-3635 A1c747e9-4170-11e8-883f-000c29c6b279:1-6 Auto_Position: 01 row in set (0.00 sec) root@localhost 00:40:54 (none) > select * from abc.test0417 +-+-+ | id | name | +-+-+ | 1 | b | +-+-+ 1 row in set (0.00 sec) it is recommended to use replicate_wild_do_table when using replicate_do_db and replicate_ignore_db parameters in the main database operation and then other operations.

Replicate_wild_ignore_table

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.