backup_mysql.sh
4.14 KB
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
#!/usr/bin/env bash
set -o pipefail
PATH="$PATH:/usr/local/bin:/usr/local/sbin"
SCRIPT_LOG=/var/log/backups_mysql.log
DB_USER=""
DB_PASSWORD=""
DB_HOST=""
DB_LIST=()
DUMP_OPTIONS="--single-transaction --quick --no-autocommit --force --add-drop-table --skip-add-locks"
DUMP_FOLDER=/backups/tmp
ARCHIVE_NAME=archive.tar.gz
DESTINATION_FOLDER=/backups
function LOG()
{
local msg="$1"
date=$(date '+%Y-%m-%d %H:%M:%S')
echo "[$date] $msg" >> $SCRIPT_LOG
}
function DELETE_TMP_FOLDER()
{
if [ -d "${DUMP_FOLDER}" ]; then
rm -rf "${DUMP_FOLDER}" &> /dev/null
if [ $? -ne 0 ]; then
LOG "ERROR: Can not delete ${DUMP_FOLDER}"
exit 2
else
LOG "SUCCESS: Delete ${DUMP_FOLDER}"
fi
fi
}
function CREATE_TMP_FOLDER()
{
if [ -d "${DUMP_FOLDER}" ]; then
LOG "ERROR: Dump folder ${DUMP_FOLDER} exists."
exit 2
else
mkdir -p "${DUMP_FOLDER}" &> /dev/null
if [ $? -ne 0 ]; then
LOG "ERROR: Create dump folder ${DUMP_FOLDER} failed."
DELETE_TMP_FOLDER
exit 2
else
LOG "SUCCESS: ${DUMP_FOLDER} is created."
fi
fi
}
function READ_DBS()
{
sql="SHOW DATABASES WHERE \`Database\` NOT IN('information_schema', 'performance_schema', 'mysql')"
DB_LIST=$(mysql -N --host=${DB_HOST} --user=${DB_USER} --password=${DB_PASSWORD} -e "$sql") &> /dev/null
if [ $? -ne 0 ]; then
LOG "ERROR: Getting list of databases failed."
exit 2
else
if [[ -n $DB_LIST ]]; then
LOG "SUCCESS: Getting list of databases success."
else
LOG "ERROR: List of databases is empty."
exit 2
fi
fi
}
function CHECK_DISK_USAGE()
{
sql="SELECT table_schema , ROUND(SUM(data_length + index_length) / 1024 / 1024, 0) FROM information_schema.TABLES;"
DBS_SIZE=$(mysql -N --host=${DB_HOST} --user=${DB_USER} --password=${DB_PASSWORD} -e "$sql"| awk '{print $2}') &> /dev/null
if [ $? -eq 0 ] ; then
LOG "SUCCESS: Determining the size of MySQL databases."
else
LOG "ERROR: Determining the size of MySQL databases failed."
exit 2
fi
DST_AVAIL_SIZE=$(df -m ${DESTINATION_FOLDER} | awk 'NR==2{print $4}') &> /dev/null
if [ $? -eq 0 ] ; then
LOG "SUCCESS: Determining the amount of available disk space on ${DESTINATION_FOLDER} partition."
else
LOG "ERROR: Determining the amount of available disk space on ${DESTINATION_FOLDER} partition failed."
exit 2
fi
if [ $DST_AVAIL_SIZE -lt $DBS_SIZE ] ; then
LOG "ERROR: There is not ${DESTINATION_FOLDER} enough available disk space."
exit 2
else
LOG "SUCCESS: There is ${DESTINATION_FOLDER} enough available disk space."
fi
}
function DUMP_DBS()
{
for DB_NAME in $DB_LIST
do
LOG "Dumping ${DB_NAME}..."
mysqldump --host=${DB_HOST} --user=${DB_USER} --password=${DB_PASSWORD} ${DUMP_OPTIONS} ${DB_NAME} > ${DUMP_FOLDER}/${DB_NAME}.sql
if [ $? -eq 0 ] ; then
LOG "SUCCESS: Dump ${DB_NAME} is created."
else
LOG "ERROR: Dump ${DB_NAME} is failed."
DELETE_TMP_FOLDER
exit 2
fi
done
}
function COMPRESS_DUMPS()
{
(cd ${DUMP_FOLDER} && tar cf - *.sql) | gzip -9 > ${DUMP_FOLDER}/${ARCHIVE_NAME}
if [ $? -eq 0 ] ; then
LOG "SUCCESS: Archive has been created."
else
LOG "ERROR: Archive creation failed."
DELETE_TMP_FOLDER
exit 2
fi
}
function CHECK_ARCHIVE()
{
tar -tf ${DUMP_FOLDER}/${ARCHIVE_NAME} &> /dev/null;
if [ $? -eq 0 ] ; then
LOG "SUCCESS: Archive verification completed."
else
LOG "ERROR: Archive verification failed"
DELETE_TMP_FOLDER
exit 2
fi
}
function MOVE_ARCHIVE()
{
date=$(date '+%Y-%m-%d_%H-%M')
mv ${DUMP_FOLDER}/${ARCHIVE_NAME} ${DESTINATION_FOLDER}/${DB_HOST}_${date}.tar.gz &> /dev/null
if [ $? -eq 0 ] ; then
LOG "SUCCESS: Archive has been moved."
else
LOG "ERROR: Archive moving failed"
DELETE_TMP_FOLDER
exit 2
fi
}
LOG "### Backup mysql started ###"
LOG "Checking the amount of available disk space on ${DESTINATION_FOLDER} partition."
CHECK_DISK_USAGE
LOG "Creatting temporary folder:"
CREATE_TMP_FOLDER
LOG "Getting list of databases:"
READ_DBS
LOG "Dump databases:"
DUMP_DBS
LOG "Compressing dumps:"
COMPRESS_DUMPS
LOG "Checking archive:"
CHECK_ARCHIVE
LOG "Moving archive:"
MOVE_ARCHIVE
DELETE_TMP_FOLDER
LOG "### Backup mysql ended ###"