Master-to-master replication:
MySQL offers parameters auto_increment_increment, and auto_increment_offset for master to master replication. Most of the time folks believe when we set auto_increment_offset to odd no., such as 1 for server 1(Master 1), and even no. such as 2, for server 2 (Master 2), we are all set. But according to Oracle these two parameter "can be used to control the operation of AUTO_INCREMENT columns." Means columns which are not using AUTO_INCREMENT for primary key, master to master replication could break at some point, and require human intervention. Default is 1 for these two parameters. Ref. https://dev.mysql.com/doc/refman/8.0/en/replication-options-master.html
For auto_increment_increment - 10, and auto_increment_offset
auto_increment_increment:
This parameter controls the interval between successive column values. Use of auto_increment_increment in table definition - col_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY. When you SET @@auto_increment_increment=10, then each row will have value 1, 11, 21, 31 etc.
auto_increment_offset:This parameter determines the starting point for the AUTO_INCREMENT column value.
Use of auto_increment_offset in table definition - col_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY. When you SET @@auto_increment_offset=5, then each row will have value 5,15,25,35 etc.
Note: When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.
In ideal situation application logic, policies and procedures needs to be protected in such a way that says "if you want to modify row on Master1, do so on Master2 also" and that needs to exist for every row or object in every table to keep continue Master to Master replication, otherwise use certification based replication such as Galera, Group Replication, or InnoDB cluster, which can handle split brain, automatic fail over, and some other issues.
MySQL offers parameters auto_increment_increment, and auto_increment_offset for master to master replication. Most of the time folks believe when we set auto_increment_offset to odd no., such as 1 for server 1(Master 1), and even no. such as 2, for server 2 (Master 2), we are all set. But according to Oracle these two parameter "can be used to control the operation of AUTO_INCREMENT columns." Means columns which are not using AUTO_INCREMENT for primary key, master to master replication could break at some point, and require human intervention. Default is 1 for these two parameters. Ref. https://dev.mysql.com/doc/refman/8.0/en/replication-options-master.html
For auto_increment_increment - 10, and auto_increment_offset
auto_increment_increment:
This parameter controls the interval between successive column values. Use of auto_increment_increment in table definition - col_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY. When you SET @@auto_increment_increment=10, then each row will have value 1, 11, 21, 31 etc.
auto_increment_offset:This parameter determines the starting point for the AUTO_INCREMENT column value.
Use of auto_increment_offset in table definition - col_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY. When you SET @@auto_increment_offset=5, then each row will have value 5,15,25,35 etc.
Note: When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.
In ideal situation application logic, policies and procedures needs to be protected in such a way that says "if you want to modify row on Master1, do so on Master2 also" and that needs to exist for every row or object in every table to keep continue Master to Master replication, otherwise use certification based replication such as Galera, Group Replication, or InnoDB cluster, which can handle split brain, automatic fail over, and some other issues.
Comments
Post a Comment