Script to Replicate Database from Remote to Local

replicate_db.sh

#!/bin/bash
# replicate_db.sh (c) by Niraj Shah

# replicate_db.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 [ "$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 [ "$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 [ "$DB_PASS" == "" ];
then
  mysql -u $DB_USER $DB_NAME < $DUMP_FILE
else
  mysql -u $DB_USER -p$DB_PASS $DB_NAME < $DUMP_FILE
fi

Line 27 should refer to REMOTE_PASS instead of DB_PASS

Line 27 should refer to REMOTE_PASS instead of DB_PASS

Thanks for the correction. Surprised no one else noticed in the past 3 years!

Line 27 should refer to REMOTE_PASS instead of DB_PASS

Thanks for the correction. Surprised no one else noticed in the past 3 years!

Well, I am too lazy to set a password for my local MySQL installation. 👻 Thanks for sharing the script!🌹

Hi! Thank you for the script. :-) Do you have a default license you apply to this script or others from your website?

Hi! Thank you for the script. :-) Do you have a default license you apply to this script or others from your website?

Happy to license it under CC BY-SA 4.0.

💖 thank you again

I have shared my minor (mostly useless) changes here: https://gist.github.com/dezren39/09c70d28bf61e12dcbd1933582d0e250
I commented out the set variables because I pass them in as container env vars.
I also escaped every variable. My passwords required it and some dump file cases could too, it's probably overkill to do all of them but was easier from the editor.

Thanks for sharing @dezren39.