List:General Discussion« Previous MessageNext Message »
From:Eric Robinson Date:January 26 2011 2:08pm
Subject:RE: InnoDB and rsync
View as plain text  
 

	> > 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/

Thread
Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesReindl Harald21 Jan
  • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesMySQL)21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford21 Jan
    • Re: Slow query on MySQL4 server doing simple inner join of two InnoDBtablesJoerg Bruehe24 Jan
      • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
        • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford24 Jan
          • RE: Slow query on MySQL4 server doing simple inner join of twoInnoDB tablesGavin Towey25 Jan
            • Re: Slow query on MySQL4 server doing simple inner join of two InnoDB tablesKendall Gifford25 Jan
  • InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMattia Merzi25 Jan
    • Re: InnoDB and rsyncJohan De Meersman25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
      • Re: InnoDB and rsyncJohan De Meersman25 Jan
        • Re: InnoDB and rsyncReindl Harald25 Jan
        • RE: InnoDB and rsyncJerry Schwartz25 Jan
    • Re: InnoDB and rsyncSteve Musumeche25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncMySQL)26 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
    • Re: InnoDB and rsyncReindl Harald25 Jan
  • RE: InnoDB and rsyncEric Robinson25 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
    • Re: InnoDB and rsyncJohan De Meersman26 Jan
  • RE: InnoDB and rsyncEric Robinson26 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncMichael Dykman28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncReindl Harald28 Jan
  • RE: InnoDB and rsyncEric Robinson28 Jan
    • Re: InnoDB and rsyncEric Bergen1 Feb