欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQl数据库备份脚本

程序员文章站 2022-10-08 20:12:12
``` !/bin/bash ==================================== MySQL定时备份 MySQL timed backup ==================================== DUMP=/usr/bin/mysqldump IPADDR=1 ......
#!/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