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
上一篇: django视图 CBV 和 FBV
下一篇: Python之文字转图片方法
推荐阅读