Skip to content

Instantly share code, notes, and snippets.

@omalave

omalave/mysqlsync

Forked from samhernandez/mysqlsync
Created September 9, 2018 16:53
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save omalave/41ab9578eebb3348838801bb4a4dd7bc to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save omalave/41ab9578eebb3348838801bb4a4dd7bc to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @samhernandez samhernandez revised this gist May 6, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion mysqlsync
    View file
    Original file line number Diff line number Diff line change
    @@ -28,7 +28,7 @@ LOCAL_PASS=root
    LOCAL_HOST=localhost
    LOCAL_DB=database_name

    NOW=$(date +"%Y%d%m-%H%M")
    NOW=$(date +"%Y%m%d-%H%M")
    REMOTE_FILE="remote-$NOW-$REMOTE_DB.sql"
    LOCAL_FILE="local-$NOW-$LOCAL_DB.sql"

  2. @samhernandez samhernandez created this gist Feb 18, 2015.
    44 changes: 44 additions & 0 deletions mysqlsync
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,44 @@
    #!/bin/bash

    # 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%d%m-%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!"