How to Recover MySQL Data After an Accidental DROP Using Binlog & Undrop Tools on a VPS

By Kevin Marcondes · Published on Oct 28, 2026 · 7 min read

If you need to recover MySQL data after accidentally dropping a table on a VPS, there are safe paths — but you must act quickly. Stop writes, take a snapshot, and work on a copy. This guide explains when to use binlog replay, snapshots, or InnoDB undrop tools, and how to validate results.

What to do immediately after accidentally dropping a table?

Stop new writes now. Every minute of activity can overwrite InnoDB pages and binary logs. Prioritize consistency and disk safety.

Disconnect the application, enable read-only mode, and create a snapshot of the VPS or volume. Always work on a copy — never on the production data directory.

If you use LVM or cloud snapshots, create the copy before attempting repairs. See an approach to snapshots on Linux in LVM snapshots + Restic (/en/lvm-restic-s3-systemd-debian).

• Put the database in read-only mode: app in maintenance or super_read_only.
• Take an immediate snapshot of the VPS disk/volume.
• Copy the binlogs and the datadir to a recovery host.
• Document the approximate time of the DROP to filter events.

-- Requer privilégio SUPER
SET GLOBAL super_read_only = ON; -- evita novas escritas
-- Opcional: derrube conexões da aplicação e pare serviços que escrevem

Is the binary log enabled? How to check and why it matters

The binary log records data-changing events. If it's enabled, you can replay events up to just before the DROP and rebuild the table.

Check whether log_bin is ON and which files exist. Without binlog and without a backup, the alternative is InnoDB undrop, which has a lower chance of success and is more labor-intensive.

SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog_format';
SHOW BINARY LOGS; -- lista arquivos ativos
-- Anote horário do DROP para definir start/stop

How to rebuild the table by applying binlogs up to just before the DROP?

Apply binlogs on a recovery server, filtering so you stop before the DROP. Recreate the target database and direct events to it.

With binlog in ROW format, use --verbose and --base64-output to decode changes. Use --stop-datetime or --stop-position to avoid re-running the DROP.

• Work on a separate MySQL instance with the same version and charset/collation.
• Copy the necessary binlog files to the recovery host.
• Create an empty database and grant minimal permissions.
• Locate the DROP in mysqlbinlog to define the stop point.

# Exemplo: aplica eventos do banco meu_db até antes do DROP
mysqlbinlog --verbose --base64-output=DECODE-ROWS \
  --database=meu_db \
  --start-datetime='2026-10-26 10:00:00' \
  --stop-datetime='2026-10-26 11:15:00' \
  /var/lib/mysql/binlog.000123 /var/lib/mysql/binlog.000124 \
| mysql -u root -p --binary-mode --host=127.0.0.1 --port=3306 meu_db_rec

# Dica: encontre o DROP e posições
mysqlbinlog --verbose binlog.000124 | grep -n "DROP TABLE `minha_tabela`"

No binlog or full backup: when to use InnoDB undrop tools?

InnoDB undrop tools scan raw disk pages and try to reconstruct records of the removed table. Success depends on pages not having been overwritten.

This works best when you stop MySQL immediately after the incident and operate on a disk image. Do NOT run destructive repairs on the original data directory.

• Stop MySQL to stop I/O to the datadir.
• Create a raw image of the disk/volume and work on that image.
• Use InnoDB page-recovery utilities to extract data.
• Rebuild the table from the exported records.

# Exemplo de imagem bruta do volume (ajuste o device)
sudo dd if=/dev/vda of=/srv/recovery/vda.img bs=4M status=progress conv=sync

# Monte a imagem como somente leitura para análise
sudo losetup --read-only --find --show /srv/recovery/vda.img

Which approach to choose? Quick comparison of options

Choose based on prerequisites and acceptable risk. Binlog replay is usually more predictable. Undrop is a last resort and requires expertise.

Combine a base snapshot with binlogs for point-in-time recovery when snapshots are available. In PostgreSQL this concept is commonly called PITR — see recovering a table in PostgreSQL (/en/recover-dropped-postgresql-table-pitr).

How to validate the recovery and avoid inconsistencies?

Validate schema, row counts, and referential integrity. Compare checksums between production and the recovered copy when possible.

Restore the recovered table under a new name, validate primary/foreign keys and indexes, and only then swap it into production during a controlled maintenance window.

• Compare SELECT COUNT(*) by partitions or natural keys.
• Review primary keys, foreign keys and indexes.
• Run critical application queries against the copy.
• Perform an atomic swap: use a temporary RENAME TABLE to replace the original.

-- Exemplos de verificação
CHECKSUM TABLE minha_tabela_rec;
SELECT COUNT(*) FROM minha_tabela_rec;
SHOW CREATE TABLE minha_tabela_rec;

How to avoid losing data again: policies and binlog retention

Prevent incidents with layers: tested backups, sufficient binlog retention, and regular restore drills. Keep retention aligned with your RPO.

Automate snapshots and offsite copies. If you don't have a reliable routine yet, implement incremental encrypted backups to S3 (/en/encrypted-incremental-s3-backups-linux-vps).

• Full + incremental backups with restoration tests.
• Binlog retention aligned with your RPO; monitor disk usage.
• Logical dumps only for emergencies; prefer physical snapshots.
• Recovery runbooks and controlled emergency access.
• Harden and monitor the VPS — see the VPS security checklist (/en/vps-linux-security-checklist).

Frequently asked questions

Can I recover without binlog and without a backup?

Sometimes. InnoDB undrop tools can extract records from pages that haven't been overwritten. Success depends on stopping I/O quickly and working from a disk image. It's more uncertain and time-consuming than replaying binlogs.

Can I restart MySQL after the DROP to try to fix things?

Avoid restarting. Rebooting and server activity can overwrite reusable InnoDB pages, reducing undrop chances. First isolate the system, take a snapshot or disk image, and only then attempt recovery on a copy.

Is it safe to apply mysqlbinlog directly on production?

Not recommended. Replay events on a recovery server, validate the results, and only then import the recovered table into production. Applying directly increases the risk of executing unwanted commands, including the DROP itself.

How do I find the exact DROP point in the binlog?

Use mysqlbinlog to inspect events and search for "DROP TABLE `my_table`" or look around the approximate time. For ROW format, decode with --verbose and --base64-output, and use --stop-datetime or --stop-position before the DROP.

Does undrop work if I use innodb_file_per_table?

It can, but recovery is harder. DROP removes the .ibd and frees pages. If pages haven't been overwritten, it's still possible to extract data by scanning the disk. The faster you act and the lower the activity, the better the chances.

What is the best binlog retention period for recovery?

It depends on your RPO and write volume. Keep binlogs long enough to cover incidents detected late, and monitor disk usage. Periodically test recovery to validate the chosen retention window.

See also

• Intermittent 502 Bad Gateway in Docker Compose with Nginx & PHP‑FPM: how to diagnose and fix timeouts and connection overloads
• How to diagnose and capture packets for intermittent network issues between Docker containers on Debian
• How to reduce build time and avoid duplicate artifacts in CI for large PHP monoliths
• How to detect and fix duplicate SKU inconsistencies between WooCommerce and your ERP without losing orders