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
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
|
#!/usr/bin/env bash
#################################################################################
# AUTHOR: H. P. <mail_redacted_for_web>
# LICENSE: LGPLv3
# Version: 0.1
#
# Small aka. rather quick and dirty script to backup your SQL databases.
#
# What this script does is looking for existing databases and backup all of them.
# The result will be sql.xz files inside /tmp/sqlbackup.XXXXXX.
#
# TO-DOS:
# 1. Create possibilities to define the following through CLI options:
# --- DONE --- 1.1. MySQL user, and password command(!)
# 1.2. DB exclusion regex
# 1.3. Location of configuration file or log
#################################################################################
function help {
printf "USAGE: %b [-h] [-l] [-b DIR] [-u USER] [-p PASSWORD]\n\n" "$(basename "$0")"
printf -- "-h:\tThis help\n"
printf -- "-b:\tBackup target directory\n"
printf -- "-l:\tLog to syslog\n"
printf -- "-p:\tPassword string for MySQL user (hint: use a command for this!)\n"
printf -- "-u:\tUser in MySQL designated for backups\n"
}
function mlogger {
[ -z "$2" ]&&return 1
[ -z "$1" ]&&return 1
logger -t "sqlbackup" -p "local3.""$1" "$2"
}
function bstatus {
case "$CURDB" in
"") printf "Status: Preparing backup.\n";;
*) printf "Status: Backing up %b.\n" "$CURDB";;
esac
}
function bkillme {
printf "\n[CRIT] Got a termination signal, committing sudoku...\n"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger warning "Terminating on external request"
exit 130
}
trap bkillme SIGINT
trap bkillme SIGKILL
trap bkillme SIGTERM
trap bstatus USR1
declare -x BUSER BDIR DBFDIR LOGGER CURDB SCHANAUZE=0 PRMERR=0
while getopts :hld:u:p:q SHOPT;do
case "$SHOPT" in
h) help;exit 0;;
l) LOGGER=1;;
b) BDIR="${OPTARG}";;
d) DBFDIR="${OPTARG}";;
u) BUSER="${OPTARG}";;
p) BPW="${OPTARG}";;
q) SCHANAUZE=1;;
*)
help
printf "\n[ ]\033[s%b Not an option or empty parameter: -%b\n" \
"$NEE" "$OPTARG" >&2
#[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&\
mlogger err "Not an option or empty parameter: -$OPTARG"
PRMERR=$((PRMERR+1))
;;
esac
done
shift $((OPTIND-1))
[ "$PRMERR" -gt 0 ]&&exit 1
# Leave this line alone - location of script
MYDIR="$(cd "$(dirname "$0")"&&pwd)"
# The backup directory. The line here means we will create /tmp/sqlbackup.XXXXXX
# and backup there. If you want another value, enter this here.
#BDIR="$MYDIR/files"
if [ -z "$BDIR" ];then
BDIR="$(mktemp -d /tmp/sqlbackup.XXXXXX)"||exit 187
fi
# The backup user:
[ -z "$BUSER" ]&&BUSER="tVY8MwA7cTEm"
# Password file. For now, this is unencrypted, so please make this file secure!
# Expects just the password, one line. Also, you will want to use a dedicated
# user for that:
# GRANT SELECT, SHOW DATABASES, LOCK TABLES ON *.* TO 'backupheiner'@'localhost';
#PWFL="$MYDIR/pw"
if [ -z "$BPW" ];then
PWFL="/etc/sqlbackup/pw"
if [ ! -r "$PWFL" ];then
help
printf "\n[ ]\033[s%b %b not readable, no password supplied!\n" "$NEE" "$PWFL" >&2
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&\
mlogger err "$PWFL not readable, no password supplied"
exit 1
fi
fi
# Location of the mysql/maria db files, default: /var/lib/mysql. No trailing slashes.
[ -z "$DBFDIR" ]&&DBFDIR="/var/lib/mysql"
# Databases to be excluded. Meant to work with grep -P, i.e. perl regular expressions.
# The minimum is to separate any database with the pipe character (|).
# Caution: an empty variable will backup nothing.
#
# The default line excludes the MySQL internal databases:
#NODB="^information_schema$|^mysql$|^performance_schema$"
# Very offensive backup, meaning even internals - should exclude information_schema:
NODB="^information_schema$|^performance_schema$"
#########
# MYSQL #
#########
DUMPOPTS=( "--add-drop-table" "--add-locks" )
DUMPOPTS+=( "--complete-insert" "--create-options" "--max_allowed_packet=512M" )
# Flush on backup of every subordinate db would create too many new files.
# Hence, commented out.
#DUMPOPTS+=( "--flush-logs" )
ISAM_DUMPOPTS=( ${DUMPOPTS[@]} "--lock-tables" )
INNO_DUMPOPTS=( ${DUMPOPTS[@]} "--single-transaction" )
############
# RET VERB #
############
OGE="\033[666D[ \033[0;32mOK \033[0m]\033[u\033[K"
NEE="\033[666D[\033[0;31mFAIL\033[0m]\033[u\033[K"
WAT="\033[666D[\033[0;33mWARN\033[0m]\033[u\033[K"
[ -z "$BDIR" ]&&printf "No backup directory specified!" >&2&&exit 191
mkdir -p "$BDIR"&&chmod 0700 "$BDIR"||exit 188
BLOG="$BDIR""/sqlbackup.log"
touch "$BLOG"||exit 189
chmod 0600 "$BLOG"||exit 190
#########
TIME="$(date +"%Y-%m-%dT%H:%M:%S%z")"
[ "$SCHANAUZE" -ne 1 ]&&printf "File timestamp: %s\n" "$TIME"
printf "File timestamp: %s\n" "$TIME" > "$BLOG"
# Flush prior to backups, do not purge.
[ "$SCHANAUZE" -ne 1 ]&&printf "\n[....] Flushing logs.\033[s..\033[0m"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger info "flushing logs"
mysql --user="$BUSER" --password="$(cat $PWFL)" -e "flush logs \\G"
if [ "$?" -ne 0 ];then
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "flushing logs failed, check $BLOG"
[ "$SCHANAUZE" -ne 1 ]&&printf "$NEE\n\n"
exit 101
else
[ "$SCHANAUZE" -ne 1 ]&&printf "$OGE\n\n"
fi
exit 0
LOGPOS="$(mysql --user="$BUSER" --password="$(cat $PWFL)" -e "show master status \G"|grep Position|awk '{print $NF}')"
[ "$SCHANAUZE" -ne 1 ]&&printf "Log position: %s\n" "$LOGPOS"
printf "Log position: %s\n" "$LOGPOS" > "$BLOG"
# Get latest non-relay binary log:
find "$DBFDIR" -maxdepth 1 -cmin 2 >/dev/null 2>&1
if [ "$?" -ne 0 ];then
[ "$SCHANAUZE" -ne 1 ]&&printf "[ ]\033[s%b Failed to read %b for binary logs.\n" "$NEE" "$DBFDIR"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "Failed to read $DBFDIR for binary logs."
exit 127
fi
BINLOGF="$(find "$DBFDIR" -maxdepth 1 -nowarn -type f -regextype posix-extended -regex ".*-bin\.[0-9]+$"|grep -v relay-bin|awk -F\. '{print $NF,$0}'|sort -n|cut -f2- -d' '|tail -1)"
BINLOGF="$(basename "$BINLOGF")"
[ -z "$BINLOGF" ]&&exit 127
DBARR=( $(mysql -u "$BUSER" --password="$(cat $PWFL)" -e "show databases \\G"|grep ^Data|awk '{print $NF}'|grep -vP "$NODB") )
j=1
[ "$SCHANAUZE" -ne 1 ]&&printf "\nDatabases being backed up\n======================\n"
[ "$SCHANAUZE" -ne 1 ]&&for i in ${DBARR[@]};do
echo -n "$i"
case "$(( j % 3 ))" in
0) echo " " ;;
*) echo -n ";";;
esac
j="$((j+1))"
done|column -ts\;
[ "$SCHANAUZE" -ne 1 ]&&printf "\n"
[ "$SCHANAUZE" -ne 1 ]&&printf "Current binary log: %b\n\n" "$BINLOGF"
for i in "${DBARR[@]}";do
CURDB="$i"
[ "$SCHANAUZE" -ne 1 ]&&printf "[....] Backing up database %s.\033[s..\033[0m" "$i"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger info "backing up database $i"
sleep 0.1337
[ "$SCHANAUZE" -ne 1 ]&&printf ".. \033[u\033[K \033[1;30mdump running...\033[0m"
if ( [ "$i" == "mysql" ] || [ "$i" == "information_schema" ] || [ "$i" == "performance_schema" ] );then
mysqldump "${ISAM_DUMPOPTS[@]}" -u "$BUSER" --password="$(cat "$PWFL")" --databases "$i" > "$BDIR/$i-$TIME.sql" 2> "$BLOG"
else
mysqldump "${INNO_DUMPOPTS[@]}" -u "$BUSER" --password="$(cat "$PWFL")" --databases "$i" > "$BDIR/$i-$TIME.sql" 2> "$BLOG"
fi
if [ "$?" -ne 0 ];then
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "backup of $i failed, check $BLOG"
[ "$SCHANAUZE" -ne 1 ]&&echo -e "$NEE"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "backup failed, check $BLOG"
exit 2
fi
sleep 0.1337
[ "$SCHANAUZE" -ne 1 ]&&printf ".. \033[u\033[K \033[1;30mcompression running...\033[0m"
xz "$BDIR/$i-$TIME.sql"&&chmod 0600 "$BDIR/$i-$TIME.sql.xz"
if [ "$?" -ne 0 ];then
[ "$SCHANAUZE" -ne 1 ]&&echo -e "$NEE"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "compression failed, check $BLOG"
exit 3
fi
sleep 0.1337
echo -e "$OGE"
done
[ "$SCHANAUZE" -ne 1 ]&&printf "[....] Purging binary logs.\033[s..\033[0m"
mysql --user="$BUSER" --password="$(cat $PWFL)" -e "purge binary logs to \"$BINLOGF\" \\G"
if [ "$?" -ne 0 ];then
[ "$SCHANAUZE" -ne 1 ]&&echo -e "$NEE"
[ ! -z "$LOGGER" ]&&[ "$LOGGER" -eq 1 ]&&mlogger err "purging of binary logs failed, check $BLOG"
exit 3
fi
sleep 0.1337
echo -e "$OGE"
|
