MySQl数据库备份脚本.doc

PAGE

PAGE1

MySQl数据库备份脚本

#!/bin/bash

#====================================

#MySQL定时备份

#MySQLtimedbackup

#====================================

DUMP=/usr/bin/mysqldump

IPADDR=

PORT=3306

USER=typecho

PASSWD=569da4de2139687044bb

DATABASE=typecho

ROOT_DIR=/data

#

set-u

[-x$DUMP]||exit100

OUT_DIR=${ROOT_DIR}/mysql/mysql-new

TAR_DIR=${ROOT_DIR}/mysql/mysql-bak-list

LOG_DIR=${ROOT_DIR}/mysql/mysql-log

LOG_DIR_file=$LOG_DIR/mysql-log

DATE=`date+%Y-%m-%d`

TAR_BAK=${DATABASE}-bak-$DATE.tar.gz

BAK_NAME=${DATABASE}-bak-$(date-d#39;-5days#39;+%Y-%m-%d).tar.gz

[-d$OUT_DIR]||mkdir-p$OUT_DIR

[-d$TAR_DIR]||mkdir-p$TAR_DIR

[-d$LOG_DIR]||mkdir-p$LOG_DIR

cd${OUT_DIR}||exit200

[[-n${OUT_DIR}]]rm-rf${OUT_DIR}/*||echoFailedtodeletebufferfile${LOG_DIR_file}

mkdir-p${OUT_DIR}/${DATE}

${DUMP}-h${IPADDR}-P${PORT}-u${USER}-p${PASSWD}--databases${DATABASE}|gzip${OUT_DIR}/${DATE}/${DATABASE}-${DATE}.sql.gz

if[$?-eq0];then

echo-n[$(date+%Y-%m-%d\%H:%M:%S)]Thebackupsuccessful，$LOG_DIR_file

#压缩

cd${OUT_DIR}

tar-zcf${TAR_BAK}${DATE}/dev/nullecho-nBackupfilepackedsuccessfully，${LOG_DIR_file}||echo-nPackagingfailure，${LOG_DIR_file}

mv${OUT_DIR}/${TAR_BAK}${TAR_DIR}echo-nBackupfilepackagingsuccessfullymovedtodatadirectorysuccessfully，${LOG_DIR_file}||echo-nMovefailure，${LOG_DIR_file}

#删除之前的备份

rm-rf${TAR_DIR}/${BAK_NAME}echoBackupsucceeded5daysbeforedeletion！${LOG_DIR_file}||echoBackupfailed5daysbeforedeletion。${LOG_DIR_file}

else

echo[$(date+%Y-%m-%d_%H:%M:%S)]Backupfailure!${LOG_DIR_file}

fi

注：

如果备份报错

mysqldump:Goterror:1449:Theuserspecifiedasadefiner(abc@%)doesnotexistwhenusingLOCKTABLES

因为mysqldump命令默认在导出时是要锁定表的，所以解决方式有两个。

1、创建相关用户

grantallon*.*toabc@#39;%#39;identifiedby#39;123456#39;;

2、在命令中加上--skip-lock-tables这个参数

${DUMP}-h${IPADDR}-P${PORT}-u${USER}-p${PASSWD}--databases--skip-lock-tables${DATABASE}${