Recover MySQL Data with Binlog: A Step‑by‑Step Guide

Friendly reminder: always back up your database before performing any operation.

1. Locate the binlog

Data recovery requires MySQL binlog to be enabled; if not, this guide does not apply. Check the MySQL configuration file (commonly /etc/my.cnf) for the log_bin setting, or look in the default data directory such as /var/lib/mysql or the compiled installation’s date directory.

# cat /etc/my.cnf
log_bin=mysql-bin
# mysql -uroot -p
Enter password:
mysql> show variables like 'log_bin%';
+------------------------------+------------------------------------------+
| Variable_name                | Value                                    |
+------------------------------+------------------------------------------+
| log_bin                     | ON                                       |
| log_bin_basename            | /home/programs/mysql-5.6.26/data/mysql-bin |
| log_bin_index               | /home/programs/mysql-5.6.26/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events   | OFF                                      |
+------------------------------+------------------------------------------+

Use MySQL commands to view the current binlog and its position.

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000001| 343629748 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+

2. Identify the erroneous statement in the binlog

Search the binlog for the time point of the wrong statement and extract the surrounding SQL, or simply export the entire binlog and later remove the bad statement.

# sudo mysqlbinlog --base64-output=DECODE-ROWS -v -d ids mysql-bin.000001 \
  | grep --ignore-case -A3 -B4 '错误的sql语句'

3. Export the binlog to an SQL script

Use mysqlbinlog to convert the binlog into an SQL file, optionally specifying start and end timestamps. If multiple binlog files were generated since the last backup, export them sequentially and import in order.

# sudo mysqlbinlog --base64-output=DECODE-ROWS -v -d ids \
  --start-datetime '2016-10-11 15:22:53' mysql-bin.000001 \
  > /home/stack/data.sql

To restore only a specific database, the -d ids option is used; for table‑level recovery, filter the exported SQL with grep.

# more data.sql | grep --ignore-case -E 'insert|update|delete' | grep table

4. Import the SQL script into the database

If duplicate‑key errors occur during import, add the -f flag to ignore them.

# mysql -uroot -p -f ids < data.sql