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.

up vote 8 down vote accepted

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.

  • I really like this script, it's fast and gets right to what we need for the backup side. Might you be able to share the flip side where it's exploding the gzips and restoring the DB? Or haven't you had to worry about that! – Tim Meers Oct 29 '10 at 20:53
  • 1
    Editing answer to show restores... – andyhky Oct 29 '10 at 20:56
#!/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.

  • Good point on the -mtime +2. I'll look at improving this process! – Tim Meers Oct 29 '10 at 20:55

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

Try this:

date=`date -Iminutes`
mysqldump --all-databases -pmypassword | gzip > /var/sqlbackup/sqlbackup-$date.gz
  • That doesn't delete outdated backups, like the asker's example does. – Fahad Sadah Oct 29 '10 at 13:10

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. :)

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.