jrd Posted January 27, 2004 Posted January 27, 2004 How do I set up an automatic backup of my MySQL database (say, once a week)? Quote
taznumber1 Posted January 27, 2004 Posted January 27, 2004 >#!/bin/bash ##################################### ### MySQL Configuration Variables ### ##################################### # MySQL Hostname DBHOST='localhost' # MySQL Username DBUSER='username' # MySQL Password DBPASSWD='password' ##################################### ### FTP Configuration Variables ##### ##################################### # FTP Hostname FTPHOST='www.example.com' # FTP Username FTPUSER='username' # FTP Password FTPPASSWD='password' # Local Directory for Dump Files LOCALDIR=/path/to/local/directory/ # Remote Directory for Offsite Backup REMOTEDIR=/path/to/remote/directory/ # Prefix for offsite .tar file backup TARPREFIX=db1 ##################################### ### Database Backup Script ######### ##################################### cd $LOCALDIR SUFFIX=`eval date +%y%m%d` DBS=`mysql -u$DBUSER -p$DBPASSWD -h$DBHOST -e"show databases"` for DATABASE in $DBS do if [ $DATABASE != "Database" ]; then FILENAME=$SUFFIX-$DATABASE.gz mysqldump -u$DBUSER -p$DBPASSWD -h$DBHOST $DATABASE | gzip --best > $LOCALDIR$FILENAME fi done chmod 400 $LOCALDIR*.gz tar -cf $TARPREFIX-$SUFFIX.tar $SUFFIX-*.gz ftp -n $FTPHOST <<END_SCRIPT quote USER $FTPUSER quote PASS $FTPPASSWD cd $REMOTEDIR put $TARPREFIX-$SUFFIX.tar quit END_SCRIPT rm -f $TARPREFIX-$SUFFIX.tar exit 0 There is the script that I use. It will create a .gz file for each database that you have then tar them all together into one file and then ftp them to a remote host. But keep in mind using this script that it will use up your backwidth to export it to a remote host. You would just place this code in a file and then change all the required variables at the top and then just set up a cron job in your cpanel and you would be good to go. Then if you needed to restore it is real easy with this script, I have used my backup to restore to a test server and they work great. But also keep in mind that TCH does daily backup and a weekly offsite backup of your site so if you want to do a backup that is just for your piece of mind. I know that I do a daily offsite backup on my account because I can not afford to loose a weeks worth of data if they did have a full server melt down and would have to do a off site restore. Quote
Nick_ Posted January 27, 2004 Posted January 27, 2004 You can, of course, control how often, and when these backups are done via the cronjob. Please try to schedule them in the middle of the night only... Quote
Lianna Posted January 28, 2004 Posted January 28, 2004 ...But also keep in mind that TCH does daily backup and a weekly offsite backup of your site so if you want to do a backup that is just for your piece of mind. ... taznumber1, The backups done by TCH are for disk/disaster recovery. Backing up your site and your data are YOUR responsibility, not just for peace of mind. TCH, although taking great steps to help you protect your data, is in no way responsible for the loss of data (per the Terms of Service). PLEASE, everyone take your data seriously! Back it up! Quote
TCH-Thomas Posted January 28, 2004 Posted January 28, 2004 Excuse a newbie (in this ) question. How do I do these back ups with cron job? -Thomas Quote
Nick_ Posted January 28, 2004 Posted January 28, 2004 Hey Thomas. Check out this link : http://www.cpanel.net/docs/cp/cronJobs.htm You could place that file in your root directory for instance, and call it backup.sh, than the command you would want to run would be "sh backup.sh" Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.