This script will pipe a database dump, with optional restricted tables across SSH to a MySQL server on a remote host. It uses bzip2 encryption to speed up the transfer. Should be easy enough to use xz instead, but for legacy compatibility I've used bzip2. Should work with any two systems which have bzip2 >= 0.9. This can be used to import a mast…

sqlpipe.sh

#!/bin/bash

# Parameters
LSQLUSER="local_sql_user"
LSQLPW="local_sql_password"
DBNAME="db_name"
TABLELIST="example users sessions posts migrations" # For all tables, set to empty
ROSUSER="remote_os_user"
RHOST="remote-host.tld"
RSQLUSER="remote_sql_user"
RSQLPW="remote_sql_password"

# Command
echo "mysqldump -u $LSQLUSER -p$LSQLPW $DBNAME $TABLELIST | bzip2 -c | ssh $ROSUSER@$RHOST 'bunzip2 | mysql -u $RSQLUSER -p$RSQLPW $DBNAME'"