mysql binlog_do_db, replicate_do_db troubles


* master-side filtering:
binlog-do-db, binlog-ignore-db,

* slave-side filtering:
replicate-do-db, replicate-ignore-db
replicate-do-table, replicate-wild-do-table



//=== https://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html
https://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html

binlog-do-db + Statement-based logging
binlog-do-db + Row-based logging

binlog-format : statement-based, row-based, mixed

"""...
...
--binlog-do-db=db_name

This option affects binary logging in a manner similar to the way that --replicate-do-db affects replication.

The effects of this option depend on whether the statement-based or row-based logging format is in use, in the same way that the effects of --replicate-do-db depend on whether statement-based or row-based replication is in use

...
the format used to log a given statement may not necessarily be the same as that indicated by the value of binlog_format. For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements,



...
Statement-based logging. Only those statements are written to the binary log where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not cause cross-database statements such as UPDATE some_db.some_table SET foo='bar' to be logged 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, the list will be treated as the name of a single database if you supply a comma-separated list.

...
Row-based logging. Logging is restricted to database db_name. Only changes to tables belonging to db_name are logged; the default database has no effect on this. Suppose that the server is started with --binlog-do-db=sales and row-based logging is in effect, and then the following statements are executed:

USE prices;
UPDATE sales.february SET amount=amount+100;
The changes to the february table in the sales database are logged in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued.



..."""




//===
http://www.percona.com/blog/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/

"""...
... filtering is not based on the contents of the query — it is based on what database you USE.

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 flow charts at http://dev.mysql.com/doc/refman/5.1/en/replication-rules-db-options.html


..."""

--> http://dev.mysql.com/doc/refman/5.5/en/replication-rules-db-options.html