|
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
157
158
159
160
161
162
163
164
165
166
167
|
一、脚本说明
1、需要备份2个库(mysqltest1 mysqltest)
2、开启4个进程
3、可以选择进行备份不压缩和备份压缩
4、可以对不需要的数据库剔除
5、backup用户在数据库里的权限
grant
select
,reload,super,lock tables,create view,show view on *.* to
'backup'
@
'localhost'
identified by
'backup'
;
flush privileges;
6、计划任务
00 00 * * *
cd
/opt/dbbak
&&
bash
mysqlback.sh mysqlbackupconf >>
/opt/dbbak/dbbackup
.log 2>&1
7、会删除三天前的binlog
8、mysql版本5.5的
9、debian7.4 (3.2.54-2 x86_64)
二、具体脚本如下
cat
mysqlback.sh
#!/bin/bash
#--------------------------------------------------
#Author:jimmygong
#Email:jimmygong@taomee.com
#FileName:mysqlback.sh
#Function:
#Version:1.0
#Created:2015-06-29
#--------------------------------------------------
if
[[ $
# -ne 1 ]]
then
echo
-e
"Usage:$0 mysqlbackupconf"
exit
1
else
config=$1
if
[[ ! -f $config ]]
then
echo
-e
"Usage:$0 mysqlbackupconf"
exit
1
fi
fi
source
$config
dbname=
""
currdate=$(
date
+%Y%m%d)
haveinnodb=0
localip=$(
ifconfig
eth0|
awk
'/inet addr:/'
|
awk
-F:
'{print $2}'
|
awk
-F
" "
'{print $1}'
)
function
purgebinlog ()
{
purdate=`
date
"+%F %T"
--
date
=
'3 day ago'
`
pur=
"purge master logs before '$purdate'"
echo
"$pur"
|mysql -u
"$dbuser"
-p
"$dbpass"
}
function
redirectlog ()
{
logfile=$logdir/${currdate}_${localip}_log
mkdir
-p $logdir
exec
1>$logfile
exec
2>$logfile
}
function
checkdestdir ()
{
destdir=$destdir/$currdate
if
[[ ! -d $destdir ]]
then
mkdir
-p $destdir
fi
}
function
runcommand ()
{
comm
=$1
mysql -u
"$dbuser"
-p
"$dbpass"
-sNe
"$comm"
}
function
generateschema ()
{
i=0
result=`runcommand
'show databases'
`
for
db
in
$result
do
rv=`
echo
$exclude|
grep
-w -i $db`
if
[[ -n
"$rv"
]]
then
continue
;
fi
dbname[i]=$db
let
i++
done
}
function
guessengine ()
{
innodb=`runcommand
'show engines'
|
grep
-i innodb|
grep
-i
yes
`
if
[[ ! -z
"$innodb"
]]
then
haveinnodb=1
fi
}
function
dobackupsql ()
{
db=$1
destname=$destdir/$db.sql
if
[[ $haveinnodb -
eq
1 ]]
then
dumpcomm=
"mysqldump --opt -u$dbuser -p$dbpass --single-transaction --databases"
else
dumpcomm=
"mysqldump --opt -u$dbuser -p$dbpass --databases"
fi
$dumpcomm $db > $destname
}
function
dobackupgzip ()
{
db=$1
destname=$destdir/$db.sql.gz
if
[[ $haveinnodb -
eq
1 ]]
then
dumpcomm=
"mysqldump --opt -u$dbuser -p$dbpass --single-transaction --databases"
else
dumpcomm=
"mysqldump --opt -u$dbuser -p$dbpass --databases"
fi
$dumpcomm $db|
gzip
> $destname
}
function
backup ()
{
actioncommand=$1
if
[[ $commpress -
eq
1 ]]
then
actioncommand=
"dobackupgzip"
else
actioncommand=
"dobackupsql"
fi
echo
-en
"`date`\tBACKUP\t$db\n"
$actioncommand $db
}
function
backupalldb ()
{
count=0
for
db
in
${dbname[@]}
do
backup $db &
let
count+=1
[[ $((count%$processnum)) -
eq
0 ]] && wait
done
wait
echo
"all backup done"
}
purgebinlog
redirectlog
checkdestdir
generateschema
guessengine
runcommand
"flush logs"
backupalldb
exit
0
三、具体配置信息
cat
mysqlbackupconf
dbuser=
"backup"
dbpass=
"backup"
exclude=
'mysql information_schema performance_schema'
destdir=
/opt/dbbak
logdir=
/opt/dbbak/log
commpress=1
processnum=4
四、备份后结果
1、root@10.131.172.202:~
# ll /opt/dbbak/20150629/
-rw-r--r-- 1 root root 4443602410 Jun 29 19:18 mysqltest1.sql.gz
-rw-r--r-- 1 root root 4443601959 Jun 29 19:19 mysqltest.sql.gz
2、root@10.131.172.202:~
# ll /opt/dbbak/log/
-rw-r--r-- 1 root root 109 Jun 29 19:19 20150629_10.131.172.202_log
|
本文转自 xdoujiang 51CTO博客，原文链接：http://blog.51cto.com/7938217/1669168，如需转载请自行联系原作者
