MySQL / MariaDB Dump Helper

dump.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 1 ]; then
  echo 1>&2 "Usage: $0 <DB_PASSWORD>"
  exit 1
fi

DB_USER=username
DB_HOST=localhost
declare -a DB_NAME=("master" "second_db")

export MYSQL_PWD
MYSQL_PWD="$1"

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

for database in "${DB_NAME[@]}"; do
    db_size=$(mysql \
        -h "$DB_HOST" \
        -u "$DB_USER" \
        --silent \
        --skip-column-names \
        -e "SELECT ROUND(SUM(data_length) * 1.09) AS \"size_bytes\" \
        FROM information_schema.TABLES \
        WHERE table_schema='$database';"
    )

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

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

    log "[INFO] Done."
done

LINK: https://stackoverflow.com/a/63482716/2875783

Hi!
Nice script! I have tried it (or actually I wrote my own inspired by yours) but for the databases I have tried the db_size always differ from the actual size of the dump file. Do you have any clues? Where for example did you get the * 1.09 number from?

Regards!

It's my magic constant :) I wanted to have more precise ETA for my usual database size. Yours could be different. What I discovered it will be never 100% true, it's only ETA.

Ok, thanks for the reply! 👍

Hello!
I don't know how to do merge request in gists, so write here.
String 36 needs to be changed to "-e "SELECT IFNULL(ROUND(SUM(data_length) * 1.09), 0) AS "size_bytes" " in case of return 0 instead of NULL if database has no tables. Because in this case in your variant numfmt in string 43 fails with "invalid number NULL".

@tcheredeev you can use Fork button