Aug 24

mysql backup script with auto delete the old one

MySQL Add comments

It’s been a while i haven’t post anything 🙂 , i got this request on how to backup all of mysql databaseand since the hdd is not quite big it also required to delete the old backup. So here is the backup script

###################################################################################################

#!/bin/sh

DATE=`date +%d%b-%H%M%S`

unset PATH
# USER VARIABLES
MYSQLUSER=user
MYSQLPWD=password
MYSQLHOST=localhost
MYSQLBACKUPDIR=/home/backup/mysql/mysql_backup$DATE

FILE=$MYSQLBACKUPDIR.tgz; # No need to modify this one

# PATH VARIABLES – It depend on your server program
MK=/bin/mkdir;
RM=/bin/rm;
GREP=/bin/grep;
MYSQL=/usr/local/mysql/bin/mysql;
MYSQLDUMP=/usr/local/mysql/bin/mysqldump;
XARGS=/usr/bin/xargs;
FIND=/usr/bin/find;
TAR=/bin/tar;
UUENCODE=/usr/bin/uuencode;

# CREATE MYSQL BACKUP

# Delete the old ones. The +7 means 7 days ago
$FIND /home/backup/mysql/ -mtime +7 |$XARGS $RM -rf

# Create new backup dir
$MK -p $MYSQLBACKUPDIR

#Dump new files
for i in $(echo ‘SHOW DATABASES;’ | $MYSQL -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST|$GREP -v ‘^Database$’); do
$MYSQLDUMP                                                    \
-u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST                         \
-Q -c -C –add-drop-table –add-locks –quick –skip-lock-tables   \
$i > $MYSQLBACKUPDIR/$i.sql;
done;

# collect and compress the sqls
$TAR -cvzpf $MYSQLBACKUPDIR.tgz $MYSQLBACKUPDIR
$RM -r $MYSQLBACKUPDIR

###################################################################################################

Then save it , i save it as mysql_backup file. Then open the crontab to run it weekly

# crontab -e

then put this

0 0 * * 0 /root/mysql_backup > /dev/null 2>&1

Leave a Reply