Collate several MYSQL master-slave synchronization specified library parameters

For mysql primary/secondary synchronization, you need to configure the specified synchronization library and the library that does not need to be synchronized

Several parameters controlling synchronization are sorted out and some details are compared

replicate-do-db

Usage: Replicate-do-db =database_name (specifies the library to synchronize)

replicate-ignore-db

Usage: replicate-ignore-db=db_name (specifies the library to ignore synchronization)

replicate-wild-do-table

Usage: replicate-wild-do-table=db_name.% (specifies all tables in the library that you want to synchronize, wildcard)

replicate-wild-ignore-table

Usage: replicate-wild-do-table=db_name.% (specifies all tables in the library that you want to ignore synchronization, wildcard)

Some minor issues with synchronization between stored procedures and functions

Replicate-wild-do-table and replicate-wild-ignore-table do not work with A stored procedure or function. Whether ignored or specified, the stored procedure triggers A primary and secondary synchronization. For example, there is only one library in the primary instance, A library in the secondary instance, but no library in the secondary instance. If you use these two parameters to synchronize only database A, database A is normally synchronized but database B is not synchronized. In this case, creating A stored procedure in database B on the primary instance will also trigger the master/slave synchronization, and an error will be reported. The secondary instance will execute CREATE B. ROCEDURE directly, indicating that database B does not exist

With replicate-do-DB and replicate-ignore-DB, you can effectively control the synchronization of stored procedures and functions. However, these two parameters have the potential to operate across libraries, resulting in a master/slave mismatch

Use the WILD parameter for out-of-control solutions for stored procedures

First, all libraries are created from the instance, even if only part of the library data needs to be synchronized, but also the other set up well, so that functions and stored procedures can be synchronized normally, there is no impact on the data, there will not be a master/slave error termination situation

2. Use the slave-skip-errors parameter to skip the errors of the specified code and specify the error_code that ignores the errors reported by the synchronization of the stored procedure, and the errors will be skipped automatically. In this way, the data will not be affected and the master and slave will not terminate (this method is not tested, and it is a bit like killing with a stick. If you are interested, have a try.)

Experimental effects of fancy combination parameters

replicate-wild-do-table = A.%

replicate-ignore-db=C

Use A Succeeded in synchronizing A

Use A Failed to synchronize B

Use A Failed to synchronize C

Use B Succeeded in synchronizing A

Use C Failed to synchronize A

replicate-do-db=A

replicate-wild-ignore-table = C.%

Use A Succeeded in synchronizing A

Use A Succeeds in synchronizing B

Use A Failed to synchronize C

Use B Failed to synchronize A

Use C Failed to synchronize A

replicate-do-db=A

replicate-ignore-db=C

Use A Succeeded in synchronizing A

Use A Succeeds in synchronizing B

Use A Succeeded in synchronizing C

Use B Failed to synchronize A

Use C Failed to synchronize A

replicate-wild-do-table = A.%

replicate-wild-ignore-table = C.%

Use A Succeeded in synchronizing A

Use A Failed to synchronize B

Use A Failed to synchronize C

Use B Succeeded in synchronizing A

Use C Succeeded in synchronizing A

The above is some data and experimental results, have questions or ideas, welcome to discuss ~