shell脚本自动备份数据库

最新推荐文章于 2025-09-06 22:56:13 发布

weixin_34117522

最新推荐文章于 2025-09-06 22:56:13 发布

阅读量120

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 shell 运维

原文链接：https://my.oschina.net/marhal/blog/1538873

本文提供了一个用于MySQL数据库备份的shell脚本示例。该脚本实现了数据库的远程备份及文件的传输过程，并记录了操作日志，同时通过邮件通知相关人员备份结果。

为什么80%的码农都做不了架构师？>>>

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

转载于:https://my.oschina.net/marhal/blog/1538873