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

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

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 ser

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

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:

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

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 inconsisten

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

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

Re: InnoDB and rsync

On Wed, Jan 26, 2011 at 6:58 AM, Robinson, Eric 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 > > > > > > Look for the section talking about "clean" backups.

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 secti

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 >

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 thi

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 direc

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 th

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

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 ple

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. Di

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? > B

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

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

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 n

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 i

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 abou

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

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

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 sy

Re: InnoDB and rsync

On Tue, Jan 25, 2011 at 3:00 PM, 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 > Interesting. I never heard of tha

Re: InnoDB and rsync

2011/1/25 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

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 re

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 w

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

InnoDB and rsync

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 c