mysql backups (was Re: Remote backups, reading from and writing to the same file)

[Greg 'groggy' Lehey]

On Sunday, 15 January 2006 at  4:28:15 +0100, Hans Nieser wrote:
> N.J. Thomas wrote:
>> * Hans Nieser <h.nieser at xs4all.nl> [2006-01-13 00:25:14 +0100]:
>>> Among the things being backed up are my mysql database tables. This
>>> made me wonder wether the backup could possibly get borked when mysql
>>> writes to any of the mysql tables while tar is reading from them.
>>
>> Yes. While MySQL is writing to the the database, it will put the files
>> on the disk in an inconsistent state. If you happen to copy those files
>> while they are in that state, MySQL will see a corrupted database.
>
> Thanks for the replies all. I think for the short term I will simply
> lock/shutdown my MySQL server (it is a home-server after all), in the long
> term I think I will look into snapshotting. I've also been thinking about
> just doing an SQL dump with mysqldump right before the backup, that will
> still copy along the tables which may be in an inconsistent state, but
> also the sql dump.

We (MySQL) are currently working on an online backup solution which
will address the issues you mention.  We don't have a firm date yet,
but it should be this year.  If you or anybody else has feature
requests, please let me know (preferably grog at MySQL.com, but this
address will work too).

Greg
--
When replying to this message, please copy the original recipients.
If you don't, I may ignore the reply or reply to the original recipients.
For more information, see http://www.lemis.com/questions.html
See complete headers for address and phone numbers.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20060116/13b684dc/attachment.bin