6.9. 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.12.3, “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.8, “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.

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?
- Permit: Do not execute the statement immediately. Defer the decision and proceed to the table-checking stage.
- Ignore: Ignore the statement and exit.

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 the statement occurs within a stored function or (prior to MySQL 5.0.12) a stored procedure. If so, execute the statement and exit. (Stored procedures are exempt from this test as of MySQL 5.0.12 because procedure logging occurs at the level of statements that are executed within the routine rather than at the CALL level.)

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 (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.
Are there any --replicate-do-table options?
- No: Proceed to the next step.
- Yes: Does the table match any of them?
  - No: Proceed to the next step.
  - Yes: Execute the statement and exit.
Are there any --replicate-ignore-table options?
- No: Proceed to the next step.
- Yes: Does the table match any of them?
  - No: Proceed to the next step.
  - Yes: Ignore the statement and exit.
Are there any --replicate-wild-do-table options?
- No: Proceed to the next step.
- Yes: Does the table match any of them?
  - No: Proceed to the next step.
  - Yes: Execute the statement and exit.
Are there any --replicate-wild-ignore-table options?
- No: Proceed to the next step.
- Yes: Does the table match any of them?
  - No: Proceed to the next step.
  - Yes: Ignore the statement and exit.
No --replicate-*-table option was matched. Is there another table to test against these options?
- No: We have now tested all tables to be updated and could not match any option. Are there --replicate-do-table or --replicate-wild-do-table options?
  - No: There were no “do” table options, so no explicit “do” match is required. Execute the statement and exit.
  - Yes: There were “do” table options, so the statement is executed only with an explicit match to one of them. Ignore the statement and exit.
- Yes: Loop.

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