RE: Here"s an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Use

> (1) innodb?=20

It's an off-the-shelf application that uses MyISAM tables. It is
possible to convert to innodb, but I have not been sold on innodb in
terms of its performance characteristics for this particular
application. Maybe I've been reading the wrong stuff. Do you have
general thoughts on the differences with respect to performance?

> (2) why delete slave logs when you can=20
> restart the slave with --skip-slave and=20
> then use CHANGE MASTER TO?

Well... I guess mainly because I didn't know about that option! I
thought I needed to "fake out" mysql on this, but it sounds like I can
just do 'flush tables with read lock;reset master;' on the master and
'change master to...;' on the slave. So cool. Thanks for the input!

--
Eric Robinson


Disclaimer - December 4, 2009=20
This email and any files transmitted with it are confidential and =
intended solely for Tom Worster,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 . Warning: =
Although 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.=20
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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Here"s an Idea for Re-Syncing Master and Slave During ProductionHours without Interrupting User

On 12/4/09 11:59 AM, "Robinson, Eric" wrote:

>> (2) why delete slave logs when you can
>> restart the slave with --skip-slave and
>> then use CHANGE MASTER TO?
>
> Well... I guess mainly because I didn't know about that option! I
> thought I needed to "fake out" mysql on this, but it sounds like I can
> just do 'flush tables with read lock;reset master;' on the master and
> 'change master to...;' on the slave. So cool. Thanks for the input!

16.1.1 is probably my favorite chapter of the manual. 16.1.1.8 is
particularly worth a read.

http://dev.mysql.com/doc/refman/5.0/en/replication-howto-exi stingdata.html



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Here"s an Idea for Re-Syncing Master and Slave DuringProduction Hours without Interrupting User

I think he's trying to say that this method wouldn't work for innodb, unles=
s you copied files from an LVM snapshot, or something similar.

I would say that it's very important to know why data is getting out of syn=
c between your master and slave. Fixing those root causes would eliminate =
the need for this. There are cases where non-deterministic queries will pr=
oduce different results, but that's what row based replication is supposed =
to solve =3D)

There are ways to resync data that don't involve all this as well: Maatkit=
has some tools that compare data between servers, and can fix them with qu=
eries. No stopping the slave or locking the master necessary. I've used t=
hem in production with good results.

Regards,
Gavin Towey



-----Original Message-----
From: Robinson, Eric [mailto:eric.robinson@psmnv.com]
Sent: Friday, December 04, 2009 9:00 AM
To: Tom Worster; mysql@lists.mysql.com
Subject: RE: Here's an Idea for Re-Syncing Master and Slave During Producti=
on Hours without Interrupting Users (Much)

> (1) innodb?

It's an off-the-shelf application that uses MyISAM tables. It is
possible to convert to innodb, but I have not been sold on innodb in
terms of its performance characteristics for this particular
application. Maybe I've been reading the wrong stuff. Do you have
general thoughts on the differences with respect to performance?

> (2) why delete slave logs when you can
> restart the slave with --skip-slave and
> then use CHANGE MASTER TO?

Well... I guess mainly because I didn't know about that option! I
thought I needed to "fake out" mysql on this, but it sounds like I can
just do 'flush tables with read lock;reset master;' on the master and
'change master to...;' on the slave. So cool. Thanks for the input!

--
Eric Robinson


Disclaimer - December 4, 2009
This email and any files transmitted with it are confidential and intended =
solely for Tom Worster,mysql@lists.mysql.com. If you are not the named addr=
essee you should not disseminate, distribute, copy or alter this email. Any=
views or opinions presented in this email are solely those of the author a=
nd might not represent those of . Warning: Although has taken reasonable p=
recautions to ensure no viruses are present in this email, the company cann=
ot accept responsibility for any loss or damage arising from the use of thi=
s 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=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Here"s an Idea for Re-Syncing Master and Slave During ProductionHours without Interrupting User

On 12/4/09 3:14 PM, "Gavin Towey" wrote:

> I would say that it's very important to know why data is getting out of sync
> between your master and slave. Fixing those root causes would eliminate the
> need for this.

i very much agree. the only instances of slaves getting out of whack that
i've experienced was when i screwed something up administratively.

> There are cases where non-deterministic queries will produce
> different results, but that's what row based replication is supposed to solve
> =)

16.3.1 lists some interesting cases to consider:

http://dev.mysql.com/doc/refman/5.0/en/replication-features. html


> There are ways to resync data that don't involve all this as well: Maatkit
> has some tools that compare data between servers, and can fix them with
> queries. No stopping the slave or locking the master necessary. I've used
> them in production with good results.

thanks for the pointer. looks handy.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org