« Metadata Field dc.type | Main | Number of assets per item in AgEcon »

Cron to do mysqldump

Create a mysql user that will do the backups

GRANT LOCK TABLES, SELECT ON *.* TO 'backup'@'%.oit.umn.edu' IDENTIFIED BY PASSWORD;

The Script to run under cron

#!/usr/bin/perl

printf("Running...");

####################
# Backup settings #
###################

my $mysql_user = "backup";
my $mysql_pwd = "password";
my $mysql_server = "mysql_host";
my $dir_base = "path_to_backup_dir";

my ($y, $m, $d) = (localtime)[5,4,3];

$y += 1900;
$m += 1;

if ($m < 10) { $m = "0" . $m; }
if ($d < 10) { $d = "0" . $d; }

$stamp = $y . "_" . $m . $d;
$file_dumpname = $stamp . "_mysqldump.sql";

# Format:
#/swadm/assetstore_stage/Backups/mysql/2009_0921_mysqldump.sql
#/swadm/assetstore_stage/Backups/mysql/2009_0922_mysqldump.sql

##################
# Handling MySQL #
##################

# Making mySQL dumps
$starttime = `date`;
printf("mysqldump -h$mysql_server -u$mysql_user -p$mysql_pwd drupalstage > $dir_base/$file_dumpname\n");

`mysqldump -h$mysql_server -u$mysql_user -p$mysql_pwd drupalstage > $dir_base/$file_dumpname`;

#############################
# Chmod -- owner/group only #
#############################
`chmod 660 $dir_base/$file_dumpname`;
`gzip -fq $dir_base/$file_dumpname`;


##############
# Concluding #
##############


# The "done" message
printf("...complete.\n");
$finishtime = `date`;

####################
# Mail the results #
####################

$title='MySQL backup';
$to='your email';
$from= 'crontab@yourhost';
$subject='[cron] vm02 mySQLdump';

open(MAIL, "|/usr/sbin/sendmail -t");

## Mail Header
print MAIL "To: $to\n";
print MAIL "From: $from\n";
print MAIL "Subject: $subject\n\n";
## Mail Body
# $dumpsize = `du  -h $dir_base/${file_dumpname}.gz`;
#print MAIL "Start:\n$starttime\nFinish:\n$finishtime\nDumpsize:\n  $dumpsize";
print MAIL "Start:\n$starttime\nFinish:\n$finishtime\nFile $dir_base/${file_dumpname}.gz";
close(MAIL);

Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)