replicate_do_db parameter found when cross-library operation is not updated from library data

problem: Using replication is set replicate_do_db parameter found when cross-library operation is not updated from library data

1 Setting the replicate_do_db = Test from the library

2 The SQL statement for the main library is a cross-library insert on TEST7 that inserts data onto the TEST.A table.

use test7;

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736616 /clipboard.png?version=1&modificationdate=1431918637000&api=v2 "alt=" clipboard.png?version=1& Modificationdate "/>

3 Main Library data updated after viewing from library information discovery data is not inserted

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736616 /clipboar23.png?version=1&modificationdate=1431918658000&api=v2 "alt=" clipboar23.png?version=1& Modificationdat "/>

4 The reason is that MySQL checks the current default library before executing SQL, so the cross-Library SQL statements are not executed.

Solution:

1 Using Parametersreplicate-wild-ignore-table =test.%

Main Library Insert Data

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/5736616/QQ%E6%88%AA%E5%9B%BE20150518113150.jpg?version=1&modificationDate=1431920158000&api=v2" alt="QQ%E6%88%AA%E5%9B%BE20150518113150.jpg?v" />

从库查看数据

650) this.width=650;" class="confluence-embedded-image" src="http://wiki.op.xywy.com/download/attachments/5736616/QQ%E6%88%AA%E5%9B%BE20150518113434.jpg?version=1&modificationDate=1431920192000&api=v2" alt="QQ%E6%88%AA%E5%9B%BE20150518113434.jpg?v" />


----------2 Combined filtering rules using replication: Filtering rules forreplicate-do-db and replicate-do-table two parameters-------------transient test failure

Parameter description

replicate_do_db:

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

statement-based replication. Tell the slave SQL thread to restrict replication to statements where the default database (which is, the one selected by USE) is db_name. To specify more than one database, use the 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 would be treated as the name of a single database

• replicate-do-db =db_name  告诉从库sql线程限制复制sql语句，只复制默认的数库，多个数据库可以使用","

  跨库sql不被执行的原因：“检查默认的数据库”行为是从sql语句中很难得知是否复制。 sql进程检查的只是默认的数据库，而不是所有的数据

  • —replicate-ignore-db =db_name

Meaning and replicate-do-db = db_name The opposite is telling the SQL process from the library database to ignore the specified database without making 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 the default database (determined by the USE statement). Because the sales Database was specified explicitly in the statement, the statement have 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 is ignored by the slave.

The same is true because the "check default database" causes the ignored database data to be updated

• replicate-do-table =db_name.tbl_name

  Tells the SQL process from the library to copy only the specified tables, specifying multiple tables using this option multiple times. This option is available for cross-Library updates and default database updates.

• This option is affects only statements the apply to tables. IT does not affect statements this apply only to other database objects, such as stored routines. To filter statements operating in stored routines, use one or more of the

• • replicate-ignore-table =db_name.tbl_name

    Tells the SQL process from the library not to copy the specified table, specifying multiple tables using this option multiple times. This option is available for cross-Library updates and default database updates.

• replicate-wild-do-table =db_name.tbl_name

Copying any Update table operation from the library's SQL process to the specified database name and table name, the pattern can contain '% ' and '--' wildcard characters, like pattern-matching operations. Applies to cross-library operations

This option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. Filter The action on the object behind 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

The SQL process from the library does not copy any Update table operations to the specified database name and table name, and the pattern can contain '% ' and '--' wildcard characters, like pattern-matching operations.

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

The use of parameters can be consulted: http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html

replicate_do_db parameter found when cross-library operation is not updated from library data