What is the MySQL binary log (binlog)?
Why Interviewers Ask This
This question targets practical, hands-on experience with MySQL / SQL. Interviewers want to see if you've worked with these concepts in real projects, not just read about them. Strong answers include concrete examples.
Answer
The binary log (binlog) is a MySQL log file that records all changes to the database (INSERT, UPDATE, DELETE, DDL like CREATE TABLE) as ordered events. It does NOT log SELECT queries (no data changes). The binlog enables two critical features: (1) Replication: replicas read the primary's binlog and replay the changes to stay synchronized — replication would not be possible without the binlog; (2) Point-in-time recovery (PITR): take a backup, then replay binlog events up to a specific point in time to recover from data loss or corruption after the backup was taken. Binlog formats: STATEMENT (logs the SQL statements — small files but some statements are non-deterministic like NOW()); ROW (logs actual row changes — larger but deterministic, recommended); MIXED (statement for safe statements, row for others). Enable: log_bin = /var/log/mysql/mysql-bin.log in my.cnf. Useful commands: mysqlbinlog utility to read/replay binlog events; SHOW BINARY LOGS;; PURGE BINARY LOGS TO "mysql-bin.000123";. The binlog is also used by CDC (Change Data Capture) tools like Debezium to stream database changes to Kafka.
Common Mistake
A common mistake is memorizing definitions without understanding implications. When asked this question, go one level deeper — explain what happens when this concept is misused or ignored.
Previous
What are prepared statements and why should you use them?
Next
What is the slow query log in MySQL?