db-replicate.sh

#!/bin/env bash
# db-replicate.sh (c) by Niraj Shah

# db-replicate.sh is licensed under a
# Creative Commons Attribution-ShareAlike 4.0 International License.

# You should have received a copy of the license along with this
# work. If not, see <http://creativecommons.org/licenses/by-sa/4.0/>.

# https://www.webniraj.com/2017/01/13/replicating-a-remote-mysql-database-to-local-environment-server/
# https://gist.github.com/niraj-shah/56c8a54bb4c83c50d347c1c76b45a0d7

# Shell script to replicate MySql database from REMOTE to LOCAL
# By Niraj Shah

# CONFIG - Only edit the below lines to setup the script
# ===============================

# # REMOTE DB SETTINGS
# REMOTE_USER="user"         # USERNAME
# REMOTE_PASS="password"     # PASSWORD
# REMOTE_HOST="mydomain.com" # HOSTNAME / IP
# REMOTE_DB="test_db"        # DATABASE NAME

# # LOCAL DB SETTINGS
# DB_USER="user"             # USERNAME
# DB_PASS="pass2"            # PASSWORD
# DB_HOST="localhost"        # HOSTNAME / IP
# DB_NAME="test_local"       # DATABASE NAME
# DUMP_FILE="temp.sql"       # SQL DUMP FILENAME

# DO NOT EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOU ARE DOING
# ===============================

# get remote database
if [[ -z "${REMOTE_PASS}" ]]; then
  mysqldump -h "${REMOTE_HOST}" -u "${REMOTE_USER}" "${REMOTE_DB}" >"${DUMP_FILE}"
else
  mysqldump -h "${REMOTE_HOST}" -u "${REMOTE_USER}" -p"${REMOTE_PASS}" "${REMOTE_DB}" >"${DUMP_FILE}"
fi

# drop all tables
if [[ -z "${DB_PASS}" ]]; then
  mysqldump -u "${DB_USER}" \
    --add-drop-table --no-data "${DB_NAME}" |
    grep -e '^DROP \| FOREIGN_KEY_CHECKS' |
    mysql -u "${DB_USER}" "${DB_NAME}"
else
  mysqldump -u "${DB_USER}" -p"${DB_PASS}" \
    --add-drop-table --no-data "${DB_NAME}" |
    grep -e '^DROP \| FOREIGN_KEY_CHECKS' |
    mysql -u "${DB_USER}" -p"${DB_PASS}" "${DB_NAME}"
fi

# restore new database
if [[ -z "${DB_PASS}" ]]; then
  mysql -u "${DB_USER}" "${DB_NAME}" <"${DUMP_FILE}"
else
  mysql -u "${DB_USER}" -p"${DB_PASS}" "${DB_NAME}" <"${DUMP_FILE}"
fi