(mysql.info) replication-rules

 
 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  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 
 replication-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?
 
         * _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.
 
   1. 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.
 
   2. 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.
 
   3. 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.
 
   4. 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.
 
   5. 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.
 
   6. 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'.)
Info Catalog
(mysql.info) replication-options
(mysql.info) replication
(mysql.info) replication-faq
automatically generated byinfo2html