Re: InnoDB and rsync
I skimmed over this thread and I think I can help clarify the innodb, rsync, and lvm situation. The basic issue with just running rsync on the files under a running mysqld is that the rsync will copy different parts of files at different points in time. This means that it could sync things to disk before and after rsync has read that part of a file which, when restored, will be corrupted. This is the worst case scenario. The next best thing is to run a FLUSH TABLES WITH READ LOCK (global read lock) and maintain the lock while the rsync is running. This works fine for myisam tables because all file activity to myisam tables is stopped while a global read lock is held. This isn't guaranteed to work with innodb. Internally to mysql flush tables with read lock only stops queries from acquiring write locks which let them modify tables. This won't make innodb hold still for a few different reasons. First a select query in innodb can actually modify data files. A select on a page with unmerged records from the insert buffer will cause innodb to merge those records before making the page available for selects. This causes some disk i/o. If this happens while rsync is running the resulting backup can be corrupted. So even while holding a global read lock and only running selects innodb can write data. The master thread(s) perform background tasks such as flushing dirty pages, merging the insert buffer and purging old records whenever innodb feels like there is spare i/o capacity. These threads don't know how to hold still during a global read lock and can corrupt a backup if it were taken with rsync. The safest way to create a backup without using something like XtraBackup is to get a snapshot at the filesystem level or below that at the block device level. This is effectively what LVM does. When you create a LVM snapshot it freezes that device at a single point in time. When you restore the backup innodb goes through it's recovery procedure as if the power went out. Some possible solutions to this were discussed on the internals list a few years ago. I'm not sure what has been implemented since then. The list thread is here: http://lists.mysql.com/internals/35527 -Eric On Fri, Jan 28, 2011 at 1:59 PM, Robinson, Eric eric.robin...@psmnv.com wrote: * 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=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- 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
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
Re: InnoDB and rsync
On Wed, Jan 26, 2011 at 6:58 AM, Robinson, Eric eric.robin...@psmnv.comwrote: 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 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.htmlserver and commit all transactions. 2. Wait until SHOW ENGINE INNODB STATUShttp://dev.mysql.com/doc/refman/5.5/en/show-engine.htmlshows 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. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
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 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/
Re: InnoDB and rsync
Am 25.01.2011 05:37, schrieb Robinson, Eric: Is there a way to safely backup an InnoDB database using rsync? Not without stop mysqld Foregt it, do not try it and stop searching if you do not waste time If you understand how innodb works you will see that this is not possible by design your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? you can get a fresgh backup of the master with the following method but nobody do that daily - this is only for init/reinit a salve * rsync on master in another local folder while running * stop master * remove bin-logs * second rsync to get last changes * start master * rsync to final destination When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. why in the world do you not leave the slaves in peace and backup them to another media because you can stop them as long you want for a consistent backup and after starting the last changes from the master are applied Right now we have a very efficient and reliable way to backup 240+ separate instances of MySQL with MyISAM tables. The databases range in size from .5GB to 16GB. During this time, users can still access the system, so our customers can work 24x7. In the process, we also refresh 240+ slave instances with a perfect byte-for-byte replica of the master databases. The whole thing takes about 30 minutes. Here's how we do it. Late at night when the number of users on the system is low, we do the following for each of the 240+ instances of MySQL... 1. Shut down the slave and remove all replication-related log files. 2. Perform an rsync of the master's data directory to the slave. Users may be making changes to tables during this rsync. 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET MASTER. 4. Perform a second rsync of the data directory from the master to the slave to copy any user changes that happened during step 2. This usually completes in a few seconds, often less than 1. If any users were trying to insert records at this exact moment, their application may appear to pause very briefly. 5. Start the slave. When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. Like I said, the whole process takes about 30 minutes because the rsync algorithm only copies the byte-level changes. IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING INNODB? I've been reading about InnoDB hot copy and other approaches, but none of them seem to work as well as the approach I have worked out with MyISAM. Unfortunately, my software wants to force us to switch to InnoDB, so I'm really stuck. If we have to switch to InnoDB and we cannot come up with a method for doing fast, rsync-style backups, it will probably mean huge, costly, and unnecessary changes to our infrastructure. Any help will be GREATLY appreciated. -- Eric Robinson Disclaimer - January 24, 2011 This email and any files transmitted with it are confidential and intended solely for 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/ -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. why in the world do you not leave the slaves in peace and backup them to another media because you can stop them as long you want for a consistent backup and after starting the last changes from the master are applied See my comment above. (But also we cannot stop them as long as we want because the slaves are used for running reports. Using my approach, each slave is down for about 30 seconds. The masters are not brought down at all.) If you understand how innodb works you will see that this is not possible by design I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) -- Eric Robinson Disclaimer - January 25, 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
2011/1/25 Robinson, Eric eric.robin...@psmnv.com: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! However, this could be a solution for your problem (maybe) http://www.pythian.com/news/5113/video-building-a-mysql-slave-and-keeping-it-in-sync/ if you watch the movie, at ~40 minutes, you can see a slide What causes slave to get out of sync... Greetings, Mattia. -- 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
On Tue, Jan 25, 2011 at 3:00 PM, Robinson, Eric eric.robin...@psmnv.comwrote: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The Interesting. I never heard of that, and can't, at first glance, seem to find a lot of useful things on Google. Could you explain what you mean ? The one thing I can think of, would be the fact that your rows are not guaranteed to be in the same disk blocks, or even necessarily in the same data block of your file. This in itself doesn't really pose a problem for backups, though, afaik ? I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no It doesn't, exactly, no; but afaik no actual data will be written. Some metadata may not be fully sync, but I do not believe a lot could happen that the recovery when you start your slave can't fix. Still, the issue is there. really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) As a stopped MySQL can't update the files or keep data in memory, that should be safe, yes. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB and rsync
Am 25.01.2011 15:00, schrieb Robinson, Eric: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. There is nothing drifting and nobody cares if the files on both servers are binary identical, the data must be consistent and it is binlog-format = ROW For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. sorry but these people have no plan My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves jesus christ nobody cares if they are binary replica as long as the data is consistent and ident I fail to see how this is crippled. It is crippled because you do not understand the sense of replication if you reinit it every day See my comment above. (But also we cannot stop them as long as we want because the slaves are used for running reports. so start another slave on the machine with his own socket for backups, i have running on all dedicated backup-servers two instances - one is useable r/w and the other one without tcp is the replication-slave, every hour the salve is stopped and datadir mirrored to the r/w-instance Using my approach, each slave is down for about 30 seconds. The masters are not brought down at all. and if you running a clean solution the salves are never down but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? no, it is a database and not designed for access from external software as long as the database is running Is stopping the service really the only way? yes, and not only for innodb try to copy oracle, postgresql, ms-sql :-) if you do not stop the service you can be sure that the backup is not useable or missing data, even if there would exist a mode sync all to disk nobody would officially support copy datafiles while the service is running, even with myisam nobody will do that And even if I stop the service, is rsync totally safe with InnoDB? why not? the server is down and you copy the whole datadir what can be unsafe there? signature.asc Description: OpenPGP digital signature
Re: InnoDB and rsync
jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: InnoDB and rsync
On 1/25/2011 8:00 AM, Robinson, Eric wrote: your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? -- 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
Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? I looked at Maatkit a year or so ago. It looked pretty interesting, but then I started reading the disclaimers carefully and they scared the bejeepers out of me. Warnings about data corruption and whatnot. I'll check it out again. (I was actually looking for it this morning but could not remember the name, so thanks for the reminder.) -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for st...@internetretailconnection.com,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 25.01.2011 15:56, schrieb Johan De Meersman: jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. Where exactly do you see any problem? * the master writes his bin-log in row format * the slaves writes his relay-log and managing inserts updates * lvm makes a snapshot of relay-log / datafiles There is none and if there could be one LVM must be broken because a snapshot has to be consistent So if you stop the slave, make the snahpshot and start the slave again there are all buffers written to the vfs-layer and the snapshot must have a defined state. Only if the db-server is running and have some data in memory cache you could have any troubles and that is why not copy the files as long the server is running Nobody out there makes a copy of database files while the server is running, really nobody! -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, January 25, 2011 9:56 AM To: Reindl Harald Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: InnoDB and rsync jesus christ nobody cares if they are binary replica as long as the data is consistent and ident Actually, I can see this being an issue if you're using LVM snapshot backups or another similar technique - if the datafiles aren't all identical you won't be able to restore to any machine from a single backup. [JS] I don't get it. Isn't this like saying that you can't substitute a dictionary with a red cover for a dictionary with a blue cover? If you do a complete LVM restore, then you have complete and (hopefully) consistent copy of your files, even if the volume images were not identical; in fact, they almost certainly will NOT be identical at the disk-image level. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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
There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Mattia Merzi,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
nobody cares if they are binary replica as long as the data is consistent and ident Like I said, I'm no expert on this, but my approach seems like the only way to 100% absolutely sure that the data on the slave is in fact consistent and identical to the data on tha master. so start another slave on the machine with his own socket for backups You say that like it doesn't mean a huge amount of additional work, expense, and complexity. We currently have 240+ master MySQL instances and are adding them at a rate of several per week. Based on everything you've said so far, I still prefer my solution. I just need a way to make the same thing work with InnoDB. -- Eric Robinson Disclaimer - January 25, 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 25.01.2011 16:56, schrieb Robinson, Eric: You say that like it doesn't mean a huge amount of additional work, expense, and complexity. We currently have 240+ master MySQL instances and are adding them at a rate of several per week. 240 mysql-servers? why there is no consolidation? Based on everything you've said so far, I still prefer my solution. I just need a way to make the same thing work with InnoDB. this is simply impossible -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
240 mysql-servers? why there is no consolidation? I said 240+ mysql *instances*, not servers. It's actually just 3 physical servers (not counting standby cluster nodes). just need a way to make the same thing work with InnoDB. this is simply impossible That is very unfortunate. Disclaimer - January 25, 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 25.01.2011 18:38, schrieb Robinson, Eric: 240 mysql-servers? why there is no consolidation? I said 240+ mysql *instances*, not servers. It's actually just 3 physical servers (not counting standby cluster nodes). 240 mysql-instances on 3 physical hosts? what crazy setup is this please? sorry but your smallest problem is really innodb per rsync just need a way to make the same thing work with InnoDB. this is simply impossible That is very unfortunate. The whole world can work with replication-slaves and you are the only one who installing an endless count of mysql-services instead a hand of large instances - i would think that not all others are ghost-drivers and nobody outside mysql would ever think of backup a running db-server signature.asc Description: OpenPGP digital signature
RE: InnoDB and rsync
240 mysql-instances on 3 physical hosts? what crazy setup is this please? Processors average 90% idle, peaks are low, iowait is low, the system is not swapping, response time is good, and our users are happy all around the country. What is crazy about that? The whole world can work with replication-slaves and you are the only one who installing an endless count of mysql-services instead a hand of large instances I don't know how the rest of the world does it, but we have been doing it like this since 2006 and it has worked great and we have never regretted having multiple instances of mysql. In fact, it is really great because we can maintain each customer's service individually, stop and start mysql without affecting other customers, turn query logs on and off for each customer, customize performance parameters, and so on. I can maintain a customer's database right in the middle of a production day and the other customers won't even notice! It has been great being able to do all these things. outside mysql would ever think of backup a running db-server Then you're just not Googling very well. :-) --Eric Disclaimer - January 25, 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
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- 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
On 1/25/2011 09:00, Robinson, Eric wrote: ... I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html Look for the section talking about clean backups. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- 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
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. Sorry, no. The software is a canned medical application so we cannot easily inspect the queries that could have been causing the problem. Even though we could capture them in various ways (sniffer, proxy, query logs) it would not be easy to isolate the culprits out of the tens of thousands issued every day. And it was a year or more ago. We have not had the problem since we started rsyncing. :-) Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Shawn Green (MySQL),Mattia Merzi,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
I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) 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 Look for the section talking about clean backups. Now we're talkin. I'll check it out. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for 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/ -- 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
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 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. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Robinson, Eric,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/ -- 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
I suspect the same trick might work with InnoDB (with pretty much the same caveats), but you'd be best off setting innodb-file-per-table - I'm sure you've already seen that the large datafiles are a hindrance to smooth rsyncing :-) Make sure to test extensively, though. On Tue, Jan 25, 2011 at 5:37 AM, Robinson, Eric eric.robin...@psmnv.comwrote: Is there a way to safely backup an InnoDB database using rsync? Right now we have a very efficient and reliable way to backup 240+ separate instances of MySQL with MyISAM tables. The databases range in size from .5GB to 16GB. During this time, users can still access the system, so our customers can work 24x7. In the process, we also refresh 240+ slave instances with a perfect byte-for-byte replica of the master databases. The whole thing takes about 30 minutes. Here's how we do it. Late at night when the number of users on the system is low, we do the following for each of the 240+ instances of MySQL... 1. Shut down the slave and remove all replication-related log files. 2. Perform an rsync of the master's data directory to the slave. Users may be making changes to tables during this rsync. 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET MASTER. 4. Perform a second rsync of the data directory from the master to the slave to copy any user changes that happened during step 2. This usually completes in a few seconds, often less than 1. If any users were trying to insert records at this exact moment, their application may appear to pause very briefly. 5. Start the slave. When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. Like I said, the whole process takes about 30 minutes because the rsync algorithm only copies the byte-level changes. IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING INNODB? I've been reading about InnoDB hot copy and other approaches, but none of them seem to work as well as the approach I have worked out with MyISAM. Unfortunately, my software wants to force us to switch to InnoDB, so I'm really stuck. If we have to switch to InnoDB and we cannot come up with a method for doing fast, rsync-style backups, it will probably mean huge, costly, and unnecessary changes to our infrastructure. Any help will be GREATLY appreciated. -- Eric Robinson Disclaimer - January 24, 2011 This email and any files transmitted with it are confidential and intended solely for 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=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
