Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

6.10. 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.11.4, “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.9, “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.

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 statement-based replication is enabled. If so and the statement occurs within a stored function, execute the statement and exit. (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.)

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. In this case, the behavior depends on whether statement-based replication or row-based replication is enabled:

      • Statement-based replication: Proceed to the next step and begin evaluating the table options in the order shown (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.

      • Row-based replication: All table row changes are filtered individually. For multiple-table updates, each table is filtered separately according to the options. Some updates may be executed and some not, depending on the options and the changes to be made. Row-based replication correctly handles cases that would not replicate correctly with statement-based replication, as in this example which assumes that tables in the foo database should be replicated:

        mysql> USE bar;
        mysql> INSERT INTO foo.sometable VALUES (1);
        
  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.)


 
 
  Published under the terms of the GNU General Public License Design by Interspire