The effect of MySQL binary log format on replication

Replication classification based on SQL statement replication-SBR

Main Library binary log format using statement

Prior to MySQL 5.1 there was only SBR mode, also known as logical replication.

The main library records the SQL statements of the CUD operation, which are read and replayed from the library.

• Advantages

  1. Generate less log volume and save network transfer IO

  2. When the order of the master-slave column is inconsistent, the SBR can still work properly.

     If you make structural changes to a large table, you can modify the slave library first and then the master-detail switch.

• Disadvantages

  1. Cannot guarantee the consistency of master-slave data for uncertain function
  2. For procedure, trigger, function may behave inconsistently with the master/slave (SBR BUG)
  3. How many rows to lock on the main library, and how many rows are needed from the library, so more row locks are needed from the library relative to row replication

Row-based Replication-RBR

The main library binary log format uses row

• Advantages

  2. Friendly to nondeterministic functions, such as UUID ()

  4. Reduce the use of database locks from libraries

      insert into t_order_cnt ( TIMESTR, total, amount) select date (order_date), count (1), sum (amout) from t_order group by date (order_date);              

     1
     • 2
     • 3
     • 1
     • 2
     • 3

     The above SQL performs a lock table operation on the T_order when the main library executes, and the same table is locked for statement replication from the library. But row-based replication only needs to increase the data for the row that corresponds to t_order .

• Disadvantages

  1. Requires the structure of the table of the master-slave database to be consistent, otherwise it may interrupt replication
  2. Unable to activate trigger from library

Github

MySQL MHA cluster built on Docker compose

The effect of MySQL binary log format on replication