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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
| # mysql_k8s-prod_bak-all-databases.sh
#!/bin/bash
#
#
day=`date +'%Y-%m-%d'`
anynowtime="date +'%Y-%m-%d %H:%M:%S'"
NOW="echo [\`$anynowtime\`][PID:$$]"
#
#function job_start
job_start()
{
echo "`eval $NOW` job_start"
}
#
job_success()
{
MSG="$*"
echo "`eval $NOW` job_success:[$MSG]"
exit 0
}
#
job_fail()
{
MSG="$*"
echo "`eval $NOW` job_fail:[$MSG]"
exit 1
}
job_start
#
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="root"
mysql_password="xxxx"
mysql_host="192.168.1.xx"
mysql_port="3306"
mysql_charset="utf8mb4"
# 备份文件存放地址(根据实际情况填写)
#backup_location=/home/sql_bak/`date +%Y%m%d%H`
backup_location=/nfs_mysql_back/mysql_back/`date +%Y%m%d%H`
test -d "$backup_location" || mkdir -p $backup_location
# 是否删除过期数据
expire_backup_delete="ON"
expire_days=30
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"
# 判断mysql实例是否正常运行
mysql_ps=`ps -ef |grep mysql |wc -l`
mysql_listen=`netstat -an |grep LISTEN |grep $mysql_port|wc -l`
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR:MySQL is not running! backup stop!"
exit
else
echo $welcome_msg
fi
#
del_bak()
{
# 删除过期数据
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
`find $backup_location -type f -mtime +$expire_days | xargs rm -rf`
if [ $? -eq 0 ] ; then
echo "Expired backup data delete complete!"
job_success "$*"
else
job_fail "$*"
fi
fi
}
#
all_bak()
{
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password --all-databases --single-transaction --quick --master-data > $backup_dir/mysql_all-databases_backup-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
# tar -zcPpf $backup_dir/mysql_$1_backup-$backup_time.zip --warning=no-file-changed --directory / $backup_dir
zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
echo "rm -rf $backup_dir/mysql_all-databases_backup-$backup_time.sql"
rm -rf $backup_dir/mysql_all-databases_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_all-databases-backup-$backup_time.sql"
job_success "$*"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
}
#
one_bak()
{
# 备份指定数据库中数据(此处假设数据库是test)
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $1 --master-data > $backup_dir/mysql_$1_backup-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
zip -jr $backup_dir/mysql_$1_backup-$backup_time.zip $backup_dir/ --exclude "*.zip"
#打包成功后删除sql文件
if [ $? = 0 ]; then
echo "rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql"
rm -rf $backup_dir/mysql_$1_backup-$backup_time.sql
fi
echo "database mysql_backup success backup to $backup_dir/mysql_backup-$backup_time.sql"
job_success "$*"
else
echo "database mysql_backup backup fail!"
job_fail "$*"
fi
}
job_shell()
{
case "$1" in
0)
del_bak
;;
1)
all_bak
;;
test)
one_bak $1
;;
nacos_test)
one_bak $1
;;
xxl_job_test)
one_bak $1
;;
*)
echo "Usage: $0 {0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]}"
exit 1
esac
exit 0
}
all_bak
#one_bak
#
#
#read -r -p "请选择全库/单库备份：｛0[删除30天之前备份]|1[全备]|单库名[ test | nacos_test | xxl_job_test]｝" bak_sh
#job_shell "$bak_sh"
|
