Statement based vs Row based

Events are recorded within the binary log in statements-based format or row-based format. Statement-based logging, the default, logs statements that made any changes, whereas row-based logging logs changes in individual table rows. A third type of logging, mixed-format logging, changes the logging format in real time according to the type of event.
To change the format for binary logging, SET the system variable binlog_format. Restart the server if the variable is set globally.

SET binlog_format = 'ROW';
SET @@binlog_format = 'STATEMENT';
SET SESSION binlog_format = 'MIXED';
SET GLOBAL binlog_format = 'ROW';
SET @@session.binlog_format = 'STATEMENT';
SET @@global.binlog_format = 'MIXED';

Statement-based logging requires much less storage space, so statement-based replication (SBR) can be completed more quickly.However, SBR has its limitations when compared to row-based replication (RBR). SBR:

is unsafe as not all statements which modify data can be replicated using it.
requires more row-level locks for INSERT, UPDATE or DELETE statements.
must re-evaluate and re-execute complex statements.
requires deterministic user-defined functions (UDFs) to be applied on the slaves.

The following statements are unsafe for SBR. They generate a warning if statement-based logging is used. If the MIXED binary logging format is used, these unsafe statements are logged using row-based format, while the other statements are logged using statement-based format.

Statements containing system functions that may return a different value on slave: FOUND_ROWS(), GET_LOCK(), IS_FREE_LOCK(), IS_USED_LOCK(), LOAD_FILE(), MASTER_POS_WAIT(), PASSWORD(), RAND(), RELEASE_LOCK(), ROW_COUNT(), SESSION_USER(), SLEEP(), SYSDATE(), SYSTEM_USER(), USER(), UUID(), and UUID_SHORT().
References to system variables
User-defined functions (UDFs)
Fulltext plugin
Trigger or stored program updates a table having an AUTO_INCREMENT column
INSERT…ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys.
Updates using LIMIT
Accesses or references log tables
Non-transactional operations after transactional operations.
LOAD DATA INFILE statements.

However, the following non-deterministic functions are safe: CONNECTION_ID(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(), LAST_INSERT_ID(), LOCALTIME(), LOCALTIMESTAMP(), NOW(), UNIX_TIMESTAMP(), UTC_DATE(), UTC_TIME(), and UTC_TIMESTAMP().

Replication with GTIDs	<<<	HOME PAGE	>>>	Administration
last update: 16 Jun 2024

MENU

Statement based vs Row based