469,112 Members | 2,016 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

AUTO_INCREMENT - configuration for replication with dual masters

I'm looking at setting up two mysql servers in replication with both
being masters. The most obvious snag in this scenario is
auto_increment, as disconnections between servers could allow user
interaction to generate duplicate keys.

In a moment of zen I thought, "What if one server dealt only in even
numbers, and the other only in odd numbers?" This solution would keep
things simple and manageable, while also keeping the servers off
eachothers' toes.

Has anyone ever tried this? Is it even possible? if not, is there a
similar solution allowing for the same kind of integration?

Going further, I wonder if it's possible to do the same thing with
three or more servers, giving each an algorythm to generate a series
of auto_increment numbers that doesnt collide with the other master
servers in the chain.
Jul 19 '05 #1
4 2602
Dark Matt wrote:
I'm looking at setting up two mysql servers in replication with both
being masters. The most obvious snag in this scenario is
auto_increment, as disconnections between servers could allow user
interaction to generate duplicate keys.

In a moment of zen I thought, "What if one server dealt only in even
numbers, and the other only in odd numbers?" This solution would keep
things simple and manageable, while also keeping the servers off
eachothers' toes.

Has anyone ever tried this? Is it even possible? if not, is there a
similar solution allowing for the same kind of integration?

Going further, I wonder if it's possible to do the same thing with
three or more servers, giving each an algorythm to generate a series
of auto_increment numbers that doesnt collide with the other master
servers in the chain.

Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. DB1 was named ROW, DB2
named EMM and DB3 simply APN. In addition to doing the individual
replication sets, we were able to bring all 3 tables into a single set
using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com

Jul 19 '05 #2
Dark Matt wrote:
I'm looking at setting up two mysql servers in replication with both
being masters. The most obvious snag in this scenario is
auto_increment, as disconnections between servers could allow user
interaction to generate duplicate keys.

In a moment of zen I thought, "What if one server dealt only in even
numbers, and the other only in odd numbers?" This solution would keep
things simple and manageable, while also keeping the servers off
eachothers' toes.

Has anyone ever tried this? Is it even possible? if not, is there a
similar solution allowing for the same kind of integration?

Going further, I wonder if it's possible to do the same thing with
three or more servers, giving each an algorythm to generate a series
of auto_increment numbers that doesnt collide with the other master
servers in the chain.

Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. In its simplest form, DB1
was named ROW, DB2 named EMM and DB3 simply APN. In addition to doing
the individual replication sets, we were able to bring all 3 tables into
a single set using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com

Jul 19 '05 #3
Dark Matt wrote:
I'm looking at setting up two mysql servers in replication with both
being masters. The most obvious snag in this scenario is
auto_increment, as disconnections between servers could allow user
interaction to generate duplicate keys.

In a moment of zen I thought, "What if one server dealt only in even
numbers, and the other only in odd numbers?" This solution would keep
things simple and manageable, while also keeping the servers off
eachothers' toes.

Has anyone ever tried this? Is it even possible? if not, is there a
similar solution allowing for the same kind of integration?

Going further, I wonder if it's possible to do the same thing with
three or more servers, giving each an algorythm to generate a series
of auto_increment numbers that doesnt collide with the other master
servers in the chain.

Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. DB1 was named ROW, DB2
named EMM and DB3 simply APN. In addition to doing the individual
replication sets, we were able to bring all 3 tables into a single set
using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com

Jul 19 '05 #4
Dark Matt wrote:
I'm looking at setting up two mysql servers in replication with both
being masters. The most obvious snag in this scenario is
auto_increment, as disconnections between servers could allow user
interaction to generate duplicate keys.

In a moment of zen I thought, "What if one server dealt only in even
numbers, and the other only in odd numbers?" This solution would keep
things simple and manageable, while also keeping the servers off
eachothers' toes.

Has anyone ever tried this? Is it even possible? if not, is there a
similar solution allowing for the same kind of integration?

Going further, I wonder if it's possible to do the same thing with
three or more servers, giving each an algorythm to generate a series
of auto_increment numbers that doesnt collide with the other master
servers in the chain.

Went through a similar challenge with 3 separate Oracle databases and
dispersed servers (hundreds of tables - ya' got love ERP - millions of
records each), several years ago. Resolved by adding a column in the
incoming tables which was named "src" char. In its simplest form, DB1
was named ROW, DB2 named EMM and DB3 simply APN. In addition to doing
the individual replication sets, we were able to bring all 3 tables into
a single set using their unique rid+src as a key/index.

Just a thought, not an endorsement *grin*.

email nospam1001 at nonags dot com

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Michael.Skolik | last post: by
2 posts views Thread by Krishna Prasad | last post: by
reply views Thread by ian douglas | last post: by
10 posts views Thread by Chris Travers | last post: by
6 posts views Thread by John | last post: by
8 posts views Thread by Mike Read | last post: by
reply views Thread by Jerad Rose | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.