Learning: MySQL point-in-time recovery for beginners - full backups, binary logs and undoing an accidental DROP TABLE

#About this session

#Module 1: the ideas you need first

A backup alone only takes you back to the moment it was taken. Everything after it is lost, unless you also kept a record of every change.

That record is the binary log (binlog). MySQL writes every committed change to it, in order, in files named binlog.000001, binlog.000002 and so on. Every event has a position (a byte offset in its file).

Point-in-time recovery (PITR) = restore the last full backup + replay the binlog from the backup's position up to just before the mistake.

  full backup                         mistake
  (binlog.000002 pos 157)             (DROP TABLE at pos 4321)
       │                                   │
 ──────┼──────── changes you want ─────────┼──── changes after ───►
       │◄──────── replay this part ───────►│

Since MySQL 8.0, binary logging is on by default.

#Module 2: start the lab server

docker run -d --name mysql-lab -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD='Lab#Pass2026' mysql:8.4

docker logs -f mysql-lab     # wait for "ready for connections" on port 3306, then Ctrl+C

A helper to save typing (run in your shell):

m() { docker exec -i mysql-lab mysql -uroot -p'Lab#Pass2026' "$@"; }
m -e "SELECT VERSION();"

Check the binlog settings:

m -e "SHOW VARIABLES WHERE Variable_name IN ('log_bin','binlog_format','binlog_expire_logs_seconds','gtid_mode');"
m -e "SHOW BINARY LOGS;"

log_bin = ON, binlog_format = ROW, retention of 2592000 seconds (30 days) by default. GTIDs are off by default; this lab uses file positions. (Ignore the warning about using a password on the command line; it's a lab.)

#Module 3: create data

m <<'SQL'
CREATE DATABASE shop;
CREATE TABLE shop.customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE shop.orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO shop.customers (name) VALUES ('Asha'),('Ben'),('Chen');
INSERT INTO shop.orders (customer_id, amount) VALUES (1,120.50),(2,75.00),(3,310.20);
SQL

#Module 4: take a consistent full backup

docker exec mysql-lab mysqldump -uroot -p'Lab#Pass2026' \
  --single-transaction --source-data=2 \
  --routines --triggers --events \
  --databases shop > shop-full.sql

Find the coordinates the backup recorded:

grep -m1 "CHANGE REPLICATION SOURCE TO" shop-full.sql
# -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=1234;

Write those two values down. They are where replay must start.

#Module 5: a normal day, then the mistake

Changes after the backup, which we must not lose:

m -e "INSERT INTO shop.orders (customer_id, amount) VALUES (1, 55.00), (2, 99.99);"
m -e "UPDATE shop.orders SET amount = amount * 1.1 WHERE customer_id = 3;"
m -e "INSERT INTO shop.customers (name) VALUES ('Dev');"
m -e "SELECT COUNT(*) AS orders, SUM(amount) AS total FROM shop.orders;"

Note the count and total. Now the accident:

m -e "DROP TABLE shop.orders;"

And, as happens in real life, a little more good work after it:

m -e "INSERT INTO shop.customers (name) VALUES ('Eve');"

#Module 6: find the mistake in the binlog

First, stop writing to the file you are about to read, so it doesn't change under you:

m -e "FLUSH BINARY LOGS;"
m -e "SHOW BINARY LOGS;"

List the events in the file that holds the mistake (the file from the backup coordinates, or the one before the newest):

m -e "SHOW BINLOG EVENTS IN 'binlog.000002';" | grep -n -i -B2 "drop table"

You'll see something like:

Log_name       Pos   Event_type      ... End_log_pos  Info
binlog.000002  4242  Anonymous_Gtid  ... 4321         SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
binlog.000002  4321  Query           ... 4455         DROP TABLE `shop`.`orders` /* generated by server */

The DROP transaction starts at the Anonymous_Gtid event, so the stop position is 4242: replay everything before that event.

You can see the same with mysqlbinlog, which also decodes row events into readable pseudo-SQL:

docker exec mysql-lab mysqlbinlog --base64-output=DECODE-ROWS -v \
  --start-position=1234 /var/lib/mysql/binlog.000002 | less

#Module 7: restore and replay

1. Restore the full backup. The dump drops and recreates each table it contains:

m < shop-full.sql
m -e "SELECT COUNT(*) FROM shop.orders;"     # 3, the state at backup time

2. Replay the binlog from the backup position to the stop position:

docker exec mysql-lab bash -c \
  "mysqlbinlog --start-position=1234 --stop-position=4242 /var/lib/mysql/binlog.000002 \
   | mysql -uroot -p'Lab#Pass2026'"

Use your own numbers. If the changes span several binlog files, pass all of them in order; --start-position applies to the first file and --stop-position to the last.

3. Check:

m -e "SELECT COUNT(*) AS orders, SUM(amount) AS total FROM shop.orders;"
m -e "SELECT * FROM shop.customers;"

Orders match the numbers you wrote down before the drop. Customers show Dev but not Eve, because Eve was added after the stop position. Replay the rest, skipping only the DROP transaction:

docker exec mysql-lab bash -c \
  "mysqlbinlog --start-position=4455 /var/lib/mysql/binlog.000002 | mysql -uroot -p'Lab#Pass2026'"

Here 4455 is the End_log_pos of the DROP event. Now Eve is back too.

Caution

On a real server, stop the application before recovery so new writes don't land on half-restored tables. Better still, restore to a separate server, check the data, then copy the table back or switch over.

#Module 8: keeping binlogs safe

PITR only works if the binlogs exist and are not on the same disk that just failed.

• Retention: binlog_expire_logs_seconds (default 30 days) must cover the time back to your oldest backup you'd use.
• Copy binlogs off the server continuously:

mysqlbinlog --read-from-remote-server --host=db01 --user=binlog_reader -p \
  --raw --stop-never --result-file=/backup/binlogs/ binlog.000001

• SHOW BINARY LOG STATUS (8.4; SHOW MASTER STATUS in 8.0) shows the current file and position.

#Module 9: clean up

docker rm -f mysql-lab
rm shop-full.sql

#Exercises

1. Instead of DROP TABLE, run DELETE FROM shop.orders; (no WHERE). Find it in the binlog with mysqlbinlog --base64-output=DECODE-ROWS -v. How does a row-format DELETE look compared with the DROP?
2. Recover using a time instead of a position: --stop-datetime="2026-10-27 10:15:00". Why is a position more precise?
3. Restart the container with --gtid-mode=ON --enforce-gtid-consistency=ON. Repeat the lab, skipping the bad transaction with --exclude-gtids.
4. Take the backup with mysqldump on a 2-million-row table and time it. What would you use for a 500 GB database instead (hint: physical backups with MySQL Enterprise Backup or Percona XtraBackup)?
5. Set binlog_expire_logs_seconds to 3600, rotate logs a few times, and watch old files disappear. What does that mean for your oldest usable backup?

#Knowledge check

1. What does the binary log record, and in which default format?
2. Why does --single-transaction matter for InnoDB tables?
3. What does --source-data=2 add to the dump, and why do you need it?
4. Where does replay start and where does it stop?
5. Why run FLUSH BINARY LOGS before reading the binlog during recovery?
6. Why restore to a separate server when you can?

#Summary

• Full backup + binlogs from its position = recovery to any point in time.
• mysqldump --single-transaction --source-data=2 records exactly where replay begins.
• SHOW BINLOG EVENTS and mysqlbinlog -v find the bad transaction's position.
• Replay with --start-position and --stop-position, then optionally replay the rest after skipping the bad event.
• Binlogs are part of your backup: keep them long enough and copy them off the server.

#Further reading

• MySQL 8.4 Reference Manual: Point-in-Time (Incremental) Recovery, The Binary Log, mysqlbinlog, mysqldump.
• On this site: MySQL DBA cheat sheet, MySQL standalone production configuration, and the InnoDB Cluster learning sessions.