Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Question

Is it possible to pipe the result of a mysqldump straight to rsync as the source argument?

Conceptually, I was thinking something like:

mysqldump -u root -p database_to_backup > db_backup_file.sql | sudo rsync -avz db_backup_file.sql myuser@mysite.com:/var/www/db_backup_file.sql

I've seen people pipe the result to mysql for their one liner backup solution, but I was curious if it was possible with rsync. You know--- cause rsync is magic :)

Thanks for your time!

Answer 1

It's not clear exactly what you're trying to accomplish, but I'll take a couple of guesses and see how close I get.

If you're wanting to let rsync magically handle the difference between your local dump file and a remote dump file, you could just replace the | with && so that if mysqldump finishes without an error, then rsync will transfer the differences after the backup is complete, and if mysqldump fails, the synch won't happen.

On the other hand, if you're just wanting to transfer the dump file across the network and drop it somewhere other than the local machine, then this is an option:

mysqldump -u root -p database_to_backup | ssh myuser@mysite.com 'cat > /var/www/db_backup_file.sql'

...where /var/www/db_backup_file.sql is a path on the remote machine.

Or if you wanted to save it both locally and remotely at the same time:

mysqldump -u root -p database_to_backup | tee db_backup_file.sql | ssh myuser@mysite.com 'cat > /var/www/db_backup_file.sql'

Answer 2

Even if this were possible without any functionality issues, I would still advise against it because of one thing, ..., COMMUNICATION !!!

Here is what I mean: For a database that has many MyISAM tables, a crucial step in the reload of a MyISAM table that can tie down communication is the step

ALTER TABLE tablename ENABLE KEYS;

This step rebuilds all non-unique indexes for a MyISAM table. Usually, in a mysqldump, the next step would be UNLOCK TABLES;. Nothing else can happen, including UNLOCK TABLES; until all indexes for the MyISAM are rebuilt. Then, UNLOCK TABLES; can be run. Then, on to the next table.

The communication between pipes must be maintained throughout the lifetime of this SQL command. For a very large MyISAM table, or a MyISAM tables with a lot of indexes, there is always the small possibility of timeouts between pipes due to the possibility of a long ENABLE KEYS step.