分类： Mysql/postgreSQL
2010-05-18 13:53:31
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 and options to control binary logging. For a description of the rules that servers use in evaluating these options, see . 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 .) The slave evaluates these options using the procedure described later in this section, which first checks the database-level options and then the table-level options.
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. 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 (, ) are checked first; see , 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 .
If any options were specified, they are applied before the --replicate-* filtering rules are tested.
When evaluating replication options, the slave begins by checking to see whether there are any or options that apply. When using or , the process is similar, but the options are checked on the master.
With statement-based replication, the default database is checked for a match. With row-based replication, the database where data is to be changed is the database that is checked. Regardless of the binary logging format, checking of database-level options proceeds as shown in the following diagram.
上面这个图我看不懂，好像任何语句都会执行，没看到过滤了，有人看过这个图吗？
The steps involved are listed here:
Are there any options?
Yes. Do any of them match the database?
Yes. Execute the statement and exit.
No. Continue to step 2.
No. Continue to step 2.
Are there any options?
Yes. Do any of them match the database?
Yes. Ignore the statement and exit.
No. Continue to step 3.
No. Continue to step 3.
Proceed to checking the table-level replication options, if there are any. For a description of how these options are checked, see .
A statement that is not yet disallowed at this stage is not yet actually executed. The statement is not executed until all table-level options (if any) have also been checked, and the outcome of that process permits execution of the statement.
For binary logging, the steps involved are listed here:
Are there any or options?
Yes. Continue to step 2.
No. Log the statement and exit.
Is there a default database (has any database been selected by )?
Yes. Continue to step 3.
No. Ignore the statement and exit.
There is a default database. Are there any options?
Yes. Do any of them match the database?
Yes. Log the statement and exit.
No. Ignore the statement and exit.
No. Continue to step 4.
Do any of the options match the database?
Yes. Ignore the statement and exit.
No. Log the statement and exit.
For statement-based logging, an exception is made in the rules just given for the , , and statements. In those cases, the database being created, altered, or droppedreplaces the default database when determining whether to log or ignore updates.
can sometimes mean “ignore other databases”. For example, when using statement-based logging, a server running with only does not write to the binary log statements for which the default database differs from sales. When using row-based logging with the same option, the server logs only those updates that change data in sales.
The slave checks for and evaluates table options only if no matching database options were found (see).
First, as a preliminary condition, the slave checks whether statement-based replication is enabled. If so, and the statement occurs within a stored function, the slave executes the statement and exits. 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.
For statement-based replication, replication events represent statements (all changes making up a given event are associated with a single SQL statement); for row-based replication, each event represents a change in a single table row (thus a single statement such as UPDATE mytable SET mycol = 1 may yield many row-based events). When viewed in terms of events, the process of checking table options is the same for both row-based and statement-based replication.
Having reached this point, if there are no table options, the slave simply executes all events. If there are any or options, the event must match one of these if it is to be executed; otherwise, it is ignored. If there are any or options, all events are executed except those that match any of these options. This process is illustrated in the following diagram.
The following steps describe this evaluation in more detail:
Are there any table options?
Yes. Continue to step 2.
No. Execute the event and exit.
Are there any options?
Yes. Does the table match any of them?
Yes. Execute the event and exit.
No. Continue to step 3.
No. Continue to step 3.
Are there any options?
Yes. Does the table match any of them?
Yes. Ignore the event and exit.
No. Continue to step 4.
No. Continue to step 4.
Are there any options?
Yes. Does the table match any of them?
Yes. Execute the event and exit.
No. Continue to step 5.
No. Continue to step 5.
Are there any options?
Yes. Does the table match any of them?
Yes. Ignore the event and exit.
No. Continue to step 6.
No. Continue to step 6.
Are there any or options?
Yes. Ignore the event and exit.
No. Execute the event and exit.
This section provides additional explanation and examples of usage for different combinations of replication filtering options.
Some typical combinations of replication filter rule types are given in the following table:
| Condition (Types of Options) | Outcome |
|---|---|
No --replicate-*options at all: | The slave executes all events that it receives from the master. |
--replicate-*-dboptions, but no table options: | The slave accepts or ignores events using the database options. It executes all events permitted by those options because there are no table restrictions. |
--replicate-*-table options, but no database options: | All events are accepted at the database-checking stage because there are no database conditions. The slave executes or ignores events based solely on the table options. |
| A combination of database and table options: | The slave accepts or ignores events using the database options. Then it evaluates all events permitted by those options according to the table options. This can sometimes lead to results that seem counterintuitive, and that may be different depending on whether you are using statement-based or row-based replication; see the text for an example. |
A more complex example follows, in which we examine the outcomes for both statement-based and row-based settings.
Suppose that we have two tables mytbl1 in database db1 and mytbl2 in database db2 on the master, and the slave is running with the following options (and no other replication filtering options):
replicate-ignore-db = db1
replicate-do-table = db2.tbl2
Now we execute the following statements on the master:
USE db1;
INSERT INTO db2.tbl2 VALUES (1);
The results on the slave vary considerably depending on the binary log format, and may not match initial expectations in either case.
Statement-based replication. The USE statement causes db1 to be the default database. Thus the option matches, and the statement is ignored. The table options are not checked.
Row-based replication. The default database has no effect on how the slave reads database options when using row-based replication. Thus, the statement makes no difference in how the option is handled: the database specified by this option does not match the database where the statement changes data, so the slave proceeds to check the table options. The table specified by matches the table to be updated, and the row is inserted.
