Backing up MySQL database

Below are just notes for my own purpose. Feel free to make use of them.

I am backing up to compressed files, onto a removable encrypted drive. So not worried about backup file password protection. This is a development system so not only do I want to keep daily backups but also the last two hours of hourly backups of a particular database.

p7zip is a port of 7za.exe for POSIX systems like Unix (Linux, Solaris, OpenBSD, FreeBSD, Cygwin, AIX, ...), MacOS X and also for BeOS and Amiga. I find it has good compression rates and I use it on my Ubuntu Servers.
http://www.7-zip.org/ or http://p7zip.sourceforge.net/

Although quite simular, I'll post both the hourly and daily backup scripts here for completeness. I save them to /root/bin/ and make them only read, write and executable (chown 600) by Root.

Code:   

sudo -s
mkdir /root/bin
cd /root/bin

/root/bin/cron_hourly_database_backup.sh:
Code:   

#!/bin/sh
# /root/bin/cron_hourly_database_backup.sh

if [ -f /tmp/database_backup_lockfile ]
then
    exit 0
else
    touch /tmp/database_backup_lockfile
fi

date=$(date +"%Y%m%d%H%M")
filename="database_hourly_backup-"

#    logger "$date: Cron ran /usr/local/bin/cron_hourly_database_backup.sh"
    mysqldump -uroot -pmypassword --opt database1 | p7zip > /var/backups/$filename$date.sql.7z
# find hourly backup files over 2 hours old and delete
    find /var/backups/$filename* -type f -amin +121 -exec rm {} \;

rm -f /tmp/database_backup_lockfile

The above script creates a lockfile. Whilst this exists I don't want any other backups ran.

mysqldump is then run with p7zip compressing the resultant file, on the fly. Note that my script may not be suitable for all situations : mysqldump needs to acquire locks to create a consistent backup, and this may raise issues on highly loaded servers.

Once backed up, the find command locates specific backup files that are over two hours old and deletes them (rm). Finally, the lockfile is deleted.

cron_daily_database_backup.sh:
Code:   

#!/bin/sh
# /root/bin/cron_daily_database_backup.sh
if [ -f /tmp/database_backup_lockfile ]
then
    exit 0
else
    touch /tmp/database_backup_lockfile
fi

date=$(date +"%Y%m%d%H%M")
filename1="database1_daily_backup-"
filename2="mysql_daily_backup-"

#    logger "$date: Cron ran /usr/local/bin/cron_daily_database_backup.sh"
    mysqldump -uroot -pmypassword --opt database1 | p7zip > /var/backups/$filename1$date.sql.7z
    mysqldump -uroot -pmypassword --opt mysql | p7zip > /var/backups/$filename2$date.sql.7z
# find daily backup files over 7 days old and delete
    find /var/backups/$filename1* -type f -atime +7 -exec rm {} \
    find /var/backups/$filename2* -type f -atime +7 -exec rm {} \

rm -f /tmp/database_backup_lockfile

Code:   

chmod 600 /root/bin/cron_hourly_database_backup.sh
chmod 600 /root/bin/cron_daily_database_backup.sh

Code:   

sudo -s
crontab -e

Crontab:
Code:   

## root crontab  
## m h  dom mon dow   command
0 01-23 * * * /root/bin/cron_hourly_database_backup.sh >> /var/backups/cron_hourly_database_backup.log 2>&1
0 0 * * * /root/bin/cron_daily_database_backup.sh >> /var/backups/cron_daily_database_backup.log 2>&1

The crontab shows that the hourly backup is ran between 01:00 to 23:00 hours. The daily backup is the only one to run at midnight (00:00 hour).

Notes:
You can dump more than one database to a file by altering one line in the script.

Example:
Code:   

mysqldump -uroot -pmypassword --opt database1,database2,database3 | p7zip > /var/backups/$filename$date.sql.7z

or

Code:   

mysqldump -uroot -pmypassword --opt --all-databases | p7zip > /var/backups/$filename$date.sql.7z

* The above has not documented how I then copy all the backups to the encrypted, swappable drives.


Restoring database

Regularly test your backup by restoring to a dummy database. If you do need to restore the actual database then drop the old one first.

Code:   

mysql -uroot -pmypassword -e "DROP DATABASE [databasename];"

Next, create the database.

Code:   

mysql -uroot -pmypassword -e "CREATE DATABASE [databasename];"

or combine the commands...

Code:   

mysql -uroot -pmypassword -e "DROP DATABASE [databasename]; CREATE DATABASE [databasename];"

Then restore backup...
Code:   

p7zip -d < /var/backups/[filename].sql.7z | mysql -u mysqlusername -pmypassword [databasename]

   
... replacing [filename] with the correct filename and replacing [databasename] with the correct database name.


MySQL-Maint

If you don't want to 'roll your own' custom scripts for MYSQL database backups and maintenance then check out MySQL-Maint.

A bash script that performs backups and maintenance on your MySQL servers.
https://github.com/mmerian/MySQL-Maint


Categories: How-To, MySQL
Tags: MySQL, database, administration, backup, backups, maintenance, optimization, optimisation