Recover a MySQL database from backups taken by mysqldump
In a previous post (Take MySQL 8.* full and incremental backups using mysqldump), we discussed how to create incremental backups of MySQL using the mysqldump utility. In this follow‑up, we’ll shift our focus to the process of restoring and recovering a MySQL server from those backups. The recovery scripts you’ll need are provided at the end of this post, along with a screenshot showing the binary log position where the table was dropped.
We’ll cover three practical recovery scenarios:
Recovering to available full and incremental backup.
Recovering up to the point just before a table was dropped, ensuring the object remains intact.
- Recovery scenario up to maximum binary log position, ensures the database is restored not only up to the moment when a table was dropped, but also includes every subsequent transaction executed after the drop, right through to the shutdown event.
✅ Operational Tips
- Run `restore_full_incr_standalone.sh YYYY-MM-DD` to restore latest full + incremental backup.
- Run `restore_full_incr_standalone.sh YYYY-MM-DD YY-MM-DD` to restore full + incremental backup up to a specific time.
- Test restores in staging before production.
- innodb_redo_log_capacity, innodb_log_buffer_size, innodb_flush_log_at_trx_commit=2 could help optimize recovery operation.
✅ Binary log screenshot of TABLE DROP statements
✅ Recovery scenarios:
Recovery scenario up to available full and incremental backup:
1. Disable automatic backup from cron scheduler.
2. Stop application traffic to the database.
3. Restore from full + incremental backup by executing restore_full_incr_standalone.sh as mentioned in the operational tips.
4. Check the current database status, row counts, etc.
5. Finally take a current full backup by renaming the existing SYSDATE full backup if exists by backup_standalone.sh script.
6. Start the application traffic to the MySQL server.
7. Enable the cron scheduler to take backups.
Tables/objects dropped recovery scenario until just before the table dropped:
1. Disable automatic backup from cron scheduler.
2. Stop application traffic to the database.
3. Find the binary log that contains the DROP TABLE command by knowing the rough time of drop operation.
Use mysqlbinlog to replay binary logs up to just before the DROP TABLE and dump as .sql dump file.
a. Find the precise binary log event position when table drop occurred.
mysqlbinlog --start-datetime="2025-11-16 18:30:00" --stop-datetime="2025-11-16 19:30:00" \
--verbose /var/lib/mysql/binlog.000200 | grep -C 12 "DROP TABLE"
b. Identify the log entry just before the first drop command in a .sql dump file, then extract the log entries just before the first drop command by below command.
Here from the above screenshot example the binary position just before the drop table is on the line # at 10793321.
mysqlbinlog --stop-position=10793321 binlog.000200 > recovery_upto_table_dropped.sql
c. Move the binary log file (e.g. binlog.000200) contained the DROP statement from binary log directory and put to home directory.
d. Note the earlier binary log file name immediate to the DROP statement in the binary log directory (e.g. binlog.000199), this will be needed at later step.
4. Restore from full + incremental backup by executing restore_full_incr_standalone.sh as mentioned in the operational tips.
a. Note the last binary log file applied from the script output. This is also available in the file named like restore-datetime.log in logs directory under the mysql backup directory.
5. Check the dropped table has been restored and take row counts if needed.
At this stage the dropped table/s has been restored from the consistent backups which only holds the rows up to the time the backups were taken.
6. To recover the database up to the state just before the table/s dropped we need to apply the binary logs just before the table drop happened from the binary log directory usually /var/lib/mysql,
also the extracted binary log entries from the binary log file where the actual table drop happened.
Apply the binary logs by executing the pitr_max_standalone.sh script.
a. In backup_restore.conf file set the START_BINLOG variable to the last restored binary log file by the restore_full_incr_standalone.sh script output at step 4.a.
b. Provide the END_BINLOG in the backup_restore.conf file to the binary log name noted in the above step 3.d.
c. Execute the script pitr_max_standalone.sh.
Apply the changes to the database from the recovery_upto_table_dropped.sql dump file to apply the latest transactions just before the drop table command.
a. Mysql -u user -pPassword < recovery_upto_table_dropped.sql
7. Finally take a current full backup by backup_standalone.sh script renaming the existing SYSDATE full backup if exists.(This will delete the source/master binary logs)
8. Start the application traffic to the MySQL server.
9. Enable the cron scheduler to take backups.
Table/object dropped recovery scenario up to maximum binary log position:
1. Disable automatic backup from cron scheduler.
2. Stop application traffic to the database.
3. Find the binary log that contains the DROP TABLE commands by knowing the rough time of drop operation.
a. Find the precise binary log event position when table drop occurred.
mysqlbinlog --start-datetime="2025-11-16 18:30:00" --stop-datetime="2025-11-16 19:30:00" \
--verbose /var/lib/mysql/binlog.000200 | grep -C 12 "DROP TABLE"
b. Identify the log entry just before the first drop command in a .sql dump file, then extract the log entries just before the first drop command by below command.
Here from the above screenshot example the binary position just before the drop table is on the line # at 10793321.
mysqlbinlog --stop-position=10793321 binlog.000200 > recovery_upto_max_position.sql
c. Extract rest of the binary log entries omitting the DROP TABLE commands to the end of the binary log file. Append the outputs to the recovery_upto_max_position.sql file.
mysqlbinlog --start-position=10793537 --stop-position=13133966 /var/lib/mysql/binlog.000200 >> recovery_upto_max_position.sql
mysqlbinlog --start-position=13134188 /var/lib/mysql/binlog.000200 >> recovery_upto_max_position.sql
d. Move the binary log file (e.g. binlog.000200) contained the DROP statement from binary log directory and put to home directory.
e. Note the earlier binary log file name immediate to the DROP statement in the binary log directory (e.g. binlog.000199), this will be needed at later step.
4. Restore from full + incremental backup by executing restore_full_incr_standalone.sh as mentioned in the operational tips.
a. Note the last binary log file applied from the script output. This is also available in the file named like restore-datetime.log in logs directory under the mysql backup directory.
5. Check the dropped table has been restored and take row counts if needed.
At this stage the dropped table/s has been restored from the consistent backups which only holds the rows up to the time the backups were taken.
6. To recover the database up to the state just before the table/s dropped we need to apply the binary logs just before the table drop happened from the binary log directory usually /var/lib/mysql,
also the extracted binary log entries from the binary log file where the actual table drop happened.
Apply the binary logs by executing the pitr_max_standalone.sh script.
1) In backup_restore.conf file set the START_BINLOG variable to the last restored binary log file by the restore_full_incr_standalone.sh script output at step 4.a.
2) Provide the END_BINLOG in the backup_restore.conf file to the binary log name noted in the above step 3.d.
3) Execute the script pitr_max_standalone.sh.
Apply the changes to the database from the recovery_upto_table_dropped.sql dump file to apply the latest transactions just before the drop table command.
1) Mysql -u user -pPassword < recovery_upto_table_dropped.sql
7. Finally take a current full backup by backup_standalone.sh script renaming the existing SYSDATE full backup if exists.(This will delete the source/master binary logs)
8. Start the application traffic to the MySQL server.
9. Enable the cron scheduler to take backups.
✅ Scripts to help recovery:
Script 1: backup_restore.conf
# MySQL credentials
MYSQL_USER="myadmin"
MYSQL_PASSWORD="myadmin@123"
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
# Backup settings
BACKUP_DIR="/backups/mysql"
FULL_BACKUP_DIR="$BACKUP_DIR/full"
INCR_BACKUP_DIR="$BACKUP_DIR/incr"
LOG_DIR="$BACKUP_DIR/logs"
RETENTION_DAYS=1
# GPG encryption (optional)
GPG_ENABLED=false
GPG_RECIPIENT="backup@yourdomain.com"
# S3 upload (optional)
S3_ENABLED=false
S3_BUCKET="s3://your-bucket-name"
AWS_PROFILE="default"
# Binary log settings for PITR
BINLOG_DIR="/var/lib/mysql"
START_BINLOG="binlog.000194" # Set this to the binary log file name at maximum PITR restore time
END_BINLOG="binlog.000199" # Set this to the binary log file name at maximum PITR restore time
# Usage notes:
# - Ensure BINLOG_DIR points to the directory where MySQL binary logs are stored.
# - START_BINLOG should be set to the binary log file name corresponding to the backup's last log position.
# - These variables are used by restore and verify scripts to apply binary logs for point-in-time recovery.
# - Keep backup.conf secure as it contains sensitive credentials and keys.
Script 2: restore_full_incr_standalone.sh
#restore_full_incr_standalone.sh
#!/bin/bash
set -euo pipefail
source "$(dirname "$0")/backup_restore.conf"
# === CONFIG ===
RESTORE_DATE="${1:-$(date +%F)}"
RESTORE_TIME="${2:-}" # Optional for PITR
NOW=$(date +%F-%H%M%S)
LOG_FILE="$LOG_DIR/restore-$NOW.log"
exec > >(tee -a "$LOG_FILE") 2>&1
echo "=== Restore Started at $(date) ==="
# === STEP 1: Locate full backup ===
FULL_BACKUP=$(find "$FULL_BACKUP_DIR" -type f -name "full-$RESTORE_DATE*.sql.gz" | sort | tail -n 1)
if [[ -z "$FULL_BACKUP" ]]; then
echo "❌ Full backup for $RESTORE_DATE not found."
exit 1
fi
echo "✅ Found full backup: $FULL_BACKUP"
# === STEP 2: Restore full backup directly ===
echo "Restoring full backup to MySQL server..."
gunzip -c "$FULL_BACKUP" | mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD"
echo "✅ Full backup restored."
# === STEP 3: Disable overhead during binlog replay ===
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" <<EOF
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL slave_parallel_workers = 8;
EOF
# === STEP 4: Locate incremental binlogs ===
INCR_DIR=$(find "$INCR_BACKUP_DIR" -type d -name "incr-$RESTORE_DATE*" | sort | tail -n 1)
if [[ -z "$INCR_DIR" ]]; then
echo "⚠️ No incremental backup found for $RESTORE_DATE. Skipping binlog recovery."
exit 0
fi
echo "✅ Found incremental binlogs: $INCR_DIR"
# === STEP 5: Apply binlogs (with PITR support) ===
for FILE in "$INCR_DIR"/*; do
BASENAME="$(basename "$FILE")"
# Skip binlog.index file
if [[ "$BASENAME" == "binlog.index" ]]; then
echo "Skipping index file: $BASENAME"
continue
fi
if [[ "$FILE" == *.gpg ]]; then
echo "Decrypting and applying: $BASENAME"
if [[ -n "$RESTORE_TIME" ]]; then
gpg --decrypt "$FILE" | mysqlbinlog --disable-log-bin --idempotent --stop-datetime="$RESTORE_DATE $RESTORE_TIME" - | mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD"
break
else
gpg --decrypt "$FILE" | mysqlbinlog - | mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD"
fi
else
echo "Applying: $BASENAME"
if [[ -n "$RESTORE_TIME" ]]; then
mysqlbinlog --disable-log-bin --idempotent --stop-datetime="$RESTORE_DATE $RESTORE_TIME" "$FILE" | mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD"
break
else
mysqlbinlog --disable-log-bin --idempotent "$FILE" | mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD"
fi
fi
done
# === STEP 6: Re-enable safety settings ===
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" <<EOF
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL slave_parallel_workers = 4;
EOF
echo "✅ Binlogs applied and binary logging re-enabled."
echo "=== Restore Finished at $(date) ==="
Script 3: pitr_max_standalone.sh
#pitr_max_standalone.sh
#!/bin/bash
set -euo pipefail
source "$(dirname "$0")/backup_restore.conf"
LOG_FILE="$LOG_DIR/verify-$(date +%F).log"
exec > >(tee -a "$LOG_FILE") 2>&1
echo "=== MySQL Restore Verification Started at $(date) ==="
# === STEP 1: Check MySQL connectivity ===
if ! mysqladmin -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_HOST" -P"$MYSQL_PORT" ping &>/dev/null; then
echo "❌ MySQL is not responding on $MYSQL_HOST:$MYSQL_PORT"
exit 1
fi
echo "✅ MySQL is reachable"
# === STEP 2: Verify databases exist ===
DBS=$(mysql -N -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_HOST" -P"$MYSQL_PORT" -e "SHOW DATABASES;")
if [[ -z "$DBS" ]]; then
echo "❌ No databases found after restore"
exit 1
fi
echo "✅ Databases detected: $(echo $DBS | tr ' ' ' ')"
# === STEP 3: Disable overhead during binlog replay ===
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" <<EOF
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL slave_parallel_workers = 8;
EOF
# === STEP 4: Apply binary logs for maximum point recovery ===
# This step assumes you have the binary logs available locally or accessible remotely.
# It applies all binary logs from the last backup position to the latest available to recover up to the maximum point.
BINLOG_DIR="$BINLOG_DIR" # Directory where binary logs are stored
START_BINLOG="$START_BINLOG" # Start applying binary log file from backup_restore.conf metadata
END_BINLOG="$END_BINLOG" # Last binary log to apply from backup_restore.conf metadata
if [[ -n "$BINLOG_DIR" && -n "$START_BINLOG" && -n "$END_BINLOG" ]]; then
echo "Applying binary logs from $START_BINLOG to $END_BINLOG in $BINLOG_DIR"
BINLOGS=($(find "$BINLOG_DIR" -type f -name "binlog.*" ! -name "binlog.index" | sort))
APPLY=false
for LOG in "${BINLOGS[@]}"; do
BASENAME=$(basename "$LOG")
if [[ "$BASENAME" == "$START_BINLOG" ]]; then
APPLY=true
fi
if $APPLY; then
echo "Applying $LOG"
mysqlbinlog --disable-log-bin --idempotent "$LOG" | \
mysql -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" -h"$MYSQL_HOST" -P"$MYSQL_PORT"
fi
if [[ "$BASENAME" == "$END_BINLOG" ]]; then
echo "✅ Reached END_BINLOG: $END_BINLOG"
break
fi
done
else
echo "⚠️ START_BINLOG, END_BINLOG, or BINLOG_DIR not set. Skipping binary log apply step."
fi
# === STEP 5: Re-enable safety settings ===
mysql -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" <<EOF
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
SET GLOBAL slave_parallel_workers = 4;
EOF
echo "=== Binary log apply Completed up to the file available in the MySQL binary log directory at $(date) ==="
Comments
Post a Comment