List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:June 5 2007 2:19pm
Subject:Re: Diff between restore from tar and recover from power outage
View as plain text  
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
Thread
Lock Tables QuestionDavid T. Ashley4 Jun
  • Re: Lock Tables QuestionGerald L. Clark4 Jun
    • Re: Lock Tables QuestionDavid T. Ashley4 Jun
      • RE: Lock Tables QuestionJerry Schwartz4 Jun
        • Re: Lock Tables QuestionDavid T. Ashley4 Jun
          • Re: Lock Tables QuestionGerald L. Clark4 Jun
            • Diff between restore from tar and recover from power outagemurthy gandikota4 Jun
              • Re: Diff between restore from tar and recover from power outageWm Mussatto4 Jun
                • Re: Diff between restore from tar and recover from power outageScott Tanner5 Jun
                  • Re: Diff between restore from tar and recover from power outagemos5 Jun
                    • Re: Diff between restore from tar and recover from power outageBaron Schwartz5 Jun
            • Re: Lock Tables QuestionDavid T. Ashley4 Jun
  • Re: Lock Tables QuestionBrent Baisley5 Jun
    • Re: Lock Tables QuestionDavid T. Ashley5 Jun
      • Re: Lock Tables QuestionBaron Schwartz5 Jun
        • Re: Lock Tables QuestionDavid T. Ashley5 Jun
          • Re: Lock Tables QuestionPaul McCullagh5 Jun