bin/mkmysqldump

   1 #!/bin/sh
   2 #
   3 # mkmysqldump -- dump MySQL (master) data
   4 # Copyright (c)2015 Alexander Barton <alex@barton.de>
   5 #
   6
   7 NAME=$(basename "$0")
   8
   9 # Defaults
  10 MYHOST="localhost"
  11 MYUSER="root"
  12 MYPW=""
  13 OUTFILE="$(hostname -s)-$(date "+%Y%m%d-%H%M%S").sql"
  14 STATS=""
  15 COMPRESS=""
  16
  17 Help() {
  18         echo "$NAME [<options> ...]"
  19         echo
  20         echo "  --host|-h <host>"
  21         echo "      MySQL server hostname [\"$MYHOST\"]."
  22         echo "  --user|-u <user>"
  23         echo "      MySQL server user [\"$MYUSER\"]."
  24         echo "  --password|-p <password>"
  25         echo "      MySQL server password [\"$MYPW\"]".
  26         echo "  --outfile|-o <file>"
  27         echo "      Path and name of SQL dump file [\"$OUTFILE\"]."
  28         echo "  --outdir|-d <directory>"
  29         echo "      Directory for automatically named dump files."
  30         echo "  --summary|-s"
  31         echo "      Display file and file system status summary."
  32         echo "  --gzip"
  33         echo "      Compress SQL dump file using gzip(1)"
  34         echo "  --bzip2"
  35         echo "      Compress SQL dump file using bzip2(1)"
  36         echo "  --xz"
  37         echo "      Compress SQL dump file using xz(1)"
  38         echo
  39 }
  40
  41 Usage() {
  42         Help
  43         exit 2
  44 }
  45
  46 for cmd in mysql mysqldump; do
  47         if ! which "$cmd" >/dev/null 2>&1; then
  48                 echo "$NAME: \"$cmd\" command not found!"
  49                 exit 1
  50         fi
  51 done
  52
  53 while [ $# -gt 0 ]; do
  54         case "$1" in
  55           "--host"|"-h")
  56                 [ $# -ge 2 ] || Usage
  57                 MYHOST="$2"
  58                 shift 2
  59                 ;;
  60           "--user"|"-u")
  61                 [ $# -ge 2 ] || Usage
  62                 MYHOST="$2"
  63                 shift 2
  64                 ;;
  65           "--password"|"-p")
  66                 [ $# -ge 2 ] || Usage
  67                 MYPW="$2"
  68                 shift 2
  69                 ;;
  70           "--outfile"|"-o")
  71                 [ $# -ge 2 ] || Usage
  72                 OUTFILE="$2"
  73                 shift 2
  74                 ;;
  75           "--outdir"|"-d")
  76                 [ $# -ge 2 ] || Usage
  77                 OUTFILE="$2/$(hostname -s)-$(date "+%Y%m%d-%H%M%S").sql"
  78                 shift 2
  79                 ;;
  80           "--gzip")
  81                 COMPRESS="gzip -v"
  82                 shift
  83                 ;;
  84           "--bzip2")
  85                 COMPRESS="bzip2 -v"
  86                 shift
  87                 ;;
  88           "--xz")
  89                 COMPRESS="xz -v"
  90                 shift
  91                 ;;
  92           "--summary"|"-s")
  93                 STATS=1
  94                 shift
  95                 ;;
  96           "--help")
  97                 Help
  98                 exit 0
  99                 ;;
 100           *)
 101                 Usage
 102         esac
 103 done
 104
 105 echo "Dumping MySQL server on \"$MYHOST\" (user \"$MYUSER\"):"
 106 echo "Started: $(date)"
 107 umask 0077
 108
 109 [ -n "$MYPW" ] && PWSWITCH="--password='$MYPW'" || PWSWITCH=""
 110
 111 echo "Getting list of databases from server ..."
 112 DATABASES=$(
 113         mysql -h "$MYHOST" -u "$MYUSER" $PWSWITCH -e 'show databases' \
 114          | grep "^\| " | cut -d' ' -f2 | grep -v "Database" \
 115          | grep -v "information_schema" \
 116          | grep -v "performance_schema" \
 117          | grep -v "mysql"
 118 )
 119 if [ $? -ne 0 ]; then
 120         echo "Failed to get list of databases! Aborting!"
 121         exit 1
 122 fi
 123
 124 echo "Dumping SQL data to file \"$OUTFILE\" ..."
 125 CMD="mysqldump \
 126         -h $MYHOST \
 127         -u $MYUSER \
 128         $PWSWITCH \
 129         --master-data=1 \
 130         --databases $DATABASES"
 131
 132 echo "Start date: $(date)"
 133 echo $CMD
 134 $CMD >"$OUTFILE"; r=$?
 135 if [ -n "$COMPRESS" -a -s "$OUTFILE" -a $r -eq 0 ]; then
 136         echo "Dump done: $(date)"
 137         echo "Compressing dump file ($COMPRESS) ..."
 138         $COMPRESS "$OUTFILE"
 139 fi
 140 echo "End: $(date)"
 141 echo
 142
 143 if [ -n "$STATS" ]; then
 144         echo "SQL dump file"
 145         for f in "$OUTFILE"*; do
 146                 ls -lh "$f"*
 147         done
 148         echo
 149         df -h "$(dirname "$OUTFILE")"
 150         echo
 151 fi
 152
 153 [ $r -eq 0 ] \
 154         && echo "Dump command exited with code 0, success." \
 155         || echo "Dump command FAILED with code $r!" >&2
 156
 157 echo
 158 exit $r