Skip to content

Instantly share code, notes, and snippets.

@hgraca
Last active September 10, 2023 08:30
Show Gist options
  • Save hgraca/0d2b4d1cf472c15c6ef8f0005d312002 to your computer and use it in GitHub Desktop.
Save hgraca/0d2b4d1cf472c15c6ef8f0005d312002 to your computer and use it in GitHub Desktop.
migrate MySQL db
#!/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!"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment