Last active
October 15, 2020 23:23
-
-
Save mccombs/e6872238ac2cf79907b5 to your computer and use it in GitHub Desktop.
BASH - Sync DB | Prod -> Local
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
# Big thanks to @sam_h for this! Works like a charm. | |
# | |
# This script assumes you have ssh access to a remote server | |
# Both databases are backed up to sql files in the same directory | |
# this script is executed from. | |
# Usage: | |
# 1. Make sure this file is executable with `chmod +x mysqlsync` | |
# 2. Set the credentials for the variables at the top | |
# (Remember, no spaces around the '=' sign) | |
# 3. Run it from a directory where you'd like the backup files to go: | |
# `./mysqlsync` | |
# | |
# You may also rename this file which makes it easier per project. | |
# SSH credentials | |
SSH_USER=user | |
SSH_SERVER=site.com | |
# Remote DB credentials | |
REMOTE_USER=user | |
REMOTE_PASS=pass | |
REMOTE_HOST=localhost | |
REMOTE_DB=database_name | |
# Local DB credential | |
LOCAL_USER=root | |
LOCAL_PASS=root | |
LOCAL_HOST=localhost | |
LOCAL_DB=database_name | |
NOW=$(date +"%Y%m%d-%H%M") | |
REMOTE_FILE="remote-$NOW-$REMOTE_DB.sql" | |
LOCAL_FILE="local-$NOW-$LOCAL_DB.sql" | |
echo "Dumping remote database to $REMOTE_FILE" | |
eval "ssh $SSH_USER@$SSH_SERVER 'mysqldump -h $REMOTE_HOST -u$REMOTE_USER -p$REMOTE_PASS $REMOTE_DB' > $REMOTE_FILE" | |
echo "Dumping local database to $LOCAL_FILE" | |
eval "mysqldump -h $LOCAL_HOST -u$LOCAL_USER -p$LOCAL_PASS $LOCAL_DB > $LOCAL_FILE" | |
echo "Importing remote database into local database" | |
eval "mysql -h $LOCAL_HOST -u$LOCAL_USER -p$LOCAL_PASS $LOCAL_DB < $REMOTE_FILE" | |
echo "Done!" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment