blob: b48f3f668ba04adc7372e17d2f11f173ffbabb35 (
plain) (
blame)
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
|
#!/bin/bash
# this script will backup all mysql databases from the server to a ftp
# server
source $(dirname $0)/ftp.settings
# mysql server informations
MYSQL_SERVER="localhost"
MYSQL_USER="backup"
MYSQL_PASSWORD="password"
# databases to exclude, seperated by space
# TODO
#MYSQL_EXCLUDE_DB=""
# temporary directory
TMP_DIRECTORY="/tmp"
# backup name
BACKUP_PREFIX="$(hostname -f)_${MYSQL_SERVER}_$(date +%F)"
# logging (0 = enabled, all other = disabled)
LOGGING="0"
## end of configuration
# load common stuff
BACKUP_ROOT="$(dirname $0)/.."
source ${BACKUP_ROOT}/common/init.sh
# check for tools
_check_tools mysqlshow mysqldump ncftpput
echo "Started backup of mysql databases ($(date +%c)):" | _log
echo | _log
# flush logs on master
# get master
MYSQL_MASTER=$(mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
-p${MYSQL_PASSWORD} <<< "SHOW SLAVE STATUS\G" | \
grep "^ *Master_Host:" | awk '{ print $2 }')
echo "flush logs;" | mysql -h${MYSQL_MASTER} -u${MYSQL_USER} \
-p${MYSQL_PASSWORD}
# pause the replication to be able to do consistent backups
mysqladmin -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
stop-slave 2>&1 >/dev/null
# create temporary dir
mkdir -p ${TMP_DIRECTORY}
TMP_DIR=$(mktemp -d --tmpdir=${TMP_DIRECTORY})
pushd ${TMP_DIR} >/dev/null 2>&1
# enumerate all mysql databases
MYSQL_DATABASES=$(mysqlshow -h${MYSQL_SERVER} -u${MYSQL_USER} \
-p${MYSQL_PASSWORD} | awk '{ print $2 }' | grep -v "^$" | sed \
'1d')
# get binlog position
MYSQL_BINLOG="-"$(mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
-p${MYSQL_PASSWORD} <<< "SHOW SLAVE STATUS\G" | \
grep "^ *Master_Log_File:" | awk '{ print $2 }')"-"$( \
mysql -h${MYSQL_SERVER} -u${MYSQL_USER} \
-p${MYSQL_PASSWORD} <<< "SHOW SLAVE STATUS\G" | \
grep "^ *Read_Master_Log_Pos:" | awk '{ print $2 }')
# backup all databases to file
for db in ${MYSQL_DATABASES}
do
mysqldump -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
${db} | bzip2 -zc > "${BACKUP_PREFIX}-${db}${MYSQL_BINLOG}.sql.bz2"
done
# restart the replication
mysqladmin -h${MYSQL_SERVER} -u${MYSQL_USER} -p${MYSQL_PASSWORD} \
start-slave 2>&1 >/dev/null
# echo created log of file sizes
du -sch * | _log
echo | _log
# move backups to ftp server
for file in *
do
ncftpput -u${FTP_USER} -p${FTP_PASSWORD} -V -DD ${FTP_SERVER} \
${FTP_TARGET} ${file} >/dev/null
done
echo "Finished backup of mysql databases ($(date +%c))" | _log
# restore old cwd
popd >/dev/null 2>&1
rmdir ${TMP_DIR}
|
