Master MySQL Binlog: Enable, Inspect, and Recover Data After Accidental Deletion

What is MySQL binlog?

Binlog (binary log) is a logical log recorded by the MySQL server layer, capturing all DDL and DML statements (excluding queries) as events, along with execution timestamps. Unlike redo logs, binlog stores the original SQL statements, does not overwrite old files, and can be configured with an expiration period.

Enabling binlog in Docker

In the demo a MySQL instance runs inside Docker. To enable binlog, edit the mysqld.cnf file located at /etc/mysql/mysql.conf.d and add the following parameters:

# Enable binlog and set storage directory
log-bin=javaboy_logbin
# Max binlog file size (100 MB)
max_binlog_size=104857600
# Keep binlog files for 7 days
expire_logs_days=7
# Optional: restrict binlog to specific databases
#binlog-do-db=javaboy_db
#binlog-ignore-db=javaboy_no_db
# Flush to disk after each transaction (0 = OS decides)
sync_binlog=0
# Unique server ID (required for MySQL 5.7+)
server-id=1

After saving the file, restart the container: docker restart mysql1 Running show variables like 'log_bin%'; now shows that binlog is active.

Common binlog operations

List binlog files : show master logs; View master status : shows the current binlog file and position.

Flush binlog : flush logs; forces creation of a new binlog file.

Reset binlog : reset master; clears all binlog files (cannot run if replicas are connected).

Inspect binlog events : show binlog events in 'javaboy_logbin.000001'; displays each recorded operation with its POS range.

Data‑recovery hands‑on

Assume a weekly backup is taken with:

mysqldump -uroot -p --flush-logs --lock-tables -B javaboy > /root/javaboy.bak.sql

Later the database is accidentally dropped. The backup restores the state up to the Wednesday 3 AM backup, but changes made after that are lost. Because --flush-logs was used, a new binlog file javaboy_logbin.000002 contains the subsequent operations, including the DROP DATABASE statement.

To recover only up to the point before the drop, run:

mysqlbinlog /var/lib/mysql/javaboy_logbin.000002 --stop-position=764 --database=javaboy | mysql -uroot -p

The --stop-position=764 stops replay at the POS just before the deletion; --database=javaboy limits the restore to the target schema. After execution, the database is restored to its pre‑deletion state.

Summary

The article walks through the fundamentals of MySQL binlog, shows how to enable it in a Docker‑based MySQL server, lists essential binlog commands for monitoring and maintenance, and provides a concrete recovery workflow that leverages binlog events to undo accidental data loss.