生产mysqldump备份脚本

一、功能

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 $*