Setting a manual increment value on synchronized mysql servers

Question

I have a mysql \ PHP application hosted on the intranet and on the Internet. Both mysql servers are replicated, that is, synchronized in real time.

I have several tables that have auto increment id as their primary key. When synchronization is disabled, for new transactions, the same auto-increment value is used both on the network and on the intranet server. Thus, even when the servers connect and synchronization starts; records with the same auto increment identifier are not synchronized. Non-overlapping identifiers will sync soon and servers will connect.

To solve this problem, I am thinking of using manual increment values ​​with different ranges on the intranet and on the Internet.

Please suggest what might be the best solution to this problem.

Also, if I need to go with a manual incremental identifier, then what is the best OR algo method to assign identifiers separately on the Internet and on the intranet.

Answer 1

There are two things you can do. The first would be to change the initial value of the live server to a very large number (above the expected number of rows)

EG:

ALTER TABLE tbl AUTO_INCREMENT = 10000; 

Now the numbers will not overlap. If this is not an option, you can change the interval using

 SET @@auto_increment_increment=10; 

But it also means that there is overlap at one point. because the server in increments of 1 will catch up with increments of 10 after .. you guessed it .. 10 lines! But you can get around this by setting up one server to start the increment from 1 and the other to 2, and then do both steps in step 2.

Something like

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

You can also use a two-column primary key to prevent duplication. Now you have an auto-increment field combined with a varchar field (live and intr), and this 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 ; 

Answer 2

I understood the solution to this problem.

When you configure mysql server replication, you must configure the auto-increment settings so that the identifiers on the servers never overlap. For example, if you have 2 servers, then one server should generate only an auto-increment identifier and the other only odd identifiers.

Here is a link for more information about this.

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

Updating the settings on both servers resolved this issue.