migrate MySQL db

migrate.sh

#!/usr/bin/env bash

#
# This script migrates a DB from one server to another
# It should be run from the source server, the 
# destination server or an intermediate server, 
# and it assumes ssh connections are configured so that there
# is no need to insert credentials.
#
# You need to call the script as
# migrator.sh \
#    ORIGIN_SSH="'origin_server'" \
#    ORIGIN_DB_HOST="'localhost'" \
#    ORIGIN_DB_USERNAME="'root'" \
#    ORIGIN_DB_PASSWORD="''" \
#    ORIGIN_DB_NAME="'my-old-db'" \
#    ORIGIN_BKP_PATH="'~'" \
#    DESTINATION_SSH="'destination_server'" \
#    DESTINATION_DB_HOST="'localhost'" \
#    DESTINATION_DB_USERNAME="'root'" \
#    DESTINATION_DB_PASSWORD="''" \
#    DESTINATION_DB_NAME="'my-new-db'" \
#    DESTINATION_BKP_PATH="'~'"
#
# This script should be run within a `screen`, so that if our connection drops, the script continues to execute
# How to use screen:
# screen -S dbmigration                                         # to start a new screen and stay in there. Use the key sequence Ctrl-a + Ctrl-d to detach from a running screen session
# screen -S dbmigration -d -m <this-script>                     # to start a command in a screen and detach
# screen -S dbmigration -dm bash -c "sleep 10; myscript.sh"     # To run multiple commands
# screen -list                                                  # to see all screens running
# screen -r dbdump                                              # to resume a detached screen session
# screen -S dbdump -X quit                                      # to kill 'dbdump' session
# Use the key sequence Ctrl-a + Ctrl-d to detach from a running screen session

# To know the DB size in a DB server:
# mysql -u <username> -p -e 'SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema;'

ORIGIN_SSH="'origin_server'"
ORIGIN_DB_HOST="localhost"
ORIGIN_DB_USERNAME="root"
ORIGIN_DB_PASSWORD=""
ORIGIN_DB_NAME="my-db"
ORIGIN_BKP_PATH="~"

DESTINATION_SSH="'destination_server'"
DESTINATION_DB_HOST="localhost"
DESTINATION_DB_USERNAME="root"
DESTINATION_DB_PASSWORD=""
DESTINATION_DB_NAME="my-db"
DESTINATION_BKP_PATH="~"

ROW_COUNT_SQL="SELECT table_name AS TABLE_NAME, table_rows AS TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '${ORIGIN_DB_NAME}';"

ARGS=( "${@}" )
eval "${ARGS[@]}"

SQL_FILE="${ORIGIN_DB_NAME}.sql"
ZIPPED_SQL_FILE="${SQL_FILE}.gz"
ORIGIN_BKP_FILE="${ORIGIN_BKP_PATH}/${ZIPPED_SQL_FILE}"

DESTINATION_BKP_FILE="${DESTINATION_BKP_PATH}/${ZIPPED_SQL_FILE}"
DESTINATION_SQL_FILE="${DESTINATION_BKP_PATH}/${SQL_FILE}"

OPERATION=0

echo
echo "Getting records in each table of the source..."
((OPERATION++))
ORIGIN_DATA_COUNT=$(ssh "${ORIGIN_SSH}" "mysql -h ${ORIGIN_DB_HOST} -u ${ORIGIN_DB_USERNAME} -p${ORIGIN_DB_PASSWORD} ${ORIGIN_DB_NAME} -e '${ROW_COUNT_SQL}'") || exit "${OPERATION}"

#--single-transaction produces a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become
#--routines dumps all stored procedures and stored functions
#--triggers dumps all triggers for each table that has them
#--ignore-table=${ORIGIN_DB_NAME}.<some-table-name> ignores a table when doing the dump, useful to exclude log tables or corrupted tables
#--set-gtid-purged=OFF for compatibility between 5.7 and 8.0 @see https://stackoverflow.com/a/67484394
# sed 's#\`geo_coordinate\`(25)#\`geo_coordinate\`#g (For fixing spatial data, it's being ignored now)
#
# We also do "| sed 's#MyISAM#innodb#g'" because galera clusters dont replicate MyISAM tables

echo
echo "Creating the DB dump..."
((OPERATION++))
time ssh "${ORIGIN_SSH}" "mysqldump -h ${ORIGIN_DB_HOST} -u ${ORIGIN_DB_USERNAME} -p${ORIGIN_DB_PASSWORD} --ignore-table=${ORIGIN_DB_NAME}.migration_versions --ignore-table=${ORIGIN_DB_NAME}.postal_code --skip-set-charset --default-character-set=latin1 --skip-tz-utc --single-transaction --routines --triggers --set-gtid-purged=OFF ${ORIGIN_DB_NAME} | sed 's#SET utf8 COLLATE utf8_bin#SET utf8mb4 COLLATE utf8mb4_unicode_ci#g' | sed 's#utf8_unicode_ci#utf8mb4_unicode_ci#g' | sed -E 's#CHARSET=utf8([; ])#CHARSET=utf8mb4\\1#g' | sed 's#CHARSET=utf8mb4;#CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;#g' | sed 's#MyISAM#innodb#g' | sed 's#utf8mb4_unicode_ci#utf8mb4_0900_ai_ci#g' | gzip > ${ORIGIN_BKP_FILE}" || exit "${OPERATION}"
#### No data variant:
#time ssh "${ORIGIN_SSH}" "mysqldump -h ${ORIGIN_DB_HOST} -u ${ORIGIN_DB_USERNAME} -p${ORIGIN_DB_PASSWORD} --no-data --ignore-table=${ORIGIN_DB_NAME}.migration_versions --ignore-table=${ORIGIN_DB_NAME}.postal_code --skip-set-charset --default-character-set=latin1 --skip-tz-utc --single-transaction --routines --triggers --set-gtid-purged=OFF ${ORIGIN_DB_NAME} | sed 's#SET utf8 COLLATE utf8_bin#SET utf8mb4 COLLATE utf8mb4_unicode_ci#g' | sed 's#utf8_unicode_ci#utf8mb4_unicode_ci#g' | sed -E 's#CHARSET=utf8([; ])#CHARSET=utf8mb4\\1#g' | sed 's#CHARSET=utf8mb4;#CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;#g' | sed 's#MyISAM#innodb#g' | sed 's#utf8mb4_unicode_ci#utf8mb4_0900_ai_ci#g' | gzip > ${ORIGIN_BKP_FILE}" || exit "${OPERATION}"

echo
echo "transferring 1/2 ..."
((OPERATION++))
time scp "${ORIGIN_SSH}":"${ORIGIN_BKP_FILE}" ./ || exit "${OPERATION}"

echo
echo "transferring 2/2 ..."
((OPERATION++))
time ssh "${DESTINATION_SSH}" "rm -rf ${DESTINATION_BKP_PATH}/*.sql*" || exit "${OPERATION}"
((OPERATION++))
time scp ./${ZIPPED_SQL_FILE} "${DESTINATION_SSH}":"${DESTINATION_BKP_PATH}" || exit "${OPERATION}"

echo
echo "unzipping..."
((OPERATION++))
time ssh "${DESTINATION_SSH}" "gzip -dk ${DESTINATION_BKP_FILE}" || exit "${OPERATION}"

echo
echo "removing db, if exists ..."
((OPERATION++))
time ssh "${DESTINATION_SSH}" "mysql -h ${DESTINATION_DB_HOST} -u ${DESTINATION_DB_USERNAME} -p${DESTINATION_DB_PASSWORD} -e \"DROP DATABASE IF EXISTS ${DESTINATION_DB_NAME};\"" || exit "${OPERATION}"

echo
echo "creating db..."
((OPERATION++))
time ssh "${DESTINATION_SSH}" "time mysql -h ${DESTINATION_DB_HOST} -u ${DESTINATION_DB_USERNAME} -p${DESTINATION_DB_PASSWORD} -e \"CREATE DATABASE ${DESTINATION_DB_NAME};\"" || exit "${OPERATION}"

echo
echo "importing db..."
((OPERATION++))
time ssh "${DESTINATION_SSH}" "time mysql -h ${DESTINATION_DB_HOST} -u ${DESTINATION_DB_USERNAME} -p${DESTINATION_DB_PASSWORD} ${DESTINATION_DB_NAME} < ${DESTINATION_SQL_FILE}" || exit "${OPERATION}"

echo
echo "Getting records in each table of the destination..."
((OPERATION++))
DESTINATION_DATA_COUNT=$(ssh "${DESTINATION_SSH}" "mysql -h ${DESTINATION_DB_HOST} -u ${DESTINATION_DB_USERNAME} -p${DESTINATION_DB_PASSWORD} ${DESTINATION_DB_NAME} -e '${ROW_COUNT_SQL}'") || exit "${OPERATION}"

echo "======================="
echo "ORIGIN DATA COUNT"
echo "${ORIGIN_DATA_COUNT}"
echo "======================="

echo "======================="
echo "DESTINATION DATA COUNT"
echo "${DESTINATION_DATA_COUNT}"
echo "======================="

echo "======================="
echo "ORIGIN/DESTINATION DIFF"
diff <(echo "$ORIGIN_DATA_COUNT") <(echo "$DESTINATION_DATA_COUNT")
echo "======================="

echo "DONE!"