MySQL database FAQ: How do I restore a MySQL backup? (Also written as, “How do I restore a
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.
The MySQL restore process
The process to restore a MySQL database from a backup file created by
mysqldump is a simple two-step process:
- Create a new MySQL database using the mysqladmin utility (or
- 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.
(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 easily restore your old MySQL database from your backup file. Here's a command I just used to test the process of restoring a Drupal database backup file I just created with
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. Again, this is very simple, and that's all you have to do.
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.
u can also use mysqldump to restore and take backup of mysql database.
1> take backup
mysqldump -uUserName -p --all-databases > FileName.sql
mysqldump -uUserName -p --all-databases < FileName.sql
from command promt the above should work fine
I've never seen mysqldump used to restore a database before. I'll try it this weekend.
FWIW, here's a link to the mysqldump docs on mysql.com.
I typically don't use MyISAM tables, but if you are using only MyISAM tables, I just read this blurb on that page about making backups:
If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores.