14

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 used this quite 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 its last legs and is, for intents and purposes, permanently mounted as read only.

I was hoping to utilize 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 disk and transferring it is clearly out of the question.

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

I've since realized due the read only file system issue I'm experiencing I cannot even connect to MySQL to do the dump. Your feedback was great and hopefully of use to someone else in the future

Improve this question
1

3 Answers 3

Reset to default
13 
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 :-)

Improve this answer
3
  • This is a really nifty one, piping the dump directly into mysql on the remote server, essentially cloning the database is one command. Am I correct?
    – xzyfer
    Commented Jan 28, 2011 at 5:17
  • 1
    you got it ;-) of course you could always simply just push to a file however i like the dumps as they allow for you to roll to a backup if needed :-) in real time just by changing over an ip in your config files ... HUGE benefit. We do this over on our linode stuff all the time
    – Glenn Kelley
    Commented Jan 28, 2011 at 5:21
  • 1
    I feel like there could maybe be some pipe buffering issues here?
    – Phil Hollenback
    Commented Jan 28, 2011 at 5:24
11 
mysqldump ... | ssh ... "cat > out.dmp"
Improve this answer
1
  • 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.
    – harperville
    Commented Jun 9, 2014 at 15:40
4

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
Improve this answer
1
  • most useful answer for larger DBs - thx Mike!
    – Max
    Commented Jun 26, 2020 at 13:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .