MySQL 마스터/마스터 replication 에서 AUTO_INCREMENT 문제 해결 방법

MySQL 서버를 마스터/마스터로 DB replication 할 경우, MySQL 테이블에 AUTO_INCREMENT key 를 포함되어 있으면 마스터#1 및 마스터#2 DB 에 모두 INSERT 할 경우 중복키로 충돌이 발생하여서 replication 을 위한 Slave SQL 쓰레드가 중단될 수 있습니다. replication 에서 AUTO_INCREMENT 로 인한 중복키 충돌을 예방하는 방법은 다음과 같습니다.

​

1. 마스터​ #1 에서 아래와 같이 실행한다.

  - 아래와 같이 실행하면 1, 3, 5 ... 와 같이 홀수로 key 가 생성된다.​

​  mysql> set global auto_increment_increment=2;

  mysql> show variables like 'auto_increment%';
+---------------------------------+-------+
| Variable_name                  | Value |
+---------------------------------+-------+
| auto_increment_increment  | 2     |
| auto_increment_offset        | 1     |
+---------------------------------+-------+

2. 마스터​ #2 에서 아래와 같이 실행한다.

  - 아래와 같이 실행하면 2, 4, 6 ... 와 같이 짝수로 key 가 생성된다.​

​  mysql> set global auto_increment_increment=2;

  mysql> set global auto_increment_offset=2;

  mysql> show variables like 'auto_increment%';
+---------------------------------+-------+
| Variable_name                  | Value |
+---------------------------------+-------+
| auto_increment_increment  | 2     |
| auto_increment_offset        | 2     |
+---------------------------------+-------+

​

3. 주의사항

​

  - 위와 같이 설정하면 홀수, 짝수 값이 순차적으로 증가하지 못하고 중간에 누락된 것처럼 보여질 수 있다.

  - 만약 AUTO_INCREMENT 값이 1부터 순차적으로 증가하되 누락된 수가 없어야 한다면 위와 같이 설정하면 안 된다.

  - INSERT 쿼리가 마스터#1 및 마스터#2 에서 임의의 순서대로 자주 실행된다면 AUTO_INCREMENT 잠금으로 인해 대기 현상이 발생할 수 있다.