Apply MySQL Binary Logs Manually to a Replica

This guide shows you how to manually apply MySQL binary logs to a replica server, which is useful when replication has stopped, you need to recover a specific transaction, or you want to bring a replica up to date without using the automatic replication thread. You’ll learn how to identify the correct log files and positions, use the mysqlbinlog utility, and apply the logs safely.

What You’ll Need

• Access to the source server’s binary log files (either on disk or copied to the replica).
• MySQL client tools installed on the replica (specifically mysqlbinlog).
• MySQL user account with sufficient privileges to execute the binary log (usually REPLICATION SLAVE or SUPER).
• Knowledge of the current replication coordinates (binary log file name and position) from the source server.

Step-by-Step

1. Stop the replica’s SQL thread (if it’s running) to prevent conflicts:

   STOP SLAVE SQL_THREAD;

   (or STOP REPLICA SQL_THREAD; in MySQL 8.0.22+).

2. Identify the binary log files on the source. Run SHOW BINARY LOGS; on the source to list them. If you’re copying files, ensure you have all logs from the last applied position to the target position.
3. Copy the binary log files to the replica if they aren’t already there. Place them in a directory accessible by the MySQL user, e.g., /tmp/binlogs/.
4. Determine the start and end positions for the logs you need. You can use SHOW SLAVE STATUS\G (or SHOW REPLICA STATUS\G) on the replica to see the last applied Relay_Master_Log_File and Exec_Master_Log_Pos. On the source, use SHOW MASTER STATUS to see the current position.
5. Convert binary logs to SQL using mysqlbinlog. For a single log file, run:

   mysqlbinlog /tmp/binlogs/mysql-bin.000123 | mysql -u root -p

   To apply from a specific position, use:

   mysqlbinlog --start-position=123456 --stop-position=789012 /tmp/binlogs/mysql-bin.000123 | mysql -u root -p

   For multiple files, list them all:

   mysqlbinlog /tmp/binlogs/mysql-bin.000123 /tmp/binlogs/mysql-bin.000124 | mysql -u root -p

6. If you need to skip specific events (e.g., a known bad transaction), use --start-datetime and --stop-datetime or the --skip-gtids option if GTIDs are in use.
7. After applying, update the replica’s coordinates so that automatic replication can resume correctly. Use CHANGE MASTER TO (or CHANGE REPLICATION SOURCE TO) with the file and position you applied up to:

   CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000125', MASTER_LOG_POS=456;

   (Replace with your actual file and position.)

8. Restart the SQL thread:

   START SLAVE SQL_THREAD;

   (or START REPLICA SQL_THREAD;).

9. Verify replication status with SHOW SLAVE STATUS\G; check that Slave_IO_Running and Slave_SQL_Running are both Yes, and that Seconds_Behind_Master is decreasing or zero.

Common Mistakes

• Applying logs out of order – Always apply logs in chronological order. Skipping a log or applying a later one first will cause errors.
• Using wrong positions – The start and stop positions must be exact. A wrong position can cause duplicate key errors or missed transactions.
• Forgetting to stop the SQL thread – If the SQL thread is running, it may apply events concurrently, leading to conflicts.
• Not updating the replica coordinates – If you don’t update CHANGE MASTER TO, the replica will try to reapply old events when the IO thread restarts, causing errors.
• Applying logs with GTIDs incorrectly – If GTIDs are enabled, you must use --skip-gtids or ensure the GTID sets are consistent, otherwise you’ll get duplicate transaction errors.

Tips

• Test on a staging replica first – Always test the manual application on a non-production replica to ensure the commands and positions are correct.
• Use --verbose with mysqlbinlog to see what events are being processed, which helps in debugging.
• Backup the replica before applying – Take a logical backup (e.g., mysqldump) or a file-system snapshot before making manual changes.
• Use --base64-output=DECODE-ROWS if you’re using row-based logging and want to see the actual data changes.
• Monitor the output – Pipe the mysqlbinlog output to a file first (e.g., mysqlbinlog ... > /tmp/apply.sql) and review it before running it against the replica.

Troubleshooting

• Errors like “Unknown database” or “Table doesn’t exist” – This usually means you’re applying logs out of order or missing a previous log. Check that the schema exists and that you have all logs from the last applied position.
• Duplicate key errors – This indicates you’re reapplying an event that was already applied. Verify your start position is correct, or use --skip-gtids if GTIDs are enabled.
• Replication breaks after manual apply – Check the Last_SQL_Error in SHOW SLAVE STATUS. It often points to the exact event that failed. Use mysqlbinlog with --verbose to see the SQL around that position.
• mysqlbinlog says “unknown option” – Make sure you’re using the correct syntax for your MySQL version. Options like --start-position are standard, but some older versions may differ.
• Permissions denied when applying – Ensure the MySQL user you’re using has the REPLICATION SLAVE privilege (or SUPER). You can grant it with GRANT REPLICATION SLAVE ON *.* TO 'user'@'host';.

Frequently Asked Questions

Can I apply binary logs from a different server?

Yes, as long as the server’s data is identical up to the point you start applying. The binary logs are not server-specific, but they contain database changes, so the schema and data must match.

What if I don’t know the exact position to start from?

You can use --start-datetime to start from a specific time, but this may include partial transactions. It’s safer to use positions. If you don’t know the position, you can use SHOW BINLOG EVENTS to inspect the log and find the right event.

How do I handle GTID-based replication?

If GTIDs are enabled, use --skip-gtids when applying via mysqlbinlog, and then set the GTID_PURGED or use CHANGE MASTER TO ... MASTER_AUTO_POSITION=1 to sync. Alternatively, you can apply with GTIDs and then set the global gtid_purged to the applied set.

Can I apply only a single transaction?

Yes, you can use --start-position and --stop-position to isolate a single transaction. Find the transaction’s start and end positions by examining the binlog with SHOW BINLOG EVENTS.

What if the replica is far behind and I have many logs?

It’s often faster to re-sync the replica from a fresh backup (e.g., using mysqldump or a physical backup) rather than applying hundreds of logs manually. Manual application is best for a small number of logs.

Do I need to stop the IO thread as well?

No, you only need to stop the SQL thread. The IO thread can keep downloading logs, but the SQL thread applies them. However, if you’re changing replication coordinates, you may need to stop both temporarily.

Related

• Characteristics of Python Snakes: Key Traits Explained
• Xiaomi Photography Kit Upgrade: What’s New
• Switch Desktop Environments Fedora: How-To