#!/bin/bash
#formbirds project
#mysql backup script
#relate define
PROJECT_NAME="117_querytooldb"
SCRIPT_NAME="mysql"
TODAY=`date +%Y%m%d`
#mysql
REMOTE_URL="192.168.2.117"
REMOTE_USER="root"
#REMOTE_USER="server"
REMOTE_DIR="/home/moma/backup_workspace"
#REMOTE_DIR="/home/server"
DATABASE_NAME="querytooldb"
MYSQL_NAME="root"
MYSQL_PASS="magicmoma"
#key
KEY_FILE="/home/moma/Backup2/ShellScript/keypairs/117ServerKey.pem"
#file
MYSQL_BACKUP_DATA_DIR="/home/moma/Backup2/Projects/crawler_data_backup/${PROJECT_NAME}/${SCRIPT_NAME}"
MYSQL_BACKUP_FILE_NAME="${PROJECT_NAME}_${SCRIPT_NAME}_${TODAY}.sql.gz"
MYSQL_BACKUP_LOG_DIR="${MYSQL_BACKUP_DATA_DIR}/log"
MYSQL_LOG_FILE_NAME="${MYSQL_BACKUP_LOG_DIR}/${PROJECT_NAME}_${SCRIPT_NAME}_${TODAY}.log"
## otherwise make the directory to store the newest backup
test ! -d "${MYSQL_BACKUP_LOG_DIR}" && sudo mkdir -p "${MYSQL_BACKUP_LOG_DIR}" && echo "info: mkdir ${MYSQL_BACKUP_LOG_DIR}"
## if the directory for storing backup does not exist and not writeable
test ! -w ${MYSQL_BACKUP_LOG_DIR} && sudo chmod +w ${MYSQL_BACKUP_LOG_DIR} && echo "info: chmod +w ${MYSQL_BACKUP_LOG_DIR}"
cd ${MYSQL_BACKUP_DATA_DIR}
echo "**************************************************"
echo "start backup mysql,position --------------`pwd`---------"
echo "**************************************************"
#log date
echo "${TODAY} backup ${PROJECT_NAME} " > ${MYSQL_LOG_FILE_NAME}
# create remote backup dir
#echo "ssh -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL} test ! -d \"${REMOTE_DIR}\" && sudo mkdir -p \"${REMOTE_DIR}\"" >> ${MYSQL_LOG_FILE_NAME}
ssh -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL} "test ! -d ${REMOTE_DIR} && mkdir -p ${REMOTE_DIR} && chmod 777 ${REMOTE_DIR} " # 2>> ${MYSQL_LOG_FILE_NAME}
## if the directory for storing backup does not exist and not writeable
ssh -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL} "chmod 777 ${REMOTE_DIR}"
#mysqldump -u $db_user -h $db_host -p$db_passwd $db | gzip -9 > "$backup_dir/$time.$db.gz"
echo "ssh -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL} mysqldump -u ${MYSQL_NAME} -p${MYSQL_PASS} ${DATABASE_NAME} | gzip -9 > ${REMOTE_DIR}/${MYSQL_BACKUP_FILE_NAME}" >>${MYSQL_LOG_FILE_NAME}
#start
ssh -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL} "mysqldump -u ${MYSQL_NAME} -p${MYSQL_PASS} ${DATABASE_NAME} | gzip -9 > ${REMOTE_DIR}/${MYSQL_BACKUP_FILE_NAME}" 2>> ${MYSQL_LOG_FILE_NAME}
#scp
echo "scp -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL}:${REMOTE_DIR}/${MYSQL_BACKUP_FILE_NAME} ." >> ${MYSQL_LOG_FILE_NAME}
scp -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL}:${REMOTE_DIR}/${MYSQL_BACKUP_FILE_NAME} . 2>> ${MYSQL_LOG_FILE_NAME}
#delete
echo "ssh -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL} rm -rf ${REMOTE_DIR}/${MYSQL_BACKUP_FILE_NAME}" >> ${MYSQL_LOG_FILE_NAME}
#
ssh -i ${KEY_FILE} ${REMOTE_USER}@${REMOTE_URL} "rm -rf ${REMOTE_DIR}/${MYSQL_BACKUP_FILE_NAME}" 2>> ${MYSQL_LOG_FILE_NAME}
echo "${MYSQL_BACKUP_DATA_DIR}" >> ${MYSQL_LOG_FILE_NAME}
#
echo "du -sh ${MYSQL_BACKUP_FILE_NAME}" >> ${MYSQL_LOG_FILE_NAME}
du -sh ${MYSQL_BACKUP_FILE_NAME} >> ${MYSQL_LOG_FILE_NAME}
#
echo **************************************************
echo mysql backup done!
echo **************************************************
#
echo please see log get more info in ${MYSQL_LOG_FILE_NAME}
mail -s "${PROJECT_NAME} ${SCRIPT_NAME} backup" shilei@magicmoma.com < ${MYSQL_LOG_FILE_NAME}
mail -s "${PROJECT_NAME} ${SCRIPT_NAME} backup" bailei@magicmoma.com < ${MYSQL_LOG_FILE_NAME}
mail -s "${PROJECT_NAME} ${SCRIPT_NAME} backup" chenyi@magicmoma.com < ${MYSQL_LOG_FILE_NAME}
mail -s "${PROJECT_NAME} ${SCRIPT_NAME} backup" tianrui@magicmoma.com < ${MYSQL_LOG_FILE_NAME}
#
exit