Replicate_wild_do_table and replicate_do_db differences in MySQL

There is a vulnerability when using replicate_do_db and replicate_ignore_db, and errors occur when you update across libraries.

If you set replicate_do_db=test on Master (Master) server (set in my.conf)
Use MySQL;
Update Test.table1 Set ...
Then the second sentence on the slave (from) server will not be executed

such as Master setting Replicate_ignore_db=mysql
Use MySQL;
Update Test.table1 Set ...
Then the second sentence on slave will be ignored execution

The reason is that after you set up replicate_do_db or replicate_ignore_db, MySQL performs SQL before checking for the current default database, so cross-Library update statements are ignored on slave.

You can use replicate_wild_do_table and replicate_wild_ignore_table on slave to resolve issues with cross-library updates, such as:
replicate_wild_do_table=test.%
Or
Replicate_wild_ignore_table=mysql.%

So you can avoid this problem.

---------------------the gorgeous line------------------------

Full version:

Original: http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/

Author: Baron Schwartz

Why MySQL ' s binlog-do-db option is dangerous

Why MySQL's binlog-do-db option is dangerous.

I see a IoT of people filtering replication with binlog-do-db, Binlog-ignore-db, Replicate-do-db, and replicate-ignore-db. Although there is uses for these, they is dangerous and in my opinion, they is overused. For many cases, there ' a safer alternative.

I found a lot of people through BINLOG-DO-DB, Binlog-ignore-db, Replicate-do-db and replicate-ignore-db to filter replication (some databases), although some use, but, in my opinion, they are dangerous, And they have been abused. For many instances, there is a more secure alternative.

The danger is simple:they don ' t work the the the the-think they do. Consider the following scenario:you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn ' t exis T on the slave) isn ' t replicated. (I'll come back to this in a second, so if you already see the problem, and Don ' t rush to the comment form.)

Why the danger is simple: they don't work as you think. Imagine the following scenario: you set binlog-ignore-db = garbage, so the data in the garbage database (which does not exist on the slave) is not copied, (I'll talk about this later, if you've already found the problem, don't rush to the comment form)

Now, do the following:

Now do the following things:

$ mysql
Mysql> Delete from Garbage.junk;
mysql> use garbage;
mysql> Update production.users Set disabled = 1 where user = "root";

You just broke replication, twice. Once, because your slave is going to execute the first query and there's no such table "Garbage.junk" on the slave. The second time, silently, because the update to production.users isn't replicated, so now the root user isn ' t disabled on The slave.

Copy will be broke2 times, first, because slave try to go West you give the first statement, but Slave does not have such table "Garbage.junk", the second time, implied, because the production.users will not be copied because The root account is not disabled on slave.

Why? Because binlog-ignore-db doesn ' t do what think. The phrase I used earlier, "Data in the garbage database isn ' t replicated," is a fallacy. That's not what it does. In fact, the IT filters out binary logging for statements issued from connections whose the default database is "garbage." In other words, filtering are not based on the contents of the query--it's based on "what database" use.

Why? Because Binlog-ignore-db is not performing as you would like, I said, "The data in the garbage database will not be copied" is wrong, in fact (the database) did not do so. In fact, he is through the default database for the "garbage" connection, filtering the binary (SQL) statement log. In other words, filtering is not a query-based string, but actually a database that you used.

The other configuration options I mentioned work similarly. The BINLOG-DO-DB and BINLOG-IGNORE-DB statements is particularly dangerous because they keep statements from ever being W Ritten to the binary log, which means can ' t use the binary log for point-in-time recovery of your data from a backup.

The other configuration options I mentioned are similar. BINLOG-DO-DB and BINLOG-IGNORE-DB statements are particularly dangerous because they write statements to binary logs. means that you cannot use binary logs to recover data from a backup for a specified time.

In a carefully controlled environment, these options can has benefits, but I'm won ' t talk about this here. (We covered.)

These options are useful in tightly controlled environments, but I won't talk about them (these are included in our book),

The safer alternative is to configure filters on the slave, with options that actually operate on the tables mentioned in The query itself. These is replicate-wild-* options. For example, the safer-avoid replicating data in the garbage database are to configure REPLICATE-WILD-IGNORE-TABLE=G arbage.%. There is still edge cases where that's won ' t work, but it works with more cases and have fewer gotchas.

The safe alternative is to configure filtering on slave, using options based on the tables actually involved in the query, which are: replicate-wild-* options, for example, to avoid replicating the data in the garbage database is configured: Replicate-wild-ign ore-table=garbage.%. There are still some special cases that do not work correctly, but can work in more situations and experience fewer surprises (gotchas).

If You is confused, you should read the replication rules sections of the manual until you know it by heart

If you are confused, you should read the Copy Rules section of the manual until you really understand.

Refer from http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous

Replicate_wild_do_table and replicate_do_db differences in MySQL