linux 脚本备份数据库,shell脚本自动备份数据库

#!/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

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