> > 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@stripped. 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/
