MySQL 5.7 Release Notes
[+/-]
If a master server does not write a statement to its binary log, the statement is not replicated. If the server does log the statement, the statement is sent to all slaves and each slave determines whether to execute it or ignore it.
On the master, you can control which databases to log changes for
by using the --binlog-do-db and
--binlog-ignore-db options to
control binary logging. For a description of the rules that
servers use in evaluating these options, see
Section 18.2.5.1, “Evaluation of Database-Level Replication and Binary Logging Options”. You should not use
these options to control which databases and tables are
replicated. Instead, use filtering on the slave to control the
events that are executed on the slave.
On the slave side, decisions about whether to execute or ignore
statements received from the master are made according to the
--replicate-* options that the slave was started
with. (See Section 18.1.6, “Replication and Binary Logging Options and Variables”.) In MySQL 5.7.3
and later, the filters governed by these options can also be set
dynamically using the CHANGE REPLICATION FILTER
statement. The rules governing such filters are the same whether
they are created on startup using --replicate-*
options or while the slave server is running by CHANGE
REPLICATION FILTER.
In the simplest case, when there are no
--replicate-* options, the slave executes all
statements that it receives from the master. Otherwise, the result
depends on the particular options given.
Database-level options
(--replicate-do-db,
--replicate-ignore-db) are checked
first; see Section 18.2.5.1, “Evaluation of Database-Level Replication and Binary Logging Options”, for a
description of this process. If no database-level options are
used, option checking proceeds to any table-level options that may
be in use (see Section 18.2.5.2, “Evaluation of Table-Level Replication Options”,
for a discussion of these). If one or more database-level options
are used but none are matched, the statement is not replicated.
For statements affecting databases only (that is,
CREATE DATABASE,
DROP DATABASE, and
ALTER DATABASE), database-level
options always take precedence over any
--replicate-wild-do-table options.
In other words, for such statements,
--replicate-wild-do-table options
are checked if and only if there are no database-level options
that apply. This is a change in behavior from previous versions of
MySQL, where the statement
CREATE DATABASE
dbx was not replicated if the slave had been started
with --replicate-do-db=dbx
--replicate-wild-do-table=db%.t1.
(Bug #46110)
To make it easier to determine what effect an option set will have, it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options.
If any --replicate-rewrite-db
options were specified, they are applied before the
--replicate-* filtering rules are tested.
Note
In MySQL 5.7, all replication filtering options
follow the same rules for case sensitivity that apply to names
of databases and tables elsewhere in the MySQL server, including
the effects of the
lower_case_table_names system
variable.
This is a change from previous versions of MySQL. (Bug #51639)
Because I'm lazy and never select db's before running a query, I use qualified statements for ALL my queries. ie:
Instead of:
USE foofar;
INSERT INTO fling VALUES( 'w00t' );
I do:
INSERT INTO foofar.fling VALUES( 'w00t' );
This was a problem when I went to set up replication. After much research, I found the solution (works with 4 and up):
In your MASTER my.cnf file, DO NOT put any 'binlog-ignore-db' or 'do-db' options. Any db's you wish to not replicate will be handled in the slave conf file ..
In your SLAVE my.cnf file, use a 'replicate-ignore-db=<db>' for all the databases from the master you wish to stop from replicating to the slave.
For all the db's you DO wish to replicate, use a 'replicate-wild-do-table=<db>.%' line.
You end up with a lot of extraneous binlog data for those tables you previously set to ignore in the master conf, but it saves you having to go through all your code and add 'use database' functionality