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

db update scripts

-- move all text into msgcodes
-- insert into msgcodes (id,lang_id,msgtext)  select concat(id,"-event"),1,event from events;
-- insert into msgcodes (id,lang_id,msgtext)  select concat(id,"-title"),1,title from events;
-- alter table events drop title;
-- alter table events drop event;

-- added code field to languages to hold two letter language code
alter table languages drop column code;
alter table languages add column code char(2) after id;
update languages set code=lcase(substring(language FROM 1 FOR 2));
alter table msgcodes change id id varchar(32);
alter table languages drop column field;
alter table msgcodes add column field varchar(32) after lang_id;
alter table msgcodes drop column fkey;
alter table msgcodes add column fkey int(10) unsigned after lang_id;
alter table msgcodes ENGINE = InnoDB;
alter table events ENGINE = InnoDB;
update msgcodes set fkey=SUBSTRING_INDEX(id,"-",1) where id  REGEXP '^[0-9]+-';
update msgcodes set field=SUBSTRING_INDEX(id,"-",-1) where id  REGEXP '^[0-9]+-';
alter table msgcodes add constraint deletecodes foreign key (fkey) references events (id) on delete cascade;

~/webs/db-update.sql::– move all text into msgcodes

--
My Emacs Files At GitHub

Drop down menus live from mySQL using CSS

I was preparing to entire the hazy horrible world of Java script to provide
event links from the navigation toolbar. And then! Bing! Why not use CSS? And it
works. Super. Click on the annoying advert below to take a gander …

--
My Emacs Files At GitHub