Skip to content

Instantly share code, notes, and snippets.

@mccombs
Last active October 15, 2020 23:23
Show Gist options
  • Save mccombs/e6872238ac2cf79907b5 to your computer and use it in GitHub Desktop.
Save mccombs/e6872238ac2cf79907b5 to your computer and use it in GitHub Desktop.
BASH - Sync DB | Prod -> Local
#!/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