It's like drush sql-sync for a Drupal site, but there's no drush installed on the remote server.

sql-sync-with-no-remote-drush.sh

# Author: Evan Lovely
# Syncs the database and files from the live production site to here. Run it anywhere with `sh sync--prod->stage.sh`
# Needs to be an absolute path
LOCAL_PATH=""
SSH="[email protected]"
DB_USER=""
DB_PASS=""
DB=""
REMOTE_PATH=""

echo "# Syncing"
echo "## Production -> Staging"
echo "### Please keep an eye on this window as it will ask for passwords often and can fail if it goes unanswered for too long."
echo "Here we go! Started: $(date)"
ssh $SSH -t -o ServerAliveInterval=60 "cd public_html; echo \"Started mysqldump: $(date)\"; mysqldump -u $DB_USER -p$DB_PASS $DB > db.sql; echo \"Finished SQL Dump.\"; echo Compressing...; gzip -f db.sql"
echo "Remote Database export and compression done: $(date)"
# afplay /System/Library/Sounds/Submarine.aiff
echo "Remote Database Export & Compression Done."
echo "Fetching remote database: $(date)"
scp $SSH:$REMOTE_PATH/db.sql.gz $LOCAL_PATH
# afplay /System/Library/Sounds/Submarine.aiff
echo "SQL Dump Downloaded: $(date)"
echo "cd $LOCAL_PATH"
cd $LOCAL_PATH
echo "Unzipping..."
gunzip db.sql.gz
echo "Unzipped: $(date)"
echo "Importing to localhost..."
drush sqlc < db.sql
echo "Imported: $(date)"
echo "Cleaning up..."
rm db.sql
echo "Turning off JS & CSS Aggregation."
drush vset preprocess_css 0
drush vset preprocess_js 0
echo "Turning on devel module."
drush en devel
echo "Clearing Cache"
drush cc all
# terminal-notifier -title "Project Helper" -message "Production SQL Sync Done" -subtitle "No Drush"
# afplay /System/Library/Sounds/Submarine.aiff
echo "All done with database: $(date)"