脚本简介：
将MySQL数据库dump出后压缩为gz格式。本脚本支持两个参数。第一个参数为数据库类型(myisam,innodb)第二个参数是想要dump的数据库。如果省略第二个参数默认备份所有。

?View Code BASH
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

#!/bin/bash
#================================================
# Name   : dumpdb.sh
# Version: 1.0
# Writer : Nathan.Zhou
# Date   : 2009.06.23
# Modify :
# Info   : 将MySQL数据库dump出后压缩为gz格式。本脚本支持两个参数。第一
#          个参数为数据库类型(myisam,innodb) 第二个参数是想要dump的
#          数据库。如果省略第二个参数默认备份所有。
# History:
#
#================================================
 
. /etc/profile
 
USER="root"
PWD="jobkoo"
CHARACTER="utf8"
 
DATE=`date +%F`
 
DUMP_MYISAM_ARG=" -u $USER -p$PWD --default-character-set=$CHARACTER
--opt --extended-insert=false --triggers -R --hex-blob -x "
 
DUMP_INNODB_ARG=" -u $USER -p$PWD --default-character-set=$CHARACTER --opt
--extended-insert=false --triggers -R --hex-blob --single-transaction "
MYSQL=`which mysql`
MYSQLDUMP=`which mysqldump`
SCP=`which scp`
 
# 本地备份后scp到目标主机的路径
TARGET_PATH="/data/db_dump_sql/"
TARGET_HOST="210.51.191.183|192.168.108.112"
TARGET_TYPE=2 # 选择 TARGET_HOST变量中的第二个主机地址 
 
BAK_FILENAME=rms_db_`date +%F`.sql.gz
 
# argument 1 : mysql data type
DATABASE_TYPE=`echo $1 | tr 'A-Z' 'a-z'`
 
# argument 2 :  to be dumped database
if [ "$2" == "" ];then
    TO_BAK_DB="all"
else
    TO_BAK_DB=`echo $2 | tr 'A-Z' 'a-z'| sed 's/,/ /g'`
fi
 
# 将备份下来的数据库scp到其他主机
function SCPTOHOST()
{
HOST=`echo $TARGET_HOST|cut -d '|' -f $TARGET_TYPE`
$SCP $1 $HOST:$TARGET_PATH
}
 
# 检查用户输入的数据库名是否存在，若存在，执行备份数据库操作
function DUMPSELECTDB()
{
for BASE in $TO_BAK_DB
do
  DB=`$MYSQL -u $USER -p$PWD -e 'show databases;' | grep $BASE`
  if [ "$DB" == "" ];then
    echo "You choose database $DB not exist! please try again!"
    continue
  else
  $MYSQLDUMP $1 -B $BASE | gzip >$TARGET_PATH/$BASE\_$DATE.sql.gz
  SCPTOHOST $TARGET_PATH/$BASE\_$DATE.sql.gz 2>&1>/dev/null
  fi
done
}
 
function DUMPALL()
{
$MYSQLDUMP $1 -A | gzip >$TARGET_PATH/$BAK_FILENAME
SCPTOHOST $TARGET_PATH/$BAK_FILENAME 2>&1>/dev/null
}
 
# main
if [ "$1" == "myisam" ];then
 
   if [ "$TO_BAK_DB" == "all" ];then
	DUMPALL "$DUMP_MYISAM_ARG"
   else
        DUMPSELECTDB "$DUMP_MYISAM_ARG"
   fi
 
elif [ "$1" == "innodb" ];then
 
   if [ "$TO_BAK_DB" == "all" ];then
	DUMPALL "$DUMP_INNODB_ARG"
   else
	DUMPSELECTDB "$DUMP_INNODB_ARG"
   fi
 
else
	echo "please input correct mysql db type!(myisam or innodb)"
	exit 1
fi