Understanding MySQL Binlog: Management, Formats, Replication, and Recovery

1. Overview The MySQL binlog is a server‑level binary log distinct from InnoDB redo/undo logs; it records SQL statements that modify data or could modify data, storing them as transactions on disk. Its main purposes are replication, data recovery, and incremental backup.

2. Binlog Management

Enable binlog by setting log_bin="/path/to/binlog" in my.cnf.

Query binlog status with show variables like '%log_bin%'; (example output shown).

List binlog files with show binary logs; and view the current master status with show master status;.

Reset binlog using reset master.

3. Binlog Content

Binlog files are binary by default; they can be inspected using:

mysqlbinlog /usr/bin/mysqlbinlog mysql-bin.000007
# options: --read-from-remote-server, --start-position, --stop-position, --start-time, --stop-time
# Example excerpt (positions 1190‑1352) shows an Intvar event, a Query event inserting a row, and an Xid commit event.

Alternatively, use the SQL command SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT ...]; to display events in a readable format.

4. Binlog Formats

MySQL supports three binlog formats, configurable via binlog_format:

ROW : records changes at the row level, providing precise data modifications but potentially generating large logs. Use --base64-output=decode-rows -v to decode.

STATEMENT : records the original SQL statements, reducing log size but requiring full context to ensure correct replay on slaves.

MIXED : combines ROW and STATEMENT, letting MySQL choose the optimal format per statement.

Guidelines: DML statements (INSERT, UPDATE, DELETE) follow the configured format; administrative statements (GRANT, REVOKE, SET PASSWORD) always use STATEMENT.

5. Replication

Replication uses binlog to propagate changes from a master to one or more slaves. The basic steps are:

a. Master writes changes to its binary log.
 b. Slave I/O thread connects to master and requests log entries from a given position.
 c. Master sends the requested log segment.
 d. Slave writes received events to its relay‑log and records the master log file/position.
 e. Slave SQL thread reads the relay‑log and re‑executes the events.

Example configuration:

# Master configuration
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
SHOW VARIABLES LIKE "log_bin";
SHOW MASTER STATUS;
# Create a sample table and insert rows.

# Slave configuration
CHANGE MASTER TO MASTER_HOST='10.108.111.14', MASTER_USER='test', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=120;
START SLAVE;
SHOW SLAVE STATUS\G

The status output shows I/O and SQL threads running and Seconds_Behind_Master = 0, indicating the slave is caught up.

6. Recovery

Binlog can be used to recover data by replaying statements from a specific position range:

# Determine current binlog position
SHOW MASTER STATUS;
# Insert test rows, note the new position.
# Delete a row.
# Recover using mysqlbinlog:
mysqlbinlog --start-position=1847 --stop-position=2585 mysql-bin.000008 > test.sql
mysql> source /var/lib/mysql/3306/test.sql;
# After sourcing, the deleted row is restored.

Recovery simply re‑executes the SQL statements stored in the selected binlog segment.

7. Summary

The article provides a concise overview of MySQL binlog principles and demonstrates its practical use for replication and point‑in‑time recovery.

8. References

https://dev.mysql.com/doc/internals/en/binary-log-versions.html

http://www.php.cn/mysql-tutorials-361643.html

https://www.jianshu.com/p/c16686b35807

https://www.cnblogs.com/jackluo/p/3336585.html

http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html