Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

5 Answers

up vote 7 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.

share|improve this answer
    
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
    
Awesome! Thanks man. –  Tim Meers Oct 29 '10 at 21:07
add comment
#!/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.

share|improve this answer
    
Good point on the -mtime +2. I'll look at improving this process! –  Tim Meers Oct 29 '10 at 20:55
add comment

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

share|improve this answer
add comment

Try this:

date=`date -Iminutes`
mysqldump --all-databases -pmypassword | gzip > /var/sqlbackup/sqlbackup-$date.gz
share|improve this answer
    
That doesn't delete outdated backups, like the asker's example does. –  fahadsadah Oct 29 '10 at 13:10
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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