Up and Running in 5 Minutes
It took me nearly two hours to track down the root cause when our production DB lost 30,000 rows from the orders table — not a code bug, but someone running a DELETE without a WHERE clause. That incident was what finally made me appreciate the true value of binary logs and the mysqlbinlog tool.
First, check whether binary logging is enabled:
mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"If the result shows Value = ON, you’re good to go. If it’s OFF, add the following lines to your MySQL configuration file and restart:
# /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian)
# /etc/my.cnf.d/mysql-server.cnf (CentOS/RHEL)
[mysqld]
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
server_id = 1
binlog_expire_logs_seconds = 1209600 # 14 days
max_binlog_size = 100MList the currently available binlog files:
mysql -u root -p -e "SHOW BINARY LOGS;"
# +------------------+-----------+-----------+
# | Log_name | File_size | Encrypted |
# +------------------+-----------+-----------+
# | mysql-bin.000001 | 524288 | No |
# | mysql-bin.000002 | 1048576 | No |
# +------------------+-----------+-----------+Read the contents of the latest binlog file:
mysqlbinlog /var/lib/mysql/mysql-bin.000002That’s all it takes — you can now view every change that has occurred in the database.
Understanding binlog_format: Why Choose ROW?
There are 3 binlog recording modes, and I tried all three before making my decision:
- STATEMENT: Records the original SQL statement — compact but risky during replay. Functions like
NOW(),UUID(), orRAND()may return different values when re-executed. - ROW: Records the actual data changes row by row (before/after image). The safest option — this is what I use on production.
- MIXED: Automatically uses STATEMENT for simple SQL and switches to ROW when necessary — a reasonable compromise for read-heavy environments.
My production database runs MySQL 8.0 with around 50GB of data. I set binlog_format = ROW and binlog_row_image = FULL to capture both before and after images. It costs more disk space, but the information available during an investigation is far more complete.
The Most Practical mysqlbinlog Commands
Filter by Time Range
This is the most common use case — when you know when an incident occurred, you can zero in on that window immediately:
mysqlbinlog \
--start-datetime="2026-06-17 14:00:00" \
--stop-datetime="2026-06-17 14:30:00" \
/var/lib/mysql/mysql-bin.000005Filter by a Specific Database
mysqlbinlog --database=myapp_db /var/lib/mysql/mysql-bin.000005Read ROW Format in Human-Readable Form
Binlog ROW format encodes data as base64 by default — not readable with the naked eye. You need to add two flags:
mysqlbinlog --base64-output=DECODE-ROWS -v \
/var/lib/mysql/mysql-bin.000005The output will display the before/after values for each row:
### UPDATE `myapp_db`.`orders`
### WHERE
### @1=1234 /* id */
### @2='pending' /* status */
### @3='2026-06-17 10:00:00' /* updated_at */
### SET
### @1=1234
### @2='completed'
### @3='2026-06-17 14:17:05'Read Multiple Consecutive Binlog Files
mysqlbinlog --base64-output=DECODE-ROWS -v \
/var/lib/mysql/mysql-bin.000003 \
/var/lib/mysql/mysql-bin.000004 \
/var/lib/mysql/mysql-bin.000005Read Binlog from a Remote Server
mysqlbinlog --read-from-remote-server \
--host=192.168.1.100 \
--user=repl_user \
--password \
--database=myapp_db \
mysql-bin.000005Practical Applications: Incident Investigation and Point-in-Time Recovery
Scenario 1 — Finding Who Deleted the Data
Scenario: The products table lost 500 rows around 15:30. Here’s the investigation workflow:
# Step 1: Identify which binlog file covers that time window
mysql -u root -p -e "SHOW BINARY LOGS;"
# Step 2: Export to a text file for easy grepping
mysqlbinlog --base64-output=DECODE-ROWS -v \
--start-datetime="2026-06-17 15:25:00" \
--stop-datetime="2026-06-17 15:35:00" \
/var/lib/mysql/mysql-bin.000005 > /tmp/incident_15h.sql
# Step 3: Find DELETE statements on the products table
grep -i "DELETE.*products" /tmp/incident_15h.sql -A 5Combined with SHOW PROCESSLIST logs or the general query log (if enabled), you can trace exactly which session executed that statement.
Scenario 2 — Point-in-Time Recovery (PITR)
This is exactly what I did to recover the 30K rows accidentally deleted from orders. The process involves two steps:
- Restore the most recent backup (taken at 03:00 AM)
- Use
mysqlbinlogto replay the binlog from after the backup point up to just before the catastrophic DELETE statement
# Find the position of the DELETE to know where to stop
mysqlbinlog --base64-output=DECODE-ROWS -v \
/var/lib/mysql/mysql-bin.000005 | grep -B 10 "DELETE FROM orders" | head -30
# Output will contain something like: # at 1234567
# That is the position to stop at
# Replay from after the backup up to just before that position
mysqlbinlog \
--start-datetime="2026-06-17 03:00:05" \
--stop-position=1234567 \
/var/lib/mysql/mysql-bin.000005 | mysql -u root -p myapp_dbKey tip: Before any risky operation (bulk delete, schema migration, etc.), always run SHOW MASTER STATUS and note down the File and Position. That’s your rollback point if something goes wrong.
mysql -u root -p -e "SHOW MASTER STATUS\G"
# *************************** 1. row ***************************
# File: mysql-bin.000005
# Position: 1189432
# Binlog_Do_DB:
# Binlog_Ignore_DB:Scenario 3 — Daily Database Activity Audit
# Export all changes for the day to a file
mysqlbinlog --base64-output=DECODE-ROWS -v \
--start-datetime="2026-06-17 00:00:00" \
--stop-datetime="2026-06-17 23:59:59" \
/var/lib/mysql/mysql-bin.000005 > audit_20260617.sql
# Count the number of UPDATE/DELETE/INSERT events
echo "INSERT count: $(grep -c '^### INSERT' audit_20260617.sql)"
echo "UPDATE count: $(grep -c '^### UPDATE' audit_20260617.sql)"
echo "DELETE count: $(grep -c '^### DELETE' audit_20260617.sql)"Advanced: Automation and Binlog Encryption
Alert Script for Large DELETE Operations
I wrote this small script to run via cron every hour, sending an alert if it detects an unusually high number of deleted rows:
#!/bin/bash
# /opt/scripts/check_large_deletes.sh
MYSQL_PASS="your_password"
THRESHOLD=500
BINLOG_PATH="/var/lib/mysql"
CURRENT_FILE=$(mysql -u root -p"$MYSQL_PASS" -Nse "SELECT @@log_bin_basename;" 2>/dev/null)
LATEST=$(ls -t ${CURRENT_FILE}.* 2>/dev/null | head -1)
[ -z "$LATEST" ] && exit 0
DELETE_COUNT=$(mysqlbinlog --base64-output=DECODE-ROWS -v \
--start-datetime="$(date -d '1 hour ago' '+%Y-%m-%d %H:%M:%S')" \
"$LATEST" 2>/dev/null | grep -c '^### DELETE')
if [ "$DELETE_COUNT" -gt "$THRESHOLD" ]; then
echo "[ALERT] $DELETE_COUNT DELETE events in the past hour on $(hostname)" | \
mail -s "MySQL Binlog Alert" [email protected]
fiBinlog Encryption on MySQL 8.0.14+
If binlog_encryption = ON is set, you cannot read binlog files directly from disk. You must connect through the server:
mysqlbinlog --read-from-remote-server \
--host=127.0.0.1 \
--user=root \
--password \
--base64-output=DECODE-ROWS -v \
mysql-bin.000005Practical Tips from 6 Months on Production
- Set an appropriate binlog retention period: I keep 14 days — enough time to investigate incidents without consuming too much disk. With 50GB of data, my binlogs generate roughly 1–2GB per day depending on traffic.
- Filter by position instead of reading everything: If a binlog file exceeds 500MB, using
--start-positionand--stop-positionis significantly faster than reading the entire file and grepping. - mysqlbinlog does not record the MySQL user: Binlog captures what changed, not who was logged in. For per-user auditing, you need to enable the MySQL Audit Log Plugin or
general_logas well. - Always test binlog replay in a test environment first: When performing PITR, always test against a database copy first — replaying binlog directly onto production without verification can create duplicates or conflicts.
- Combine with
pt-query-digest: This Percona Toolkit tool can read binlogs and aggregate query patterns — very useful for generating weekly audit reports.
After six months operating a MySQL 8.0 production database with around 50GB of data, I think of the binlog as the flight data recorder of your database — rarely needed, but absolutely critical when you do need it. Investing the time to get comfortable with mysqlbinlog now will save you hours of stressful troubleshooting when a real incident strikes.
