MySQl数据库备份脚本

#!/bin/bash
#====================================
#   mysql定时备份
#   mysql timed backup
#====================================

dump=/usr/bin/mysqldump
ipaddr=127.0.0.1
port=3306
user=typecho
passwd=569da4de2139687044bb
database=typecho
root_dir=/data


#----------------------
set -u
[ -x $dump ] || exit 100
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 '-5 days' "+%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} ||exit 200
[[ -n "${out_dir}" ]] && rm -rf ${out_dir}/* || echo "failed to delete buffer file" >> ${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 [ $? -eq 0 ];then
    echo -n "[$(date +%y-%m-%d\ %h:%m:%s)] the backup successful，" >> $log_dir_file
    
#压缩
    cd ${out_dir}
    tar -zcf ${tar_bak} ${date} &>/dev/null && echo -n "backup file packed successfully，" >> ${log_dir_file} || echo -n " packaging failure，" >> ${log_dir_file}
    mv ${out_dir}/${tar_bak} ${tar_dir} && echo -n "backup file packaging successfully moved to data directory successfully，" >> ${log_dir_file} || echo -n "move failure，" >> ${log_dir_file}

#删除之前的备份
    rm -rf ${tar_dir}/${bak_name} && echo "backup succeeded 5 days before deletion！" >> ${log_dir_file} || echo "backup failed 5 days before deletion。" >> ${log_dir_file}
else
    echo "[$(date +%y-%m-%d_%h:%m:%s)] backup failure!" >> ${log_dir_file}
fi

注：

如果备份报错
mysqldump: got error: 1449: "the user specified as a definer ('abc'@'%') does not exist" when using lock tables
因为mysqldump命令默认在导出时是要锁定表的，所以解决方式有两个。
1、创建相关用户

grant all on *.* to abc@'%' identified by '123456';

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

${dump} -h${ipaddr} -p${port} -u${user} -p${passwd} --databases --skip-lock-tables  ${database} > ${out_dir}/${date}/${database}-${date}.sql