DatabaseBackupProcedure

Introduction

Be it mysql or postgresql, both have bash backup scripts deployed by puppet. The concept is fairly simple: dump the db in files in /var/lib/db_backups/ , always to the same files, and then back this up by backuppc which will decide which versions to keep and do the rotation.

Mysql

Here is the script as deployed by puppet:

#!/bin/bash

# FILE DEPLOYED BY PUPPET DO NOT EDIT DIRECTLY

# Random sleep - Backups shouldn't start at the exact same time to prevent the KVM host from overloading
if [[ $* != *--nosleep* ]]
then
        sleep $(($RANDOM % 1800));
fi

DATABASES=`/usr/bin/mysql -e 'show databases' -s --skip-column-names 2> /tmp/mysqlbackup.log`;
if [ $? -ne 0 ] ; then
        /bin/cat /tmp/mysqlbackup.log | /usr/bin/mail -s "MySQL backup error on `/bin/hostname -f`"  itsupport@thehumanjourney.net;
        /bin/rm /tmp/mysqlbackup.log ;
        exit 1
fi


for i in $DATABASES; do
        if [ "$i" = "information_schema" ]; then continue ; fi ;
        if [ "$i" = "performance_schema" ]; then continue ; fi ;
        /usr/bin/mysqldump $i 2> /tmp/mysqlbackup.log > "/var/lib/db_backups/$i.sql";
        if [ $? -eq 0 ] ; then
                /bin/rm -f "/var/lib/db_backups/$i.sql.gz";
                /bin/gzip -f "/var/lib/db_backups/$i.sql";
        else
                /bin/cat /tmp/mysqlbackup.log | /usr/bin/mail -s "MySQL backup error on `/bin/hostname -f`"  itsupport@thehumanjourney.net;
                /bin/rm /tmp/mysqlbackup.log ;
		exit 1;
        fi;
done;

exit 0;

Postgresql

Please make sure you have rsync and mailutils installed. Proposed script for backing up of databases on all postgreSQL VMs: This script is held in /opt/bin/pgsql_backup.sh

#!/bin/bash
# FILE DEPLOYED BY PUPPET DO NOT EDIT DIRECTLY

# Random sleep - Backups shouldn't start at the exact same time to prevent the KVM host from overloading
if [[ $* != *--nosleep* ]]
then
        sleep $(($RANDOM % 1800));
fi

logfile="/var/lib/db_backups/pgsql.log"
backup_dir="/var/lib/db_backups"
touch $logfile

dateinfo=`date '+%Y-%m-%d %H:%M:%S'`
echo "$dateinfo Start " >> $logfile
/usr/bin/vacuumdb -za >/dev/null 2>&1
dateinfo=`date '+%Y-%m-%d %H:%M:%S'`
echo "$dateinfo Vacuum complete for all databases"  >> $logfile

databases=`psql template1 -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | grep -v : | awk {'print $1'} | grep -v \|`

for i in $databases; do
        /usr/bin/pg_dump -F c $i -f $backup_dir/$i-backup.sql 2> /tmp/psqlbackup.log
	if [ $? -eq 0 ] ; then
		/bin/gzip -f $backup_dir/$i-backup.sql ; 
	else
		/bin/cat /tmp/psqlbackup.log >> $logfile
		/bin/cat /tmp/psqlbackup.log | /usr/bin/mail -s "PostgreSQL backup error on `/bin/hostname -f` for database $i "  itsupport@thehumanjourney.net;
		/bin/rm /tmp/psqlbackup.log ; 			
	fi ;
        dateinfo=`date '+%Y-%m-%d %H:%M:%S'`
        echo "$dateinfo Backup complete for database $i " >> $logfile
done
rsync -t /etc/postgresql/*/main/postgresql.conf $backup_dir
rsync -t /etc/postgresql/*/main/pg_hba.conf $backup_dir
dateinfo=`date '+%Y-%m-%d %H:%M:%S'`
echo "$dateinfo Done " >> $logfile
echo " " >> $logfile

The related puppet recipe:

# /etc/puppet/manifests/classes/sudo.pp

class postgres_backup {
    file { "/var/lib/db_backups/":
        owner => "postgres",
        group => "postgres",
        mode  => 770,
        ensure => directory,
    }

    file { "/opt/bin/":
        owner => "root",
        group => "root",
        mode  => 755,
        ensure => directory,
    }

    file { "/opt/bin/pgsql_backup.sh":
                owner => "postgres",
                group => "postgres",
                mode  => 755,
                source => "puppet://puppet.thehumanjourney.net/dist/apps/pgsql/postgresql_backup.sh",
    }

    file { "/etc/cron.d/database_backup":
                content         => "30 23 * * * postgres /opt/bin/pgsql_backup.sh",
                owner           => root,
                group           => root,
                mode            => 644,
    }
}

Here is the current script, taken directly from the puppet recipe:

    file { "/etc/cron.d/database_backup":
                content         => "30 23 * * * postgres /usr/bin/pg_dumpall > /var/lib/db_backups/database_dump.sql 2> /tmp/psqlbackup.log ; if [ $? -eq 0 ] ; then  /bin/gzip -f /var/lib/db_backups/database_dump.sql ; else  /bin/cat /tmp/psqlbackup.log | /usr/bin/mail -s \"Postgresql backup error on `/bin/hostname -f`\"  itsupport@thehumanjourney.net; /bin/rm /tmp/psqlbackup.log ; fi ;\n",
                owner           => root,
                group           => root,
		mode		=> 644,
    }

Manual procedure to prepare a new server

• create folder /opt/bin

sudo mkdir /opt/bin

• create folder /var/lib/db_backups

sudo mkdir /var/lib/db_backups

• set the owner to the user that will run the backup script

sudo chown postgres /var/lib/db_backups

• copy the mysql or postgresql code into a SH file, for example /opt/bin/pgsql_backup.sh or /opt/bin/mysql_backup.sh or simply /opt/bin/db_backup.sh
• set owner to the user that will run the backup script

sudo chown postgres /opt/bin/pgsql_backup.sh

• grant the user execute permissions on the script

sudo chmod u+x /opt/bin/pgsql_backup.sh

• add a cronjob to the desired user to run this script nightly

sudo crontab -u postgres -e

00 23 * * * /opt/bin/mysql_backup.sh

• install mailutils so the script can notify ITsupport when something fails

sudo apt-get install mailutils

• install rsync so the script can synch files to backup folder

sudo apt-get install rsync

• test to see if the script runs okay. Please bear in mind that the script starts with a sleep command that might take long to finish, so change that when testing.

sudo su postgres /opt/bin/pgsql_backup.sh