#!/bin/sh
# name: mysql_backup.sh
# description: mysql backup and upload
# version: v1.0.0
# author: Shi Zhuolin
# E-mail: shizhuolin@hotmail.com shizhuolin@qq.com
# ---------------------------------------------------------------------- 
# the directory for story your backup file. 
backup_dir="/home/mysql_bak"
# your mysql login information 
# db_user is mysql username 
# db_passwd is mysql password 
# db_host is mysql host 
# -----------------------------
db_host=""
db_user=""
db_passwd=""
# -----------------------------
# your ftp login information
# ftp_host is ftp server host address
# ftp_user is ftp server username
# ftp_passwd is ftp server password
# ftp_path is upload path for ftp root
ftp_host=""
ftp_user=""
ftp_passwd=""
ftp_path="/"
# -----------------------------
# mysql, mysqldump and some other bin's path
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
FTP="/usr/bin/ftp"
FTPCMD="$backup_dir/ftpcmd.txt"
FTPLOG="$backup_dir/upload.log"
# date format for backup file (yyyy-mm-dd-HH-nn-ss) 
STRDATE=$(date +"%Y-%m-%d")
STRTIME=$(date +"%H%M%S")
PREFIX="$STRDATE-$STRTIME-"

if [ "$ftp_host" != "" ]; then
    echo "open $ftp_host">$FTPCMD
    echo "user $ftp_user $ftp_passwd">>$FTPCMD
    echo "prompt off">>$FTPCMD
    echo "binary">>$FTPCMD
    echo "cd $ftp_path">>$FTPCMD
fi

# get all databases 
all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"
for db in $all_db; do
    if [ "$db" != "information_schema" -a "$db" != "performance_schema" ]; then
      echo "backup $db to $backup_dir/$PREFIX$db.gz"
      $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/$PREFIX$db.gz"
      if [ "$ftp_host" != "" ]; then
	  echo "put $backup_dir/$PREFIX$db.gz $PREFIX$db.gz">>$FTPCMD
      fi
    fi
done

if [ "$ftp_host" != "" ]; then
    echo "bye">>$FTPCMD
    $FTP -n<$FTPCMD>>$FTPLOG
fi

exit 0;