Sync prod Mysql database to stage

synd_prod_db_to_stage.sh

#!/bin/bash

#1. Add ssh key (stage key) to PROD machine
#    1.1 On stage:
#        cat ~/.ssh/id_rsa.pub
#    1.2 On prod
#        nano ~/.ssh/authorized_keys
#        * paste key*

DATE=$(date +"%Y-%m-%d")
REMOTE_HOST="user@ip"
REMOTE_FILE="dbname-${DATE}.sql.gz"
REMOTE_FILE_PATH="/path-to-backups/${REMOTE_FILE}"
LOCAL_FILE="dbname-${DATE}.sql"
LOCAL_MYSQL_DB="dbname"
LOCAL_MYSQL_USER="user"
LOCAL_MYSQL_PASSWORD="password"


#2. Copy DB backup file from prod to stage (on stage)
    echo "Fetching backup file from prod server.."
    scp $REMOTE_HOST:$REMOTE_FILE_PATH ~/
    echo " -> Done\n"

#3. Extract db backup file
    echo "Extracing backup.."
    gunzip $REMOTE_FILE
    echo " -> Done\n"

#4. Replace DB from db backup file
    echo "Replacing database.."
    mysql -u$LOCAL_MYSQL_USER -p$LOCAL_MYSQL_PASSWORD $LOCAL_MYSQL_DB < ~/$LOCAL_FILE
    echo " -> Done\n"

#5. Remove db backup
    echo "Removing temp backup.."
    rm ~/$LOCAL_FILE
    echo " -> Done\n"

#6. Done
    echo "All done!"