MySQL restore: How to restore a MySQL database from a backup

MySQL/MariaDB database FAQ: How do I restore a MySQL backup? (Also written as, “How do I restore a mysqldump file?”)

Assuming that you’ve created a MySQL backup file as shown in my “MySQL backup - How to Backup a MySQL database” tutorial, you can use the commands shown here to restore your MySQL database.

Note: There are several different ways to restore a MySQL database. I think this is the easiest approach, so I show it here.

The MySQL restore process

The process to restore a MySQL database from a backup file created by mysqldump is a simple two-step process:

1. Create a new MySQL database using the mysqladmin utility (or mysql command).
2. Restore your database backup to this new database using one of several possible commands.

Step 1: Create your new database

Assuming you have the root password for your MySQL database server, you can create a new database with the mysqladmin command, like this:

mysqladmin -u root -p create mytestdatabase

After you are prompted for the root password, and successfully enter it, the mysqladmin utility will create a new database named mytestdatabase for you. That’s all you have to do to create it.

Note: You can also log into your MySQL server and then use the CREATE DATABASE command to perform the same task, if you prefer.

Step 2: Restore the database from the backup file

Next, you can restore your old MySQL database from your backup file using the mysql command. Here's a command I just used to test the process of restoring a Drupal database backup file I just created with mysqldump:

mysql -u root -p mytestdatabase < drupaldb-20090505.sql

Warning: Be very careful with this command! In particular, you want to make sure that the database you are restoring to (mytestdatabase in my case) is empty before you run this command.

In this case I’m using a MySQL backup file named drupaldb-20090505.sql to restore the contents of that database to my new database, which I named mytestdatabase.

As mentioned, you can restore your database backup file using several different possible commands, but I think this command is the easiest, and I’ll leave it at that for today.

(Another way to restore the database backup is to log into your database with the mysql command; then issue the use mytestdatabase command to work in that database, and then “source” your database backup file.)

In summary, if you wanted to see how to restore a MySQL database backup file, I hope this example is helpful.