Restoring from a mysqldump Backup

Restoring a mysqldump backup involves feeding the SQL file back into MySQL. The process recreates tables, stored procedures, and data exactly as they were at backup time.

Basic Restore

Use the mysql command-line client and redirect the SQL file as input. If the database does not exist yet, create it first with CREATE DATABASE.

Restore Best Practices

Restore to a test server first to verify integrity before applying to production
Disable foreign key checks during restore to avoid ordering issues (SET FOREIGN_KEY_CHECKS=0)
Use --force on the mysql command to continue past errors
Monitor the restore progress with pv (pipe viewer) for large files

Partial Restore

To restore only specific tables, extract the relevant INSERT statements from the dump file using grep or sed, then apply those statements manually.

Example

-- Create the database if it does not exist
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS myapp_db;"

-- Restore from dump file
mysql -u root -p myapp_db < myapp_full_2025-05-02.sql

-- Restore from compressed dump
gunzip -c myapp_db_2025-05-02.sql.gz | mysql -u root -p myapp_db

-- Restore with FK checks disabled (SQL inside the dump)
-- SET FOREIGN_KEY_CHECKS=0; (add at top of dump file if needed)

SQL

Full Editor

Pro Tip

Always restore to a test environment first to confirm the backup is valid before touching production.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned