Skip to content

Instantly share code, notes, and snippets.

Last active September 12, 2023 04:55
  • Star 13 You must be signed in to star a gist
  • Fork 7 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
MySQL / MariaDB Dump Helper
#!/usr/bin/env bash
# MySQL / MariaDB Dump Helper
# ===========================
# FEATURES: Progress bar with ETA, support multiple databases (dump into separated files) and password as argument
# =============
# GNU Core Utilities, mysql, mysqldump, pv (
set -e
if [ $# -ne 1 ]; then
echo 1>&2 "Usage: $0 <DB_PASSWORD>"
exit 1
declare -a DB_NAME=("master" "second_db")
export MYSQL_PWD
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")
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."
Copy link

o5 commented Aug 19, 2020

Copy link

xperjon commented Dec 3, 2020

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?


Copy link

o5 commented Dec 3, 2020

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.

Copy link

xperjon commented Dec 3, 2020

Ok, thanks for the reply! 👍

Copy link

tcheredeev commented May 11, 2021

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".

Copy link

o5 commented May 12, 2021

@tcheredeev you can use Fork button

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment