CODEWITHSUNDEEP
phpmysql
rkmourya
rkmourya

Reputation: 466

Setting manual increment value on synchronized mysql servers

I have a mysql\PHP application hosted on intranet and on internet. Both mysql servers are replicated i.e., synchronized on real time.

I have some tables which have auto increment id as primary key. When sync goes off, for new transactions same auto increment value is used on online as well as intranet server. So even when servers get connected and sync starts; records with same auto increment id do not get sync. Ids with non overlapping values get synced soon the servers get connected.

To resolve this issue, I am thinking of using manual increment values with different range on intranet and online.

Please suggest, what could be the best solution for this problem.

Also if I have to go with manual increment ids, what would be the best technique OR algo to assign ids separately on online and on intranet.

Upvotes: 2

Views: 449

Answers (2)

Hugo Delsing
Hugo Delsing

Reputation: 14173

There are a two things you can do. The first would be to change the starting value of the live server to a very high number (higher then the expected number of rows)

EG:

ALTER TABLE tbl AUTO_INCREMENT = 10000;

Now the numbers wont overlap. If that is not an option you can change the interval with 

SET @@auto_increment_increment=10;

But this would also mean there is an overlap at one point. because the server with increment steps of 1 will catch up with the steps of 10 after.. you guessed.. 10 rows! But you could bypass this by setting one server to start increment at 1 and the other at 2, and then make both have increment steps of 2.

That would make something like

intranet 1, 3, 5, 7, 9
live 2, 4, 6, 8, 10

You could also use a two column primary key to prevent duplication. Now you have an auto increment field in combination with a varchar field (live and intr) and that is your unique key.

CREATE TABLE `casetest`.`manualid` (
    `id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
    `server` VARCHAR( 4 ) NOT NULL DEFAULT 'live',
    `name` INT NOT NULL ,
    PRIMARY KEY ( `id` , `server` )
) ENGINE = MYISAM ;

Upvotes: 1

rkmourya
rkmourya

Reputation: 466

I figured out the solution to this problem.

While configuring the replication of the mysql servers auto increment settings should be adjusted such the ids on the servers never overlap. Example if you have 2 servers replicated one server should only generate even auto increment ID's and other only odd ids.

Here's the link for detail information on this.

http://jonathonhill.net/2011-09-30/mysql-replication-that-hurts-less/

Updating the settings on both the servers resolved this issue.

Upvotes: 1

Related Questions