The three modes of STATEMENT, ROW and MIXED need to be selected according to the scene. 1. STATEMENT mode records SQL statements with small log volume and strong readability, but may lead to inconsistency between master and slaves. It is suitable for log sensitive and simple SQL environments; 2. ROW mode records row changes, with high consistency, suitable for financial applications and data audits, but large logs and poor readability; 3. MIXED mode automatically switches, taking into account performance and consistency, and is suitable for general systems. If you pay attention to logs and determinism, choose STATEMENT; if you pay attention to consistency or use uncertain functions, choose ROW; if you consider the compromise, choose MIXED. Pay attention to storage engine compatibility and architecture requirements when setting up.

MySQL binlog has three formats: STATEMENT, ROW, and MIXED. Which one to choose depends on your usage scenario and needs.

1. STATEMENT mode: The SQL statement is recorded

In this mode, binlog records the SQL statements you execute, such as UPDATE table SET col = 1 WHERE id = 2 . When the master and slave copy, the slave library will re-execute these statements.

advantage:

• Small log volume, saving disk space
• Strong readability, you can directly see which SQLs are executed

shortcoming:

• Not accurate enough, some functions (such as NOW(), UUID()) may produce different results on the master and slave
• May cause inconsistency in master-slave data

Applicable scenarios:

• Systems that are sensitive to log volume
• SQL is a simple and deterministic application
• Not very dependent on a completely consistent environment for master-slave

2. ROW mode: The actual modified data row is recorded

The ROW pattern records the changes in each row of data, not the SQL executed. For example, a field on which row is changed from A to B.

advantage:

• High data consistency, suitable for systems with high accuracy requirements
• Avoid uncertainty issues in STATEMENT mode

shortcoming:

• Large log volume, especially when large batch updates
• The log is poorly readable, and it requires tool analysis to understand what has been changed.

Applicable scenarios:

• Systems with high master-slave consistency requirements, such as financial applications
• Business using uncertain functions or triggers
• Scenarios where data audits or incremental recovery are required

3. MIXED mode: Use two modes in a mixed manner

MySQL automatically determines when to use STATEMENT and when to use ROW. By default, STATEMENT is used for deterministic operations, and the uncertainties are automatically switched to ROW.

advantage:

• Take into account performance and consistency
• Reduce log volume while ensuring the security of critical operations

shortcoming:

• Inflexible control, sometimes you may want to force a certain format but cannot intervene
• Not as clear as a single format when debugging

Applicable scenarios:

• General system with certain requirements for performance and consistency
• Don't want to manually adjust the format frequently, leave it to MySQL for your own judgment

How to choose? These points need to be considered clearly:

• If you value log size and readability , and SQL is mostly deterministic, consider STATEMENT.
• If you care about master-slave consistency , or often use nondeterministic functions and triggers, you should use ROW.
• If you are not sure and don’t want to make trouble, MIXED is a compromise.

In addition, some functions (such as GTID) recommend using ROW mode; some master-slave architectures (such as multi-level replication) are also more suitable for ROW.

The setup method is very simple, add a sentence to the configuration file:

 binlog_format=ROW

Or dynamic modification at runtime:

 SET GLOBAL binlog_format = 'ROW';

However, it should be noted that not all storage engines support ROW mode, such as the MEMORY engine, cannot.

Basically that's it. Which format to choose is not complicated, but it is easy to ignore the actual impact. It is best to decide based on your own business characteristics.

The above is the detailed content of What are the binlog formats (STATEMENT, ROW, MIXED) and how to choose one?. For more information, please follow other related articles on the PHP Chinese website!