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进程检查的只是默认的数据库,而不是所有的数据
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 UPDATE
Statement ' 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.
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
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