- 16.4 Replication Implementation
- 16.4.1 Replication Implementation Details
- 16.4.2 Replication Relay and Status Files
- 16.4.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 write events to the
binary log 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 5.2.4, “The Binary Log”. You should not use these options to
control the databases and tables that 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 16.1.3, “Replication and Binary Logging Options and Variables”.) 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 nonwildcard options.
Evaluation of --replicate-* options is affected
by whether row-based or statement-based logging is in use, but
regardless of the logging format, database-level options
(--replicate-do-db,
--replicate-ignore-db) are checked
first; see Section 16.4.3.1, “Evaluation of Database-Level Replication and Binary Logging Options”, for a
description of this process. If no matching database-level options
are found, option checking proceeds to any table-level options
that may be in use, as discussed in
Section 16.4.3.2, “Evaluation of Table-Level Replication Options”.
User Comments
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
I believe the comment immediately above ONLY applies to Statement based replication. Row based should work fine with db.qualified queries.
Add your own comment.