I am not a sophisticated man and my idea of elegance is hitting things with hammers. Bear that in mind when you look over the below code, which is here more for posterity than for any public edification.
One of the backup strategies we have is a complete table level dump and subsequent encryption of those files for storage in S3. Sure, it’s not a scalable solution as we discovered when the first iteration of the script did a complete dump of the db rather than by tables. This version runs them in parallel for the dump and works well enough for me not to sweat the timing at this point (~20 minutes from start to finish).
#!/bin/bash
# set variables
DAYNOW=$(date +%j)
TIMENOW=$(date +%H%M)
RPTIME=$(date +%x-%H:%M)
ME=$(hostname)
# stop the slave
mysql -utears -plamentations -e 'slave stop;'
# create directories
mkdir -p /mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW
function r {
for J in $(cat $1) ; do
mysqldump -utears -plamentations sorrowdb --tables $J | gzip > "/mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW/$J.sql.gz"
done
exit
}
mysql -u tears -plamentations -e 'show tables from sorrowdb' -s --skip-column-names | sort -R | split -d -l 5
for I in $(find x*) ; do
(r $I &)
done
sleep 15s
while [ "$(pgrep -c -f mysqldump)" -gt "0" ]
do
sleep 1s
done
#start slave
mysql -utears -plamentations -e 'slave start;'
cd /mnt/tmp/backup
gpg-zip --encrypt -r yourmom --output $ME-$DAYNOW-$TIMENOW.gpg $ME-$DAYNOW-$TIMENOW
# copy to s3
BACKUP=$(s3cmd --acl-private put /mnt/tmp/backup/$ME-$DAYNOW-$TIMENOW.gpg s3://sorrowdb-db-backups/ | sed \
-e 's/File //g' \
-e 's/\/mnt\/tmp\/backup\///g' \
-e 's/s3:\/\/sorrowdb-db-backups\/'"$HOSTNAME"'-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9].tgz.gpg//g' \
-e 's/\[1 of 1\]//g' \
-e 's/(//g' \
-e 's/)//g' | awk '{ print $1 " " $2 " " $3 " " $5/1048576 "MB" }')
echo $BACKUP | mutt yourmom@sorrowdb.com -s "$ME - $DAYNOW $TIMENOW"
# clean up
rm -r /mnt/tmp/backup/
Now just like the World’s Most Interesting Man, I often only test things after it’s shipped. In this case I’ve tested that I can decrypt the package and see that all the files are there but I never took the time to actually do an import. Today is that day.
Version 0.01-ALPHA-WTFISTHISSHIT-b of the script just handles parallel imports of the tables from the dump file. As a bonus shoddiness, you need to run it in the directory where the files are sitting.
#!/bin/bash
function r {
for J in $(cat $1) ; do
gunzip < $J | mysql -utears -plamentations sorrowdb
done
exit
}
ls $1 | sort -R | split -d -l 18
for I in $(find x*) ; do
(r $I &)
done
Ugly but it works. Feel free to laugh, I always do when I look at my work.
Comments
James, Dale
james, Mike
james, Mike, james [...]
james, Mike
james, Mike
james, Kyle Daigle