RE: InnoDB and rsync
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 aces.html Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoBD background threads per Shawn's message above? -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com,Shawn Green (MySQL). 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
FLUSH TABLES WITH READ LOCK does work consistently on MyISAM and my experience confirms this. I do remember reading something on this list eons ago that asserted that it is not necessarily effective on InnoDB due to it's multi-versioning.. uncommited transactions might be caught in an inconsistent state. In one extreme instance, having a few terabytes of data across several instances (on distinct hosts), I was required to do a full-refactoring data migration with an absolute limitation on allowable downtime. Among the technique which I used (and I can't take credit for this one) was to use rsync on the live server for innodb files (this phase took a very long time, but did not interfere with operations). The result of this phase was, as you would expect, a set a seriously broken files which were notheless very similar to the correct files. When that phase was complete, I shut the server down and did another rsync. It required perhaps a minute or 2, but the result was 100% clean innodb data files which satisfied my downtime limitations. FLUSH TABLES WITH READ LOCK might suffice if all transactions are completed/rolled-back but I would stil advise that you scan SHOW ENGINE INNODB STATUS but I would carefully experiment with that. As for maat-kit, don't let the disclaimers discourage you. If you read the disclaimers carefully on any product (at least those released with the benefit(?) of legal advice), you would have a hard time trusting any of it with your enterprise. The maat-kit team (and Baron Schwartz in particular) and quite simply the *best* MySQL engineering team out there, with the possible exception of the vendor. I would not hesitate to trust them with my data. - michael dykman On Fri, Jan 28, 2011 at 11:04 AM, Robinson, Eric eric.robin...@psmnv.com wrote: 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 aces.html Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoBD background threads per Shawn's message above? -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com,Shawn Green (MySQL). 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: InnoDB and rsync
In one extreme instance, having a few terabytes of data across several instances (on distinct hosts), I was required to do a full-refactoring data migration with an absolute limitation on allowable downtime. Among the technique which I used (and I can't take credit for this one) was to use rsync on the live server for innodb files (this phase took a very long time, but did not interfere with operations). The result of this phase was, as you would expect, a set a seriously broken files which were notheless very similar to the correct files. When that phase was complete, I shut the server down and did another rsync. It required perhaps a minute or 2, but the result was 100% clean innodb data files which satisfied my downtime limitations. FLUSH TABLES WITH READ LOCK might suffice if all transactions are completed/rolled-back but I would stil advise that you scan SHOW ENGINE INNODB STATUS but I would carefully experiment with that. You just described almost the exact procedure that I described at the beginning of this thread, except I use MyISAM so my question was whether the same technique could work with InnoDB. It sounds like it very well could if combined with SHOW ENGINE INNODB STATUS. I will definitely test it to be sure. As for maat-kit, don't let the disclaimers discourage you. If you read the disclaimers carefully on any product (at least those released with the benefit(?) of legal advice), you would have a hard time trusting any of it with your enterprise. The maat-kit team (and Baron Schwartz in particular) and quite simply the *best* MySQL engineering team out there, with the possible exception of the vendor. I would not hesitate to trust them with my data. I will definitely look at it again. Thanks. --Eric Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Michael Dykman,mysql@lists.mysql.com,Shawn Green (MySQL). 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 28.01.2011 17:04, schrieb Robinson, Eric: Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoBD background threads per Shawn's message above? Damned start your brain, read documentations and hear what peopole say http://dev.mysql.com/doc/refman/5.1/en/alter-table-problems.html If you use ALTER TABLE on a transactional table or if you are using Windows or OS/2, ALTER TABLE unlocks the table if you had done a LOCK TABLE on it. This is done because InnoDB and these operating systems cannot drop a table that is in use. And you will build your business on a hotcopy with external tools beause you do not trust replication? laughable! Do what you want, but dont come back and cry if all goes down You were told in which way you can use rsync with minimum downtime or that replication can be used to stop only salves for a short time and you are acting like a child mama i will do what i said the whole time signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
And you will build your business on a hotcopy with external tools beause you do not trust replication? laughable! Do what you want, but dont come back and cry if all goes down You were told in which way you can use rsync with minimum downtime or that replication can be used to stop only salves for a short time and you are acting like a child mama i will do what i said the whole time Our current model has been working well since 2006. We will be careful to verify the reliability of any proposed changes. Have a great day! -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB and rsync
Am 28.01.2011 22:30, schrieb Robinson, Eric: Our current model has been working well since 2006. We will be careful to verify the reliability of any proposed changes. Have a great day! this is ok because MyISAM is so simple that you can even without any flushes make a copy while the server is running and after a repair table on the destination machine all tables are useable With InnoDB it is much difficult because table spaces Eeven with innodb_file_per_table there are dependencies of the table-files and ibdata1 in the main datadir If there is only a minimal problem it is possible that mysqld will not start and if this is a time where you need your backup really you would like to die :-) Even if there would be documentaed ways to rsync while the server is running i would never ever do that because it is so hot that a simple mysql-bug in a later release could break it temporarliy and you would not notice this, so you sgould simply go a safe way * flush atbles * rsync while mysqld is running * stop mysqld * second rsync With this steps you can copy real big databases with a minimum downtime, to reduce this again select a local folder as target if your disks are fast and after mysqld is runnign again you can sync this copy to another machine without stress signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
* flush atbles * rsync while mysqld is running * stop mysqld * second rsync Unless we can verify 100% that there is a safe way to do it without shutting down MySQL, then I'm sure the approach you described above is the one we will end up with. Thanks for your input. -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
