Row vs Statement vs Mixed Replication in MySQL: Key Differences and Usage

In MySQL, statement-based replication copies SQL statements to replicas, row-based replication copies actual data changes, and mixed replication switches between the two modes automatically. Statement replication is compact but can cause inconsistencies, while row replication is precise but uses more bandwidth. Mixed replication balances both by choosing the best mode per event.

Quick Comparison

This table summarizes the main differences between Row, Statement, and Mixed replication modes in MySQL.

Factor	Statement-Based Replication	Row-Based Replication	Mixed Replication
Replication Method	Sends SQL statements executed on master	Sends actual changed rows data	Automatically switches between statement and row modes
Data Consistency	Can cause inconsistencies with non-deterministic queries	Ensures exact data replication	Improves consistency by choosing safest mode
Network Usage	Lower bandwidth, smaller logs	Higher bandwidth, larger logs	Balanced usage depending on event
Performance Impact	Less CPU and disk usage	More CPU and disk usage	Variable, depends on mode chosen
Use Case	Simple, deterministic workloads	Complex queries, non-deterministic functions	General purpose with mixed workloads

Key Differences

Statement-based replication sends the exact SQL commands executed on the master server to the replicas. This method is efficient in terms of network and storage but can lead to data inconsistencies if the statements are non-deterministic or depend on server-specific settings.

Row-based replication sends the actual data changes (row images) to replicas. This guarantees that replicas have the exact same data as the master, avoiding inconsistencies caused by non-deterministic statements. However, it uses more network bandwidth and storage because it replicates all changed rows.

Mixed replication combines both methods by automatically choosing statement-based replication for safe, deterministic statements and row-based replication for complex or unsafe statements. This mode aims to balance performance and consistency, making it suitable for most workloads.

Statement-Based Replication Example

sql

INSERT INTO employees (id, name, salary) VALUES (1, 'Alice', 50000);
UPDATE employees SET salary = salary * 1.1 WHERE id = 1;

Output

1 row inserted
1 row updated

Row-Based Replication Equivalent

plaintext

/* Instead of sending SQL, MySQL sends row changes like: */
{table: 'employees', action: 'insert', row: {id:1, name:'Alice', salary:50000}}
{table: 'employees', action: 'update', row_before: {id:1, salary:50000}, row_after: {id:1, salary:55000}}

Output

Row insert and update events sent with exact data changes

When to Use Which

Choose statement-based replication when your workload uses simple, deterministic SQL statements and you want to save bandwidth and storage. Use row-based replication when your queries involve non-deterministic functions, triggers, or complex updates that require exact data consistency. Opt for mixed replication if you want a balanced approach that automatically selects the best mode for each event, providing both performance and reliability.

Key Takeaways

Statement-based replication sends SQL commands and is efficient but can cause inconsistencies.

Row-based replication sends actual data changes ensuring accuracy but uses more resources.

Mixed replication automatically switches modes to balance performance and consistency.

Use statement mode for simple, deterministic workloads and row mode for complex or non-deterministic queries.

Mixed mode is best for general use with mixed query types.