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

Troubleshooting and Analysis of Cross-database problems in MySQL Master-Slave synchronization part of Library

Problem: using replication is to set the Replicate_Do_DB parameter to discover that slave data is not updated during cross-library operations

1 set the replicate_do_db = test of the slave library

2 the sql statement of the main library is to insert data into the table of test.an on test7 across the insert of the library.

Use test7

3 check the slave database information after the master database data update and find that the data is not inserted

4 the reason is that mysql checks the current default library before executing sql, so cross-library sql statements will not be executed.

Solution:

1 use the parameter replicate-wild-ignore-table = test.%

The main library inserts data

View data from the library

-2 filter rules using replication combination: filter rules for replicate-do-db and replicate-do-table parameters-temporary test failed

Parameter description

Replicate_Do_DB:

The effects of this option depend on whether statement-based or row-based replication is in use.

Statement-based replication. Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such asUPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected.

Warning

To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database

Replicate-do-db = db_name tells the sql thread from the library to restrict copying sql statements, copying only the default database, and multiple databases can use ","

The reason why cross-library sql is not executed: the "check default database" behavior is that it is difficult to know whether to copy from the sql statement. The sql process examines only the default database, not all the data

O

-replicate-ignore-db = db_name

O

The opposite of replicate-do-db = db_name tells the sql process from the library database to ignore the specified database and not to make any replication.

USE prices

UPDATE sales.january SET amount=amount+1000

The UPDATE statement is replicated in such a case because-replicate-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based replication, the UPDATEstatement's effects are not propagated to the slave, and the slave's copy of the sales.january table is unchanged; in this instance,-- replicate-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored by the slave.

It is also because "check the default database" causes ignored database data updates

Replicate-do-table = db_name.tbl_name

Tells the slave sql process to copy only the specified tables, specifying that multiple tables use this option multiple times. This option applies to cross-library updates and default database updates

This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the

O

Replicate-ignore-table = db_name.tbl_name

O

Tells the slave sql process not to copy the specified table, and specifies that multiple tables use this option multiple times. This option applies to cross-library updates and default database updates

O

Replicate-wild-do-table = db_name.tbl_name

From the sql process of the library to copy any operation to update the table to the specified database name and table name, the schema can contain'% 'and "-" wildcards, like schema match operations. Suitable for cross-library operations

This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. Filter actions on objects after the statement

This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. To filter statements operating on the latter objects, use one or more of the

Replicate-wild-ignore-table = db_name.tbl_name

From the sql process of the library does not copy any operations to update the table to the specified database name and table name, the schema can contain'% 'and "-" wildcards, like schema match operations.

Reference: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html

For the use of parameters, please refer to: http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html

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.