MySQL Point-in-Time Recovery for Dropped Schema

Dropping a MySQL schema by accident can lead to severe data loss, but Point-in-Time Recovery (PITR) allows you to restore your database to the exact millisecond before the destructive command was executed. This article provides a direct, step-by-step guide on how to perform PITR for a dropped MySQL schema using your latest full backup and the MySQL binary logs.

Prerequisites for Recovery

To successfully perform a point-in-time recovery, you must have: 1. Binary Logging Enabled: The log_bin system variable must have been enabled on the server before the schema was dropped. 2. A Full Backup: A physical (Percona XtraBackup) or logical (mysqldump / mysqlpump) backup taken before the schema was dropped.

Step 1: Prevent Further Data Modification

As soon as the schema is dropped, minimize further changes to the database. If possible, set the database to read-only mode or restrict application access to prevent the binary logs from rolling over or becoming unnecessarily bloated.

SET GLOBAL read_only = ON;

Step 2: Identify the Exact Drop Event Time or Position

You must find the exact position or timestamp of the DROP SCHEMA or DROP DATABASE statement in the binary logs so you can stop the recovery process right before it.

Use the mysqlbinlog utility to search your binary log files for the drop statement:

mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/binlog.0000* | grep -i -B 5 -A 5 "DROP DATABASE"

(Replace binlog.0000* with the path to your active binary log files).

Look for output resembling the following:

# at 104523
#231024 14:30:15 server id 1  end_log_pos 104610 CRC32 0xabcdef12 Query thread_id=8 exec_time=0 error_code=0
SET TIMESTAMP=1698157815/*!*/;
DROP DATABASE `target_schema`

Record the following values immediately preceding the drop command: * Stop Position: 104523 (Recommended for precision) * Stop Datetime: 2023-10-24 14:30:15

Step 3: Restore the Last Full Backup on a Staging Instance

To avoid downtime and corruption on your production server, perform the recovery process on a separate staging or temporary MySQL instance.

  1. Initialize a clean MySQL instance.
  2. Restore your last full backup (taken prior to the schema drop) to this staging instance.

If using a logical backup:

mysql -u root -p < full_backup.sql

Step 4: Extract and Apply Binary Logs Up to the Drop Event

Now, you must replay all transactions that occurred between the time of the full backup and the moment right before the schema was dropped.

  1. Identify the binlog file and log position where your full backup ended. (If you used mysqldump with --master-data, this information is written at the top of the SQL dump file).
  2. Generate a recovery SQL file from the binary logs, specifying the start position (from the backup) and the stop position (just before the DROP statement):
mysqlbinlog --start-position=401 --stop-position=104523 /var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.000002 > recovery_events.sql
  1. Apply the generated SQL file to your staging MySQL instance:
mysql -u root -p < recovery_events.sql

Your staging instance now contains the fully recovered schema as it existed immediately before it was dropped.

Step 5: Export and Restore the Recovered Schema to Production

Once the data is verified on the staging instance, export only the recovered schema and import it back to your production server.

  1. Export the schema from the staging server:
mysqldump -u root -p --databases target_schema > recovered_schema.sql
  1. Import the schema back into your production server:
mysql -u root -p < recovered_schema.sql
  1. Disable read-only mode on production to restore normal operations:
SET GLOBAL read_only = OFF;