With all the databases I run, I need a daily sanity check. I download a copy of each database and do a test load on my system at home. Today I timed that process:
$ time ~/bin/backup-supernews.sh
…
real 77m57.323s
user 12m52.610s
sys 0m56.136s
There are 11 such databases. The dump files range in size from 5K (globals.sql) to 2.8G (freshports.sql).
The script that does this is pretty simple, but has evolved over the years.
#!/bin/sh
#
# This file does a backup of each database on the nyi server.
# It relies upon a file on nyi to do the actual backup,
# then uses rsync to copy the files from nyi to here.
#
BACKUPDIR=${HOME}/backups/supernews/database-backup
IDENTITY_FILE=${HOME}/.ssh/id_dsa_no_passphrase
SERVER_TO_BACKUP=supernews.example.org
cd ${BACKUPDIR}/postgresql
YYYYMMDD=`eval date "+%Y.%m.%d"`
echo will tar things up into ${YYYYMMDD} format
FILES=`echo *.sql`
echo will be processing ${FILES}
# maybe there are no such files
if [ "x${FILES}" != 'x\*.sql' ]
then
for i in $FILES
do
# mv ${i} archive
tar -cvzf archive/`basename ${i} .sql`.${YYYYMMDD}.sql.tgz ${i}
done
else
echo no files found for processing
fi
cd ${BACKUPDIR}/mysql
FILES=`echo *.sql`
# maybe there are no such files
if [ "x${FILES}" != 'x\*.sql' ]
then
for i in $FILES
do
# mv ${i} archive
tar -cvzf archive/`basename ${i} .sql`.${YYYYMMDD}.sql.tgz ${i}
done
fi
cd ${BACKUPDIR}
#
# dump the databases on the remote server
#
logger "dumping the databases on ${SERVER_TO_BACKUP} from `hostname`"
ssh -i ${IDENTITY_FILE} dan@${SERVER_TO_BACKUP} ~dan/bin/backup.sh
#
# use rsync to get local copies
#
/usr/local/bin/rsync -e "ssh -i ${IDENTITY_FILE}" --recursive -avz --stats --progress \
--exclude 'archive' dan@${SERVER_TO_BACKUP}:~/database-backup/ ${BACKUPDIR}
#
# now do a test of the backups to ensure they actually restore
~/bin/test-backups.sh ${BACKUPDIR}
That is the job that archives the old files and downloads the new ones.
The following script loads up that database and is referred to in the last line of the above script:
#!/bin/sh
#
# test the files we've recovered
#
BACKUPDIR=$1
GLOBALS="globals"
PSQL=/usr/local/bin/psql
CREATEDB=/usr/local/bin/createdb
DROPDB=/usr/local/bin/dropdb
cd ${BACKUPDIR}/postgresql
echo dropping test databases
FILES=`echo *.sql`
for i in $FILES
do
DBNAME=`basename $i .sql`
if [ ${DBNAME} != ${GLOBALS} ]
then
${DROPDB} ${DBNAME}_test
fi
done
echo loading GLOBALS
if [ -r "${GLOBALS}.sql" ]
then
${PSQL} postgres < ${GLOBALS}.sql
fi
echo creating the databases
for i in $FILES
do
DBNAME=`basename $i .sql`
if [ ${DBNAME} != ${GLOBALS} ]
then
${CREATEDB} ${DBNAME}_test -E SQL_ASCII
${PSQL} --quiet ${DBNAME}_test < ${DBNAME}.sql
fi
done
The final part of the puzzle is the script run on the production server which dumps all the databases required:
#!/bin/sh
PGDUMP=/usr/local/bin/pg_dump
MYSQLDUMP=/usr/local/bin/mysqldump
BACKUPROOT=${HOME}/database-backup
cd ${BACKUPROOT}/postgresql
DATABASES="bsdcert fpphorum freshports.org fsphorum openx nagios \
pentabarf pentabarf_bsdcan pentabarf_pgcon postgres"
for database in ${DATABASES}
do
echo dumping $database
if [ -f ${database}.sql ]
then
mv ${database}.sql archive
fi
${PGDUMP} ${database} > ${database}.sql
if [ $? -ne 0 ]
then
echo backup of ${database} failed
fi
done
#
# Now dump the globals
#
if [ -f globals.sql ]
then
mv globals.sql archive
fi
/usr/local/bin/pg_dumpall -g > globals.sql
if [ $? -ne 0 ]
then
echo backup of globals failed
fi
DATABASES="cacti mysql"
cd ${BACKUPROOT}/mysql
for database in ${DATABASES}
do
echo dumping $database
if [ -f ${database}.sql ]
then
mv ${database}.sql archive
fi
${MYSQLDUMP} -u root ${database} > ${database}.sql
if [ $? -ne 0 ]
then
echo backup of ${database} failed
fi
done
Any questions? :) Continue reading »