Automated Backup Help!!

[jmckeand]

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?

 

James.

[Trailz]

Lookup mysqldump

 

I think its mysqldump nameofdatabase > databasename.sql

 

that would make a dump of it.

[jficarra]

James,

 

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.

 

Summary:

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

 

 

#!/usr/bin/perl

# 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///-/;

chomp($dateportion);

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

 

-Jim

[Guest]

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

#!/bin/sh



# 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

backupdir=/backup



# MySQL dump command, use the full path name here

mysqldumpcmd=/usr/local/mysql/bin/mysqldump



# 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

gzip=/bin/gzip

uuencode=/usr/bin/uuencode

mail=/bin/mail



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

# Set to "y" if you do

sendbackup="n"

subject="Your MySQL Backup"

mailto="you@yourdomain.com"



# Create our backup directory if not already there

mkdir -p ${backupdir}

if [ ! -d ${backupdir} ] 

then

  echo "Not a directory: ${backupdir}"

  exit 1

fi



# Dump all of our databases

echo "Dumping MySQL Databases"

for database in $databases

do

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

done



# Compress all of our backup files

echo "Compressing Dump Files"

for database in $databases

do

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

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

done



# Send the backups via email

if [ $sendbackup = "y" ]

then

  for database in $databases

  do

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

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

  done

fi



# And we're done

ls -l ${backupdir}

echo "Dump Complete!"

exit

[joongpark]

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?

 

Thanks.

[jficarra]

Perhaps I'll have some time in the next couple of days to clean up my script and contribute it with an installation note.

 

-Jim

[joongpark]

That would be super...Thank you so much.

 

:o :o :o :o

[jficarra]

Ok,

 

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

 

http://www.oscommerce.com/community/contributions,847

 

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.

 

-Jim

[joongpark]

Thank you so much...I will try it tomorrow when I am sober.

Thanks.