MySQL / MariaDB Dump & Restore

db_backup_restore.sh

#!/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