Set variable group_replication_auto_increment_increment=1 in Mysql


Check the status

            show global variables like 'group_replication_auto_increment_increment';
            show global variables like 'auto_increment_increment';
            show global variables like 'auto_increment_offset';
If all above variable values have greater than one, follow below steps

On the Fly:

1.set global group_replication_auto_increment_increment =1;

2.set global auto_increment_increment =1;

Note: on the fly we con't update auto_increment_offset value.


In my.ini/my.cnf


1.Stop the replication in Secondary server using below command

            STOP GROUP_REPLICATION;

2.Stop the replication in primary server 

            STOP GROUP_REPLICATION;

3.Stop Primary server service


4.Stop Secondary Server Service


5.Add below variables in  my.ini/cnf file in both Primary and Secondary servers and save

            group_replication_auto_increment_increment =1 
            auto_increment_increment =1
            auto_increment_offset=1

6.Start the Primary server service


7.Start the Secondary server service


8.Secondary server side execute below query

      reset master;

9.Primary server side execute below query

      reset master;
      SET GLOBAL group_replication_bootstrap_group=ON;
      START GROUP_REPLICATION;
      SET GLOBAL group_replication_bootstrap_group=OFF;

10.Secondary Server side

      START GROUP_REPLICATION;

11.Check replication Member_state=ONLINE

      SELECT * FROM performance_schema.replication_group_members;



NOTE:These changes are recommended for MASTER-SLAVE group replication only.

for more details visit Mysql official site: 
https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html







Comments

Popular Posts