6.10. How Servers Evaluate Replication Rules
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 side, decisions about which statements to log are
based on the --binlog-do-db and
--binlog-ignore-db options that control binary
logging. For a description of the rules that servers use in
evaluating these options, see Section 5.11.4, “The Binary Log”.
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 6.9, “Replication Startup Options”.) The slave
evaluates these options using the following procedure, which first
checks the database-level options and then the table-level
options.
In the simplest case, when there are no
--replicate-* options, the procedure yields the
result that the slave executes all statements that it receives
from the master. Otherwise, the result depends on the particular
options given. In general, 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 non-wildcard options.
Stage 1. Check the database
options.
The slave performs the following test At this stage, the slave
checks whether there are any --replicate-do-db or
--replicate-ignore-db options that specify
database-specific conditions:
No: Permit the statement and proceed to
the table-checking stage.
-
Yes: Test the options using the same
rules as for the --binlog-do-db and
--binlog-ignore-db options to determine
whether to permit or ignore the statement. What is the result
of the test?
This stage can permit a statement for further option-checking, or
cause it to be ignored. However, statements that are permitted at
this stage are not actually executed yet. Instead, they pass to
the following stage that checks the table options.
Stage 2. Check the table options.
First, as a preliminary condition, the slave checks whether
statement-based replication is enabled. If so and the statement
occurs within a stored function, execute the statement and exit.
(If row-based replication is enabled, the slave does not know
whether a statement occurred within a stored function on the
master, so this condition does not apply.)
Next, the slave checks for table options and evaluates them. If
the server reaches this point, it executes all statements if there
are no table options. If there are “do” table
options, the statement must match one of them if it is to be
executed; otherwise, it is ignored. If there are any
“ignore” options, all statements are executed except
those that match any “ignore” option. The following
steps describe how this evaluation occurs in more detail.
-
Are there any --replicate-*-table options?
No: There are no table restrictions,
so all statements match. Execute the statement and exit.
-
Yes: There are table restrictions.
Evaluate the tables to be updated against them. There
might be multiple tables to update, so loop through the
following steps for each table looking for a matching
option. In this case, the behavior depends on whether
statement-based replication or row-based replication is
enabled:
Statement-based replication:
Proceed to the next step and begin evaluating the
table options in the order shown (first the non-wild
options, and then the wild options). Only tables that
are to be updated are compared to the options. For
example, if the statement is INSERT INTO
sales SELECT * FROM prices, only
sales is compared to the options).
If several tables are to be updated (multiple-table
statement), the first table that matches
“do” or “ignore” wins. That
is, the server checks the first table against the
options. If no decision could be made, it checks the
second table against the options, and so on.
-
Row-based replication: All table
row changes are filtered individually. For
multiple-table updates, each table is filtered
separately according to the options. Some updates may
be executed and some not, depending on the options and
the changes to be made. Row-based replication
correctly handles cases that would not replicate
correctly with statement-based replication, as in this
example which assumes that tables in the
foo database should be replicated:
mysql> USE bar;
mysql> INSERT INTO foo.sometable VALUES (1);
-
Are there any --replicate-do-table options?
-
Are there any --replicate-ignore-table
options?
-
Are there any --replicate-wild-do-table
options?
-
Are there any --replicate-wild-ignore-table
options?
-
No --replicate-*-table option was matched. Is
there another table to test against these options?
Examples:
-
No --replicate-* options at all
The slave executes all statements that it receives from the
master.
-
--replicate-*-db options, but no table
options
The slave permits or ignores statements using the database
options. Then it executes all statements permitted by those
options because there are no table restrictions.
-
--replicate-*-table options, but no database
options
All statements are permitted at the database-checking stage
because there are no database conditions. The slave executes
or ignores statements based on the table options.
-
A mix of database and table options
The slave permits or ignores statements using the database
options. Then it evaluates all statements permitted by those
options according to the table options. In some cases, this
process can yield what might seem a counterintuitive result.
Consider the following set of options:
[mysqld]
replicate-do-db = db1
replicate-do-table = db2.mytbl2
Suppose that db1 is the default database
and the slave receives this statement:
INSERT INTO mytbl1 VALUES(1,2,3);
The database is db1, which matches the
--replicate-do-db option at the
database-checking stage. The algorithm then proceeds to the
table-checking stage. If there were no table options, the
statement would be executed. However, because the options
include a “do” table option, the statement must
match if it is to be executed. The statement does not match,
so it is ignored. (The same would happen for any table in
db1.)
