Configure Row-Based or Mixed Mode Replication in Mysql

by

Source:

http://mysql.wingtiplabs.com/documentation/row639ae/configure-row-based-or-mixed-mode-replication

http://www.xaprb.com/blog/2012/08/23/avoiding-statement-based-replication-warnings/

By default, MySQL binary logging and replication is statement-based: when the master server commits a change, it writes the SQL statement into its binary log, and any slaves that replicate it execute the same SQL statement into their own database.

MySQL also supports row-based replication: the master server logs the data affected by a change (information to INSERT or UPDATE, the identity of rows to DELETE), and the slave applies those changes directly to its database.

Row-based replication was introduced to provide perfect replication of data that is non-deterministic: when the same statement was executed on the master and slave, the outcome was different.

It can also have a performance impact. Short SQL queries that affect a lot of rows would require more bandwidth to transmit as row-based replication. For example, if replicated by row, this statement would have to uniquely identify 10,000 rows; it would be much more efficiently transmitted as statement-based: 

DELETE FROM important.stuff WHERE id BETWEEN 1 AND 10000;

But difficult to evaluate queries that change relatively little data would be much faster to apply to slaves if the master told them what to change instead of forcing them to repeat the work. For example, this statement requires a table scan and a hash calculation on every row; slaves could save considerable processor time if the master just told them the outcome of all those calculations using row-based replication: 

DELETE FROM user WHERE MD5(User) = "c498faa0787b2eaf054b81f814b1aa12";

The MySQL documentation recommends you use Mixed Mode replication. In Mixed Mode replication, most queries are replicated by statement. But transactions MySQL knows are non-deterministic are replicated by row.

Mixed Mode uses row-based replication for any transaction that:

  • Uses user defined functions
  • Uses the 
    UUID()

    , 

    USER()

    , or 

    CURRENT_USER()

    functions

  • Uses 
    LOAD_FILE

    (which otherwise assumes every slave has the exact same file on the local file system and doesn’t replicate the data)

  • Updates two tables with 
    auto_increment

    columns (the binlog format only carries one 

    auto_increment

    value per statement)

Leave a Comment