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
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
# /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
# /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
chmod 600 /root/bin/cron_daily_database_backup.sh
Code:
sudo -s
crontab -e
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
## 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
