MySQL Backup Crontab

simple script code untuk backup mysql database, script ini akan logs in ke database server, membackup dan mengompress dengan gzip.

#!/usr/bin/perl

$BACKUPPATH="/usr/backup"; #path do backup directory
$BACKUPPREFIX="backup"; #name to begin our backups with
$DBNAME="FORUM DATABASE"; #name of database
$DBUSER="ROOT"; #user with access to database
$DBPASS="PASSWORD"; #path to db
$MYSQLDUMP = "/usr/local/bin/mysqldump"; # path to mysql dump binary program
$LSOPTS="-tr"; #options to sort in reverse by creation date

$KEEP=5; #number of backups to keep

#$DOM=`date +%d%b%S`; #replace this line with the line below it
#to get rid of the seconds at the end of the
#filename (used for testing)
$DOM=`date +%d%b%S`;

#---------------------shouldn't have to change anything below here

chomp $DOM;

$FILENAME = "$BACKUPPREFIX-$DBNAME-$DOM.sql";

#========== deal with local backups ==========#
#get existing backups
@backups=`ls $LSOPTS $BACKUPPATH/$BACKUPPREFIX-$DBNAME-*`;

#count their number
$count = $#backups;
$count++;

#over desired number, kill the oldest
if ($count >= $KEEP){
chomp @backups[0];
system "rm @backups[0]";
}

#housekeeping out of the way, let's do a backup
system "$MYSQLDUMP --databases $DBNAMEs --add-locks --extended-insert -u $DBUSER -p$DBPASS | gzip > $BACKUPPATH/$FILENAME.gz";

edit script ini dg editor favorite anda (vi, pico, etc) dan sesuaikan variabel2 diatas dan simpan dgn nama backup.pl (di /usr/local/bin misalnya).
buat file cron.txt untuk crontab dng isi sbg berikut:

0 2 * * * perl /path/to/backup.pl

dengan shell command “crontab cron.txt” untuk meload dan mengeset cron.
backup akan dijalankan setiap pagi jam 2am

terjemahan bebas dari adminzone