crash-safe replication

예기치못한 MySQL shutdown, Duplicate Key 왜 발생할까 ?

• 먼저 MySQL replication Architecture를 보자
  

SQL thread

• replication Architecture 를 봤으니, 다시 왜 중복이 나는지 돌아가보자
• 간단하게 끝나는 내용 SQL thread 먼저
  
• Replica에 3의 값을 적용후 metadata 갱신 하지 못한 채로 갑작스러운 SHUTDOWN
• relay_log_info_repository=FILE인 경우, Replica에 적용내용과
  metadata정보가 틀어짐.
• TABLE의 경우 트랜잭션 처리되서 적용내용과 meatadata가 틀어짐을 방지

slave_relay_log_info

• 테이블 구조
  Create Table: CREATE TABLE slave_relay_log_info (
  Number_of_lines int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  Relay_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  Relay_log_pos bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  Master_log_name text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  Master_log_pos bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  :
  PRIMARY KEY (Channel_name)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information'
  If you set master_info_repository and relay_log_info_repository to TABLE, the mysql.slave_master_info and mysql.slave_relay_log_info tables are created using the InnoDB transactional storage engine.
  Updates to the replica's applier metadata repository table are committed together with the transactions,
  meaning that the replica's progress information recorded in that repository is always consistent with what has been applied to the database, even in the event of an unexpected server halt

IO thread

• metadata 갱신이 안된상태로 종료됨
• MySQL 기동시 insert into tab values(3)의 relay log가 기록된지 모르고
  복구과정에서 relay log file에 다시 기록하게 된다.
• Duplicate key 오류 발생
  Last_Error: Error 'Duplicate entry for key 'PRIMARY'' on query.

Crash-safe Replication

MySQL 서버가 예기치 못한 장애로 인해 비정상 종료되는경우,
재구동했을 때도 복제가 원활하게 재개될수 있는 것

• Crash-safe Replicaion 최소한의 설정
  relay_log_info_repository=TABLE
  relay_log_recovery = on
• 버전별 기본값
  variable / MySQL- 5.7/ MySQL- 8.0
  relay_log_recovery / OFF / OFF
  relay_log_info_repository / FILE / TABLE
  sync_relay_log / 10000 10000
  slave_preserve_commit_order OFF / OFF ≥ 8.0.27 ON / ≤ 8.0.26 OFF

relay_log_recovery

Set relay_log_recovery = ON, which enables automatic relay log recovery immediately following server startup.
This global variable defaults to OFF and is read-only at runtime, but you can set it to ON with the --relay-log-recovery option at replica startup following an unexpected halt of a replica.
Note that this setting ignores the existing relay log files, in case they are corrupted or inconsistent.
The relay log recovery process starts a new relay log file and fetches transactions from the source beginning at the replication SQL thread position recorded in the applier metadata repository.
The previous relay log files are removed over time by the replica's normal purge mechanism.

https://dev.mysql.com/doc/refman/8.0/en/replication-solutions-unexpected-replica-halt.html

MTS 및 GTID 환경에서 다른 추가 설정 필요