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
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
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
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
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
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
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
* 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
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
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
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
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
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,
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
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,
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
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
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
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
-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
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
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
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?
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.
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?
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
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
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
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?
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
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
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
32 matches
Mail list logo
