Jump to content
Sign in to follow this  
jrd

Autobackup Of Mysql Database

Recommended Posts

How do I set up an automatic backup of my MySQL database (say, once a week)?

Share this post


Link to post
Share on other sites

>#!/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.

Share this post


Link to post
Share on other sites

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... <_<

Share this post


Link to post
Share on other sites
...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! :)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...