added script for mysql backup

script for mysql backup:
       backup all mysql databases in single file bzip2 file
       move all backups to ftp
       logs size of all backups if wished

1 files changed, 80 insertions, 0 deletions

diff --git a/mysql/mysql_backup.sh b/mysql/mysql_backup.sh
new file mode 100755
index 0000000..675df3b
--- /dev/null
+++ b/mysql/mysql_backup.sh
@@ -0,0 +1,80 @@
+#!/bin/bash
+# this script will backup all mysql databases from the server to a ftp
+# server
+
+# mysql server informations
+MYSQL_SERVER="localhost"
+MYSQL_USER="backup"
+MYSQL_PASSWORD="password"
+
+# databases to exclude, seperated by space
+# TODO
+#MYSQL_EXCLUDE_DB=""
+
+# ftp server data
+FTP_SERVER="backup"
+FTP_USER="ftp"
+FTP_PASSWORD="password"
+
+# target directory on the ftp (if no / at the beginning, relativ to
+# the first directory after login)
+FTP_TARGET="mysql"
+
+# temporary directory
+TMP_DIRECTORY="/tmp"
+
+# backup name
+BACKUP_PREFIX="$(hostname -f)_${MYSQL_SERVER}_$(date +%F)_"
+
+# logging
+LOGGING="0"
+
+## end of configuration
+
+log() {
+    if [ $LOGGING -eq 0 ]; then
+	echo "$@"
+    fi 
+}
+
+# check for tools
+for prog in mysqlshow mysqldump ncftpput
+do 
+    if ! hash $prog 2>/dev/null ; then
+	echo "Error: This script needs: $prog. Exiting..." >&2
+	exit 1
+    fi
+done
+
+echo "Started backup of mysql databases ($(date +%c)):" | log
+echo | log
+
+# create temporary dir
+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')
+
+# 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}.sql.bz2"
+done
+
+# echo created log
+du -sch * | log
+
+# move backups to ftp server
+for file in *
+do
+    ncftpput -u${FTP_USER} -p${FTP_PASSWORD} -DD ${FTP_SERVER} \
+	${FTP_TARGET} ${file} >/dev/null
+done
+
+# restore old cwd
+popd >/dev/null 2>&1
+rmdir ${TMP_DIR}