FutureQuest, Inc. FutureQuest, Inc. FutureQuest, Inc.
Knowledgebase: PHP/MySQL
How to Restore a Database from Backup
Posted on 24 October 2003 04:28 PM

If you have created a backup of your MySQL databases and need to restore it, or you want to add data to your database from a mysqldump you performed, you're in the right place!

First, this tutorial only covers backups done with mysqldump.

Second, it only covers situations where you want to restore your data into an empty database, or at least a database that does not contain any tables of the same name as those within your dump.

For the remainder of this tutorial, we will assume that you have created your dump using the mysqldump tutorial and that you are dumping your data into a completely empty database.

To begin, SSH to your account. Do not log into MySQL - this procedure is all done from the command line. Once you are connected via SSH, find the directory containing the file you created with mysqldump. You can view a listing of the current directory by typing ls and pressing [Enter].

Once you are in the directory containing your file, uncompress your file if necessary. If your file ends in .gz, it is a gzip compressed file and will need to be uncompressed.

Assuming the file is called myfile.txt.gz, you would type the following:

    gunzip myfile.txt

Note that we do not type the .gz part. Once this is done, you should have an uncompressed text file. You can check this by again typing ls.

Next, you will dump the data into MySQL using a command that might look like this (you will need to make substitutions as explained further below):

    mysql -uxmysqlusername -p -hmysql.example.com xdatabase <./myfile.txt

We will run through each portion of the command to help better explain what we are doing.

This is the name of the command we are running.
-u is the switch to specify the user. Replace xmysqlusername with your actual MySQL username.
This tells MySQL that you need a password to log in. It will prompt you for the password once you enter the command.
-h tells MySQL what host to use. Replace example.com with your actual domain name.
This specifies which database you wish to place your data in. Again, make sure this is an empty database or, at the very least, make sure it does not have any table names that are the same as the ones in your dump file.
This tells MySQL that you are giving it the data within your file. The ./ specifies that the file is in the current directory. You can, if you like, specify the full path:

Once you press [Enter], it will prompt you for your password. Type carefully, as you will not see your password as you type it.

Finally, log into your MySQL and check that your data is there. That's it!

Important Note: You can find the information for the host name and MySQL username by visiting the MySQL Manager in your CNC panel. If you have forgotten your MySQL password, you will also be able to reset it through your MySQL Manager. Note that doing so will change the MySQL password for all of your MySQL databases under that package and you'll need to change the password in any scripts that are dependent on that password as well.