MySQL 8.0 Full Backup and Incremental Recovery Using mysqldump and mysqlbinlog

This guide walks through a complete backup and point‑in‑time recovery workflow for MySQL 8.0.

First, basic database operations are shown, such as listing databases, creating a table, and inserting sample rows:

mysql> show databases;

mysql> use mzl;

mysql> create table class (id int(10) not null auto_increment, name varchar(30), grade varchar(20), primary key(id));

mysql> insert into class values (25,'田总','博士'),(26,'范总','大四');

To create a full logical backup, the mysqldump utility is used with options for a single‑transaction dump and master‑log information:

mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --flush-privileges --events --routines --all-databases > /tmp/mzldb0105.sql

After the backup, the original database is dropped and recreated, then the dump file is imported to restore the full backup:

mysql> drop database mzl;

mysql> create database mzl;

mysql> source /tmp/mzldb0105.sql

Because new rows were inserted into the tables after the backup (e.g., into class and class02 ), the restored database lacks these later changes. To recover the missing data, the binary log is processed with mysqlbinlog to perform an incremental recovery.

The start position corresponds to the point right after the full backup (the MASTER_LOG_POS shown in the dump), and the stop position is the position just before the failure or before the unwanted data:

# View the dump header for start position

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=191;

After confirming the end position (e.g., 958) in the binary log, the incremental recovery is executed:

mysqlbinlog --no-defaults --start-position=191 --stop-position=958 /data/mysql/mysql/mysql-bin.000005 | mysql -uroot -p123456

This command replays the binary log events between the specified positions, restoring the rows that were inserted after the full backup.