How to Restore a MySQL Database from mysqldump Backup

To restore a MySQL database from a mysqldump backup, use the command mysql -u username -p database_name < backupfile.sql. This imports the SQL statements from the dump file into the specified database.

Syntax

The basic syntax to restore a MySQL database from a mysqldump file is:

• mysql: The MySQL command-line client used to run SQL commands.
• -u username: Your MySQL username.
• -p: Prompts for your MySQL password securely.
• database_name: The name of the database where you want to restore the data.
• < backupfile.sql: Redirects the dump file as input to the MySQL client.

bash

mysql -u username -p database_name < backupfile.sql

Example

This example shows how to restore a database named mydb from a dump file called mydb_backup.sql. You will be prompted to enter your password after running the command.

bash

mysql -u root -p mydb < mydb_backup.sql

Output

Enter password: ********
-- The dump file contents are imported into the 'mydb' database --

Common Pitfalls

Common mistakes when restoring from mysqldump include:

• Trying to restore into a database that does not exist. You must create the database first using CREATE DATABASE database_name;.
• Using the wrong username or password, which causes authentication errors.
• Not having sufficient privileges to write to the database.
• Restoring a dump that includes CREATE DATABASE statements without the --one-database option, which can cause errors if the database already exists.

Wrong way:

mysql -u root -p < mydb_backup.sql

This command misses the database name and will fail.

Right way:

mysql -u root -p mydb < mydb_backup.sql

Quick Reference

Command Part	Description
mysql	MySQL command-line client to run SQL commands
-u username	MySQL user name
-p	Prompt for password
database_name	Target database to restore into
< backupfile.sql	Redirect input from dump file

Key Takeaways

Always specify the target database name when restoring with mysqldump.

Create the database before restoring if it does not exist.

Use the correct username and password with sufficient privileges.

Redirect the dump file into the mysql client using the < operator.

Check the dump file content for database creation statements to avoid conflicts.