Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@o5
Last active February 21, 2024 04:52
Show Gist options
  • Star 13 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
  • Save o5/8bf57b3e5fa4649a81a4449787ba3691 to your computer and use it in GitHub Desktop.
Save o5/8bf57b3e5fa4649a81a4449787ba3691 to your computer and use it in GitHub Desktop.
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
# 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
@o5
Copy link
Author

o5 commented Aug 19, 2020

@xperjon
Copy link

xperjon commented Dec 3, 2020

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!

@o5
Copy link
Author

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.

@xperjon
Copy link

xperjon commented Dec 3, 2020

Ok, thanks for the reply! 👍

@tcheredeev
Copy link

tcheredeev commented May 11, 2021

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

@o5
Copy link
Author

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