Error
Error Code:
1536
MariaDB Error 1536: Replication Log Format Mismatch
Description
This error occurs when a MariaDB slave server, configured with --log-slave-updates, attempts to replicate row-based binary log events from its master while its own binary log format is not set to row-based. This inconsistency prevents the slave from correctly logging the replicated transactions, halting the replication process. It indicates a conflict in how binary logs are handled between the master and slave.
Error Message
Slave running with --log-slave-updates must use row-based binary logging to be able to replicate row-based binary log events
Known Causes
3 known causesSlave Binlog Format Mismatch
The slave's `binlog_format` system variable is explicitly configured to `STATEMENT` or `MIXED`, which is incompatible with the `ROW` format events received from the master.
Master Binlog Format Change
The master's `binlog_format` was changed to `ROW` after the slave was initially set up with a different format, and the slave's configuration was not subsequently updated.
Inconsistent Replication Setup
The initial replication setup did not ensure `binlog_format` compatibility between master and slave, leading to a mismatch when `--log-slave-updates` is active.
Solutions
3 solutions available1. Configure Slave for Row-Based Replication easy
Modify the slave's configuration to explicitly enable row-based binary logging.
1
Stop the replication threads on the slave server.
STOP SLAVE;2
Edit the MariaDB configuration file (my.cnf or mariadb.conf.d/my.cnf) on the slave server. Locate the `[mysqld]` section.
sudo vi /etc/mysql/my.cnf # Or your specific configuration file path3
Add or modify the following lines to ensure row-based logging is enabled:
[mysqld]
binlog_format=ROW
log_bin=mysql-bin
log_slave_updates=14
Save the configuration file and restart the MariaDB service on the slave server.
sudo systemctl restart mariadb5
Start the replication threads again.
START SLAVE;2. Reconfigure Master to Use Row-Based Binary Logging medium
Change the master's binary log format to ROW, which is the most compatible with `log_slave_updates`.
1
On the master server, edit the MariaDB configuration file (my.cnf or mariadb.conf.d/my.cnf). Locate the `[mysqld]` section.
sudo vi /etc/mysql/my.cnf # Or your specific configuration file path2
Ensure the `binlog_format` is set to `ROW`:
[mysqld]
binlog_format=ROW
log_bin=mysql-bin3
Save the configuration file and restart the MariaDB service on the master server.
sudo systemctl restart mariadb4
On the slave server, reset the replication connection and reconfigure it to point to the master. This may involve using `CHANGE MASTER TO` with the correct master log file and position.
STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER TO MASTER_HOST='<master_ip>', MASTER_USER='<replication_user>', MASTER_PASSWORD='<replication_password>', MASTER_LOG_FILE='<master_current_log_file>', MASTER_LOG_POS=<master_current_log_pos>;
START SLAVE;5
Obtain the current master log file and position from the master server using `SHOW MASTER STATUS;` before resetting the slave.
3. Temporarily Disable `log_slave_updates` (Use with Caution) easy
Disable `log_slave_updates` on the slave if row-based logging is not critical for cascading replication.
1
Stop the replication threads on the slave server.
STOP SLAVE;2
Edit the MariaDB configuration file (my.cnf or mariadb.conf.d/my.cnf) on the slave server. Locate the `[mysqld]` section.
sudo vi /etc/mysql/my.cnf # Or your specific configuration file path3
Comment out or remove the `log_slave_updates` line. Ensure `binlog_format` is compatible if you intend to use the slave as a master for other replicas.
[mysqld]
#log_slave_updates=1
binlog_format=MIXED # Or ROW if needed for other replicas4
Save the configuration file and restart the MariaDB service on the slave server.
sudo systemctl restart mariadb5
Start the replication threads again.
START SLAVE;