Duplicate MySQL database via SSH

Question

Let's say that I have database1 and database2.

database1 - contains data

database2 - is empty.

---

I want to copy all data from database1 to database2 via SSH - duplicate database1.

What command should I use?

---

I have tried

mysqldump -u user -p database1 > database1.sql
Enter password: mysqldump: Got error: 1045: Access denied for user 'user'@'localhost' (using password: NO) when trying to connect

Answer 1

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.

Answer 2

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