Mysql – Backup MySQL database and gzip

gzipMySQL

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.

Best Answer

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.

Related Topic