sympl-sqldump 16.1 KB
Newer Older
1
2
3
4
#!/bin/bash
#
# sympl-sqldump - a fairly simple utility to dump databases sanely
#
5
# Copyright 2019, Paul Cammish <sympl@kelduum.net>
6
#
7
# Licenced under GPL3+
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

# Set basic environment

set -o pipefail
#set -e

if [ "$( echo "$@" | grep -c ' --debug ' )" != "0" ]; then DEBUG="TRUE"; fi

#############################################################################
#                        Base Debug/Error Functions                         #
#############################################################################

function _debug {
  if [ $DEBUG ]; then
    echo -e "\e[2mDEBUG: $@\e[0m"
  fi
}

function _warn {
  echo -e "\e[1m\e[33m WARN: $@\e[0m"
  echo "WARNING: $@" >> "$DUMP_DIR/$LOGNAME"
  echo "WARNING: $@" >> "$_TEMP-$LOGNAME"

}

function _error {
  echo -e "\e[1m\e[31mERROR: $@\e[0m"
  echo "ERROR: $@" >> "$DUMP_DIR/$LOGNAME"
  echo "ERROR: $@" >> "$_TEMP-$LOGNAME"
  _exit 1
}

function _verbose {
  if [ $DEBUG ]; then
    echo " INFO: $@"
  elif [ $VERBOSE ]; then
    echo "$@"
  fi
  if [ ! -d "$DUMP_DIR" ]; then mkdir -p $DUMP_DIR; fi
  echo "$@" >> "$DUMP_DIR/$LOGNAME"
  echo "$@" >> "$_TEMP-$LOGNAME"
}

function _check_stderr {
  if [ $( cat "$STDERR" | wc -l ) -gt 0 ] || [ "$1" -ne "0" ]; then
    _error "== errorcode $1 =="
    cat "$STDERR" | while read line; do
      _error "$line"
    done
    _exit 1
  fi
  rm "$STDERR"
}

function _exit {
  _debug Exiting with errorcode: $1
  if [ $DEBUG ]; then ls -la $DUMP_DIR; fi
  rm $_TEMP-* 2> /dev/null
  if [ -f "$DUMP_DIR/$LOGNAME" ]; then
    tail -n 10000 $DUMP_DIR/$LOGNAME > $DUMP_DIR/$LOGNAME.new
    mv $DUMP_DIR/$LOGNAME.new $DUMP_DIR/$LOGNAME
  fi
  exit $1
}

function _dump_failed {
  _warn "Dump of database '$DATABASE' failed."
  rm "$@"
}

function _bytesToHuman() {
  # Taken from https://unix.stackexchange.com/a/259254
  b=${1:-0}; d=''; s=0; S=(B {K,M,G,T,P,E,Z,Y});
  while ((b > 1024)); do
    d="$(printf ".%02d" $((b % 1024 * 100 / 1024)))"
    b=$((b / 1024))
    let s++
  done
  echo "$b$d${S[$s]}"
}

function _validate_dump {
  TEST_FILENAME="$@"
  _debug "Checking $TEST_FILENAME for valid dump"
  if [ $( cat "$TEST_FILENAME" | gunzip | tail -n 3 | grep -c '^-- PostgreSQL database dump complete\|^-- Dump completed on\|^-- PostgreSQL database cluster dump complete' ) -eq 1 ]; then
    _verbose "  Completed $(_bytesToHuman $(gzip -l $TEST_FILENAME | tail -n 1 | awk '{ print $2 }' )) dump of '$DATABASE' at $( date ) ($(_bytesToHuman $(gzip -l $TEST_FILENAME | tail -n 1 | awk '{ print $1 }' )) gzip, $(gzip -l $TEST_FILENAME | tail -n 1 | awk '{ print $3 }'))"
  else
    _warn "Dump of $DATABASE appears to be truncated"
    _dump_failed $TEST_FILENAME
  fi
}



#############################################################################
#                               Set Defaults                                #
#############################################################################

# Note: To override thse defaults, copy and paste the modified entries to '/etc/sympl-sqldump.config',
# or if using another path, use the --config command line parameter

# Basic settings

DUMP_DIR=/var/backups/mysql
KEEP_MAX_COPIES="2"

# Complex settings - be careful!

LOGNAME=sympl-sqldump.log
_TEMP=/tmp/__sympl-sqldump
STDERR="$_TEMP-stderr"
SEPERATOR='--'
CONFIG_FILE=/etc/sympl-sqldump.config

# MySQL defaults

MYSQL=$( which mysql 2> /dev/null )
MYSQLDUMP=$( which mysqldump 2> /dev/null )
126
MYSQL_DEFAULTS="/home/sympl/.my.cnf"
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
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
MYSQL_SKIP_DB='information_schema performance_schema'
MYSQLDUMP_OPTIONS='--create-options --no-create-db --events --triggers --routines --dump-date --tz-utc'

# Output settings

#DEBUG=TRUE
#VERBOSE=TRUE

# Determine possible defaults

# Enable MySQL dumps if we can see both the client and the dump util in the path
if [ -f "$MYSQL" ] && [ -f "$MYSQLDUMP" ]; then
  BACKUP_MYSQL=TRUE
else
  unset BACKUP_MYSQL
fi


#############################################################################
#                         Import Default Overrides                          #
#############################################################################

function import_default_overrides {
  if [ -f "$CONFIG_FILE" ]; then
    if [ $( find "$CONFIG_FILE" -user root -perm 600 | wc -l ) == 1 ]; then
      _debug Executing config file $CONFIG_FILE
      . $CONFIG_FILE
    else
      _warn Config file found but not secured. Ignoring.
    fi
  fi
}

if [ "$( echo "$@" | grep -c ' --config ' )" == "0" ]; then
  import_default_overrides
else
  _debug "Config override detected in command line, will run once parsed"
fi

#############################################################################
#                       Read Command Line Parameters                        #
#############################################################################

_debug "Command line parameters: $@"

PARAMETERS=()
while [ $# -gt 0 ]; do
  key="$1"

  case $key in
    --keep|-k)
      KEEP_MAX_COPIES="$2"
      shift; shift
    ;;
    --dir|-d)
      DUMP_DIR="$2"
      shift; shift
    ;;
    --mysql)
      MYSQL="$2"
      shift; shift
    ;;
    --mysqldump)
      MYSQLDUMP="$2"
      shift; shift
    ;;
    --mysql-defaults)
      MYSQL_DEFAULTS="$2"
      shift
    ;;
    --debug)
      DEBUG="TRUE"
      shift
    ;;
    --verbose|-v)
      VERBOSE="TRUE"
      shift
    ;;
    --force)
      FORCE="TRUE"
      shift
    ;;
    --config)
      CONFIG_FILE="$2"
      import_default_overrides
      shift; shift
    ;;
#    --restore) # Currently incomplete. Will be split to 'sympl-sqlrestore'
#      RESTORE="$2"
#      VERBOSE="TRUE"
#      shift; shift
#    ;;
    *) # unhandled parameter
      PARAMETERS+=("$1") # save it in an array for later
      shift # past argument
    ;;
  esac
done

#set -- "${PARAMETERS[@]}"
# restore positional parameters

_debug "Runtime variables:
------------------------------------------
UNKNOWN             |$PARAMETERS
CONFIG_FILE         |$CONFIG_FILE
MYSQLDUMP_OPTIONS   |$MYSQLDUMP_OPTIONS
KEEP_MAX_COPIES     |$KEEP_MAX_COPIES
DUMP_DIR            |$DUMP_DIR
MYSQL_SKIP_DB       |$MYSQL_SKIP_DB
MYSQL               |$MYSQL
MYSQLDUMP           |$MYSQLDUMP
BACKUP_MYSQL        |$BACKUP_MYSQL
------------------------------------------"

#############################################################################
#                   Default to help text if unhanded input                  #
#############################################################################

if [ "${#PARAMETERS}" -gt "0" ]; then
  echo -e "Usage: \033[1msympl-sqldump <options>\033[0m

Common

  --dir, -d <path>         Override path to dump directory.
252
                             Will be created/chown'd/chmod'd to sympl user.
253
254
255
256
257
258
259
260
261
262
263
264
265
                             Defaults to $DUMP_DIR
  --keep, -k <number>      Number of dumps to keep before removing old copies.
                             Defaults to $KEEP_MAX_COPIES
  --verbose, -v            Output progress to both stdout and log file.

Executable paths

  --mysql <file>           Override path to 'mysql' executable.
  --mysqldump <file>       Override path to 'mysqldump' executable.

Authentication

  --mysql-defaults         Optional 'defaults-file' to use in debian.cnf
266
                             format. Defaults to $MYSQL_DEFAULTS,
Paul Cammish's avatar
Paul Cammish committed
267
268
                             /etc/mysqldump/sympl-sqldump.cnf and
                             /etc/mysqldump/debian.cnf in that order.
269
                             Must be secured to root or sympl user only.
270
271
272
273
274
275

Other

  --force                  Force a run even if currently locked.
  --config <file>          Specify a config file to override defaults.
                             Defaults to /etc/sympl-sqldump.conf if exists
276
                             and must be secured to root user only.
277
278
279
280
281
282
283
284
285
286
287
  --help                   This text.
"

  _exit 0
fi

#############################################################################
#                       Check for root priviledges                          #
#############################################################################

if [ $( id -u ) -ne 0 ]; then
288
289
  echo "Error: This must be run as root"
  exit 256
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
fi


_verbose "Starting sympl-sqldump at $(date)"


#############################################################################
#                        Check for and Enable Lock                          #
#############################################################################

if [ $FORCE ]; then
  _debug "Ignoring current lock status"
  touch /var/run/sympl-sqldump.lock
else
  _debug "Checking lock status"
  if [ -f /var/run/sympl-sqldump.lock ]; then
    if [ $(find /var/run/sympl-sqldump.lock -mmin -$((60*18)) | wc -l ) -gt 0 ]; then
      _error "Locked. Started running some time in the last 18 hours and hasn't finished yet. Use --force to override."
      _exit 128
    else
      _warn "Removing stale lock file"
      touch /var/run/sympl-sqldump.lock
    fi
  else
    touch /var/run/sympl-sqldump.lock
  fi
fi



#############################################################################
#                         Determine MySQL Flavour                           #
#############################################################################
_debug Determine MySQL Flavour


MYSQL_HELP_TEXT="$( $MYSQL --help | head -n 2 )"
if [ ${#MYSQL_HELP_TEXT} -eq 0 ]; then
  _error Unable to get the MySQL branch
fi

if [ $( echo $MYSQL_HELP_TEXT | grep -c -i 'MariaDB' ) -gt 0 ]; then
  MYSQL_FORK='mariadb'
else
  if [ $( echo $MYSQL_HELP_TEXT | grep -c -i 'Percona' ) -gt 0 ]; then
  MYSQL_FORK='percona'
else
  MYSQL_FORK='mysql'
  fi
fi

#############################################################################
#                  Determine MySQL Authentication Method                    #
#############################################################################

## MariaDB can simply be used with 'mysql', others will need
## $HOME/.my.cnf, /etc/mysql/sympl-sqldump.cnf or /etc/mysql/debian.cnf

348
## Use credentials from (in order) $HOME/.my.cnf, /etc/mysql/sympl-sqldump.cnf,
349
350
351
352
353
## /etc/mysql/debian.cnf or the normal MySQL methods available to root.

# look for /etc/mysql directory (won't exist on centos)
if [ -d /etc/mysql ]; then
  if [ -f "$MYSQL_DEFAULTS" ]; then
354
355
356
    if [ "$( find "$MYSQL_DEFAULTS" -user sympl -perm 0600 | wc -l )" == "1" ]; then
      _debug Secure $MYSQL_DEFAULTS found.
      AUTH="--defaults-file=$MYSQL_DEFAULTS"
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
    fi
  elif [ -f /etc/mysql/sympl-sqldump.cnf ]; then
    if [ "$( find /etc/mysql/sympl-sqldump.cnf -user root -perm 0600 | wc -l )" == "1" ]; then
      _debug Secure /etc/mysql/sympl-sqldump.cnf found.
      AUTH="--defaults-file=/etc/mysql/sympl-sqldump.cnf"
    fi
  elif [ -f /etc/mysql/debian.cnf ]; then
    if [ "$( find /etc/mysql/debian.cnf -user root -perm 0600 | wc -l )" == "1" ]; then
      _debug Secure /etc/mysql/debian.cnf found.
      AUTH="--defaults-file=/etc/mysql/debian.cnf"
    fi
  fi
else
  # It should still pick up the config from /root/.my.cnf or /root/.mylogin.cnf
  _debug Falling back to built-in auth methods.
  AUTH=""
fi
MYSQL="$MYSQL $AUTH"
MYSQLDUMP="$MYSQLDUMP $AUTH"

#############################################################################
#                        Determine MySQL Version                            #
#############################################################################

## As database structures can be fairly different between MySQL versions,
## we need to make sure we have the DB version in the filename to make it
## clear there are compatability differences when using the restore function.

MYSQL_VER="$( $MYSQL -BNe 'SHOW VARIABLES LIKE "version";' | awk '{ print $2 };' | grep -oi '^[a-z0-9]*\.[a-z0-9]*' | head -n 1 2> $STDERR )"
_check_stderr $?

MYSQL_PREFIX="${MYSQL_FORK}_${MYSQL_VER}"
_debug "Database dump prefix: $MYSQL_PREFIX"

#############################################################################
#                       Make List of Databases to Dump                      #
#############################################################################

_debug 'Retrieve list of databases to dump'
MYSQL_TO_DUMP="$( for db in $( $MYSQL -BNe 'SHOW DATABASES;' ); do if [ "$( echo $MYSQL_SKIP_DB | grep -c "$db" )" -eq 0 ]; then echo $db; fi; done )"

_debug "MySQL Databases to dump: $( echo $MYSQL_TO_DUMP | tr '\n' ' ' )"

#############################################################################
#                        Output Databases to Dump                           #
#############################################################################

if [ ${#MYSQL_TO_DUMP} -gt 0 ]; then
  _verbose "Identified MySQL databases: $( echo $MYSQL_TO_DUMP | tr '\n' ' ' )($MYSQL_PREFIX)"
fi

if [ ${#PGSQL_TO_DUMP} -gt 0 ]; then
  _verbose "Identified PostgreSQL databases: $( echo $PGSQL_TO_DUMP | tr '\n' ' ' )($PGSQL_PREFIX)"
fi

#############################################################################
#                       Secure SQL Dump Directory                           #
#############################################################################

_debug 'Secure SQL Dump Directory'
if [ ! -d "$DUMP_DIR/." ]; then
  mkdir -p $DUMP_DIR
  _verbose "Created dump target directory $DUMP_DIR"
fi
421
422
423
if [ $( find $DUMP_DIR -maxdepth 0 -user sympl -group sympl -perm 770 | wc -l ) -ne 1 ]; then
  chown sympl:sympl "$DUMP_DIR/."
  chmod 770 "$DUMP_DIR/."
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
  _verbose "Adjusted permissions for $DUMP_DIR"
fi

#############################################################################
############################## Do the Dumps #################################
#############################################################################


# itterate through databases
for DATABASE in ${MYSQL_TO_DUMP}; do

  _debug "Determining target file name for database '$DATABASE'"
  ### name format is <service><version>_<database>_YYYY-MM-DD_HH:MM:SS_ZZZ_DDDDD.sql.gz
  TIMESTAMP="$(date +%Y%m%d_%H%M%S_%Z_%A)"

  ## dump database
  DUMP_FILENAME="${DUMP_DIR}/${MYSQL_PREFIX}${SEPERATOR}${DATABASE}${SEPERATOR}${TIMESTAMP}.sql.gz"

  if [ -f $DUMP_FILENAME ]; then
    _error "Dump with filename '$DUMP_FILENAME' already exists. This shouldn't happen."
  else
    _debug 'Creating dummy file and setting permissions'
    touch $DUMP_FILENAME
447
448
    chown sympl:sympl $DUMP_FILENAME
    chmod 660 $DUMP_FILENAME
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496

    ## determine dump type
    ### MyISAM tables need to be locked, InnoDB tables don't, and use different dump types
    ###   so we need to determine if there are only InnoDB tables in the specific schema,
    ###   and if so, we can use that method.
    NON_INNODB_TABLES=$( $MYSQL -BNe "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '$DATABASE' AND engine <> 'InnoDB';" )

    if [ $NON_INNODB_TABLES -eq 0 ]; then
      _debug "There are no non-InnoDB tables in '$DATABASE', running dump with --single-transaction"
      _verbose "Dumping database '$DATABASE' into '$( echo $DUMP_FILENAME | sed 's|.*/||' )'"
      _debug "Running: $MYSQLDUMP $MYSQLDUMP_OPTIONS --single-transaction $DATABASE | gzip --fast 2> $STDERR > ${DUMP_FILENAME}"
      $MYSQLDUMP $MYSQLDUMP_OPTIONS --single-transaction $DATABASE | gzip --fast 2> $STDERR > "${DUMP_FILENAME}"
      _check_stderr $?
    else
      _debug "There are '$NON_INNODB_TABLES' non-InnoDB tables in '$DATABASE', running dump in compatability mode"
      _verbose "Dumping database '$DATABASE' into '$( echo $DUMP_FILENAME | sed 's|.*/||' )'"
      _debug "Running: $MYSQLDUMP $MYSQLDUMP_OPTIONS --lock-tables $DATABASE | gzip --fast 2> $STDERR > ${DUMP_FILENAME}"
      $MYSQLDUMP $MYSQLDUMP_OPTIONS --lock-tables $DATABASE | gzip --fast 2> $STDERR > "${DUMP_FILENAME}"
      _check_stderr $?
    fi
  fi

  _validate_dump $DUMP_FILENAME

  _debug "Cleaning up old dumps of $DATABASE - keeping $KEEP_MAX_COPIES copies"
  if [ $KEEP_MAX_COPIES -ge 1 ]; then
    find ${DUMP_DIR} -name "${MYSQL_PREFIX}${SEPERATOR}${DATABASE}${SEPERATOR}*.sql.gz" -print | sort > $_TEMP-dumps
    tail -n -${KEEP_MAX_COPIES} $_TEMP-dumps > $_TEMP-keepdumps
    cat $_TEMP-dumps | while read DUMPNAME; do
      if [ $( grep -c $DUMPNAME $_TEMP-keepdumps ) -eq 0 ]; then
        _verbose "  Removing old dump file '$( echo $DUMPNAME | sed 's|.*/||' )'"
        rm $DUMPNAME
      else
        _debug "  keeping wanted dump $DUMPNAME"
      fi
    done
  fi

done


rm "/var/run/sympl-sqldump.lock"

_verbose "sympl-sqldump complete at $(date)"

_verbose " "

_exit 0