fast mysql backups (and fast restore instructions)

fast_mysql_bak.sh

#!/bin/bash

### WARNING!!!!!   WARNING!!!!!   WARNING!!!!!
#
# this script should *not* be used as is without understanding everything it does (and possibly modifying):
#  1. it assumes you want to backup all your dbs on the source machine
#  2. the restore instructions are also for restoring all dbs in the tar file
#  3. there are hardcoded paths to various utilities
#
# i'm not responsible for your lost data
#
### WARNING!!!!!   WARNING!!!!!   WARNING!!!!!


# to rebuild a database from one of these tarballs:
#  mysql -u root -BNe "show databases" | egrep -ve "^information_schema|^mysql|^performance_schema" > dbs_id_like_to_kill
# edit dbs_id_like_to_kill and then run (i'm not responsible for you losing all your data 'cause you left in a db from this file...):
#  for db in $(cat dbs_id_like_to_kill); do mysql -u root -e "drop database $db"; done 
# stop mysql
# delete leftover files
#  for db in $(cat dbs_id_like_to_kill); do sudo rm -rf /var/lib/mysql/$db; done
# restart mysql
# verify innodb is working
#  mysql -u root -e "show engines;" | grep -i innodb
# from the untarred directory:
# create the databases
#  mysql -u root < alldbs.sql
# create the tables and import the data
#  for db in $(find . -type d -exec basename {} \; | grep -v "\."); do for sql in $db/*.sql; do cat $sql | mysql -u root $db; done; \
#    for data in $(pwd)/$db/*.txt; do table=$(basename $data .txt); echo "working on $db.$table"; \
#      echo "LOAD DATA LOCAL INFILE '$data' INTO TABLE \`$table\` FIELDS ENCLOSED BY '\"';" | mysql -u root --local-infile $db; \
#    done; done;

destfile=$1
workdir=$2
: ${destfile:="dbs"}
: ${workdir:="/tmp"}

tmpdir="$workdir/$destfile/"

tarball="$workdir/$destfile.tgz"
completed_tarball="/var/dbbackups/$destfile.tgz"

mysql="/usr/bin/mysql -u root"
mysqldump="/usr/bin/mysqldump -u root"
tar="/bin/tar"
egrep="/bin/egrep"

# make a directory that the mysqld process can write to
mkdir -m 777 -p $tmpdir

# iterate over all databases we care about
dbs=$($mysql --skip-column-names --silent --execute="show databases" |\
        $egrep --invert-match --regexp="^information_schema$|^mysql$|^performance_schema$")

# get database creation sql
$mysqldump --no-data --no-create-info --quick --opt --databases $dbs > $tmpdir/alldbs.sql

for db in $dbs; do
    echo "[`date`] dumping $db..."
    dbworkdir="$tmpdir/$db/"
    mkdir -m 777 -p $dbworkdir
    $mysqldump --tab=$dbworkdir --fields-enclosed-by=\" \
        --quick --opt --master-data=2 --single-transaction $db > $dbworkdir/master-data.sql
done

echo "[`date`] creating $tarball"

# create a tarball
cd $workdir
$tar czf $tarball $destfile

# move into place once done
mv $tarball $completed_tarball

echo "[`date`] done, just cleaning up"

rm -rf $tmpdir