-
October 29th, 2004, 10:20 AM
#1
A poor mans MySQL backup solution.
Summary
In this tutorial you’ll find a poor-mans backup solution for a MySQL database server. Implementing this solution, you’ll end up with a script that:- creates daily local backups going back up to a month
- saves one of those backups each month. The others are discarded.
- mails a copy of the backups somewhere (root, for example), to create an offsite backup.
For Windows users
This tutorial is not for you. A MySQL Administator that provides backup functionality can be downloaded from http://www.mysql.com.
Background
I wanted to create a back-up solution for a database-server I had no regular physical access to. The databases I use aren’t that big and the server is connected to the internet through a DSL connection, so using E-mail to send out the backups wasn’t a problem. I chose to mail on Sundays, because my network traffic is low in the weekends. I came up with this SH-based script, which runs every night using cron.
I wrote this script on a FreeBSD machine, but you should be able to run it on most machines running a ‘sh’ shell. I have the script ran by a dedicated backup-user (‘backup’). The script uses these commands. Most are very basic, but if your OS doesn’t support them, you’ll have to replace them with an alternative suitable to your situation:- sh
- mail
- uuencode (to attach files to mail)
- date (returns current date)
- mysqldump (comes with MySQL)
- gzip (compress data)
Implementation
Below you’ll find the source of the script. Copy it into a file (preferably with a .sh extension), and use ‘chmod’ to make that file executable (chmod 500 filename.sh). Make sure to replace USER and PWD – you should enter a suitable MySQL account here.
Create the directories specified in the first few lines of the script. Defaults are these:- /home/backup/mysql
- /home/backup/mysql/old
- /home/backup/mysql/monthly
Now, run the script, and check for errors. Running the script should result in a file called something like backup_datebases-2004-10-29-02:00:00.sql.gz in /home/backup/mysql. If it happens to be the first of the month, you’ll find the file in the monthly/ directory. If it happens to be a Sunday, a copy will be mailed to root. Check the size of your backup, and make sure that you have at least 31 times that size as free space on your partition, plus one time that size for each month you plan to use this script.
Finally, setup cron to run this script daily, preferably at a time the databases aren’t used. As you can see in the filename, I’m running it at 2 AM.
Enter this to edit your cron table:
crontab -e
Add a line like this:
0 2 * * * /path/to/filename.sh
That’s it! Next Sunday, you should be mailed a copy of the backup.
Restore information
As said, this script outputs several gzipped tarfiles. After decompressing, you'll find ordinary SQL in these files (you should be able to look at it using any text editor, unless your database consists of binary data, of course).
You can use this SQL to restore your databases, or parts of it, if needed. Normally, you'd issue this command to do perform a restore: mysql -u root -p < /path/to/ungzippedfile.sql. You’ll be prompted for a password before any restoring takes place.
Depending on your situation, you could have to add some commands (to delete existing/left-over old data for example). You’ll have to make an assessment of your current situation before you do any restoring. Don’t just execute commands, or you might end up creating a bigger mess than the one you were already in. Databases with false information usually are a bigger problem than databases with no information at all.
You could alter the parameters for mysqldump used in the backup-script, so that the resulting data matches your criteria better. I used the --opt parameter, which is shorthand for a bunch of default parameters, but you might want to include or exclude some others. For more information, have a look at the MySQL documentation, paragraph 8.8 ("The mysqldump Database Backup Program") at http://dev.mysql.com/doc/mysql/en/mysqldump.html.
I found this script to be useful, but if you have any suggestions to add functionality, or improve this script in any other way, please reply to this thread.
Script code
Code:
#!/bin/sh
date=`date "+%Y-%m-%d-%H:%M:%S"`
file="backup_datebases-$date.sql.gz"
workdir="/home/backup/mysql"
old="$workdir/old"
monthly="$workdir/monthly"
if [ -d $workdir ] && [ -d $old ] && [ -d $monthly ]; then
# Toss out old backups each month
if [ `date "+%d"` = 1 ]; then
mv $workdir/backup* $old/
fi
# Dump all databases.
/usr/local/bin/mysqldump --all-databases -u USER --password="PWD" --opt | gzip > $workdir/$file
# Finish tossing out those old backups.
if [ `date "+%d"` = 1 ]; then
rm $old/*
fi
# Mail a copy to root each sunday (offsite backup)
if [ `date "+%w"` = 0 ]; then
uuencode $workdir/$file $file | mail -s "Uuencoded database backup $file" root
fi
# Save one copy of the databases each month in monthly/
if [ `date "+%d"` = 1 ]; then
cp $workdir/$file $monthly/
fi
else
echo "Database-backup not executed at $date - Working directories are missing.";
fi
I wish to express my gratitude to the people of Italy. Thank you for inventing pizza.
-
October 29th, 2004, 07:08 PM
#2
Hi
Nice script, I like it. Good work!
I actually use something similar, that involves a backup-server (called repository) and the
mysql-server (called mysql). I mention it, because it runs under windows, and might be
of interest for some people. The basics to create a more complex script, like yours, is
given. Note, there is no tar-zcf'ing, no backup_rotating, no mail-notification etc.
(otherwise I would write a tutorial of my own ).
It is running on a daily basis, using the windows scheduler.
Code:
@echo off
net use m: \\mysql\bin /user:mysql_maintenance
net use r: \\repository\mysql /user:mysql_maintenance
set thedate=%DATE%
set MYSQL_DIR=m:\
set BACKUP_DIR=r:\
echo Backup to %BACKUP_DIR%
cd r:\
r:\
%MYSQL_DIR%mysqldump -h mysql -u mysql_maintenance --password=something particular_db >particular_db_%thedate%.sql
%MYSQL_DIR%mysqldump -h mysql -u mysql_maintenance --password=something -A >all_dbs_%thedate%.sql
rem Now you can zip them, backup_rotate them, ...
Cheers
If the only tool you have is a hammer, you tend to see every problem as a nail.
(Abraham Maslow, Psychologist, 1908-70)
-
October 30th, 2004, 03:02 PM
#3
Again, you made the critical mistake made by a lot of people's backup plans:
There are no instructions as to how to restore a database (or part thereof). Please fix.
Slarty
-
November 1st, 2004, 10:38 AM
#4
'Again'? Dude, lighten up, this is my first how-to-install-dash-backup-something tutorial. Nevertheless, you're right, I should've included some restore-information. Read this as an addition to the tutorial.
Restore information
As said, this script outputs several gzipped tarfiles. After decompressing, you'll find ordinary SQL in these files (you should be able to look at it using any text editor, unless your database consists of binary data, of course).
You can use this SQL to restore your databases, or parts of it, if needed. Normally, you'd issue this command to do perform a restore: mysql -u root -p < /path/to/ungzippedfile.sql. You’ll be prompted for a password before any restoring takes place.
Depending on your situation, you could have to add some commands (to delete existing/left-over old data for example). You’ll have to make an assessment of your current situation before you do any restoring. Don’t just execute commands, or you might end up creating a bigger mess than the one you were already in. Databases with false information usually are a bigger problem than databases with no information at all.
You could alter the parameters for mysqldump used in the backup-script, so that the resulting data matches your criteria better. I used the --opt parameter, which is shorthand for a bunch of default parameters, but you might want to include or exclude some others. For more information, have a look at the MySQL documentation, paragraph 8.8 ("The mysqldump Database Backup Program") at http://dev.mysql.com/doc/mysql/en/mysqldump.html.
I wish to express my gratitude to the people of Italy. Thank you for inventing pizza.
-
November 1st, 2004, 11:35 AM
#5
Here's my backup script..
Code:
#!/bin/bash
# Backup script
#
# (c) 2003 Anne Jan Brouwer
# Vernet Verzuim netwerk BV
# anne_jan@vernet.nl
#
# Revised October 2004 to make it generally more usable
# by Anne Jan Brouwer (the_JinX)
#
# Backups to DVD+R
# Requires dvd+rw-tools ( dvd+rw-mediainfo and growisofs )
#
# This version works with MySQL and PostgreSQL databases
#
# In the example config backup is installed in /home/backup/
# Add lines like the following to the root crontab to schedule backups
#
# # Run daily cron job at 21:20 every week day:
# 20 21 * * mon-fri /home/backup/backup daily &> /dev/null
#
# # Run weekly cron job at 21:30 on sat ( haha backup does work weekends ):
# 30 21 * * sun /home/backup/backup weekly &> /dev/null
#
# GNU GPL
# CONFIG SYSTEM
ISO="/home/backup/backup.iso" # Disk image (iso) file name
ISOTEMP="/home/backup/iso" # ISO temp folder
MINFREE=1 # Minimum % free space on TARGET
KEEP=5 # Keep nr of files (5 is one week 10 is two weeks etc.)
SOURCE="/home/the_jinx /etc" # Folder(s) to be backed up
TARGET="/home/backup/files" # Folder to place backups
TARFLAGS="" # Extra tar flags, 'rvf' are always done (man tar)
DATABASES="mydatabase yourdatabase" # Database(s) to backup
DBTARGET="/home/backup/sql" # Folder to place db backups
#SQLDUMP="/usr/local/pgsql/bin/pg_dump -C -U postgres" # PostgreSQL version
SQLDUMP="/usr/bin/mysqldump -c" # MySQL version
# CONFIG MAIL ADRESSES
MAIL="the_jinx@localhost" # Mail adress of backup operator
ADMINMAIL="root@localhost" # Mail adress of sys admin
FROM="backup@localhost" # Mail from adress (backup)
# CONFIG MAIL OPTIONS
SUCCESSHEADER="Backup: Successfull :)" # Mail header (success)
NODISKHEADER="Backup: no DVD present" # Mail header (no disk)
NOTEMPTYHEADER="Backup: DVD is full !" # Mail header (not empty)
FATALHEADER="Fatal, no backup made !!" # Mail header supplement (fatal (AFTER) no disk/notempty)
BODYFILE="/home/backup/donderdag.txt" # Mail text file
WARNBODYFILE="/home/backup/friday.txt" # Warning mail text file (thursday)
ERRORBODYFILE="/home/backup/failure.txt" # Error mail text file
# System config ( DO NOT TOUCH UNLESS YOU KNOW WTF YOU ARE DOING !! )
VERSION="0.3"
DEVICE="/dev/dvd"
LOGGER="/usr/bin/logger -s"
# Inits
EXT=$1 # make $1 available to "functions"
DATE=`date +%Y%m%d`
# log it !
logit()
{
# syslog
$LOGGER $*
}
# ERROR HANDLER !!
error()
{
logit $*
cat $ERRORBODYFILE | mail -s "Backup: $*" -r "$FROM" $MAIL $ADMINEMAIL
exit 1
}
# checks if there arn't allready too much backups
check_nr()
{
i=1
for file in $( ls -r $TARGET/folders-* 2> /dev/null)
do
if [[ $i -ge $KEEP ]]
then
echo "DELETED: $file"
rm -f $file
fi
let "i++"
done
NRDB=0
for db in $DATABASES
do
let "NRDB++"
done
let "DBKEEP=NRDB*KEEP"
i=1
for file in $( ls -r $DBTARGET/sql-* 2> /dev/null)
do
if [[ $i -ge $DBKEEP ]]
then
echo "DELETED: $file"
rm -f $file
fi
let "i++"
done
}
# check for day of the week
disk_check()
{
echo -n "Checking DVD: "
nogood=false
if [[ $( dvd+rw-mediainfo $DEVICE 2>&1 | grep Disc | grep blank ) == "" ]]
then
echo "not usable!"
nogood=true
if [[ $( dvd+rw-mediainfo $DEVICE 2>&1 | grep Disc | grep complete ) == "" ]]
then
mailheader=$NOTEMPTYHEADER
else
mailheader=$NODISKHEADER
fi
else
echo "usable.."
fi
# check for disk
if [[ $nogood == true ]]
then
if [[ $EXT == "weekly" ]]
then
error $mailheader$FATALHEADER
else
echo "not fatal"
fi
case $( date +%u ) in
3 ) cat $BODYFILE | mail -s "$mailheader" -r "$FROM" $MAIL && logit $mailheader ;;
4 ) cat $WARNBODYFILE | mail -s "$mailheader" -r "$FROM" $MAIL $ADMINEMAIL && logit $mailheader ;;
* ) echo "ignoring"
esac
else
logit "DVD is empty, good!"
fi
}
check_source()
{
# source folder check
if [[ ! -e $1 || ! -d $1 ]]
then
error "Source folder $1 does not exis!"
fi
}
check_target()
{
if [[ ! -e $1 ]]
then
echo -n "Target folder does not exist, attempting to create: "
mkdir $1
if [[ $? == 0 ]]
then
echo "successfull."
else
error "failed to create $1"
fi
fi
if [[ ! -d $1 ]]
then
error "Target folder $1 exist but is a file!"
fi
}
check_space()
{
# free space check
space=$(df -h $1)
noend=${space%"%"*}
clean=${noend##*" "}
let "freespace = 100 - $clean"
if [[ $freespace -lt $MINFREE ]]
then
error "Backup skipped, space on $TARGET is only $freespace%, while $MINFREE% is required!"
fi
}
# do checks
do_checks()
{
for folder in $SOURCE
do
check_source $folder
done
# user check
if [[ `id -u` != 0 ]]
then
logit `whoami`" is not allowd to do backups!"
exit 13
fi
check_target $TARGET
check_target $DBTARGET
check_target $ISOTEMP
check_space $TARGET
check_space $DBTARGET
check_space $ISOTEMP
logit "Backup $VERSION: operation: $EXT"
}
# daily backups
backup_daily()
{
do_checks
disk_check
rm $ISOTEMP -r
check_nr
# files
if [[ -e $TARGET/folders.list ]]
then
rm $TARGET/folders.list
fi
logit "Gathering files"
target="$TARGET/folders-$DATE.tar"
for folder in $SOURCE
do
tar rvf $TARFLAGS $target $folder >> $ARGET/folders.list
if [[ $? != 0 ]]
then
error "Error while trying to gather files: $folder"
fi
done
logit "Compressing"
bzip2 $target
if [[ $? != 0 ]]
then
error "Error while trying to compress $target"
fi
if [[ -e $DBTARGET/databases.list ]]
then
rm $DBTARGET/databases.list
fi
for DATABASE in $DATABASES
do
# SQL
logit "SQL dump"
echo $DATABASE >> $ARGET/folders.list
target="$DBTARGET/sql-$DATE-$DATABASE.dmp"
$SQLDUMP $DATABASE > $target
if [[ $? != 0 ]]
then
error "Error while trying to dump psql dbase: $DATABASE to $target"
fi
logit "Compressing dump"
bzip2 $target
if [[ $? != 0 ]]
then
error "Error while trying to compress $target"
fi
done
}
# weekly backups
backup_weekly()
{
do_checks
disk_check
target=$( ls $TARGET/folders-* | tail -n1 )
dbtargets=$( ls $DBTARGET/sql-* | tail -n$NRDB )
# make iso temp folder
logit "preparing for iso creation"
ln $target $ISOTEMP
for dbtarget in $dbtargets
do
ln $dbtarget $ISOTEMP
done
cp $TARGET/folders.list $DBTARGET/databases.list $ISOTEMP
# make iso
mkisofs -o $ISO -J -r -V "backup_$DATE" $ISOTEMP
if [[ $? != 0 ]]
then
error "Error while trying to create iso $ISO from $TARGET"
else
logit "iso created !"
rm -r $ISOTEMP
fi
# burn iso
growisofs -Z $DEVICE=$ISO
if [[ $? != 0 ]]
then
error "Error while trying to write iso $ISO to $DEVICE"
else
logit "dvd created !"
rm $ISO
logit "Backup burned successfull !"
echo "Backup made ;)" | mail -s "$SUCESSHEADER" -r "$FROM" $MAIL $ADMINEMAIL
fi
}
# The main execution
case "$1" in
'daily')
backup_daily
;;
'weekly')
backup_weekly
;;
*)
echo "Usage: $0 { weekly | daily }"
esac
#EOF
This baby has no restore info either and is released AS IS under the GNU GPL
ASCII stupid question, get a stupid ANSI.
When in Russia, pet a PETSCII.
Get your ass over to SLAYRadio the best station for C64 Remixes !
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|