mysqldump from remote host

Question

Is it possible to dump a database from a remote host through an ssh connection and have the backup file on my local computer.

If so how can this be achieved?

I am assuming it will be some combination of piping output from the ssh to the dump or vice versa but cant figure it out.

I have replied to similar answer here: http://stackoverflow.com/questions/6564882/automate-mysqldump-to-local-computer-windows/13584425#13584425 — Ashwin A, Nov 28 '12 at 09:39

score 70 · Accepted Answer · answered Nov 27 '12 at 23:26

70

This would dump, compress and stream over ssh into your local file

ssh -l user remoteserver "mysqldump -mysqldumpoptions database | gzip -3 -c" > /localpath/localfile.sql.gz

answered Nov 27 '12 at 23:26

Michel Feldheim

17,625
5
60
77

Pierre de LESPINAY · Answer 2 · 2017-03-10T07:58:16.860

21

Starting from @MichelFeldheim's solution, I'd use:

$ ssh user@host "mysqldump -u user -p database | gzip -c" | gunzip > db.sql

edited Mar 10 '17 at 07:58

answered Sep 01 '15 at 10:54

Pierre de LESPINAY

44,700
57
210
307

Michael · Answer 3 · 2012-11-27T23:21:24.950

3

ssh -f user@server.com -L 3306:server.com:3306 -N

then:

mysqldump -hlocalhost > backup.sql

assuming you also do not have mysql running locally. If you do you can adjust the port to something else.

edited Nov 27 '12 at 23:21

answered Nov 27 '12 at 23:14

Michael

3,568
3
37
50

1

I need this in a single command though thats why i thought there would be a combination of pipes – Marty Wallace Nov 27 '12 at 23:18
1

Is this opening a tunnel on the specified port? Didn't see this approach yet – Michel Feldheim Nov 27 '12 at 23:31
1

@Feldheim yes creates a tunnel which all communications will be encrypted. As for the best answer for this question I like your approach. You get my upvote. – Michael Nov 28 '12 at 16:23

score 2 · Answer 4 · answered Nov 16 '17 at 20:58

I have created a script to make it easier to automate mysqldump commands on remote hosts using the answer provided by Michel Feldheim as a starting point:

mysqldump-remote

The script allows you to fetch a database dump from a remote host with or without SSH and optionally using a .env file containing environment variables.

I plan to use the script for automated database backups. Feel free to create issues / contribute - hope this helps others as well!

mysqldump from remote host

4 Answers4