mos wrote:
> At 09:59 PM 6/4/2007, you wrote:
>> If mysql has been shut down, and given enough time for the tables to
>> be flushed to disk, then backing up the data dir using tar is fine. You
>> can tar the data dir without shutting down, providing all transactions
>> have been stopped and the tables have been flushed to disk. This is how
>> we backup our servers, and we haven't had an issue yet (restoring daily
>> to a testing environment).
>
> Scott,
>
> What if you stop the transactions and updates to the tables, and flush
> the cache to disk, BUT leave the tables open for reading? Can you then
> do a backup of the files during when only Select statements are used on
> them? Then when the backup is over, re-allow updates to the tables? I'm
> thinking of doing this for a webserver so I don't have to shut it down
> for reading, but just for updates for a few minutes while the backup is
> underway.
I haven't seen you say what storage engine you are using. If you're using InnoDB, the
answer is no. There are background threads that do things even when the database is
not actively being updated.
Peter Zaitsev wrote a good article on hot backups with a snapshot-capable filesystem
like LVM. I personally think this is probably the best way to take hot backups at the
moment. Another alternative is to backup from a slave, but you need to make sure the
slave has the same data as the master. (shameless plug: mysql-table-checksum does this).
Baron
