MySQL database dump from remote host without temporary file

Question

I'm trying to implement a database backup cron (other solutions welcome) in my job but I have a small problem:

I have a large database that is over 10GB in space and the current vm doesn't have space to store it in the temporary file that mysql creates.

I know I can use mysqldump with a host parameter, but my question is, when doing that does the temporary file generated by mysqldump stay at the machine that is running it or does it stay on the database server?

UPDATE: I forgot to mention that I'm trying to backup a network of websites and that some of them are behind a firewall (needing VPN access), some need server hopping to get to the database server.

Answer 1

You can run a shell script from an archive host, where you've traded password-less ssh keys with the database server. This lets you transfer the file directly over ssh, without creating any temp files on the remote database server:

ssh -C myhost.com mysqldump -u my_user --password=bigsecret \ 
  --skip-lock-tables --opt database_name > local_backup_file.sql

Obviously there are ways to secure that password on the command line, but this a method that could accomplish what you want. One advantage of this method is that it doesn't require the archive host to have access to port 3306 on the remote host.

This guy's version is cool because it also compresses the data on-the-fly before transferring it over the network, and then he uncompresses it before loading it into a local database.

ssh me@remoteserver 'mysqldump -u user -psecret production_database | \
  gzip -9' | gzip -d | mysql local_database

But that's why my version uses ssh -C, which enables its own compression algorithm and avoids extra gzip pipes.