MySQL / MariaDB Dump Helper

mysqldump-progress-single-db.sh

#!/usr/bin/env bash

# MySQL / MariaDB Dump Helper
# ===========================
# FEATURES: Progress bar with ETA, support multiple databases (dump into separated files) and password as argument

# REQUIREMENTS:
# =============
# GNU Core Utilities, mysql, mysqldump, pv (https://github.com/icetee/pv)

set -e

if [ $# -ne 2 ]; then
  echo 1>&2 "Usage: $0 <DB_PASSWORD> <DB_NAME>"
  exit 1
fi

DB_USER=root
DB_HOST=localhost
DB_NAME="$2"

export MYSQL_PWD
MYSQL_PWD="$1"

log () {
    time=$(date --rfc-3339=seconds)
    echo "[$time] $1"
}

db_size=$(mysql \
    -h "$DB_HOST" \
    -u "$DB_USER" \
    --silent \
    --skip-column-names \
    -e "SELECT IFNULL(ROUND(SUM(data_length) * 1.09), 0) AS \"size_bytes\" \
    FROM information_schema.TABLES \
    WHERE table_schema='$DB_NAME';"
)

file="$PWD/$DB_NAME.sql"
size=$(numfmt --to=iec-i --suffix=B "$db_size")
log "[INFO] Dumping database '$DB_NAME' (≈$size) into $file ..."

rm ${PWD}/${DB_NAME}_state_* 2> /dev/null || true
touch ${PWD}/${DB_NAME}_state_started

mysqldump \
    -h "$DB_HOST" \
    -u "$DB_USER" \
    --compact \
    --databases \
    --dump-date \
    --hex-blob \
    --order-by-primary \
    --quick \
    "$DB_NAME" \
| pv --size "$db_size" \
| gzip -c \
> "$file.tgz"

touch ${PWD}/${DB_NAME}_state_finished

log "[INFO] Done."