path: root/mysql/mysql_backup.sh

#!/bin/bash
# this script will backup all mysql databases from the server to a ftp
# server

source $(dirname $0)/ftp.settings

# mysql server informations
MYSQL_SERVER="localhost"
MYSQL_USER="backup"
MYSQL_PASSWORD="password"

# databases to exclude, seperated by space
# TODO
#MYSQL_EXCLUDE_DB=""

# temporary directory
TMP_DIRECTORY="/tmp"

# backup name
BACKUP_PREFIX="$(hostname -f)_${MYSQL_SERVER}_$(date +%F)"

# logging (0 = enabled, all other = disabled)
LOGGING="0"

## end of configuration

# load common stuff
BACKUP_ROOT="$(dirname $0)/.."
source ${BACKUP_ROOT}/common/init.sh

# check for tools
_check_tools mysqlshow mysqldump ncftpput

echo "Started backup of mysql databases ($(date +%c)):" | _log
echo | _log

# flush logs on master
# get master
MYSQL_MASTER=$(mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
    -p${MYSQL_PASSWORD} <<< "SHOW SLAVE STATUS\G" | \
    grep "^ *Master_Host:" | awk '{ print $2 }')

echo "flush logs;" | mysql -h${MYSQL_MASTER} -u${MYSQL_USER} \
    -p${MYSQL_PASSWORD}

# pause the replication to be able to do consistent backups
mysqladmin -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
    stop-slave 2>&1 >/dev/null

# create temporary dir
mkdir -p ${TMP_DIRECTORY}
TMP_DIR=$(mktemp -d --tmpdir=${TMP_DIRECTORY})
pushd ${TMP_DIR} >/dev/null 2>&1

# enumerate all mysql databases
MYSQL_DATABASES=$(mysqlshow -h${MYSQL_SERVER} -u${MYSQL_USER} \
    -p${MYSQL_PASSWORD} | awk '{ print $2 }' | grep -v "^$" | sed \
    '1d')

# get binlog position
MYSQL_BINLOG="-"$(mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
    -p${MYSQL_PASSWORD} <<< "SHOW SLAVE STATUS\G" | \
    grep "^ *Master_Log_File:" | awk '{ print $2 }')"-"$( \
    mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
    -p${MYSQL_PASSWORD} <<< "SHOW SLAVE STATUS\G" | \
    grep "^ *Read_Master_Log_Pos:" | awk '{ print $2 }')

# backup all databases to file
for db in ${MYSQL_DATABASES}
do
    mysqldump -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
	${db} | bzip2 -zc > "${BACKUP_PREFIX}-${db}${MYSQL_BINLOG}.sql.bz2"
done

# restart the replication
mysqladmin -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
    start-slave 2>&1 >/dev/null

# echo created log of file sizes
du -sch * | _log
echo | _log

# move backups to ftp server
for file in *
do
    ncftpput -u${FTP_USER} -p${FTP_PASSWORD} -V -DD ${FTP_SERVER} \
	${FTP_TARGET} ${file} >/dev/null
done

echo "Finished backup of mysql databases ($(date +%c))" | _log

# restore old cwd
popd >/dev/null 2>&1
rmdir ${TMP_DIR}