Duplicate MySQL database via SSH

This will copy database from S1 to S2

mysqldump --opt <database> | gzip -c | ssh user@wherever 'cat > /tmp/yourfile.sql.gz'

Unzip file

gunzip /tmp/yourfile.sql.gz

Next you'll have to import on S2

mysql -h<host> -u<user> -p<password> < /tmp/yourfile.sql

enjoy

Duplicate a MySQL database over SSH in just one command:

mysqldump -u <local-user> -p <local-db> | gzip | ssh user@hostname \
  "gunzip | mysql -u <remote-user> -p<password> <remote-db>"

Note that you must create the remote database first.

More advanced version:

mysqldump -u <local-user> -p <local-db> | xz | pv -W | ssh user@hostname \
"tee remote-dump.sql.xz | unxz | mysql -u <remote-user> -p<password> <remote-db>"

The advanced version:

• has better compression using xz/unxz (Though take care that compression speed doesn't become a bottleneck - if xz is at 100% CPU then it has probably become a bottleneck and you might be better off with gzip)

• shows a progress indicator using pv

• saves a copy of the dump using tee

Only one problem I haven't solved is how to avoid specifying the password in the remote command. It would be really nice to be able to enter this password interactively on the command line – if anyone knows how, please chime in.