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