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
| #!/usr/bin/env bash
#
# Name: backupDB
#
# Description: This is a script to backup a given database to a given location.
#
# Synopsis:
# backupDB dbname [backupdir] [-ht] [-d dbhost] [-P dbport] [-n dbuser] [-p dbpass] [-m remotehost] [-s sshport] [-u sshuser]
#
# Notes:
# dbuser is same as dbname by default.
# Default behaviour is to lock the individual database when performing
# backups using mysqldump option -lock-tables (For rationale see:
# http://www.mysqlperformanceblog.com/2012/03/23/best-kept-mysqldump-secret/
# To change behaviour to --single-transaction pass the option -t. Backup
# does not save stored procedures or triggers (see --routines and --triggers
# options for mysqldump)
#
# Requires:
# date, gzip, ssh if database is not local.
#
#
# Copyright: Anntoin Wilkinson (2012)
help()
{
echo >&2 "usage: $0 dbname [-f backupdir] [-ht] [-d dbhost] [-P dbport] [-n dbuser] [-p dbpass] [-m remotehost] [-s sshport] [-u sshuser]"
exit 1
}
# Variables with default values
DUMPOPTIONS="--lock-tables"
if [ $1 ]; then
DBNAME=$1
else
help
fi
DBHOST=localhost
DBPORT=3306
DBUSER=$DBNAME
DBPASS=
REMOTEHOST=
SSHPORT=22
SSHUSER=$USER
BACKUPDIR=./$DBHOST/$DBNAME
# Handle Option flags
OPTIND=2
while getopts htf:d:P:n:p:m:s:u: opt
do
case "$opt" in
t) DUMPOPTIONS="--single-transaction";;
f) BACKUPDIR="$OPTARG";;
d) DBHOST="$OPTARG";;
P) DBPORT="$OPTARG";;
n) DBUSER="$OPTARG";;
p) DBPASS="$OPTARG";;
m) REMOTEHOST="$OPTARG";;
s) SSHPORT="$OPTARG";;
u) SSHUSER="$OPTARG";;
h) help;;
\?) # unknown flag
help;;
esac
done
# Test if backup directory exists and is a directory
if [ ! -d $BACKUPDIR ]; then
echo "Not a valid backup directory"; help
else
echo "Backup directory is $BACKUPDIR"
fi
# Additional Variables
DATE=`date +%R-%d-%b-%Y`
BACKUPFILE="$DATE.sql.gz"
DUMPCOMMAND="mysqldump $DUMPOPTIONS -h$DBHOST -P$DBPORT -u$DBUSER -p$DBPASS $DBNAME"
echo "MySQL will request your user password."
# Dump database, if remote use ssh
if [ -z $REMOTEHOST ]; then
$DUMPCOMMAND | gzip -c > $BACKUPDIR/$BACKUPFILE
else
ssh $SSHUSER@$REMOTEHOST "$DUMPCOMMAND | gzip -c"> $BACKUPDIR/$BACKUPFILE
fi
echo "Backup created in $BACKUPDIR/$BACKUPFILE"
|
