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

Why MySQL’s binlog-do-db option is dangerous
Posted by Baron Schwartz | Vote on Planet MySQL
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.


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

Now you do the following:

PLAIN TEXTCODE:
$ 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.

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.

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.

In a carefully controlled environment, these options can have benefits, but I won't talk about that here. (We covered that 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.

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

Posted by Baron Schwartz @ 6:01 am :: replication, tips
Print This Post del.icio.us :: digg
Comment RSS
Related posts: :Dangerous command::Beware: ext3 and sync-binlog do not play well together::Global Transaction ID and other patches available!:

10 Comments »
1. John Swindells
This is good to know. Is it true, therefore, that binlog-do-db behaves itself if you have always selected your database beforehand? When you say ‘default database’, does that include a database selected by USE DATABASE?

Comment :: May 14, 2009 @ 7:07 am

2. Sheeri K. Cabral

The other important issue to consider is that binary logs are not only used for replication. They are incremental backups, and if you chose to use the “do” statements, you are effectively erasing history, and should you have a disaster and need the incremental backups, there is no way to retrieve the ignored information.

Comment :: May 14, 2009 @ 7:24 am

3. Baron Schwartz

John, yes that’s true.

Comment :: May 14, 2009 @ 7:26 am

4. peter

Sheeri,

Indeed – so any binlog filtering is evil if you care about your data.
With row level replication it is probably safe to skip tables which you do not care about such as temporary tables from the logging but this is about it.

Comment :: May 14, 2009 @ 8:58 am

5. Robert Hodges

Master side filtering has a host of pitfalls, but even so it is sometimes necessary. Applications can turn off the binlog for selected statements using SET SQL_LOG_BIN=0. That’s better because at least you presumably know exactly what you are doing at the application level. We use this feature for Tungsten Replicator catalogs–replicating them would break our application. We have very flexible filters both on the master as well as the slave but our experience has been exactly what Baron found, namely that filtering on the slave is best.

Comment :: May 14, 2009 @ 5:40 pm

6. Morgan Christiansson
Which is why this behaviour is very clearly documented in the MySQL manual for this setting.

http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html

Comment :: May 14, 2009 @ 7:04 pm

7. Baron Schwartz

Morgan, I have slowly come to realize that most people never read the manual. They read wikihow.com or some other garbage and think they know things. Sad but true.

Comment :: May 14, 2009 @ 8:31 pm

8. Shantanu Oak

>> Because binlog-ignore-db doesn’t do what you think.
I wish it did what it says. It would have brought down the network traffic when I have to update the slaves located in other cities.

Comment :: May 14, 2009 @ 9:56 pm

9. Morgan Christiansson
Shantanu, there is also –replicate-do-table and –replicate-wild-do-table which have different behaviour than –binlog-do-db

See
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-do-table
http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-wild-do-table

The documentation even says “This works for cross-database updates, in contrast to –replicate-ignore-db.”

But you still need to be aware of it’s quirks, queries that JOIN the wrong tables in updates could leave your replication out of sync.

Comment :: May 14, 2009 @ 10:35 pm

10. Simon Mudd

Row based replication in 5.1 makes this sort of thing clearer and that’s almost certainly one of the reasons it was added. For certain SQL statements it can also be much quicker.

The whole replicate-wild* or replicate*ignore options would be so much better handled if they were done differently, for example as done by Sybase were you can easily configure replication on a per table basis.

Even having a few mysql.XXXX tables which define the replication rules would be better than using the my.cnf only options. I’ve only looked in detail at Sybase replication, so am not sure what is offered by Oracle or other commercial RDBMS vendors. MySQL’s replication facilities for simple stuff is great, but the moment you want to do things in a slightly more complex fashion opens the way for potential headaches if you are not really aware of how replication works in MySQL.

Which is why I stand by the claim in my blog posting a while back that it would be so much better if replication were pulled out of mysqld and moved to a separate process dedicated to the task.

Then as mentioned the binlog could be used for what it’s supposed to be: point in time recovery and the replication process could be improved without having to worry so much about what goes on in the database.