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:
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):
We will run through each portion of the command to help better explain what we are doing.
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.