Backup MySQL database and gzip

Question

I'm trying to get a little more usefulness out of my MySQL backups. I'm wanting to gzip my backup when it's completed from a cron job. Here's the script I have so far to do the backup.

#!/bin/sh
date=`date -Iminutes`
mysqldump --all-databases > /var/sqlbackup/sqlbackup-$date.sql -pmypassword
find /var/sqlbackup/ -mtime 3 | xargs rm

Any help would be beneficial, even if it's a pointer on how to do it better.

Answer 1

Here's a backup/maintenance script I use:

#!/usr/bin/sh
#backup all mysql databases
# list MySQL databases and dump each
DIR=
DATESTAMP=$(date +%Y%m%d)
DB_USER=
DB_PASS=

# remove old backups
find ${DIR} -type f -mtime +5 -exec rm -rf {} \;


DB_LIST=`mysql -u $DB_USER -p"$DB_PASS" -e'show databases;'`
DB_LIST=${DB_LIST##Database}
for DB in $DB_LIST;
do
  FILENAME=${DIR}${DB}-${DATESTAMP}.sql.gz
  mysqldump -u $DB_USER -p"$DB_PASS" --opt --flush-logs $DB | gzip > $FILENAME
done

mysqlcheck -u $DB_USER -p"$DB_PASS" --all-databases > /root/mysql_backups/check_errors-${DATESTAMP}.log

This script generates an individual gzipped backup of each database so that you don't have to restore the entire server's databases if there's just a problem with one DB. It also includes some mysql check sanity and finds old database backups and deletes them.

To restore, as requested:

I've had to restore a couple of times. It happens.

gunzip backup_file.sql.gz
mysql -u <username> -p  <database_name> < backup_file.sql 

There's probably a more 'one line' way of doing it... but that's how it's worked for me.

Answer 2

#!/bin/sh
date=`date -Iminutes`
find /var/sqlbackup/ -name 'sqlbackup-*' -mtime +2 -delete
mysqldump --all-databases -pmypassword | gzip > /var/sqlbackup/sqlbackup-$date.sql.gz

You can pipe to gzip to compress.

I moved the find to before the mysqldump. I'm not sure how big your databases are, but it'll help to keep disk usage down and avoid any space issues.

It's usually better to use -mtime +2 instead of -mtime 3 to affect anything older than two days rather than files exactly 3 days old. This way if your cron misses a day, you don't get older files missed by the delete.

Personally I prefer to restrict find to a name pattern, too, to make sure you aren't deleting anything unexpectedly.

The -delete option in find is helpful if you don't want to use xargs. Both ways will work, but I prefer to use fewer commands when possible.

Answer 3

You can just add gzip /var/sqlbackup/sqlbackup-$date.sql to the end of your script.

Answer 4

Try this:

date=`date -Iminutes`
mysqldump --all-databases -pmypassword | gzip > /var/sqlbackup/sqlbackup-$date.gz

Answer 5

Have you ever looked into irsync by R-FX networks?

A great little script that will allow you to take hot copies and gzips of your databases and send it to a remote server. :)