【mysqldump备份指定数据库表】my_dump.sh
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
|
#!/bin/bash
#
# 2015/6/8
# 根据指定的db.table,执行批量导出备份的任务,并归档到your_ip::bak上,发送邮件通知,最后再通知业务清理数据。
list_tables=(
t_a_201311
t_a_201312
t_b_201311
t_b_201312
t_c_201311
t_c_201312
t_d_201311
t_d_201312
t_e_201311
t_e_201312
)
function
dump_db_table() {
local
d_db_base=
'/data/app/mysql'
local
s_datetime=$(
date
+%Y%m%d_%H%M%S)
local
s_port=3306
local
s_password=
'xxx'
local
s_db=$1
if
[ -z ${s_port} ];
then
echo
"Usage: $0 database table"
exit
2;
fi
local
s_table=$2
local
d_backup=
"/home/backup/mysql/${s_port}/mysqldump/${s_db}"
if
[ -z ${s_table} ];
then
local
dump_args=
"${d_db_base}/bin/mysqldump -h 127.0.0.1 -P ${s_port} -u root --password=${s_password} --hex-blob -R -E -e -q --triggers --default-character-set=latin1 ${s_db}"
local
f_dump_tar=
"${d_backup}/${s_datetime}-${s_db}.gz"
else
local
dump_args=
"${d_db_base}/bin/mysqldump -h 127.0.0.1 -P ${s_port} -u root --password=${s_password} --hex-blob -R -E -e -q --triggers --default-character-set=latin1 ${s_db} ${s_table}"
local
f_dump_tar=
"${d_backup}/${s_datetime}-${s_table}.gz"
fi
test
-d ${d_backup} ||
mkdir
-p ${d_backup}
echo
"[+] `date` Port: ${s_port} DB: ${s_db}.${s_table}"
${dump_args} |
gzip
>${f_dump_tar}
echo
"[-] `date` Saved to: ${f_dump_tar}"
echo
"[-] `date` Done."
echo
sleep
1s
}
function
do_dump() {
num_tables=${
#list_tables[@]}
echo
"[numble of tables]: ${num_tables}"
for
((i=0;i<${num_tables};i++))
do
echo
"[${i}]导出表:${list_tables[$i]}"
# 要执行真实操作时取消注释下面这1行
#dump_db_table d_test2013 ${list_tables[$i]}
done
}
function
do_bak() {
do_dump
#echo "[-] `date` rsync to your_ip::bak. Start."
#rsync -avzP your_src_path your_ip::bak
#echo "[-] `date` rsync to your_ip::bak. Done."
}
do_dump
# 要执行真实操作时取消注释下面这3行,并注释上面的这1行
#do_bak >/root/dump_db_table.log 2>&1
#rsync -avzP /root/dump_db_table.log your_ip::bak
#/usr/local/bin/python3 /usr/local/bin/mailman.py 'admin@company.com' '[backup] job done' " ^.^ at `date`, backup some tables from d_test2013, now fininshed. Later on, I sent all tar.gz files to your_ip::bak by using rsync. (see log as attachement)" /root/dump_db_table.log
|
本文转自 pcnk 51CTO博客,原文链接:http://blog.51cto.com/nosmoking/1594800,如需转载请自行联系原作者