Using MySQL Binlog for Data Recovery and Rollback

Backend developers often need to revert accidental data changes in production; this article provides a step‑by‑step method using MySQL binlog to recover or roll back data safely.

Step 1: Ensure MySQL binlog is enabled. Run show variables like '%log_bin%'; to check; the value should be ON. Also locate the binlog directory with show variables like '%datadir%'; (e.g., /data/mysql).

Step 2: Navigate to the binlog directory and identify the relevant log files.

Step 3: Switch to the mysqlbinlog tool directory (usually /usr/local/mysql/bin/ or /mysql/bin/) to use the built‑in binlog parser.

Step 4: Use mysqlbinlog to extract DML statements for a specific time window. Examples:

mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015 > template_coupon_tb_product_category.txt

mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015 | more

mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015 | grep template_coupon_tb_product_category > template_coupon_tb_product_category.txt

Common mysqlbinlog options include --start-position, --stop-position, --start-datetime, --stop-datetime, and --database to limit output to a specific schema.

Step 5: Review the extracted SQL statements (or the generated text file), filter as needed, and execute them to re‑insert or update the corrected data.

Enabling binlog on production MySQL instances is strongly recommended because it provides a reliable way to reconstruct and roll back unintended data modifications.

After applying the filtered statements, the database should return to the desired state, mitigating the impact of accidental deletions or incorrect updates.