> > You need to quiesce the InnoDb background threads. One
technique is
> > mentioned here:
> >
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp
<http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp>
> aces.html
> >
> > Look for the section talking about "clean" backups.
>
> Now we're talkin. I'll check it out.
>
>
I read that section but it is not at all clear (1) how one
quiesces the
InnoDB background threads, or (2) if there is a way to keep them
quiesced while the backup is in progress.
>From what I see there:
You can make a clean backup .ibd file using the following
method:
1. Stop all activity from the mysqld
<http://dev.mysql.com/doc/refman/5.5/en/mysqld.html> server and commit
all transactions.
2. Wait until SHOW ENGINE INNODB STATUS
<http://dev.mysql.com/doc/refman/5.5/en/show-engine.html> shows that
there are no active transactions in the database, and the main thread
status of InnoDB is Waiting for server activity. Then you can make a
copy of the .ibd file.
> I would assume that "flush tables with read lock" would work
> for 1. and then you wait for 2. to happen. Probably shouldn't
> take very long, especially in the quiet moments.
Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to
"quiesce" the InnoDB background threads? When Googling this, I'm seeing
posts saying that even after a flush & lock, InnoDB keeps making changes
to certain files (metadata?) and that's why it is never really safe
(according to them) to copy the files from a running instance of MySQL.
Call me persistent (or just annoyingly thick-headed) but I am not fully
satisfied their explanations.
--Eric
Disclaimer - January 26, 2011
This email and any files transmitted with it are confidential and intended
solely for Johan De Meersman,Shawn Green (MySQL),Reindl
Harald,mysql@lists.mysql.com. If you are not the named addressee you should not
disseminate, distribute, copy or alter this email. Any views or opinions
presented in this email are solely those of the author and might not represent
those of Physicians' Managed Care or Physician Select Management. Warning:
Although Physicians' Managed Care or Physician Select Management has taken
reasonable precautions to ensure no viruses are present in this email, the
company cannot accept responsibility for any loss or damage arising from the
use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
