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

Replicate-ignore-db

Set replicate-ignore-db = test on the slave server (set in my.conf)

Execute the following on master

Use test

This statement is not executed on delete from moedb.moe_userinfo where id=3; slave

Replicate_do_db

For example, set replicate_do_db = test on the slave server (set in my.conf)

Execute the following on master

Use moedb

Insert into test.moe (id,name) values (1); this statement is not executed on slave

The reason is that after setting replicate_ignore_db or replicate_do_db, MySQL checks the current default database before performing sql, so cross-library update statements are ignored on Slave.

You can use replicate_wild_do_table and replicate_wild_ignore_table on Slave to solve cross-library update issues, such as:

Replicate_wild_ignore_table=test.%

Or

Replicate_wild_do_table=test.%

If it is for multiple libraries, one library name per line, for example, test and mysql need to be ignored, as follows:

Replicate_wild_ignore_table=test.%

Replicate_wild_ignore_table=mysql.%

References are as follows:

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 lot of people filtering replication with binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases, there's a safer alternative.

I find that many people filter replication (some databases) through binlog-do-db, binlog-ignore-db, replicate-do-db and replicate-ignore-db, although some of them are used, but in my opinion, they are dangerous and they are abused. For many instances, there are more secure alternatives.

The danger is simple: they don't work the way you think they do. Consider the following scenario: you set binlog-ignore-db to "garbage" so data in the garbage database (which doesn't exist on the slave) isn't replicated. (I'll come back to this in a second, so if you already see the problem, 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 slave) will not be copied. (I'll talk about this later, if you've found a problem, don't rush to the comment form.)

Now you do the following:

Now do the following:

$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.

It will be copied broke2 times, the first time, because slave tries to go west and you give the first statement, but there is no such table "garbage.junk" on slave, the second time, implicitly, 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 you 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, it filters out binary logging for statements issued from connections whose default database is "garbage." In other words, filtering is not based on the contents of the query-- it is based on what database you USE.

Why? Because binlog-ignore-db does not execute as you think, I said earlier that "data in the garbage database will not be replicated" is wrong, in fact (the database) did not do so. In fact, he filters the SQL statement log through the default database connection "garbage". In other words, filtering is not based on the query string, but actually on your used database.

The other configuration options I mentioned work similarly. The binlog-do-db and binlog-ignore-db statements are particularly dangerous because they keep statements from ever being written to the binary log, which means you 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 the binary log. It means that you cannot use binary logs to restore data at a specified time from a backup.

In a carefully controlled environment, these options can have benefits, but I won't talk about that here. (We covered that in our book.)

These options are useful in a tightly controlled environment, 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 are replicate-wild-* options. For example, the safer way to avoid replicating data in the garbage database is to configure replicate-wild-ignore-table=garbage.%. There are still edge cases where that won't work, but it works in more cases and has fewer gotchas.

The safe alternative is to configure filtering on slave, using options based on the tables really involved in the query, these are: replicate-wild-* options, for example, a secure solution to avoid replicating data in an garbage database is to configure: replicate-wild-ignore-table=garbage.%. There are still some special circumstances in which it doesn't work properly, but it can work in more cases and encounter fewer gotchas.

If you are confused, you should read the replication rules section of the manual until you know it by heart

If you are confused, you should read the copy rules section in the manual until you really understand it.

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

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.