Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  How Servers Evaluate Replication Filtering Rules

17.2.3 How Servers Evaluate Replication Filtering 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, 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, “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 17.1.3, “Replication and Binary Logging Options and Variables”.)

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, “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, “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.

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. An example of the latter that may have unintended effects is the use of --replicate-do-db and --replicate-wild-do-table together, where --replicate-wild-do-table uses a pattern for the database name that matches the name given for --replicate-do-db. Suppose a replication slave is started with --replicate-do-db=dbx --replicate-wild-do-table=db%.t1. Then, suppose that on the master, you issue the statement CREATE DATABASE dbx. Although you might expect it, this statement is not replicated because it does not reference a table named t1.

If any --replicate-rewrite-db options were specified, they are applied before the --replicate-* filtering rules are tested.


Database-level filtering options are case-sensitive on platforms supporting case sensitivity in filenames, whereas table-level filtering options are not (regardless of platform). This is true regardless of the value of the lower_case_table_names system variable.

Download this Manual
User Comments
  Posted by Paul Appleyard on September 12, 2006
Just a note on replicating QUALIFIED statements.

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
  Posted by Dewey Gaedcke on July 2, 2008
I believe the comment immediately above ONLY applies to Statement based replication. Row based should work fine with db.qualified queries.
Sign Up Login You must be logged in to post a comment.