Login

Username:

Password:

Remember me



Lost Password?

Register now!

Related Sponsor



Author : John Seymour
Article ID : 102
Audience : Top News
Version 1.00.02
Published Date: 2005/6/10 11:22:17
Reads : 15339

Click to see original Image in a new windowIn 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.

Printer Friendly Page Send this Article to a Friend
The comments are owned by the author. We aren't responsible for their content.
Author Thread
gibaphp
Published: 2005/6/27 3:59  Updated: 2005/6/27 3:59
Just popping in
Joined: 2005/2/8
From:
Comments: 4
 Re: MySQL Database Restore
Please,

Exist module Xoops for this ?
jseymour
Published: 2005/6/27 8:12  Updated: 2005/6/27 8:12
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: MySQL Database Restore
Maybe, maybe not. I use this script through the cron interface on cPanel, if I need to restore. Restore is usually a onetime operation, but, cron gives me access to the command line, since my host does not allow direct command line access.
gibaphp
Published: 2005/7/2 2:12  Updated: 2005/7/2 2:12
Just popping in
Joined: 2005/2/8
From:
Comments: 4
 Re: MySQL Database Restore
Ok, very Thanks
nightshade
Published: 2005/7/20 3:58  Updated: 2005/7/20 3:58
Supporter
Joined: 2005/6/23
From:
Comments: 21
 Re: MySQL Database Restore
Is Plesk any good? My hosting company offers it instead of cPanel. Or would you say that cPanel is much better and I should investigate trying to use it?
jseymour
Published: 2005/7/20 9:52  Updated: 2005/7/20 9:52
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: MySQL Database Restore
Plesk is good, but I prefer cPanel as it gives me more control over the options for my site. As long as you have access to MySQL and cron in plesk you can use this command line script to restore a database backup.
Guest
Published: 2005/9/7 23:19  Updated: 2005/9/7 23:19
 Re: MySQL Database Restore
Do you know if the database name you are restoring to can be different from the original one you did the backup from?

I.e if you backup the database 'data1' using mysqldump in cron, can you restore that data to a database called data2?
jseymour
Published: 2005/9/8 23:29  Updated: 2005/9/8 23:29
Webmaster
Joined: 2004/11/5
From: Ponte Vedra Beach, Florida
Comments: 201
 Re: MySQL Database Restore
To restore to a database with a different name you will have to edit the SQL file to reflect the name of the new database. Then it will restore with no trouble.
Guest
Published: 2005/9/10 23:03  Updated: 2005/9/10 23:03
 Re: MySQL Database Restore
Works a treat too!

The mysqldump with cron is excellent for automated backups. Being able to restore to a different database means I can work on a copy of my xoops site without affecting the original. Perfect for testing out new modules without affecting the original working site and data.

Thanks for your help with this.
ravert
Published: 2010/2/9 14:29  Updated: 2010/2/9 14:29
Just popping in
Joined: 2010/2/9
From:
Comments: 1
 Re: MySQL Database Restore
Very nice review! I searched for some tutorials at http://rapid4me.com , but found nothing informative, your article helped me much!
svetorim
Published: 2010/9/16 0:21  Updated: 2010/9/16 0:21
Just popping in
Joined: 2010/9/16
From:
Comments: 1
 Re: MySQL Database Restore
Interesting!
valik
Published: 2011/11/5 14:49  Updated: 2011/11/5 14:49
Just popping in
Joined: 2011/11/5
From:
Comments: 1
 Re: MySQL Database Restore
I use this script through the cron , but it does not work....
????

Related Sponsor

Bookmark and Share