Database restauring pediodicly


#1

Hi,

Could you please explain to me what steps I need to do in order to restaure my MySQL dump every day?

I now I need to use Cron over the DreamHost panel, but I don’t know exactly where I need to save my dump, and I don’t really know how to write a correct cron tab for this.

Thanks a lot.
Cyril


#2

You mean restore, as in put it back to the way it was before? Like a reset?

Or do you mean to back it up every day? For this, see this wiki section:
http://wiki.dreamhost.com/Automatic_Backup#Mysql_Database_Backup

Scroll up to get the full explanation. Scroll down to see how to add a cron job for this.

-Scott


#3

[quote]You mean restore, as in put it back to the way it was before? Like a reset?

[/quote]

Yes, absolutly. After searching information, I had found this (on http://blog.dreamhosters.com/kbase/index.cgi?area=2931):
To restore your database from a backup:
Take that db dump and do:
cat filename.sql | mysql -uusername -ppassword -h yourMySQLHostname dbname

But I don’t know if I need to use the shell user (and if so, I this shell user can be chrooted) and I don’t know the path where I need to put my filename.sql file on the server.

Thanks you for your help.

Cyril


#4

First off, I’m no DBA, but I don’t know for sure if that restore command will drop all old data. Hopefully someone will clarify this. The wiki says “it might be recommended to empty it first.”

That is a shell command, but can be executed via cron. I’d create a script that does all of it for me, and make darned sure it’s not group or world readable (i.e. chmod 700) since it has your db password.

#!/bin/bash (this is a bash shell script)
cd ~/dbackups (change to my backups directory in my home folder)
cat filename.sql | mysql -uusername -ppassword -h yourMySQLHostname dbname

http://wiki.dreamhost.com/Restore_SQL_backup

-Scott