Skip to content

Instantly share code, notes, and snippets.

@niraj-shah
Last active July 7, 2022 20:39
Show Gist options
  • Save niraj-shah/56c8a54bb4c83c50d347c1c76b45a0d7 to your computer and use it in GitHub Desktop.
Save niraj-shah/56c8a54bb4c83c50d347c1c76b45a0d7 to your computer and use it in GitHub Desktop.
Script to Replicate Database from Remote to Local
#!/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
@time4Wiley
Copy link

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!🌹

@dezren39
Copy link

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

@niraj-shah
Copy link
Author

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.

@dezren39
Copy link

💖 thank you again

@dezren39
Copy link

dezren39 commented Jan 8, 2022

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.

@niraj-shah
Copy link
Author

Thanks for sharing @dezren39.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment