MySql - Restoring databases from backups

Restoring Databases from Backups in MySQL 

Restoring a MySQL database from a backup is a crucial task for any DBA or developer. Whether recovering from accidental data loss, system failure, or migrating to a new environment, restoring ensures business continuity and data integrity. Depending on how the backup was created—logical or physical—the restoration process varies. This comprehensive guide explains how to restore databases using both methods, including step-by-step instructions, best practices, and error handling techniques.

Overview of Restore Strategies

Before restoring, it's essential to understand the backup format:

• Logical Backups: Text-based SQL dumps created using tools like mysqldump.
• Physical Backups: Binary-level copies of database files using tools like Percona XtraBackup or filesystem snapshots.

Restoration can also involve:

• Full database restore
• Single table or object restore
• Point-in-time recovery using binary logs

Restoring from Logical Backups

Logical backups are typically restored using the mysql client tool.

1. Using mysqldump SQL File

To restore a database from a SQL dump file:

mysql -u root -p mydatabase < mydatabase_backup.sql

2. Creating the Database Before Restoration

If the database doesn't exist yet:

mysql -u root -p
CREATE DATABASE mydatabase;
exit
mysql -u root -p mydatabase < mydatabase_backup.sql

3. Restoring All Databases

If the backup includes multiple databases (using --all-databases):

mysql -u root -p < all_databases_backup.sql

4. Restoring Compressed Backups

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

5. Restoring Specific Tables

If the dump includes only specific tables:

mysql -u root -p mydatabase < customers_orders.sql

Common mysqldump Restore Issues

• Foreign key constraint errors – Temporarily disable them during import:

SET FOREIGN_KEY_CHECKS=0;
-- Run restore here
SET FOREIGN_KEY_CHECKS=1;

• Duplicate entry errors – May occur if data already exists. Use truncate or drop the database before restoring.

DROP DATABASE mydatabase;
CREATE DATABASE mydatabase;

Restoring from Physical Backups

Physical backups restore data by copying binary files back into the MySQL data directory. Tools like Percona XtraBackup or MySQL Enterprise Backup are used for consistent physical restoration.

1. Using Percona XtraBackup

Assume a backup was taken using:

xtrabackup --backup --target-dir=/backups/xtrabackup

Step 1: Prepare the Backup

xtrabackup --prepare --target-dir=/backups/xtrabackup

This applies logs and ensures consistency.

Step 2: Stop the MySQL Server

systemctl stop mysql

Step 3: Restore the Backup

xtrabackup --copy-back --target-dir=/backups/xtrabackup

Step 4: Set Proper File Permissions

chown -R mysql:mysql /var/lib/mysql

Step 5: Start MySQL

systemctl start mysql

2. Using File System Copy (Simple Physical Backup)

If a backup was taken using file copy after shutting down MySQL:

cp -R /backup/mysql_backup/* /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysql

Note: This method is only safe for cold backups where MySQL was stopped.

Restoring Individual Tables

1. From mysqldump

To restore a single table:

mysqldump -u root -p mydatabase customers > customers.sql
mysql -u root -p mydatabase < customers.sql

2. Using SELECT INTO OUTFILE and LOAD DATA

-- Export
SELECT * FROM orders INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Import
LOAD DATA INFILE '/tmp/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Point-In-Time Recovery (PITR)

Use binary logs to roll forward changes from a full backup to a specific point in time.

Step 1: Restore Full Backup

mysql -u root -p mydatabase < full_backup.sql

Step 2: Identify Binary Log File and Position

Use the backup's log file position (included in the backup if --master-data was used).

Step 3: Apply Binary Logs

mysqlbinlog --start-datetime="2025-07-10 09:00:00" \
--stop-datetime="2025-07-10 10:30:00" \
/var/lib/mysql/mysql-bin.000001 | mysql -u root -p

Restoring in Replication Setups

To restore a replica and sync it with the primary server:

1. Restore a fresh backup from the primary on the replica server.
2. Configure replication settings (e.g., CHANGE MASTER TO).

CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154;

Start replication:

START SLAVE;

Restoring in Docker Environment

To restore a SQL dump in a MySQL Docker container:

docker cp mydatabase_backup.sql mysql-container:/backup.sql
docker exec -i mysql-container sh -c \
'mysql -u root -p"$MYSQL_ROOT_PASSWORD" mydatabase < /backup.sql'

Restoring Cloud Backups

1. From AWS RDS Snapshots

Use the AWS Console or CLI to create a new instance from a snapshot:

aws rds restore-db-instance-from-db-snapshot \
--db-instance-identifier mydb-restore \
--db-snapshot-identifier mydb-snapshot

2. From GCP or Azure

Both provide managed backups via their interfaces or APIs. Choose the appropriate backup and initiate a restore to a new instance.

Verifying a Successful Restore

After restoring, ensure data integrity by:

• Checking table counts
• Verifying application connectivity
• Running test queries

SELECT COUNT(*) FROM orders;
SELECT * FROM users LIMIT 10;

Restore Automation and Monitoring

Integrate restore scripts in CI/CD pipelines or disaster recovery plans. Monitor error logs and email alerts for failed restores.

Example: Automated Restore in Bash

#!/bin/bash
DB="mydatabase"
BACKUP_FILE="/backups/mydatabase.sql"
mysql -u root -pMyPass -e "DROP DATABASE IF EXISTS $DB; CREATE DATABASE $DB;"
mysql -u root -pMyPass $DB < $BACKUP_FILE

Restoring MySQL databases from backups is a foundational skill for ensuring data resilience and recovery. Depending on the type of backup—logical (mysqldump) or physical (XtraBackup)—the approach varies. Point-in-time recovery with binary logs further enhances the ability to recover specific transactions. Implementing automated, well-documented, and frequently tested restore processes is essential for reducing downtime and preventing data loss in production environments.