How to recover MySQL or MariaDB database to a point in time

Point-in-time recovery restores a MySQL or MariaDB dataset to the last known good moment after an accidental delete, a bad deployment, or an application bug. It avoids choosing between an old backup and a broken present by replaying only the safe changes that happened after a base backup.

Point-in-time recovery relies on a consistent full backup plus the server’s binary log stream (binlog). The base backup provides the starting snapshot, and mysqlbinlog replays transactions from a recorded binary log file and position up to a chosen stop time or stop position.

Binary logging must have been enabled before the incident and retained long enough to cover the recovery window, and the safest workflow restores into an isolated host to avoid overwriting remaining production data. Timestamp filters in mysqlbinlog are interpreted using the local time zone of the machine running the command, so an explicit UTC workflow avoids DST and time zone mismatches, and GTID or privilege settings can affect replay behavior.

Prerequisites: a consistent full backup taken before the recovery point, binary logs retained from the backup coordinate through the recovery point, and a restore host running the same major MySQL or MariaDB version.

Steps to recover a MySQL or MariaDB database to a point in time:

Choose a recovery stop time in UTC using the YYYY-MM-DD HH:MM:SS format.

Use TZ=UTC when running mysqlbinlog so --start-datetime and --stop-datetime comparisons stay unambiguous.

Check the source server time zone settings.

$ mysql -e "SELECT @@global.time_zone AS global_tz, @@session.time_zone AS session_tz;"
+-----------+------------+
| global_tz | session_tz |
+-----------+------------+
| SYSTEM    | SYSTEM     |
+-----------+------------+

Verify binary logging is enabled on the source server.

$ mysql -e "SHOW VARIABLES LIKE 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

log_bin=OFF means point-in-time recovery from binlogs is not possible for the missing window.

List retained binary logs on the source server.

$ mysql -e "SHOW BINARY LOGS;"
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000121 |  10485760 |
| mysql-bin.000122 |  10485760 |
| mysql-bin.000123 |   7340032 |
| mysql-bin.000124 |  10485760 |
##### snipped #####
+------------------+-----------+

SHOW BINARY LOGS typically requires the REPLICATION CLIENT privilege.

Create a restore workspace on the restore host.
```
$ sudo mkdir -p /srv/mysql-restore/binlogs
```
Check free disk space for the restore workspace.
```
$ df -h /srv/mysql-restore
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1       200G   32G  159G  17% /
```
mysqlbinlog output can be large for busy systems or long recovery windows.

Copy the base backup into the restore workspace.

$ cp /backups/mysql/full-2025-12-10.sql.gz /srv/mysql-restore/

Decompress the base backup when stored as .gz.

$ gzip -dk /srv/mysql-restore/full-2025-12-10.sql.gz

Extract the binlog start coordinate embedded in a mysqldump created with --master-data or --source-data.
```
$ grep -m 1 "CHANGE MASTER TO" /srv/mysql-restore/full-2025-12-10.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;
```
mariabackup and xtrabackup store equivalent coordinates in xtrabackup_binlog_info inside the backup directory.
Copy the required binlog files into the restore workspace.
```
$ cp /backups/mysql/binlogs/mysql-bin.{000123..000130} /srv/mysql-restore/binlogs/
```
A missing binlog in the replay range forces recovery to stop at the last available log.

Verify the first binlog file referenced by the backup exists on the restore host.

$ ls -lh /srv/mysql-restore/binlogs/mysql-bin.000123
-rw-r----- 1 backup backup 7.1M Dec 10 14:36 /srv/mysql-restore/binlogs/mysql-bin.000123

Confirm the restore server responds and matches the expected major version.

$ mysql --user=root --password -e "SELECT VERSION() AS version;"
Enter password:
+---------------------------+
| version                   |
+---------------------------+
| 10.11.6-MariaDB-log       |
+---------------------------+

A version mismatch can change binlog event semantics, collation defaults, or SQL modes.

Import the base backup into the restore server.
```
$ mysql --user=root --password < /srv/mysql-restore/full-2025-12-10.sql
Enter password:
```
Running the import against the wrong host overwrites data and can destroy the remaining post-incident state.
Confirm the base backup import exited successfully.
```
$ echo $?
0
```
Generate a replay SQL file from the recorded position up to the stop time.
```
$ TZ=UTC mysqlbinlog --start-position=456789 --stop-datetime="2025-12-10 14:35:00" /srv/mysql-restore/binlogs/mysql-bin.{000123..000130} > /srv/mysql-restore/replay.sql
```
Use --stop-position instead of --stop-datetime for a precise cut immediately before a known destructive event.

Inspect the end of the replay file to confirm the final event timestamp does not exceed the stop time.

$ tail -n 20 /srv/mysql-restore/replay.sql
# at 987654
#251210 14:34:58 server id 1  end_log_pos 988120  CRC32 0x3a8c1d22  Query   thread_id=531 exec_time=0 error_code=0
SET TIMESTAMP=1765377298/*!*/;
COMMIT/*!*/;
# at 988120
#251210 14:34:59 server id 1  end_log_pos 988401  CRC32 0x9b2d0c10  Xid = 772901
COMMIT/*!*/;

Apply the replay file to advance the restored database to the chosen point in time.
```
$ mysql --user=root --password < /srv/mysql-restore/replay.sql
Enter password:
```
Any unsafe statement that occurs before the stop point is replayed as written, including accidental DROP or DELETE statements.
Confirm the replay import exited successfully.
```
$ echo $?
0
```

List restored databases to confirm expected schemas are present.

$ mysql --user=root --password -e "SHOW DATABASES;"
Enter password:
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| appdb              |
| reporting          |
+--------------------+

Enable global read-only mode on the restored server during validation.
```
$ mysql --user=root --password -e "SET GLOBAL read_only=ON;"
Enter password:
```
MySQL can also use super_read_only=ON to reduce accidental writes by privileged accounts.

Verify the read-only setting on the restored server.

$ mysql --user=root --password -e "SHOW VARIABLES LIKE 'read_only';"
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

Review recent server log lines for restore or replay errors.

$ sudo journalctl -u mysql -n 50
Dec 10 14:36:02 db-restore mysqld[1337]: InnoDB: Buffer pool(s) load completed at 251210 14:36:02
Dec 10 14:36:02 db-restore mysqld[1337]: mysqld: ready for connections.
Dec 10 14:36:02 db-restore mysqld[1337]: Version: '10.11.6-MariaDB-log'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Debian 12
##### snipped #####

Create a post-recovery logical backup snapshot of the restored state.
```
$ mysqldump --single-transaction --routines --triggers --events --all-databases > /srv/mysql-restore/post-pitr.sql
```
A clean post-recovery backup simplifies roll-forward testing and makes the recovered state reproducible.
Record the recovery metadata for audit and repeatability.

Capture stop time, MASTER_LOG_FILE, MASTER_LOG_POS, and the last binlog file included in the replay.

Author: Mohd Shakir Zakaria
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.

Discuss the article:

Comment anonymously. Login not required.