Backup methods

[Anatoli]

Actually, mysqldump performs a lock on the records it's dumping. If its 
for a MyISAM db, the entire table is locked. If it's for InnoDB and 
similar, an internal snapshot is created and only the records the dump 
is reading are unavailable for writing.

Cyrus could also implement a per-user lock, but in reality it doesn't 
need that complex syncro mechanisms, a simple global write lock would be 
enough (reading would not be affected, son only I, not I/O, and not to 
stop it but just to suspend). After all, the *write* lock would last 
only a second or so, the fs snapshots are almost instantaneous. If you 
can't tolerate a 1 second delay for writing in Cyrus, you are probably 
not a SME.

And you don't need to hold the data to transfer it. You can dump it 
directly to a nfs share or pass it as stdout to ssh: mysqldump | xz -9 | 
ssh remote_server "cat > /bkp/`date +%y%m%d_%H%M`.sql.xz". With a couple 
of pipes more you can encrypt the data on the fly so it's secure to 
store it in a cheap VPS overseas... or you could upload it to dropbox.

*From:* Jason L Tibbitts Iii
*Sent:* Thursday, May 10, 2018 18:41
*To:* Anatoli
*Cc:* Info-cyrus
*Subject:* Re: Backup methods

>>>>> "A" == Anatoli  <me at anatoli.ws> writes:

A> What about mysqldump > dump.sql, then mysql < dump.sql? Also a wrong
A> way and didn't have to be implemented?

No, that's exactly my point.  Thanks for making it for me!  The analog
to the way you indicated that you would like it to work would be having
the mysql server stop IO so that you can take a filesystem snapshot
while the database is in a consistent state.  But instead, the database
(like cyrus) implements a backup method which you can use to extract the
data.  And it also requires disk space to hold the backup until you can
transfer it to your backup medium.

  - J<



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.andrew.cmu.edu/pipermail/info-cyrus/attachments/20180510/277f90cd/attachment.html>