mysqldump Backup script using Bzip

Here is an example of a backup script using bzip.  This takes longer but compresses upto 20% more.

 

 

backup_dbs_bzip.sh

 

#!/bin/sh

 

EMAIL_LIST="dbateam@uk.abc.com"

 

date=`/bin/date +"%d-%m-%y-%H:%M"`

umask 066

( /usr/bin/mysqldump -uroot --single-transaction --all-databases | bzip2 -cz1) > /home/backup/all-$date-`hostname -s`.sql.bz2 2> /home/backup/all-$date-`hostname -s`.sql.err.log

 

ls -ltr /home/backup/all-$date-`hostname -s`.sql.bz2 > /home/backup/filelist.out

ls -ltr /home/backup/all-$date-`hostname -s`.sql.err.log >> /home/backup/filelist.out

 

mail -s "mySQL Backup on `hostname -s`" $EMAIL_LIST < /home/backup/filelist.out

 

# Clear up old data, anything older then 20 days

find /home/backup/*.bz2 -ctime +20 -exec rm {} \;

find /home/backup/*.log -ctime +20 -exec rm {} \;

 

 

 

backup_dbs_gzip.sh

 

#!/bin/sh

 

EMAIL_LIST="dbateam@uk.abc.com"

 

date=`/bin/date +"%d-%m-%y-%H:%M"`

umask 066

( /usr/bin/mysqldump -uroot --single-transaction --all-databases | gzip) > /home/backup/all-$date-`hostname -s`.sql.gz 2> /home/backup/all-$date-`hostname -s`.sql.err.log

 

ls -ltr /home/backup/all-$date-`hostname -s`.sql.gz > /home/backup/filelist.out

ls -ltr /home/backup/all-$date-`hostname -s`.sql.err.log >> /home/backup/filelist.out

 

mail -s "mySQL Backup on `hostname -s`" $EMAIL_LIST < /home/backup/filelist.out

 

# Clear up old data, anything older then 20 days

find /home/backup/*.gz -ctime +20 -exec rm {} \;

find /home/backup/*.log -ctime +20 -exec rm {} \;

 

 

 

for multiple databases, use  --database db_name1 db_name2  , instead of –all-databases