1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
| #!/bin/bash
# mysql_backup.sh: backup mysql databases and keep newest 5 days backup.
#
# ${db_user} is mysql username
# ${db_password} is mysql password
# ${db_host} is mysql host
# —————————–
#/root/mysql_backup.sh
# everyday 3:00 AM execute database backup
# 0 3 * * * /root/mysql_backup.sh
#/etc/cron.daily
# date format for backup file (dd-mm-yyyy)
time="$(date +"%Y-%m-%d")"
db_user="root"
db_password="123456"
db_host="118.190.244.203"
# 备份的文件夹 末尾不加/
backup_dir="/home/backup/mysql"
# mysql 与 mysqldump 环境
# mysql="/usr/local/mysql-5.5.33/bin/mysql"
# mysqldump="/usr/local/mysql-5.5.33/bin/mysqldump"
# mysql="docker exec -i mysql57 mysql"
# mysqldump="docker exec -i mysql57 mysqldump"
mysql="/usr/bin/mysql"
mysqldump="/usr/bin/mysqldump"
# 要备份的数据库名
# all_db="$(${mysql} -u ${db_user} -h ${db_host} -p${db_password} -Bse 'show databases')"
all_db="ems_new
emsdb"
# 要保留的备份天数
backup_day=7
#数据库备份日志文件存储的路径
logfile="/var/log/mysql_backup"${time}".log"
ip=118.190.244.203
#
ssh_port=1204
#
id_rsa=/root/auth_key/id_rsa_153.141.rsa
#
id_rsa_user=rsync
#
clientPath="/home/backup/mysql"
#
serverPath=${backup_dir}
#
web_ip="118.190.244.203"
#
gotifyServer="http://gotify.shelli.site"
#
gotifyToken="A5tYxPO_F9Nouu5"
# the directory for story the newest backup
test ! -d ${backup_dir} && mkdir -p ${backup_dir}
log()
{
echo "[ INFO ] ["$(date +'%Y-%m-%d %T')"] "$1 >>${logfile}
}
#备份数据库函数
mysql_backup()
{
# 取所有的数据库名 #
for db in ${all_db}
do
backname=${db}.${time}
dumpfile=${backup_dir}"/"${backname}
#将备份的时间、数据库名存入日志
log "database "${db}" backup"
${mysqldump} -F -u${db_user} -h${db_host} -p${db_password} ${db} > ${dumpfile}.sql 2>>${logfile} 2>&1
#开始将压缩数据日志写入log
log "zip ${dumpfile}.sql"
#将备份数据库文件库压成ZIP文件，并删除先前的SQL文件. #
tar -czvf ${backname}.tar.gz ${backname}.sql 2>&1 && rm ${dumpfile}.sql 2>>${logfile} 2>&1
#将压缩后的文件名存入日志。
log "backup file name:"${dumpfile}".tar.gz"
log "Ending database "${db}" backup"
done
}
delete_old_backup()
{
log "delete backup file:"
# 删除旧的备份 查找出当前目录下七天前生成的文件，并将之删除
find ${backup_dir} -type f -mtime +${backup_day} | tee delete_list.log | xargs rm -rf
cat delete_list.log >>${logfile}
}
rsync_mysql_backup()
{
# rsync 同步到其他Server中 #
for j in ${web_ip}
do
log "mysql_backup_rsync to ${j} begin "
### 同步 ###
rsync -avz --progress --delete $serverPath -e "ssh -p "${ssh_port}" -i "${id_rsa} ${id_rsa_user}@${j}:$clientPath >>${logfile} 2>&1
log "mysql_backup_rsync to ${j} done"
done
}
push()
{
message=$2
curl -X POST \
-H "Content-Type:application/json" \
--data-binary "{\"title\":\"$1(${ip})\",\"message\":\"${message}\",\"priority\":1,\"extras\":{\"client::display\":{\"contentType\":\"text/markdown\"}}}" \
"${gotifyServer}/message?token=${gotifyToken}"
}
#进入数据库备份文件目录
cd ${backup_dir}
mysql_backup && delete_old_backup 2> "error"${time}".log"
# rsync_mysql_backup
if [ "$?" -ne "0" ]; then
push "任务执行失败" "**于${time}备份失败。\n**\`\`\`\n日志:${backup_dir}/error"${time}".log\n\`\`\`"
exit 1
fi
log "mysql backup && rsync done!"
echo -e "\n" >>${logfile}
cat ${logfile}
push "任务执行成功" "**于${time}备份成功。**\n\`\`\`\n日志:${logfile}\n备份文件夹:${backup_dir}\n\`\`\`"
exit 0
|