How do I restore a dump file from mysqldump?

Comprehensive restoration guide

Setting up the environment

Ensure you have a database ready to contain the restoration:

CREATE DATABASE reinstate_me; /* ☝️ This one's for you, Arnold Schwarzenegger */


Location, location, location

Before executing any commands, navigate your command prompt to the directory housing mysql.exe. If you're a Windows user, you'll often find it in C:\Program Files\MySQL\MySQL Server X.X\bin.

Multi-database dump files

When dealing with a dump file containing multiple databases, each USE database_name; statement should precede its respective database within the dump file.

Potential obstacles

Watch out for any version conflicts, corrupted dump files, and unexpected errors during import process. It's good practice to retain backup copies until the restoration is verified successful.

Advanced restore command guide

Restoring a single-database dump

If your .sql file is specific to a single database:

mysql -u bobBuilder -p castleDB < singleCastle.sql /* Can we fix it? Yes, we can! */


Exclude the database_name as it should already be declared within the .sql file.

Restoring multi-database dump

In case your dump file contains multiple databases:

mysql -u bobBuilder -p < castleVillage.sql /* An entire village this time, brace yourselves! */


Checking permissions

Ensure your MySQL user has sufficient database privileges, or you may face access-denied errors:

GRANT ALL PRIVILEGES ON castleDB.* TO 'bobBuilder'@'localhost' WITH GRANT OPTION; /* Now, Bob rules the Kingdom! */


Avoiding common pit traps

Don't be tripped up by file size or character encoding issues. Be patient with large dumps, and ensure the dump and restore operations use the same collation and character set.