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";

TOP output

9:44pm up 2 days, 2 min, 1 user, load average: 0.14, 0.12, 0.09
90 processes: 82 sleeping, 1 running, 7 zombie, 0 stopped
CPU0 states: 0.4% user, 1.1% system, 0.0% nice, 97.4% idle
CPU1 states: 0.5% user, 1.3% system, 0.0% nice, 97.1% idle
Mem: 1288720K av, 1056260K used, 232460K free, 0K shrd, 145644K buff
Swap: 522104K av, 0K used, 522104K free 469764K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
30997 ed 16 0 1100 1100 840 R 1.7 0.0 0:00 top
1120 root 5 -10 249M 174M 71508 S