2

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.

5 Answers 5

10

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.

2
  • 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, 2010 at 20:53
  • 1
    Editing answer to show restores...
    – ahill
    Oct 29, 2010 at 20:56
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.

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

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

0

Try this:

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .