Sync remote mysql database to local over ssh

mysqlsync

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

hello i have an ssh key to add it , is not problem
But how i can add the auto passphrase in the script ?

I just wanted the opposite .... importing local database into remote ... would be really helpful in development mode but dangerous after going live ...

Uh being lazy is awesome, pushing the database from local -> server is magic,

#!/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=emad
SSH_SERVER=kassoumafzc.com

# Remote DB credentials
REMOTE_USER=adventurelandsh
REMOTE_PASS=A*N@x~kT9]HF
REMOTE_HOST=localhost
REMOTE_DB=adventurelandsharjah

# Local DB credential
LOCAL_USER=root
LOCAL_PASS=root
LOCAL_HOST=localhost
LOCAL_DB=adventurelandsharjah

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 --column-statistics=0 > $LOCAL_FILE"

echo "Uploading local database to server"
eval "scp $LOCAL_FILE $SSH_USER@$SSH_SERVER:/home/$SSH_USER/$LOCAL_FILE"

echo "Importing local database into remote database"
eval "ssh $SSH_USER@$SSH_SERVER 'mysql -h $REMOTE_HOST -u$REMOTE_USER -p$REMOTE_PASS $REMOTE_DB < /home/$SSH_USER/$LOCAL_FILE'"

echo "Done!"

echo "Dumping local database to $LOCAL_FILE"
eval "mysqldump -h $LOCAL_HOST -u$LOCAL_USER -p$LOCAL_PASS $LOCAL_DB --column-statistics=0 > $LOCAL_FILE"

Thank you for --column-statistics=0 )