By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 2,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

AUTO_INCREMENT - configuration for replication with dual masters

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.