SystemAdministration/Backups

MySQL Backups

The primary MySql database for each slash server is now backed up to oxygen via a script that runs from a crontab job on neon, lithium, and nitrogen, to back up production, dev, and staff respectively. The jobs are run by the user slash from the /srv/soylentnews.org directory. The scripts are similar for each, but production is run daily, while dev and staff run weekly. A local copy is generated, gziped, then stored in /srv/soylentnews.org/db_dumps. Old copies are kept for two weeks and deleted by the script.

Files are permanently stored in /home/backups/db_dumps on oxygen. These files are not automatically deleted so there may be a need in the future to delete the old files.

Here is the mysql_backup.sh script for neon:

#! /bin/bash
HOME=/srv/soylentnews.org

backup_dir=$HOME/db_dumps
today=`date +%F`
week=`date --date="-14 days" +%F`

if [ -e $backup_dir/soylentnews-$week.sql.gz ]
  then
    rm -rf $backup_dir/soylentnews-$week.sql.gz
fi

/opt/mysql-5.6.17-linux-glibc2.5-x86_64/bin/mysqldump -h 127.0.0.1 --single-transaction soylentnews > $backup_dir/soylentnews-$today.sql

gzip $backup_dir/soylentnews-$today.sql

kinit -k -t $HOME/slash.keytab slash

scp $backup_dir/soylentnews-$today.sql.gz oxygen:/home/backups/db_dumps/soylentnews-$today.sql.gz

Here is the assoicated crontab entry:

4 5 * * * /srv/soylentnews.org/mysql_backup.sh

For now production mysqldump takes only a few seconds and the transfer to oxygen takes actually more time. Current dump size is approximately 250Mbi uncompressed and 40Mbi compressed for the production database.