Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

Automated Backup Help!!


Recommended Posts

I am a general linux newbie, but I would like to automate the backup of the database. This would prevent a problem if the store keeper forgets to go to Database Backup Manager on a regular basis.


I want to come up with a cron job that does the database backup to a file, creates a tarball, then copies the file off the server for safe keeping (to a seperate ftp server or to tape or to cdrw media).


I have moved a OSC from one Linux box to annother. I did this by running Database Backup Manager, making a tarball of the /var/www/html (including the admin/backups directory). Then moving the tarball to the new box, extracting the tarball, creating the db and importing the .sql file created by the Database Backup Mananger. Everything worked. So, I know it can be done.


The part I need help with is the backup of the db. How can one run the backup outside of the admin's Database Backup Manager?


Any Suggestions?





If you are not having fun, your not doing it right... :wink:

Link to comment
Share on other sites



Maybe this will help. I wrote this PERL script for backing up MySQL Databases. You'll need to modify some of the variables to fit your environment. It's probably not perfect but it does the trick and I've been using it for 6 months with no problems.



It actually does two backups and puts them in the directory as specifiedin the $basepath variable. First it uses mysqldump to dump the database to a .sql file and compresses it, then it uses mysqlhotcopy to do a hot database backup, tars and compresses it. It attaches the date and the time when the backup was made to the final files. It will also remove the previous backup when it runs, so you'll need to get the files off of there after the backup. I'll explain the variables you'll need to make changes to:


$basepath - path in which the two dump files will be stored


$dumpfilebasename - basename of the dumped files (probably same name as the databas woudl be good)


$mysqldump - path to the mysqldumputility


$mysqluser - root DB user or user who has permissions to backup the database


$mysqlpass - password for $mysqluser


$mysqldb - name of the database you want to backup




# Created on 7/18/02 by Jim Ficarra

# Backup script for any MYSQL Database

# Script uses "mysqldump" to dump the database and "mysqlhotcopy"

# to create a "hot backup" of the database.

# Modify the variables below with the correct paths, names, and password

my $basepath="/PathOfWhereYouWantBackupToGo/";

my $dumpfilebasename="DumpfileBaseName";

my $mysqldump="/usr/bin/mysqldump";

my $mysqluser="AuthorizedDBUser";

my $mysqlpass="password";

my $mysqldb="dbname";

my $hotbackupdir=$basepath . $mysqldb;

my $delfile;

my $dumpfile;

my $tarfile;

my $dateportion;

# Remove hotbackup folder if it already exists

# if (-e $hotbackupdir) {

# rmdir $hotbackupdir;

# }

# Remove Previous SQL Dump

$delfile=$basepath . "${dumpfilebasename}-*";

system("rm $delfile");

# Perform Database Dump

$dateportion=`date +%D`;

$dateportion =~s///-/;

$dateportion =~s///-/;


$dumpfile=$basepath . "${dumpfilebasename}-" . $dateportion . ".sql";

system("$mysqldump --user=$mysqluser --password=$mysqlpass $mysqldb > $dumpfile");

# GZIP the dumped SQL file

system("gzip $dumpfile");

# Perform Database Hot Backup

system("/usr/bin/mysqlhotcopy --allowold --user=$mysqluser --password=$mysqlpass $mysqldb $basepath");

# Tar and compress the MySQL Hotcopy

$tarfile=$basepath . "${dumpfilebasename}-hotbackup-" . $dateportion . ".tar.gz";

system("tar --create --verbose --gzip --file=$tarfile $hotbackupdir");

system("rm -R $hotbackupdir");


Put this script in a cron job at the interval of your choice, then let it rip.


Hope this helps.



Link to comment
Share on other sites

Here is another script that will email the backup to you:


# List all of the MySQL databases that you want to backup in here, 

# each seperated by a space

databases="mysql forum"

# Directory where you want the backup files to be placed


# MySQL dump command, use the full path name here


# MySQL Username and password

userpassword=" --user=root --password=password"

# MySQL dump options

dumpoptions=" --quick --add-drop-table --add-locks --extended-insert --lock-tables"

# Unix Commands




# Send Backup?  Would you like the backup emailed to you?

# Set to "y" if you do


subject="Your MySQL Backup"


# Create our backup directory if not already there

mkdir -p ${backupdir}

if [ ! -d ${backupdir} ] 


  echo "Not a directory: ${backupdir}"

  exit 1


# Dump all of our databases

echo "Dumping MySQL Databases"

for database in $databases


  $mysqldumpcmd $userpassword $dumpoptions $database > ${backupdir}/${database}.sql


# Compress all of our backup files

echo "Compressing Dump Files"

for database in $databases


  rm -f ${backupdir}/${database}.sql.gz

  $gzip ${backupdir}/${database}.sql


# Send the backups via email

if [ $sendbackup = "y" ]


  for database in $databases


     $uuencode ${backupdir}/${database}.sql.gz > ${backupdir}/${database}.sql.gz.uu

     $mail -s "$subject : $database" $mailto < ${backupdir}/${database}.sql.gz.uu



# And we're done

ls -l ${backupdir}

echo "Dump Complete!"


Link to comment
Share on other sites

I think a lot of people are looking for this...I was searching for this about a month ago and did not find answer for it so I gave up.


I am glad to find the answer but can someone make a contribution from it with installation note?



Link to comment
Share on other sites



I cleaned up my script and added tons of documentation in the script itself as well as a lengthy installation guide.




I like Wayne Luke's idea of e-mailing the backups, however this version will not include that. Wayne, I hope you don't mind but I stole some of your ideas in your script such as making the mysqldump options a string. I plan on making revisions to this script at some point, hopefully in the near future, to provide the option to e-mail the backups as Wayne's script does.


E-mail me if you have any questions.



Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...