docker-mysql备份脚本

#/bin/bash
#脚本写于2021/3/29
#mysql备份，只适用于docker-mysql备份，并且docker只有安装1个mysql ，多个mysql没有测试过
#mysql5.7和mysql8都适用
#自动删除功能还没写

#mysql密码
passwd=123456
#mysql用户
user=root
#用来统计一共备份多少个
count=0
#host=localhost   默认当前主机
#备份目录
backup_dir=/home/mysqlbak
Date=`date +%F`


#判断有没有安装docker
docker -v  &>/dev/null	
if [ $? -eq 1  ];then
	echo "请您先安装docker和mysql"
	ssh root@192.168.1.95
	exit
fi

#判断有没有安装mysql
docker ps | grep mysql &>/dev/null
if  [ $? -eq 1 ];then
	echo "请您先安装mysql"
	ssh root@192.168.1.95
	exit
fi

#判断目录存不存在，不存在就创建目录
if [ ! -d $backup_dir/$Date ];then
	mkdir -p $backup_dir/$Date
	echo "备份目录$backup_dir/$Date已创建,准备备份数据库"
fi

#docker_id 是为了截取mysql容器的id
docker_id=`docker ps |grep mysql| awk '{print $1}'`  

#抓取数据库，然后对文本进行修改
touch $backup_dir/$Date/data{1..3}.txt
docker exec -it $docker_id mysql -u${user} -p${passwd} -e "show databases" > $backup_dir/$Date/data1.txt
awk '{$NF="";print}' $backup_dir/$Date/data1.txt > $backup_dir/$Date/data2.txt
awk '{$1="";print}' $backup_dir/$Date/data2.txt > $backup_dir/$Date/data3.txt
sed -i '1,3d' $backup_dir/$Date/data3.txt 
sed -i 's/^[ \t]*//g' $backup_dir/$Date/data3.txt


############以下是排除不需要备份的数据库功能##########################

	echo "~~~~~~~~~~~~~~请选择不需要备份的数据库~~~~~~~~~~~~~~~~"

        cat $backup_dir/$Date/data3.txt

    echo "~~~~~~~~~~~~~~请选择不需要备份的数据库~~~~~~~~~~~~~~~~"



	
read -p "请输入不用备份的数据库[最多输入20个,回车跳过这一步]: " ex1 ex2 ex3 ex4 ex5 ex6 ex7 ex8 ex9 ex0 ex11 ex12 ex13 ex14 ex15 ex16 ex17 ex18 ex19 ex20

	echo -e  "$ex1\n$ex2\n$ex3\n$ex4\n$ex5\n$ex6\n$ex7\n$ex8\n$ex9\n$ex0\n$ex11\n$ex12\n$ex13\n$ex14\n$ex15\n$ex16\n$ex17\n$ex18\n$ex19\n$ex20" > $backup_dir/$Date/del_all.txt
	touch $backup_dir/$Date/del.txt

	for j in `cat $backup_dir/$Date/del_all.txt`
	do
			grep -w $j $backup_dir/$Date/data3.txt  &>/dev/null
			if [ $? -eq 0 ]; then
					echo $j >> $backup_dir/$Date/del.txt
			else
					echo -e "\e[1;33m$j 数据库不存在或输入错误,重输请按[n]\e[0m"
			fi
	done

	echo  "您确定不备份以下数据库 [如果显示为空，按y备份全部数据库]"

	cat $backup_dir/$Date/del.txt

	read -p "请输入[y/n]: " select2

	case $select2 in
	y)
			awk '{print $0}' $backup_dir/$Date/data3.txt $backup_dir/$Date/del.txt |sort|uniq -u > $backup_dir/$Date/data4.txt
			;;
	n)
			echo "">$backup_dir/$Date/del.txt
			echo "请重来"
			exit
			;;
	*)    	
			echo "">$backup_dir/$Date/del.txt		
			echo "请按指示输入"
	        	  exit
			;;
	esac
		
	################################################



for i in `cat $backup_dir/$Date/data4.txt ` 
do		
	echo "正在备份${i}...................................."
	count=$[$count+1]
	docker exec -it $docker_id mysqldump -u${user} -p${passwd}  ${i} > ${backup_dir}/$Date/${i}.sql 
	#下面这一个是无奈之举，因为备份数据库的时候密码写在命令上
	#因此会输出mysql mysqldump [Warning] Using a password on the command line interface can be insecure. 在备份文件的第一行
	#把他删掉才能正常导入,目前我试过网上的方式，没有解决。
	sed -i '1d' ${backup_dir}/$Date/${i}.sql
	echo "已备份${i}.sql" |tee -a ${backup_dir}/${Date}bak.log
done

echo "已备份$count个数据库"

wait

if [ $count -eq 0 ] ;then
	echo "备份出错.."
	rm -rf ${backup_dir}*
	exit
fi

#进入备份路径打包
rm -f $backup_dir/*.txt 

cd $backup_dir  

echo "正在打包.....不能中断....."

trap "" INT HUP

tar -zcf mysqlbak${Date}.tar.gz  $Date

echo "finish......"

rm -rf $backup_dir/$Date