Re: Doubt Regd. Circular Replication In Mysql

[Adarsh Sharma]

Agreed with your point Rick, right now i am maintaining my datadir &
logging in my EBS volumes so if any of the instance goes down ,we will
launch new instance & use the existing EBS volumes and start replication
again.

I think it will start automatically from the point where it goes down and
start replicating again.

Can we use any other prevention for automating the failover.

Thanks

On Tue, Sep 25, 2012 at 12:41 AM, Rick James  wrote:

> Don't use circular replication with more than 2 servers.  If one of your 3
> crashes and cannot be recovered, you will have a nightmare on your hands to
> fix the broken replication.
>
> > -Original Message-
> > From: Stillman, Benjamin [mailto:bstill...@limitedbrands.com]
> > Sent: Monday, September 24, 2012 11:56 AM
> > To: Shawn Green
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Doubt Regd. Circular Replication In Mysql
> >
> > I stand corrected and apologize. Numerous multi-master setup
> > descriptions I've read have said to set this (including the one linked
> > in the original question). However, as you said, the entry in the
> > manual clearly says it defaults to 0. Learn something new every day.
> > Thanks Shawn.
> >
> >
> >
> > On Sep 24, 2012, at 2:05 PM, "Shawn Green" 
> > wrote:
> >
> > > replicate-same-server-id = 0
> >
> > 
> >
> > Notice: This communication may contain privileged and/or confidential
> > information. If you are not the intended recipient, please notify the
> > sender by email, and immediately delete the message and any attachments
> > without copying or disclosing them. LBI may, for any reason, intercept,
> > access, use, and disclose any information that is communicated by or
> > through, or which is stored on, its networks, applications, services,
> > and devices.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

RE: Doubt Regd. Circular Replication In Mysql

[Rick James]

Don't use circular replication with more than 2 servers.  If one of your 3 
crashes and cannot be recovered, you will have a nightmare on your hands to fix 
the broken replication.

> -Original Message-
> From: Stillman, Benjamin [mailto:bstill...@limitedbrands.com]
> Sent: Monday, September 24, 2012 11:56 AM
> To: Shawn Green
> Cc: mysql@lists.mysql.com
> Subject: Re: Doubt Regd. Circular Replication In Mysql
> 
> I stand corrected and apologize. Numerous multi-master setup
> descriptions I've read have said to set this (including the one linked
> in the original question). However, as you said, the entry in the
> manual clearly says it defaults to 0. Learn something new every day.
> Thanks Shawn.
> 
> 
> 
> On Sep 24, 2012, at 2:05 PM, "Shawn Green" 
> wrote:
> 
> > replicate-same-server-id = 0
> 
> 
> 
> Notice: This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, please notify the
> sender by email, and immediately delete the message and any attachments
> without copying or disclosing them. LBI may, for any reason, intercept,
> access, use, and disclose any information that is communicated by or
> through, or which is stored on, its networks, applications, services,
> and devices.
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Doubt Regd. Circular Replication In Mysql

[Stillman, Benjamin]

I stand corrected and apologize. Numerous multi-master setup descriptions I've 
read have said to set this (including the one linked in the original question). 
However, as you said, the entry in the manual clearly says it defaults to 0. 
Learn something new every day. Thanks Shawn.



On Sep 24, 2012, at 2:05 PM, "Shawn Green"  wrote:

> replicate-same-server-id = 0



Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LBI may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Doubt Regd. Circular Replication In Mysql

[Shawn Green]

Hello Benjamin,

On 9/24/2012 10:52 AM, Stillman, Benjamin wrote:

replicate-same-server-id = 0 keeps MySQL from replicating binary log entries 
from itself. For instance, here's a rough overview:

You write to Server A.
Server A writes that to its binary log.
Server B reads Server A's binary log and completes the same thing.
Because log-slave-updates is enabled, Server B writes it to its own binary log.
Server C reads Server B's binary log and completes the same thing.
Again, with log-slave-updates enabled, Server C writes it to its own binary log.
Server A reads Server C's binary log.

Here's where the issue starts. Without replicate-same-server-id = 0, Server A 
will complete the insert/update/delete as it reads it from Server C's binary 
log. However, this query originated from Server A, so it's just going to do it 
again. Then it's again replicated to Server B, Server C, and so on. This can 
create a loop and/or break replication. For instance, if you drop a table on A. 
It replicates across, and back to A. Replication will error out because when it 
tries to drop the same table again, it already doesn't exist. You need 
replicate-same-server-id = 0 set so that it knows not to execute any binary log 
entries with its own server ID.



Not true.

Replication, by default, operates with --replicate-same-server-id=0. The 
only time you need to change it to a 1 is for certain recovery 
scenarios. We added this variable specifically to allow for exceptions 
to the rule that every server in a replication chain (or ring) must have 
their own, unique, --server-id value.


It's not required for normal operations. In fact we recommend you do not 
set it at all. Each server will automatically ignore any event that 
originates from a server with the same --server-id setting unless you 
specifically set --replicate-same-server-id=1 .


Regards
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Doubt Regd. Circular Replication In Mysql

[Stillman, Benjamin]

replicate-same-server-id = 0 keeps MySQL from replicating binary log entries 
from itself. For instance, here's a rough overview:

You write to Server A.
Server A writes that to its binary log.
Server B reads Server A's binary log and completes the same thing.
Because log-slave-updates is enabled, Server B writes it to its own binary log.
Server C reads Server B's binary log and completes the same thing.
Again, with log-slave-updates enabled, Server C writes it to its own binary log.
Server A reads Server C's binary log.

Here's where the issue starts. Without replicate-same-server-id = 0, Server A 
will complete the insert/update/delete as it reads it from Server C's binary 
log. However, this query originated from Server A, so it's just going to do it 
again. Then it's again replicated to Server B, Server C, and so on. This can 
create a loop and/or break replication. For instance, if you drop a table on A. 
It replicates across, and back to A. Replication will error out because when it 
tries to drop the same table again, it already doesn't exist. You need 
replicate-same-server-id = 0 set so that it knows not to execute any binary log 
entries with its own server ID.



From: Adarsh Sharma [mailto:eddy.ada...@gmail.com]
Sent: Monday, September 24, 2012 10:39 AM
To: Stillman, Benjamin
Subject: Re: Doubt Regd. Circular Replication In Mysql

Yes I fixed , but i solve the issue by enabling log-slave-updates only
Why we use the below parameter :-
replicate-same-server-id = 0

Ya i configured auto-increment settings properly.

Thanks

Thanks
On Mon, Sep 24, 2012 at 8:03 PM, Stillman, Benjamin 
mailto:bstill...@limitedbrands.com>> wrote:
Sounds like you're missing the following in your my.cnf on server B (probably 
all of them):

replicate-same-server-id = 0
log-slave-updates

While you're checking, might as well as make sure your auto-increment settings 
are in there and correct also.




-Original Message-
From: Adarsh Sharma [mailto:eddy.ada...@gmail.com<mailto:eddy.ada...@gmail.com>]
Sent: Monday, September 24, 2012 10:23 AM
To: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com>
Subject: Doubt Regd. Circular Replication In Mysql

Hi all,

Today i set up a circular replication between three nodes A,B & C successfully.
I expect whatever writes on A will propagated to B & then Propagated to C 
because the structure is like below :-

A -> B - > C -> A

I created a sample table stag in test database in A and insert few records that 
are also replicated to B but not to C. Now when i created the same table in C , 
it shows errors in show slave status\G output in A node.

I needed this setup because all these servers are in different  colos so that 
whatever writes to any node would replicated to others also for one database.

I followed the below  link for setting this circular replication :-

http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch-p2

Is it possible to achieve whatever i needed or i need to create Multi Master 
set up 2 nodes only.

Thanks


Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LBI may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

RE: Doubt Regd. Circular Replication In Mysql

[Stillman, Benjamin]

Sounds like you're missing the following in your my.cnf on server B (probably 
all of them):

replicate-same-server-id = 0
log-slave-updates

While you're checking, might as well as make sure your auto-increment settings 
are in there and correct also.




-Original Message-
From: Adarsh Sharma [mailto:eddy.ada...@gmail.com]
Sent: Monday, September 24, 2012 10:23 AM
To: mysql@lists.mysql.com
Subject: Doubt Regd. Circular Replication In Mysql

Hi all,

Today i set up a circular replication between three nodes A,B & C successfully.
I expect whatever writes on A will propagated to B & then Propagated to C 
because the structure is like below :-

A -> B - > C -> A

I created a sample table stag in test database in A and insert few records that 
are also replicated to B but not to C. Now when i created the same table in C , 
it shows errors in show slave status\G output in A node.

I needed this setup because all these servers are in different  colos so that 
whatever writes to any node would replicated to others also for one database.

I followed the below  link for setting this circular replication :-

http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch-p2

Is it possible to achieve whatever i needed or i need to create Multi Master 
set up 2 nodes only.

Thanks



Notice: This communication may contain privileged and/or confidential 
information. If you are not the intended recipient, please notify the sender by 
email, and immediately delete the message and any attachments without copying 
or disclosing them. LBI may, for any reason, intercept, access, use, and 
disclose any information that is communicated by or through, or which is stored 
on, its networks, applications, services, and devices.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Doubt Regd. Circular Replication In Mysql

[Adarsh Sharma]

Hi all,

Today i set up a circular replication between three nodes A,B & C
successfully.
I expect whatever writes on A will propagated to B & then Propagated to C
because the structure is like below :-

A -> B - > C -> A

I created a sample table stag in test database in A and insert few records
that are also replicated to B but not to C. Now when i created the same
table in C , it shows errors in show slave status\G output in A node.

I needed this setup because all these servers are in different  colos so
that whatever writes to any node would replicated to others also for one
database.

I followed the below  link for setting this circular replication :-

http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch-p2

Is it possible to achieve whatever i needed or i need to create Multi
Master set up 2 nodes only.

Thanks

Re: Circular Replication & GRANTs

[Johan De Meersman]

- Original Message -
> From: "Jason J. W. Williams" 
> 
> I've got an issue where two MySQL servers are in circular/multimaster
> replication. One is server_id 6871 and the other 206871. The issue is
> that GRANT statements issued on 6871 are replicated to 206871 and
> then
> back to 6871 where they're applied again. My understanding was that
> 6871 should filter out any replicated statements with it's own
> server_id, however it doesn't seem to be doing that. I've also tried

It should, indeed. How did you end up noticing this? An already-applied grant 
being reapplied isn't exactly something you easily notice :-)

As a first thought, you may have log-slave-updates on, which would cause 206871 
to rebroadcast the received grants back to 6871, who will then log them in it's 
relay log but, normally, ignore them after that.

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

Circular Replication flawed?

[Hayden Livingston]

I was reading the circular replication post on Onlamp.com, how they
achieve this master-master configuration. I was wondering if this will
always work out in a scenario. For example:

auto_increment_increment = 10
auto_increment_offset = 1 (for NodeA), and 2 for (NodeB)

Node1 starts at time A, 5 inserts happen, so 1, 11, 21, 31, 41

now Node2 starts at time B, puts in the five inserts, 1, 11, 21, 31, 41

Now if somebody inserts a record in Node2, the next record will be 51,
and what if before the Node1 reads the binlog for Node2, we get an
insert in Node1, that will also be 51.

So how does this new auto increment variables help this scenario?

Hayden

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular replication

[Jimmy Guerrero]

Hello,

EDS and MySQL are having a webinar on the subject of circular replication 
today. Check out:

http://www.mysql.com/news-and-events/web-seminars/index.html

If you can't make it today, it will be archived in the "On-Demand" section.

-- Jimmy

Alex Arul Lurthu wrote:

Chain replication is fine as long as reading stale data from the last slave
in your chain is ok. the staleness depends on the write throughput and
capacity of the intermediate slaves. But Chain replication with circular
replication is a definite no no in prod since if any intermediate fails, you
will not be able to restore it easily and the data goes out of sync.

On Dec 5, 2007 12:31 AM, Krishna Chandra Prajapati <[EMAIL PROTECTED]>
wrote:


Hi All,

Is circular replication or chain replication is suitable for production
environment. Whether any testing has been done. If yes then, Please let me
know. There is any other issue related to circular replication.

Thanks
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular replication

[Alex Arul Lurthu]

Chain replication is fine as long as reading stale data from the last slave
in your chain is ok. the staleness depends on the write throughput and
capacity of the intermediate slaves. But Chain replication with circular
replication is a definite no no in prod since if any intermediate fails, you
will not be able to restore it easily and the data goes out of sync.

On Dec 5, 2007 12:31 AM, Krishna Chandra Prajapati <[EMAIL PROTECTED]>
wrote:

> Hi All,
>
> Is circular replication or chain replication is suitable for production
> environment. Whether any testing has been done. If yes then, Please let me
> know. There is any other issue related to circular replication.
>
> Thanks
> --
> Krishna Chandra Prajapati
> MySQL DBA,
> Ed Ventures e-Learning Pvt.Ltd.
> 1-8-303/48/15, Sindhi Colony
> P.G.Road, Secunderabad.
> Pin Code: 53
> Office Number: 040-66489771
> Mob: 9912924044
> URL: ed-ventures-online.com
> Email-id: [EMAIL PROTECTED]
>



-- 
Thanks
Alex
http://alexlurthu.wordpress.com

Circular replication

[Krishna Chandra Prajapati]

Hi All,

Is circular replication or chain replication is suitable for production
environment. Whether any testing has been done. If yes then, Please let me
know. There is any other issue related to circular replication.

Thanks
-- 
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 53
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]

Re: Circular Replication

[Devananda]

Jigal van Hemert wrote:

Stefan Kuhn wrote:


Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet:


Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit :


I'm using it with four machines (geographically separate) and it works
fine. Stefan



And can writes on each server in simultaneous time ?



I don't understand the question. Replication is pretty fast, but is 
not guaranteed to happen in a certain time (not real-time).



Stefan,

I think Raphaël wants to know if a user can use any server in the circle 
to update or insert records and that the changes will be replicated to 
all other servers.


In another thread he stated that it was for maintaining student 
information on various remote locations (a student can login into the 
system on any of the locations).


What happens if a record is updated on two servers and the changes are 
forwarded to the other servers in the circle? I dont' think that this 
would occur often with student information, but both the student and the 
administration department might update a record "simultaniously" (in 
comparison to the speed of replication with several remote locations).


Regards, Jigal.




Any time you are running circular replication, it is possible for a 
situation to arise where two servers receive "conflicting" updates at 
nearly the same time; this can cause replication to stop on both servers 
at the point where they read the other server's conflicting update from 
their master's binary log.


For example, imagine 4 servers, A->B->C->D->A, and imagine that A and C 
both receive an insert statement containing the same unique key. 
Replication would stop when A reads C's insert from D's binary log, and 
when C reads A's insert from B's binary log. When this type of conflict 
happens, it can be difficult or impossible to restore data integrity 
between all servers. Another example, imagine that A had received an 
update like "SET $val = $val + 5" while C had received "SET $val = $val 
* 2". These would not conflict - in the sense that replication would 
continue uninterrupted - but data integrity would be lost.


In short, any time you are running circular replication, you have to 
ensure that updates are processed in the same order on all servers, or 
know that the order is irrelevant to your data integrity.



Regards,
Devananda vdv

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Jigal van Hemert]

Stefan Kuhn wrote:

Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet:


Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit :


I'm using it with four machines (geographically separate) and it works
fine. Stefan


And can writes on each server in simultaneous time ?


I don't understand the question. Replication is pretty fast, but is not 
guaranteed to happen in a certain time (not real-time).


Stefan,

I think Raphaël wants to know if a user can use any server in the circle 
to update or insert records and that the changes will be replicated to 
all other servers.


In another thread he stated that it was for maintaining student 
information on various remote locations (a student can login into the 
system on any of the locations).


What happens if a record is updated on two servers and the changes are 
forwarded to the other servers in the circle? I dont' think that this 
would occur often with student information, but both the student and the 
administration department might update a record "simultaniously" (in 
comparison to the speed of replication with several remote locations).


Regards, Jigal.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Stefan Kuhn]

Am Thursday 27 October 2005 12:56 schrieb Raphaël 'SurcouF' Bordet:
> Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit :
> > I'm using it with four machines (geographically separate) and it works
> > fine. Stefan
>
> And can writes on each server in simultaneous time ?
I don't understand the question. Replication is pretty fast, but is not 
guaranteed to happen in a certain time (not real-time).
Stefan
>
> --
> Raphaël 'SurcouF' Bordet
> http://debianfr.net/ | surcouf at debianfr dot net

-- 
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
Zülpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428   Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Raphaël 'SurcouF' Bordet]

Le vendredi 16 septembre 2005 à 18:14 +0200, Stefan Kuhn a écrit :
> I'm using it with four machines (geographically separate) and it works fine.
> Stefan

And can writes on each server in simultaneous time ?

-- 
Raphaël 'SurcouF' Bordet
http://debianfr.net/ | surcouf at debianfr dot net




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Hank]

> I'll be setting up a second master to do this same
> thing once per day to act as my daily backup.

Oops...I meant to say "second slave".

-Hank

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Hank]

> The long story short is we use the fact that MySQL has the ability to
> run the SQL thread and the IO thread of replication separately, and
> control them individually.

I'm fairly green with replication, but I have a simple cron job that
starts a PHP program that issues a "slave start", watches for the
"time behind master" to be zero seconds, then issues a "slave stop".
This repeats every 10 minutes (it takes about one minute to update 10
minutes of master data), so my slave is at most (worst case) 10
minutes behind the master.  This could be done every two hours or even
once per day.  I'll be setting up a second master to do this same
thing once per day to act as my daily backup.  Once the daily backup
completes replication, I can flush tables and backup the database
tables to the backup device for long term backups.

What are the differences between doing this and turning the SQL and IO
threads on spearetly? Just IMO, that seems like alot of manipulation
that's not really necessary, but it's possible I'm missing something.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Bruce Dembecki]

On Sep 21, 2005, at 5:23 AM, Jeff wrote:


I am interested in how you go about doing a "delayed replication" to
protect against operator error.  We've already fallen victim to that
situation here.



The long story short is we use the fact that MySQL has the ability to  
run the SQL thread and the IO thread of replication separately, and  
control them individually. In practice we use cron and a whole bunch  
of scripts to stop the I/O thread (the one reading from the master)  
most of the time, and manage when the SQL thread replicates... eg at  
4:00 cron stops the SQL thread. At 4:01 we start the I/O thread (this  
can read a lot of changes very quickly from the master, so only need  
a short time to catch up with all the changes). At 4:05 we stop the I/ 
O thread. Then we wait a few minutes to give ourselves a buffer...  
then finally at 4:15 we start the SQL thread and repeat the cycle  
every two hours.


The upshot is at the small end we are 10 minutes behind (the time  
between we stop I/O at 4:05 and the time when we start SQL at 4:15),  
and at the long end we are 2 hours behind (at 4:07 for example the  
last query that the SQL thread could have executed came from the  
master at 2:05).


Our scripts are a little more complicated to marry into our  
monitoring system without setting off alerts that replication has  
stopped and so on (and of course the machine that runs this speaks to  
many masters using many instances of MySQL, so we need to manage this  
for every instance of MySQL). We also manage things to allow an  
emergency stop by having the scripts do an existence check on a  
specific file, and if the file isn't there don't start any  
replication processes. We then have a stop script which tells the  
instances to stop whatever they are doing and deletes the file. At  
that point replication can't resume until we replace the file  
manually - we tie that emergency script to a TCP port and hey  
presto... in the event of an emergency all someone needs to do is hit  
the right tcp port on the server (telnet to it, hit it with a  
browser, anything that will cause the port to see some activity) and  
all the replication comes to a stop.


Also as part of our 2 hourly cycle we do a lot of binary log flushing  
on the slave and the masters, so if we ever need to roll back we can  
roll back to a specific point in time and only have to deal with  
fixing problems in the logs form that point in time onwards. if an  
operator error gets by before we can stop we can go to yesterdays  
backup and only execute those binary logs from before the incident,  
and then deal with the issue in question.


This process has reduced our downtime in the event of a total  
database corruption from four hours to recover from yesterdays data  
and be missing everything since, to 30 minutes and be only missing  
the data since the last 2 hourly roll over. And it doesn't take long  
to dump the last set of binary logs to a text file, find and fix/ 
remove the corrupting command and apply that whole log into the  
database, effectively giving us almost zero lost data and back online  
in no time (although when clients are screaming even 30 minutes feels  
like an eternity). This is all of course so much better than the four  
hour downtime we had before this system.


And there are side benefits... for example backups are easier to do  
because the data isn't being changed except for a few minutes every 2  
hours. Instead of co-ordinating timing scripts and locking tables and  
doing dumps and so on we can do simple file system duplication of the  
data directories.


Best Regards, Bruce

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Circular Replication

[Jeff]

> -Original Message-
> From: Bruce Dembecki [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 23:05
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
> 
> 
> On Sep 16, 2005, at 11:07 AM, Jeff wrote:
> 
> > There shouldn't be a problem if:
> >
> > server A is ver 4.0.x
> > server B is ver 4.1.x
> >
> > should there?
> >
> 
> There will totally by a problem here... The 4.1 server will take the  
> 4.0 feed without issue. The 4.1 server however puts all sorts of  
> information into the binary log which isn't in the original query,  
> like what sort of collation to use, and which character set it uses  
> and so on... 4.0 doesn't understand such commands and lots of things  
> break in this situation.
> 
> As a side note we deploy servers in pairs, with circular 
> replication.  
> We did three and four server circles, but it gets messy if  
> replication stops somewhere, the data becomes unpredictably  
> inconsistent (assuming all the servers in the circle are getting  
> production updates). Now we do simple two way replication between a  
> pair, and we hang a third server off the pair somewhere just  
> reading... the third server we use for backups, data dumps, reports  
> and other non production issues. Essentially it is something like A<- 
>  >B->C, where A and B have two way replication and C is used for  
> backups/reports etc... anything that changes the data happens 
> on A or B.
> 

I assume you then need to start server B with "--Log_slave_updates"?

> We do some other black magic to manage the replication on C so it's  
> perpetually behind the master servers by between 15 minutes and 2  
> hours... that way if we have a stupid operator error or some other  
> data corrupting event we can stop replication on the backup server  
> before it executes and start from there rather than having to 
> go back  
> to yesterdays backup or something.
> 
> Best Regards, Bruce
> 

Bruce,

Thanks for the info.  All my downstream servers are 4.1 and my masters
are all 4.0.  They will all be brought up to the latest when I can
figure out a production schedule that will allow it.

So for now what I'll have is:

A <-> B -> C
|\ 
D E

Where A and B (4.0.16) are masters in a circular replication and C,D,E
(4.1.13) are backups and other read only slave servers .

The reason for needing the circular replication is we are moving our
production systems to another data center and I need to be able to
quickly switch the applications over to the new data center and then
back again if something goes south.

After that however I think I'll keep the two db servers at the new data
center in a circular replication for redundancy probably.

I am interested in how you go about doing a "delayed replication" to
protect against operator error.  We've already fallen victim to that
situation here.

Thanks,

Jeff



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Bruce Dembecki]

On Sep 16, 2005, at 11:07 AM, Jeff wrote:


There shouldn't be a problem if:

server A is ver 4.0.x
server B is ver 4.1.x

should there?



There will totally by a problem here... The 4.1 server will take the  
4.0 feed without issue. The 4.1 server however puts all sorts of  
information into the binary log which isn't in the original query,  
like what sort of collation to use, and which character set it uses  
and so on... 4.0 doesn't understand such commands and lots of things  
break in this situation.


As a side note we deploy servers in pairs, with circular replication.  
We did three and four server circles, but it gets messy if  
replication stops somewhere, the data becomes unpredictably  
inconsistent (assuming all the servers in the circle are getting  
production updates). Now we do simple two way replication between a  
pair, and we hang a third server off the pair somewhere just  
reading... the third server we use for backups, data dumps, reports  
and other non production issues. Essentially it is something like A<- 
>B->C, where A and B have two way replication and C is used for  
backups/reports etc... anything that changes the data happens on A or B.


We do some other black magic to manage the replication on C so it's  
perpetually behind the master servers by between 15 minutes and 2  
hours... that way if we have a stupid operator error or some other  
data corrupting event we can stop replication on the backup server  
before it executes and start from there rather than having to go back  
to yesterdays backup or something.


Best Regards, Bruce

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Circular Replication

[Jeff]

> -Original Message-
> From: Devananda [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 17:13
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
> 
> 
> Jeff wrote:
> >>-Original Message-
> >>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> >>Sent: Monday, September 19, 2005 10:10
> >>To: [EMAIL PROTECTED]
> >>Cc: mysql@lists.mysql.com
> >>Subject: Re: Circular Replication
> >>
> >>
> >>Sid Lane <[EMAIL PROTECTED]> wrote on 19/09/2005 15:02:58:
> >>
> >>
> >>>stupid ?:
> >>>
> >>>what keeps them from getting caught in a write loop? turning off 
> >>>log_slave_updates?
> >>>
> >>>I had never thought of this but is has intriging possibilities...
> >>
> >>Each update is marked with the unique server id of the server which
> >>originated it. When the update returns to its originating 
> >>server, it is 
> >>dropped instead of being executed. That is why every server 
> >>must have a 
> >>unique id.
> >>
> >>Alec
> >>
> > 
> > 
> > Actually, isn't it required that you start each server with 
> > --log_slave_updates?
> > 
> > Or is that only necessary in a replication situation like this with 
> > more than 2 servers?
> > 
> > A -> B -> C -> A
> > 
> > If it's just:
> > 
> > A -> B -> A
> > 
> > Is it necessary to start the servers with --log_slave_updates?
> > 
> > Thanks,
> > 
> > Jeff
> > 
> > 
> > 
> 
> log-slave-updates causes the server to write to it's own binlog any 
> statements it read from it's master's binlog. This is necessary in 
> chains of servers (ie, A->B->C->A); in such chains, it is 
> necessary that 
> all servers have BOTH log-bin and log-slave-updates.
> 
> With just 2 servers (A->B->A), you need log-bin, but do not need 
> log-slave-updates.
> 
> Regards,
> Devananda vdv
> 

Muchos Gracias!



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Devananda]

Jeff wrote:

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, September 19, 2005 10:10

To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Circular Replication


Sid Lane <[EMAIL PROTECTED]> wrote on 19/09/2005 15:02:58:



stupid ?:

what keeps them from getting caught in a write loop? turning off
log_slave_updates?

I had never thought of this but is has intriging possibilities...


Each update is marked with the unique server id of the server which 
originated it. When the update returns to its originating 
server, it is 
dropped instead of being executed. That is why every server 
must have a 
unique id.


   Alec




Actually, isn't it required that you start each server with
--log_slave_updates?

Or is that only necessary in a replication situation like this with more
than 2 servers?

A -> B -> C -> A

If it's just:

A -> B -> A 


Is it necessary to start the servers with --log_slave_updates?

Thanks,

Jeff





log-slave-updates causes the server to write to it's own binlog any 
statements it read from it's master's binlog. This is necessary in 
chains of servers (ie, A->B->C->A); in such chains, it is necessary that 
all servers have BOTH log-bin and log-slave-updates.


With just 2 servers (A->B->A), you need log-bin, but do not need 
log-slave-updates.


Regards,
Devananda vdv

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Circular Replication

[Jeff]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 19, 2005 10:10
> To: [EMAIL PROTECTED]
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
> 
> 
> Sid Lane <[EMAIL PROTECTED]> wrote on 19/09/2005 15:02:58:
> 
> > stupid ?:
> > 
> > what keeps them from getting caught in a write loop? turning off
> > log_slave_updates?
> > 
> > I had never thought of this but is has intriging possibilities...
> 
> Each update is marked with the unique server id of the server which 
> originated it. When the update returns to its originating 
> server, it is 
> dropped instead of being executed. That is why every server 
> must have a 
> unique id.
> 
> Alec
> 

Actually, isn't it required that you start each server with
--log_slave_updates?

Or is that only necessary in a replication situation like this with more
than 2 servers?

A -> B -> C -> A

If it's just:

A -> B -> A 

Is it necessary to start the servers with --log_slave_updates?

Thanks,

Jeff



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Sid Lane]

NOW I see the violence inhierent in the system...

this has some profoundly cool possibilities...

BWAH-HA-HA-HA!!!

muchos!

Re: Circular Replication

[Alec . Cawley]

Sid Lane <[EMAIL PROTECTED]> wrote on 19/09/2005 15:02:58:

> stupid ?:
> 
> what keeps them from getting caught in a write loop? turning off 
> log_slave_updates?
> 
> I had never thought of this but is has intriging possibilities...

Each update is marked with the unique server id of the server which 
originated it. When the update returns to its originating server, it is 
dropped instead of being executed. That is why every server must have a 
unique id.

Alec

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Sid Lane]

stupid ?:

what keeps them from getting caught in a write loop? turning off 
log_slave_updates?

I had never thought of this but is has intriging possibilities...

Re: Circular Replication

[Devananda]

Jeff wrote:

-Original Message-
From: Jeff McKeon 
Sent: Friday, September 16, 2005 13:19

To: Devananda
Cc: mysql@lists.mysql.com
Subject: RE: Circular Replication




-Original Message-
From: Devananda [mailto:[EMAIL PROTECTED]
Sent: Friday, September 16, 2005 11:55
To: Jeff
Cc: mysql@lists.mysql.com
Subject: Re: Circular Replication


Jeff wrote:


Am I correct in this setup process:

Server A exists
Server B to be built

Stop Server A, take snapshot, record Master info.
Start Server A

Setup server B, Install snapshot from A
Set B up as a master
Set B up as a slave to A

Set A up as a slave to B, no need for binlog file or


position info as


it will start at the default 001 and pos 4

Circular replication is now running...

Did I mis anything?

Thanks,

Jeff





If you are using InnoDB, then you do not need to stop server
A to take a 
snapshot. (see http://dev.mysql.com/doc/mysql/en/mysqldump.html)


"The simultaneous use of --master-data and
--single-transaction works as 
of MySQL 4.1.8. It provides a convenient way to make an 


online backup 

suitable for point-in-time recovery if tables are stored in 
the InnoDB 
storage engine."


As far as setting up the replication, that looks ok. Each
server needs 
to have binary logging enabled, and needs to have the other 


server as 


it's master.

Regards,
Devananda vdv


Nope, all tables are MyISAM.




There shouldn't be a problem if:

server A is ver 4.0.x 
server B is ver 4.1.x 


should there?

Thanks,

jeff




There could be problems with that.
See http://dev.mysql.com/doc/mysql/en/replication-compatibility.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Circular Replication

[Jeff]

> -Original Message-
> From: Jeff McKeon 
> Sent: Friday, September 16, 2005 13:19
> To: Devananda
> Cc: mysql@lists.mysql.com
> Subject: RE: Circular Replication
> 
> 
> > -Original Message-
> > From: Devananda [mailto:[EMAIL PROTECTED]
> > Sent: Friday, September 16, 2005 11:55
> > To: Jeff
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Circular Replication
> > 
> > 
> > Jeff wrote:
> > > 
> > > Am I correct in this setup process:
> > > 
> > > Server A exists
> > > Server B to be built
> > > 
> > > Stop Server A, take snapshot, record Master info.
> > > Start Server A
> > > 
> > > Setup server B, Install snapshot from A
> > > Set B up as a master
> > > Set B up as a slave to A
> > > 
> > > Set A up as a slave to B, no need for binlog file or
> > position info as
> > > it will start at the default 001 and pos 4
> > > 
> > > Circular replication is now running...
> > > 
> > > Did I mis anything?
> > > 
> > > Thanks,
> > > 
> > > Jeff
> > > 
> > > 
> > > 
> > If you are using InnoDB, then you do not need to stop server
> > A to take a 
> > snapshot. (see http://dev.mysql.com/doc/mysql/en/mysqldump.html)
> > 
> > "The simultaneous use of --master-data and
> > --single-transaction works as 
> > of MySQL 4.1.8. It provides a convenient way to make an 
> online backup 
> > suitable for point-in-time recovery if tables are stored in 
> > the InnoDB 
> > storage engine."
> > 
> > As far as setting up the replication, that looks ok. Each
> > server needs 
> > to have binary logging enabled, and needs to have the other 
> server as 
> > it's master.
> > 
> > Regards,
> > Devananda vdv
> 
> Nope, all tables are MyISAM.
> 

There shouldn't be a problem if:

server A is ver 4.0.x 
server B is ver 4.1.x 

should there?

Thanks,

jeff



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Circular Replication

[Jeff McKeon]

> -Original Message-
> From: Devananda [mailto:[EMAIL PROTECTED] 
> Sent: Friday, September 16, 2005 11:55
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
> 
> 
> Jeff wrote:
> > 
> > Am I correct in this setup process:
> > 
> > Server A exists
> > Server B to be built
> > 
> > Stop Server A, take snapshot, record Master info.
> > Start Server A
> > 
> > Setup server B, Install snapshot from A
> > Set B up as a master
> > Set B up as a slave to A
> > 
> > Set A up as a slave to B, no need for binlog file or 
> position info as 
> > it will start at the default 001 and pos 4
> > 
> > Circular replication is now running...
> > 
> > Did I mis anything?
> > 
> > Thanks,
> > 
> > Jeff
> > 
> > 
> > 
> If you are using InnoDB, then you do not need to stop server 
> A to take a 
> snapshot. (see http://dev.mysql.com/doc/mysql/en/mysqldump.html)
> 
> "The simultaneous use of --master-data and 
> --single-transaction works as 
> of MySQL 4.1.8. It provides a convenient way to make an online backup 
> suitable for point-in-time recovery if tables are stored in 
> the InnoDB 
> storage engine."
> 
> As far as setting up the replication, that looks ok. Each 
> server needs 
> to have binary logging enabled, and needs to have the other server as 
> it's master.
> 
> Regards,
> Devananda vdv

Nope, all tables are MyISAM.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Devananda]

Jeff wrote:


Am I correct in this setup process:

Server A exists
Server B to be built

Stop Server A, take snapshot, record Master info.
Start Server A

Setup server B, Install snapshot from A
Set B up as a master
Set B up as a slave to A

Set A up as a slave to B, no need for binlog file or position info as it
will start at the default 001 and pos 4

Circular replication is now running...

Did I mis anything?

Thanks,

Jeff



If you are using InnoDB, then you do not need to stop server A to take a 
snapshot. (see http://dev.mysql.com/doc/mysql/en/mysqldump.html)


"The simultaneous use of --master-data and --single-transaction works as 
of MySQL 4.1.8. It provides a convenient way to make an online backup 
suitable for point-in-time recovery if tables are stored in the InnoDB 
storage engine."


As far as setting up the replication, that looks ok. Each server needs 
to have binary logging enabled, and needs to have the other server as 
it's master.


Regards,
Devananda vdv

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Stefan Kuhn]

I'm using it with four machines (geographically separate) and it works fine.
Stefan


Am Friday 16 September 2005 17:31 schrieb Jeff:
> Does anyone use circular replication with MySQL 4.x?  For instance:
>
>  A to B
>  B to A
>
> I know it's possible as long as you're carefull with your client
> applications and the way they write/update the db.  Just wondering if
> anyone has had success or problems with this type of situation.
>
> Thanks,
>
> Jeff

-- 
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
Zülpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428   Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Circular Replication

[Jeff]

> -Original Message-
> From: Dan Nelson [mailto:[EMAIL PROTECTED] 
> Sent: Friday, September 16, 2005 11:34
> To: Jeff
> Cc: mysql@lists.mysql.com
> Subject: Re: Circular Replication
> 
> 
> In the last episode (Sep 16), Jeff said:
> > Does anyone use circular replication with MySQL 4.x?  For instance:
> > 
> >  A to B
> >  B to A
> > 
> > I know it's possible as long as you're carefull with your client 
> > applications and the way they write/update the db.  Just 
> wondering if 
> > anyone has had success or problems with this type of situation.
> 
> Works just fine here.
> 
> -- 
> 
>   Dan Nelson
>   [EMAIL PROTECTED]
> 
> -- 
> 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?> [EMAIL PROTECTED]
> 
> 

Am I correct in this setup process:

Server A exists
Server B to be built

Stop Server A, take snapshot, record Master info.
Start Server A

Setup server B, Install snapshot from A
Set B up as a master
Set B up as a slave to A

Set A up as a slave to B, no need for binlog file or position info as it
will start at the default 001 and pos 4

Circular replication is now running...

Did I mis anything?

Thanks,

Jeff



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Circular Replication

[Dan Nelson]

In the last episode (Sep 16), Jeff said:
> Does anyone use circular replication with MySQL 4.x?  For instance:
> 
>  A to B
>  B to A
> 
> I know it's possible as long as you're carefull with your client
> applications and the way they write/update the db.  Just wondering if
> anyone has had success or problems with this type of situation.

Works just fine here.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Circular Replication

[Jeff]

Does anyone use circular replication with MySQL 4.x?  For instance:

 A to B
 B to A

I know it's possible as long as you're carefull with your client
applications and the way they write/update the db.  Just wondering if
anyone has had success or problems with this type of situation.

Thanks,

Jeff



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Circular replication in an HA configuration

[Ralph Forsythe]

I've had some brief discussion about this with other people on other lists
and have decided to move the conversation here, since there are far wiser
MySQL gurus here than I.  Here's the scenario:

I want to have two (or more, but for now let's say two) db servers
running.  These servers will have MySQL and OpenLDAP2 on them and some
other minor processes we won't worry about for the time being.  Even
OpenLDAP can be left out of the conversation since it's unrelated to this
list.

Now, for now we don't care about any freaky load balancing or anything
(not that it could be done anyway), the load isn't that high - but it is
operationally critical, and having the db server fail would mean bad
things until it could be restored.  If performance on the one server
becomes an issue we'll just put in a faster machine / faster drives, or
whatever.  Oh, did I mention these will be running at a colocatoin
facility where I'm not at most of the time? :)

My idea is as follows:  Both DB servers think they are masters.  Normally
this induces bad dreams of simultaneous db updates and such, but that
isn't a concern here.  Both servers are configured on an IP address using
VRRP - the primary server is the only one taking client queries normally.
The second server either replicates on a different IP alias, or a second
interface completely (isolated vlan) on a fairly constant basis, to
maintain a complete dataset of the first db.

In the event that server 1 commits seppuku, server 2's VRRP process
assumes the shared IP and MAC address from the first machine, and except
for a few seconds in between client systems should never notice the
difference.  This is why server 2 needs to be a master normally, because
at this point it will be updating the tables just like the first one.  Ok,
so there is failover step #1.  Now for restoring the primary...  The
primary server is down, and will be kept that way (i.e. would require a
manual switch back to primary) since we want intervention to know why it
broke.  So I'm assuming we can manually script it to re-replicate from
server 2 to get a current set of information, then after that is
accomplished assume the primary role again.

So, is this even possible?  It was suggested to me that 3.23.x cannot do
this, but that MySQL 4 *might* be able to.  What sayeth the gurus?  Am I
just too hopeful for an HA solution, is there a better/different way to
accomplish the same thing, or what?

Thanks!!
- Ralph Forsythe
Aspiring ISP Ninja


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Circular replication

[Heikki Tuuri]

Hi!

- Original Message -
From: ""Madscientist"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Tuesday, April 23, 2002 10:44 PM
Subject: Circular replication


> Hi,
>
> We're doing something weird (what else is new). We're hoping to use
> MySQL as the base of a distributed database with peer replication. Most
> of the peering and control mechanisms will be in the core application
> code, but we're counting on MySQL's ability to replicate for some of the
> lower-level peer synchronization mechanisms. It appears from the docs
> that most of this will be straightforward, however it is not clear (or I
> missed it) if replication works with all table types.
>
> This is important because the issue of complex transactions has recently
> come up and we may need to use innodb tables.
>
> It is not entirely clear wether replication will work with innodb
> tables.
> We're hoping to use MySQL 4.x

MySQL replication works with InnoDB tables. Currently MySQL replication does
not support transactions completely, because commit marks are not written to
the binlog and the slave runs in the auto-commit mode. Thus a reader on the
slave may see half a transaction in his query. This restriction may be
removed soon because a potential license customer wants the commit marks to
the binlog.

> Can we build reliable, circular replication with innodb tables in MySQL?
>
> We will have the application pick a single master node for any
> particular table and/or database until all nodes are synchronized...
> Then the app will vote for a new master if needed.
>
> For example.
>
>NODE1   NODE2NODE3
>MASTER OF A MASTER OF B  MASTER OF C
>
> ...--->[ ABC ]>[ ABC ]->[ ABC ]...
>
> Will this work with innodb.
> If not directly, then how?

If circular replication requires that several nodes are updated at the same
time, then you will bump into the inherent consistency/performance problems
of distributed databases. These problems are not specific to MySQL or
InnoDB, but to distributed transactions in general.

> Thanks in advacnce,
> _M

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Circular replication

[Madscientist]

Hi,

We're doing something weird (what else is new). We're hoping to use
MySQL as the base of a distributed database with peer replication. Most
of the peering and control mechanisms will be in the core application
code, but we're counting on MySQL's ability to replicate for some of the
lower-level peer synchronization mechanisms. It appears from the docs
that most of this will be straightforward, however it is not clear (or I
missed it) if replication works with all table types.

This is important because the issue of complex transactions has recently
come up and we may need to use innodb tables.

It is not entirely clear wether replication will work with innodb
tables.
We're hoping to use MySQL 4.x

Can we build reliable, circular replication with innodb tables in MySQL?

We will have the application pick a single master node for any
particular table and/or database until all nodes are synchronized...
Then the app will vote for a new master if needed.

For example.

   NODE1   NODE2NODE3
   MASTER OF A MASTER OF B  MASTER OF C

...--->[ ABC ]>[ ABC ]->[ ABC ]...

Will this work with innodb.
If not directly, then how?

Thanks in advacnce,
_M


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Circular replication

[David Turner]

Just tell your boss that if you ever have turnover the former employee
will be able to log into all the customers' accounts and do whatever he
wants.

Dave
On Thu, Dec 13, 2001 at 03:29:41AM +1100, Duncan Maitland wrote:
> My questions concern a setup where a public server is running at our
> hosting company and a local office server is behind a firewall
> (connected to the net via a somewhat unreliable ADSL).
> 
> The servers are configured in a circular master-slave relationship but
> only a limited number of tables in the database are replicated between
> the two (public doesn't need all of them, so no use in replicating). Of
> these tables only 3 need to accept writes from both the public and
> office server (all the other writes happen at the office). Of those 3
> tables only 1 makes use of a unique primary key.
> 
> 
> So my questions are:
> 
> 1) Replicating a table with a primary key raises the possibility of
> conflicts if, while the office link is broken, two records are created
> with the same key. So I plan to generate my own keys in the project
> source code (without auto_increment) - the public site generates records
> with even numbers, the office site with odd numbers.
> 
> Is this a reasonable setup or is there a more correct way? Out of
> interest, how will MySQL 4.0 replication handle this situation?
> 
> 
> 2) MySQL docs state "It is possible for client A to make an update to
> co-master 1, and in the meantime, before it propagates to co-master 2,
> client B could make an update to co-master 2 that will make the update
> of client A work differently than it did on co-master 1. Thus when the
> update of client A will make it to co-master 2, it will produce tables
> that will be different than what you have on co-master 1, even after all
> the updates from co-master 2 have also propagated."
> 
> Say the office link is down, and a particular record in the
> above-mentioned table is edited on both the public and office servers.
> When the servers re-sync will one record take precedence (if so, which
> one?) or does the public get one and the office get the other? The
> former seems to be the case when doing basic testing on my LAN at home,
> but the MySQL doc is confusing in that it implies the latter.
> 
> 
> 
> To those of you who have read all the way down to here, I thank you very
> much! :)
> 
> Cheers,
> from Duncan Maitland
> [EMAIL PROTECTED]
> 
> 
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
> 
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Circular replication

[Brent Cowgill]

even/odd is a little limiting, what happens down the road when another 
site needs to be added.
A better method might be to use a unique session ID for each client site 
in combination with a
generated sequence ID see the white paper 
at:http://www.ambysoft.com/persistenceLayer.html
in particular the persistenceLayer.pdf document on that page.

Duncan Maitland wrote:

>My questions concern a setup where a public server is running at our
>hosting company and a local office server is behind a firewall
>(connected to the net via a somewhat unreliable ADSL).
>
>The servers are configured in a circular master-slave relationship but
>only a limited number of tables in the database are replicated between
>the two (public doesn't need all of them, so no use in replicating). Of
>these tables only 3 need to accept writes from both the public and
>office server (all the other writes happen at the office). Of those 3
>tables only 1 makes use of a unique primary key.
>
>
>So my questions are:
>
>1) Replicating a table with a primary key raises the possibility of
>conflicts if, while the office link is broken, two records are created
>with the same key. So I plan to generate my own keys in the project
>source code (without auto_increment) - the public site generates records
>with even numbers, the office site with odd numbers.
>
>Is this a reasonable setup or is there a more correct way? Out of
>interest, how will MySQL 4.0 replication handle this situation?
>
>
>2) MySQL docs state "It is possible for client A to make an update to
>co-master 1, and in the meantime, before it propagates to co-master 2,
>client B could make an update to co-master 2 that will make the update
>of client A work differently than it did on co-master 1. Thus when the
>update of client A will make it to co-master 2, it will produce tables
>that will be different than what you have on co-master 1, even after all
>the updates from co-master 2 have also propagated."
>
>Say the office link is down, and a particular record in the
>above-mentioned table is edited on both the public and office servers.
>When the servers re-sync will one record take precedence (if so, which
>one?) or does the public get one and the office get the other? The
>former seems to be the case when doing basic testing on my LAN at home,
>but the MySQL doc is confusing in that it implies the latter.
>
>
>
>To those of you who have read all the way down to here, I thank you very
>much! :)
>
>Cheers,
>from Duncan Maitland
>[EMAIL PROTECTED]
>
>
>-
>Before posting, please check:
>   http://www.mysql.com/manual.php   (the manual)
>   http://lists.mysql.com/   (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail <[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>

-- 
Regards,
Brent

interactivetools.com, inc.
Tel: (604)689-3347 - Fax: (604)689-3342 - Toll Free: 1(800)752-0455
Software for your Website - http://www.interactivetools.com/  

Attachments accepted: TXT, HTML, RTF, PDF 
I do not accept attachments which are capable of harbouring viruses, 
for example Word for Windows. Please use File/Save As RTF or HTML.





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Circular replication

[Duncan Maitland]

My questions concern a setup where a public server is running at our
hosting company and a local office server is behind a firewall
(connected to the net via a somewhat unreliable ADSL).

The servers are configured in a circular master-slave relationship but
only a limited number of tables in the database are replicated between
the two (public doesn't need all of them, so no use in replicating). Of
these tables only 3 need to accept writes from both the public and
office server (all the other writes happen at the office). Of those 3
tables only 1 makes use of a unique primary key.


So my questions are:

1) Replicating a table with a primary key raises the possibility of
conflicts if, while the office link is broken, two records are created
with the same key. So I plan to generate my own keys in the project
source code (without auto_increment) - the public site generates records
with even numbers, the office site with odd numbers.

Is this a reasonable setup or is there a more correct way? Out of
interest, how will MySQL 4.0 replication handle this situation?


2) MySQL docs state "It is possible for client A to make an update to
co-master 1, and in the meantime, before it propagates to co-master 2,
client B could make an update to co-master 2 that will make the update
of client A work differently than it did on co-master 1. Thus when the
update of client A will make it to co-master 2, it will produce tables
that will be different than what you have on co-master 1, even after all
the updates from co-master 2 have also propagated."

Say the office link is down, and a particular record in the
above-mentioned table is edited on both the public and office servers.
When the servers re-sync will one record take precedence (if so, which
one?) or does the public get one and the office get the other? The
former seems to be the case when doing basic testing on my LAN at home,
but the MySQL doc is confusing in that it implies the latter.



To those of you who have read all the way down to here, I thank you very
much! :)

Cheers,
from Duncan Maitland
[EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php