WarPigW2

In the previous segment of this series of articles we discussed the backing up of the MySQL database with mysqldump and cron. I have since (through testing the restore process) revised the script slightly in that article. Please re read the article and add the change to your script.

Now we will discuss the restoring of our database backup. The good thing about using mysqldump to backup a database is that it uses native SQL language, and as such makes it easy to import via the MySQL CLI. With the previously discussed script, the database as well as all tables can be created from scratch.



Now as with mysqldump, if access to the command line is not available, the command can be run through cron. All that would need to be done is to enter the command, set the script to be run one time, that you specify, then delete the job from cron when finished. And of course with command line access, it is just a matter of running the script from the CLI itself.

The syntax to restore a database from within the CLI or cron is:

mysql --host=database host IP --user=username --password=password --database full_database_name < filename.sql

Lets go through these commands:

1. mysql - The program to be run from the command.

2. --host - The host name of your database server.(Can be omitted if the database is on localhost. Localhost is default.)

3. --user - The username used to connect to the desired database.

4. --password - The password for the database user.

5. --database - The full name of the database including any prefix.

6. < - I mention this because this is very important. In mysqldump you use ">" to pipe the dump to the file. When using the mysql command you need to change it to "<" to pipe the information from the file to the database. Failing to do this will cause the file to become blank, as it will be overwritten with nothing from mysql.

7. filename.sql - The name of the file to be inserted into the database.

This has worked on my site with no problems. As always test on a non production site to make sure it works before relying on it for a production site.

MySQL is a very powerful database tool. There are many other options available. Check the MySQL Site for much more information.