mysql数据库备份脚本

#!/bin/bash

#要使用root账户来备份

BACKUP_DIR="/workspace/backup"

backup_time=`date  '+%Y%m%d%H%M'`

log=${BACKUP_DIR}/backup.log

MYSQL_HOST="localhost"

MYSQL_USER=root

MYSQL_PW='root'

echo "###################################">>$log

date  '+%Y-%m-%d %H:%M' >>$log

/workspace/mysql/bin/mysqldump -h ${MYSQL_HOST} -u${MYSQL_USER} --single-transaction --routines --triggers --events  -p${MYSQL_PW} -A >${BACKUP_DIR}/${backup_time}.sql

if [ $? -ne 0 ]; then

echo "backup databases failed !" >>$log

exit 1

else

date  '+%Y-%m-%d %H:%M' >>$log

echo "backup databases successful ." >>$log

fi

cd ${BACKUP_DIR} && tar zcf ${BACKUP_DIR}/${backup_time}.tar.gz  ${BACKUP_DIR}/${backup_time}.sql && rm -rf ${BACKUP_DIR}/${backup_time}.sql

find ${BACKUP_DIR} -name "*.tar.gz" -mtime +14 -exec rm -rf {} \;

echo "###################################">>$log