Redirect output of mysqldump to scp

Question

I worked with a linux pro years ago who did this nifty trick. He could do a mysqldump but redirect the output to an scp/ssh connection rather than writing to disk. We utilised this quiet a bit where i used to work but I cannot remember how to do this anymore.

I'm now faced with the issue where my hard drive in my server is on it's last legs and is, for intensive purposes, permanently mounted as read only.

I was hoping to utilise this command line trickery to still be able to back up my databases on to a new server, since writing the dump to the local diska nd transferring it is clearly out of the question.

Is this actually little trick actually possible? If so what is the syntax?

I've since realised due the read only file system issue I'm experience I cannot even connect to mysql to do the dump. But your feed back was great and hopefully of use to someone else in the future

Solution 1

mysqldump dbname | ssh [email protected] "mysql -D dbname"

that should work :-)

Set up keys between the systems as well so you can do so w/o needing a login/pass :-)

Solution 2

mysqldump ... | ssh ... "cat > out.dmp"

Solution 3

I like @GlennKelley's answer, but wanted to point out the following:

We had a database that was 450GB, but only 500GB provisioned on the host. We could not export locally, so we exported remotely. We validated the output and there were hundreds of instances where the output was corrupt due to pipe buffering issues.

The best solution here, is, from the target host where you want your dump to end up on, run mysqldump but use the -h option for host. Point the host to the MySQL server and export the data using a >.

mysqldump -u root -p -h 10.1.1.199 --all-databases ...<more options>... > dump.sql

Comments

[harperville]

This one worked great for me. I set up public key authentication on my Mac OS X laptop and ran mysqldump -p <dbname> | ssh <user>@<ip_address> "cat > <filename>.sql" Exactly what I needed.

[Max]

most useful answer for larger DBs - thx Mike!