Skip to content

Instantly share code, notes, and snippets.

@sbellver
Forked from o5/dump.sh
Last active September 26, 2022 20:49
Show Gist options
  • Save sbellver/eebf6d709192873b703b2bc2dafa5e00 to your computer and use it in GitHub Desktop.
Save sbellver/eebf6d709192873b703b2bc2dafa5e00 to your computer and use it in GitHub Desktop.
MySQL / MariaDB Dump & Restore
#!/usr/bin/env bash
# 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 "Uso: $0 <DB_NAME>"
echo "Qué BD hay que copiar?"
read DB_NAME
else
DB_NAME="$1"
fi
#ORIGIN TO DUMP
DB_USER=user
DB_HOST=localhost
export MYSQL_PWD
MYSQL_PWD=password
#SERVER TO RESTORE
DB_USER_LOCAL=user
DB_HOST_LOCAL=localhost
export MYSQL_PWD_LOCAL
MYSQL_PWD_LOCAL=password
dir=/root/scripts/db
#Se puede pasar las DB en un listado
#declare -a DB_NAME=("master" "second_db")
#O se puede pasar todas las DB de ORIGIN
#DB_NAME=`mysql -u$USER -h$HOST -p$PW --skip-column-names -e'SHOW DATABASES'`
read -p "Quieres restaurar $DB_NAME en el servidor al finalizar el dump? (y/n) " yn
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.14) AS \"size_bytes\" \
FROM information_schema.TABLES \
WHERE table_schema='$database';"
)
file="$dir/$database.sql"
size=$(numfmt --to=iec-i --suffix=B "$db_size")
log "[INFO] Copiando database '$database' (≈$size) en $file ..."
echo "SET FOREIGN_KEY_CHECKS = 0;" > $file
mysqldump \
-h "$DB_HOST" \
-u "$DB_USER" \
--compact \
--databases \
--dump-date \
--hex-blob \
--order-by-primary \
--quick \
--add-drop-table \
"$database" \
| pv --size "$db_size" \
>> "$file"
#RDS to MariaDB fix
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' $dir/$DB_NAME.sql
echo "SET FOREIGN_KEY_CHECKS = 1;" >> $file
log "[INFO] Done."
case $yn in
[yYsS] )
echo "Comenzamos la importación de $database"
DB_EXIST=`mysql -u$DB_USER_LOCAL -p$MYSQL_PWD_LOCAL -e "SHOW DATABASES" | grep -F -w $database`
if [ "$DB_EXIST" == "$database" ];
then
echo "La base de datos $DB_EXIST existe"
mysql -u$DB_USER_LOCAL -p$MYSQL_PWD_LOCAL -h$DB_HOST_LOCAL -e "DROP DATABASE $DB_NAME"
mysql -u$DB_USER_LOCAL -p$MYSQL_PWD_LOCAL -h$DB_HOST_LOCAL -e "CREATE DATABASE $DB_NAME"
else
echo "La base de datos $DB_EXIST NO existe, la creamos"
#mysql -u$DB_USER_LOCAL -p$MYSQL_PWD_LOCAL -h$DB_HOST_LOCAL -e "DROP DATABASE $DB_NAME"
mysql -u$DB_USER_LOCAL -p$MYSQL_PWD_LOCAL -h$DB_HOST_LOCAL -e "CREATE DATABASE $DB_NAME"
fi
pv $dir/$DB_NAME.sql | mysql -u$DB_USER_LOCAL -p$MYSQL_PWD_LOCAL -h$DB_HOST_LOCAL --default-character-set=utf8 -f $DB_NAME < $dir/$DB_NAME.sql
echo "Restauración finalizada"
echo
echo "Asegúrate de cambiar DNS's o configuación del site para que apunte al nuevo servidor"
break;;
[nN] ) echo "NO se ha restaurado $DB_NAME"
echo Proceso finalizado...;
break;;
* ) echo "No hacemos nada, no te he entendido";;
esac
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment