Here's a little script for the google cache and email archive engines to
store in case anyone ever needs this.
We have several servers (dev/test/prod) and replication setup too.
Sometimes we need to restore one server from another, however we have
different mysql user accounts setup on each for various web/scripts that
need varying levels of security.
This will cycle through each server and do a schema and data dump and
create a tarball for you. Useful for a weekly or monthly or even a
one-off.
Use as you like:
________________________________________________________________________
#!/bin/bash
# written by Daevid Vincent on 01/22/09
# This will harvest all the 'mysql' tables schema and data from each
$MYSQLRDBMS
# make sure we're running as root
if (( `/usr/bin/id -u` != 0 )); then { echo -e "\e[00;31mSorry, must be
root. Exiting...\e[00m"; exit; } fi
USERNAME=mysql_user_on_all_servers
PASSWORD=mysql_user_password_on_all_servers
# .01 Dev Master
# .02 Dev Slave
# .03 Test Master
# .04 Test Slave
# .05 Prod Master
# .06 Prod Slave
MYSQLRDBMS="01 02 03 04 05 06"
MYSQLDUMPOPTS="--skip-opt --add-drop-table --add-drop-database
--add-locks --create-options --complete-insert --quote-names
--disable-keys --extended-insert --quick --set-charset --comments"
BACKUPDIR="/tmp"
echo
set $(date)
for ip in $MYSQLRDBMS; do
HOST=10.10.10.$ip
CONNECTION="-u $USERNAME -p$PASSWORD -h$HOST"
echo -e "\e[00;31m[DEBUG] `date`\e[00m"
echo -e "\e[01;37m[DEBUG] mysql $CONNECTION\e[00m"
echo -e "\e[01;32mHarvesting 'mysql' database on $HOST\e[00m"
mysqldump $CONNECTION $MYSQLDUMPOPTS --databases mysql >
"/tmp/$6-$2-$3_mysql_$HOST.sql"
echo -e "\e[00;31m[DEBUG] `date`\e[00m"
echo
done #hosts
echo -e "\e[01;32mTarballing mysqldump of ALL databases to
$BACKUPDIR/$6-$2-$3_mysql_db.tgz\e[00m"
/bin/tar czvpf
"$BACKUPDIR/$6-$2-$3_mysql_db.tgz" /tmp/$6-$2-$3_mysql_*.sql
rm -f /tmp/$6-$2-$3_mysql_*.sql
echo -e "\e[00;31m[DEBUG] `date`\e[00m"
echo -e "\e[01;33mCompleted.\e[00m"
