一、功能
1. 记录日志功能;
2. 传参的情况可以实现单库备份;
3. 无传参实现全库备份;
二、需要优化的点:
1. 会警告命令行输入密码不安全,mysql 5.5以上的版本;
2. mysqldump是冷备会造成数据不一致,备份时候可以指定锁全表备份;
3. 在循环备份到最后一个库的时候可以加 --flush-logs 参数,日志滚动;
三、注意的点:
1. 备份尽量别用root,因为不安全,可以授权一个账号进行备份;
四、脚本内容如下:
#!/bin/bash
#########################
# Author: xiaofei #
# Date: 2020/07/17 #
#########################
# date format
year=`date +%Y`
month=`date +%m`
day=`date +%d`
date_format=`date "+%Y-%m-%d %H:%M:%S"`
date_backup_format=`date "+%Y_%m_%d_%H%M%S"`
backup_dir="/home/data/${year}/${month}/${day}"
INTERVAL=2
# SHOW LOG
# 1:The log is displayed on the screen
# 0:close
SHOW_LOG=1
log_file="/tmp/mysql_backup.log"
LOG(){
if [[ "${SHOW_LOG}" -eq 0 ]];then
echo "${date_format} [$USER]# $*" >> ${log_file}
fi
if [[ "${SHOW_LOG}" -eq 1 ]];then
echo "${date_format} [${USER}]# $*"|tee -a ${log_file}
fi
}
# create backup dir
[[ ! -d "${backup_dir}" ]]&&mkdir -p ${backup_dir}
# mysql infomation
my_user='root'
my_pwd='123456'
my_port='3306'
my_sock='/tmp/mysql.sock'
my_cmd="mysqldump -u${my_user} -p"${my_pwd}" -S ${my_sock} --events --routines --single-transaction -B"
check_mysqld(){
[[ ! -e "${my_sock}" ]]&&awk 'BEGIN{print "mysql service not running"}'
}
backup(){
echo "Backup begins..."
sleep $INTERVAL
all_db=(`mysql -u${my_user} -p${my_pwd} -e "show databases;"|awk 'NR>2{print $1}'|grep -E 'schema|sys' -v`)
if [ "$#" -eq 0 ];then
for dbname in `echo ${all_db[*]}`;do
LOG "(${my_cmd} ${dbname}|gzip >${backup_dir}/${date_backup_format}_${dbname}.gzip) 2>/dev/null"
(${my_cmd} ${dbname}|gzip >${backup_dir}/${date_backup_format}_${dbname}.gzip) 2>/dev/null
done
else if [ `echo "${all_db[@]}"|grep -w ${1}|wc -l` -eq 0 ];then
echo "database ${1} not exists"
exit 1
else
LOG "(${my_cmd} ${1}|gzip >${backup_dir}/${date_backup_format}_${1}.gzip) 2>/dev/null"
(${my_cmd} ${1}|gzip >${backup_dir}/${date_backup_format}_${1}.gzip) 2>/dev/null
fi
fi
LOG "[ ********** Backup complete *********** ]"
echo -e "\n" >>${log_file}
}
main(){
check_mysqld
backup $*
}
main $*