Pages

Sunday, August 14, 2011

Backup all mysql databases for project infrastructure

Frequently I need to back up all the databases on the server and restore locally on the development machine for testing/backup. These scripts (on the bash $PATH) do just that. Sorry about the indentation but I dont have the inclination to fight with bloggers defaults today....

(1) backup-web-databases



#!/bin/bash
for dir in "${1:-$HOME/webs}"/*/
do
if [[ $(basename "$dir") != "template" ]]
then
cd ${dir}
if [ -e "db" ]
then
echo "Backing up database in ${dir}"
backup-database
fi
fi
done
cd ~/webs


(2) backup-database : this parses an authinfo file in the format "user password dbinstance host"


#!/bin/bash
[ -a db/backups ] || mkdir db/backups
read -r uid pwd dbname rest < <(head -1 db/authinfo)
mysqldump --skip-lock-tables -u${uid} -p${pwd} --database ${dbname} > db/backups/db-backup.sql


(3) restore-web-databases



#!/bin/bash
cd ~/webs
for dir in "${1:-$HOME/webs}"/*/
do
if [ "$(basename "$dir")" != "template" ]
then
cd ${dir}
if [ -e "db" ]
then
restore-database
fi
fi
done


(4) restore-database


#!/bin/bash
[ -a db/backups ] || mkdir db/backups
read -r uid pwd dbname rest < <(head -1 db/authinfo)
if [ -e "db/backups/db-backup.sql" ]
then
echo "Restoring DB for `pwd`"
mysql -u ${uid} -p${pwd} < db/backups/db-backup.sql
else
echo "No DB back found in `pwd`"
fi

No comments:

Post a Comment