mysql auto increment and merging multiple databases into one master

Question

We have Master Database A and clients DATABASE B and DATABASE C.

Now Database A is having auto_increment = 10000 and auto_increment_offset = 10000.

We want my clients DATABASE B and DATABASE C setted with auto_increment = 10001 and auto_increment_offset = 10000 AND auto_increment = 10002 and auto_increment_offset = 10000 respectively to get merge with Master Database A.

My question is that above clients part is possible or not, if possible, what is the percentage of getting conflicts and how to avoid it.

Also,about performance that we are concerned as per table auto increment increases by 10000 at a time rather than typical increment by 1.

score 1 · Answer 1 · answered Sep 26 '12 at 17:39

1

We have done the opposite, don't interleave, but give them separate spaces:

Database A auto_increment = 10000000 for all tables
Database B auto_increment = 20000000
Database C auto_increment = 30000000

...and merge parts of Database C with Database B, etc, or how ever we like. But we don't have much chance of conflict because the number of rows per database is relatively low < 10000, and this is not a master-slave situation.

answered Sep 26 '12 at 17:39

Scott Presnell

1,528
10
23

Thanks, our major concern is about the productivity/performance. Will any of your suggested model or our model will have any performance issue. – Jyotir Bhandari Sep 26 '12 at 18:00
There has been no performance issue for us, I don't think that your design will have a performance issue. – Scott Presnell Sep 26 '12 at 18:21
I swear I'd read negative reports about using non-sequential indexes, but I can't find anything definitive in a quick search. – Alain Collins Sep 26 '12 at 18:32

mysql auto increment and merging multiple databases into one master

1 Answers1