How to Restore MySQL Database Quickly and Easily

To restore a MySQL database, use the mysql command with the database name and input the backup file using <. For example, run mysql -u username -p database_name < backup.sql to restore from a SQL dump file.

Syntax

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

• mysql: The command-line tool to interact with MySQL.
• -u username: Your MySQL username.
• -p: Prompts for your MySQL password.
• database_name: The name of the database you want to restore.
• < backup_file.sql: Redirects the backup SQL file as input to restore.

bash

mysql -u username -p database_name < backup_file.sql

Example

This example shows how to restore a database named mydb from a backup file called mydb_backup.sql. You will be prompted to enter your password.

bash

mysql -u root -p mydb < mydb_backup.sql

Output

Enter password: ********
-- No output means success --

Common Pitfalls

Common mistakes when restoring a MySQL database include:

• Trying to restore to a database that does not exist. You must create the database first using CREATE DATABASE database_name;.
• Using the wrong username or password, causing authentication errors.
• Not having proper permissions to restore the database.
• Restoring a backup that contains CREATE DATABASE statements without the --one-database option, which can cause errors.

bash

/* Wrong: restoring to a non-existent database */
mysql -u root -p non_existent_db < backup.sql

/* Right: create database first */
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS non_existent_db;"
mysql -u root -p non_existent_db < backup.sql

Quick Reference

Command	Description
mysql -u username -p database_name < backup.sql	Restore database from SQL dump file
mysql -u root -p -e "CREATE DATABASE db_name;"	Create database before restoring
mysql -u root -p	Open MySQL shell to run manual commands
SHOW DATABASES;	List all databases
USE database_name;	Select database to work with

Key Takeaways

Use the command: mysql -u username -p database_name < backup_file.sql to restore a MySQL database.

Make sure the target database exists before restoring; create it if needed.

Enter the correct username and password to avoid authentication errors.

Check that you have sufficient permissions to restore the database.

Restoring produces no output if successful; errors will be shown if any.