MySQL Point-in-Time Recovery on a VPS Using Binary Logs

A nightly mysqldump only gets you back to last night. If someone runs a bad UPDATE at 2pm and nobody notices until 5pm, that dump can't recover the five hours of orders, comments, or inventory changes in between. Binary log point-in-time recovery (PITR) can — but only if you turned it on before the disaster.

What point-in-time recovery actually does

MySQL's binary log (binlog) records every write query in the order it happened, timestamped. A full backup gives you a starting point; the binlog lets you replay everything that happened after that backup, up to any second you choose — including the second right before the bad query ran.

So the recipe is: restore last night's dump, then replay binlog events from the dump's position up to (but not including) the mistake. That's the whole idea. Everything below is the mechanics of doing it correctly on a self-managed VPS.

Symptom: "we can only restore to last night"

This shows up after an incident, not before. Someone runs DELETE FROM orders WHERE ... with a WHERE clause that didn't match what they meant, or a migration script updates the wrong table. The team pulls the last mysqldump and realizes every transaction since midnight is gone for good.

The cause is almost always the same: binary logging was never enabled, or it was enabled but rotated out before anyone needed it.

Step 1: Enable binary logging

Check whether it's already on:

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"

If log_bin comes back OFF, edit your MySQL/MariaDB config — usually /etc/mysql/mysql.conf.d/mysqld.cnf on Debian/Ubuntu, or /etc/my.cnf.d/server.cnf on AlmaLinux/CentOS — and add:

[mysqld]
server-id        = 1
log_bin           = /var/lib/mysql/binlog
binlog_format     = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size   = 200M

A few notes on those settings:

• server-id is required even on a standalone server — MySQL won't start binary logging without it.
• binlog_format = ROW logs the actual row changes rather than the SQL statement. It's slightly larger on disk but far more reliable to replay — statement-based logging can behave differently on replay if it depends on NOW(), auto-increment order, or triggers.
• binlog_expire_logs_seconds controls how long old binlogs are kept before automatic purge. 604800 seconds is 7 days — set it to cover the gap between your full backups, plus slack. If you back up weekly, don't leave this at 3 days.

Restart the service to apply the change:

systemctl restart mysql   # or mariadb, depending on your distro

Binlogs now accumulate in /var/lib/mysql/ as binlog.000001, binlog.000002, and so on, with an index file (binlog.index) tracking which ones exist.

This only protects you going forward. If binary logging has never been on, there's no way to recover transactions from before you enabled it — which is exactly why this is worth setting up before you need it, not after.

Step 2: Get binlogs off the box a bad command can't reach

Binlogs living on the same disk as the database they protect are a single point of failure — a full-disk incident or a careless rm takes out both. Ship them off-server on a schedule:

# cron entry: copy new binlogs off the VPS every 15 minutes
*/15 * * * * rsync -az /var/lib/mysql/binlog.* user@backup-host:/backups/mysql-binlogs/

If you're already using rclone to push backups to S3-compatible storage, point the same job at the binlog directory instead of (or alongside) your dump files.

Step 3: Take full backups on a known schedule

PITR replays binlog events on top of a full backup — it doesn't replace one. Keep your regular mysqldump (or mariabackup/xtrabackup for larger databases) running as usual. What matters for PITR is capturing which binlog file and position the backup was taken at:

mysqldump -u root -p --single-transaction --master-data=2 \
  --all-databases > /backups/full-$(date +%F).sql

--master-data=2 writes the binlog file name and position as a comment near the top of the dump, like:

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000042', MASTER_LOG_POS=154;

That one line is what tells you where to start replaying from during a recovery. Without it, you're guessing at timestamps instead of an exact position — workable, but much less precise.

Step 4: The actual recovery, step by step

Say the bad query ran at 14:32 on a Tuesday and your last full backup was from Monday night.

1. Restore the full backup to a scratch database first — not production. Never replay binlogs directly against a live database; if you get the cutoff wrong you can't undo it.

mysql -u root -p -e "CREATE DATABASE recovery_test;"
mysql -u root -p recovery_test < /backups/full-2026-08-17.sql

2. Find where the backup left off. Grep the top of the dump for the CHANGE MASTER TO line from Step 3 — that gives you the starting binlog file and position.

3. Replay everything from that position up to just before the bad query, using mysqlbinlog to extract and pipe the events:

mysqlbinlog --start-position=154 \
  --stop-datetime="2026-08-18 14:31:59" \
  /var/lib/mysql/binlog.000042 /var/lib/mysql/binlog.000043 \
  | mysql -u root -p recovery_test

List every binlog file that falls between the backup and the incident, in order — mysqlbinlog accepts multiple files on one command line and processes them sequentially.

4. Verify the data looks right in recovery_test before touching production — check row counts, spot-check the record that got wiped out, confirm timestamps line up with what you expect.

5. Only then swap it into place — either by exporting the recovered tables and importing them into production, or by renaming databases during a maintenance window if a full-database swap is safe for your setup.

Using an exact position instead of a timestamp

Timestamps in binlogs are wall-clock time on the server, which is fine for finding the general neighborhood of an incident — but if you know the exact GTID or log position of the bad statement (for example from SHOW BINLOG EVENTS), use --stop-position instead of --stop-datetime for a precise cutoff:

mysqlbinlog --start-position=154 --stop-position=98234 \
  /var/lib/mysql/binlog.000042 | mysql -u root -p recovery_test

This matters when several statements ran within the same second — a timestamp cutoff can accidentally include or exclude a neighbor of the bad query.

Common mistakes that ruin a recovery

Prevention: make PITR routine, not a scramble

• Enable binary logging on every VPS running MySQL/MariaDB in production, not just the ones you think need it.
• Automate the off-box binlog copy — don't rely on remembering to do it manually.
• Test a recovery on a scratch database every few months. A backup strategy nobody has ever restored from is a guess, not a plan.
• Document your server-id, binlog path, and retention window somewhere the whole team can find it during an incident, not just in your head.
• If your database is large enough that mysqldump restores take hours, look at mariabackup/xtrabackup for faster physical backups — PITR still layers on top the same way.