2023/08/02 ~ 2023/08/07

16.3.2 글로벌 트랜잭션 아이디(GTID) 기반 복제

MySQL 5.5 버전까지는 복제를 설정할 때 바이너리 로그 파일 위치 기반 복제 방식만 가능
이 경우, 이벤트 식별은 바이너리 로그 파일이 저장돼 있는 소스 서버에서만 유효함
- 동일한 이벤트가 레플리카 서버에서도 동일한 파일명의 동일한 위치에 저장된다는 보장이 없어 복제에 문제가 생길 수 있음
소스 서버에서 발생한 각 이벤트들이 복제에 참여한 모든 SQL 서버들에서 동일한 고유 식별값을 가진다면 좀 더 유연하게 대처할 수 있을 것
- 글로벌 트랜잭션 아이디 Global Transaction Identifier (GTID): 전체 MySQL 서버들에서 고유하도록 각 이벤트에 부여된 식별값

16.3.2.1 GTID의 필요성

소스서버 A, A서버와 완전히 동기화된 레플리카 서버 B, 복제가 일부만 진행된 레플리카 서버 C 가 있다고 가정
- 예시1. 바이너리 로그 파일 위치 기반 복제 방식 - A서버의 장애가 발생한 경우
- A서버와 완전히 동기화된 레플리카 서버 B가 소스 서버로 승격
- A서버와 C서버 간의 복제는 끊어짐
- B서버로 사용자 트래픽이 유입되고 있고, C서버는 데이터 동기화되지 않은 상태이므로 서비스 불가
- B서버는 과부하 상태가 되고, C서버는 A서버와의 복제가 중단되어 복제를 최종 시점까지 동기화할 방법 없음
  - B서버의 릴레이 로그가 남아있다면 필요한 부분만 가져와서 C서버의 복제가 가능할 수도 있지만 보통 릴레이로그는 자동 삭제되므로 상당히 제한적인 방법
- 예시2. 글로벌 트랜잭션 아이디 복제 방식 - A 서버의 장애가 발생한 경우
- B서버를 C서버의 소스서버가 되도록 C서버에서 소스 서버 변경 명령이 실행됨
- C서버 복제 중단 시점은 B서버에 기록된 트랜잭션에서도 동일하게 알 수 있으므로 B서버의 바이너리 로그 이벤트를 이용해 동기화
- 레플리카 서버 C가 새로운 소스서버인 B와 동기화할 수 있도록 준비되면 클라이언트 쿼리요청을 B, C 서버가 나눠서 실행하도록 설정

16.3.2.2 글로벌 트랜잭션 아이디

바이너리 로그 파일에 기록된 이벤트들을 바이너리 로그 파일명과 파일 내의 위치로 식별하는 방식은 물리적인 방식
GTID는 논리적인 의미로서 물리적인 파일의 이름이나 위치와는 전혀 무관하게 생성됨
- GTID는 커밋되어 바이너리 로그에 기록된 트랜잭션에 한해서만 할당됨
- 읽기만 수행하는 SELECT 쿼리나 sql_log_bin 설정이 비활성화돼 있는 상태에서 발생한 트랜잭션은 바이너리에 기록되지 않음
- GTID가 할당되지 않음
- GTID = [source_id]:[transaction_id]
- 소스 아이디: 트랜잭션이 발생한 소스 서버를 식별하기 위한 값. server_uuid 시스템 변수 값을 사용
- 트랜잭션 아이디: 서버에서 커밋된 트랜잭션 순서대로 부여되는 값. 1부터 1씩 단조 증가하는 형태로 발급
  - auto.cnf파일 내에 server_uuid 값이 저장되어 있음
  - auto.cnf파일이 자동으로 생성된다고 하더라도 해당 파일에 저장돼 있는 UUID 값은 복제가 설정된 소스 서버와 레플리카 서버의 GTID 값에 사용되는 값이므로 삭제되지 않도록 주의
gtid_executed 테이블이나 gtid_executed 시스템 변수를 통해 GTID값 확인 가능 (p. 449 ~ 450)
- 연속된 값들인 경우 범위로 보여질 수 있음. 이렇게 하나 이상의 GTID 값으로 구성돼 있는 것을 GTID Sets 라고 함
- GTID 셋에서는 서로 다른 UUID를 가지는 GTID 값들도 포함될 수 있음 -> UUID 값 변경, 여러 서버에서 데이터 복제 등
- 서로 다른 UUID를 가지는 각각의 GTID는 콤마로 구분되어 표시됨 ex) {uuid1}:1-2, {uuid2}:1-5
- mysql.gtid_executed 테이블은 단순히 현재 실행된 GTID 값 저장 외에도
- 레플리카 서버에서 바이너리 로그가 비활성화돼 있는 상태에서 GTID 기반의 복제를 사용할 수 있도록 함
- 예기치 못한 문제로 바이너리 로그가 손실됐을 때 GTID 값이 보존될 수 있도록 함
- MySQL 5.7.5 버전에서 처음 도입되었고 InnoDB 스토리지 엔진으로 설정돼 있음
- MySQL 8.0.17 이상 버전의 경우에는 매 트랜잭션이 커밋될 때마다 mysql.gtid_executed 테이블에도 GTID값이 바로 저장됨
- 특정 시점에만 저장이 되는 상태로 설정된 경우 마지막에 실행된 트랜잭션의 GTID값을 조회시 gtid_executed 시스템 변수를 확인
- MySQL 서버는 주기적으로 mysql.gtid_executed 테이블에 대해 쌓여있는 전체 데이터를 하나로 압축
- 압축: mysql.gtid_executed 테이블에 여러 레코드로 저장된 interval_start와 interval_end를 연속된 것들끼리 모아서 1건의 레코드로 만드는 것
- 바이너리 로그가 활성화돼 있는 경우 바이너리 로그 파일이 로테이션될 때 자동으로 압축이 수행됨
- 바이너리 로그가 비활성화돼있는 경우 실행된 트랜잭션 수가 gtid_executed_compression_period 시스템 변수에 지정된 수까지 도달하면 스레드에서 압축을 수행하고, 그 후 다시 다음 주기가 돌아올 때까지 슬립 모드를 유지
- gtid_executed_compression_period 시스템 변수값이 0이면 압축을 수행하지 않다가 필요에 따라 자동으로 실행함

16.3.1.1 바이너리 로그 파일 위치 기반의 복제 구축

16.3.1.1.4 복제시작

복제를 설정하는 명령어

CHANGE REPLICATION SOURCE TO 또는 CHANGE MASTER TO

mysqldump로 백업 받은 파일의 헤더 부분에서 해당 명령어를 참조 할 수 있다

백업 받은 파일은 크기 때문에 vi보다는 less 같은 페이지 단위의 뷰어를 이용하는 것이 좋다

복제 설정 예제

-- // mysql 8.0.23 이상 버전
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='source_server_host',
  SOURCE_PORT=3306,
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='repl_user_password',
  SOURCE_LOG_FILE='binary-log.000002',
  SOURCE_LOG_POS=2708
  GET_SOURCE_PUBLIC_KEY=1;

-- // mysql 8.0.23 미만 버전
CHANGE MASTER TO
  MASTER_HOST='source_server_host',
  MASTER_PORT=3306,
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_user_password',
  MASTER_LOG_FILE='binary-log.000002',
  MASTER_LOG_POS=2708,
  GET_MASTER_PUBLIC_KEY=1;

위에 옵션들 말고도 더 있음

https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html

GET_SOURCE_PUBLIC_KEY(또는 GET_MASTER_PUBLIC_KEY)는 RSA 키 기반 비밀번호 교환 방식의 통신을 위해 공개키를 소스 서버에 요청할 것인지 여부를 나타낸다

복제 설정에 보안된 연결(SSL)과 관련된 옵션들을 명시하지 않아 레플리카 서버와 소스 서버가 암호화되지 않은 통신 방식으로 연결되는 경우 GET_SOURCE_PUBLIC_KEY=1 구문 없이 복제 설정 후 복제 시작시 에러 발생

에러가 발생되는 이유

MySQL 8.0.4버전 부터 기본 인증 플러그인이 바뀜
- mysql_native_password → caching_sha2_password
- 그래서 MySQL 서버에 계정을 생성할 때 별도로 인증 플러그인을 지정하지 않는 이상의 서버의 기본 인증 플러그인인 caching_sha2_password로 설정된다
caching_sha2_password 플러그인으로 설정되고 그 계정을 사용해 MySQL에 접속할 때 반드시 보안된 연결을 사용하거나 RSA 키를 사용해 패스워드를 교환하는 방식의 비암호화된 연결을 사용해야 한다

복제 설정 후 SHOW REPLICA STATUS (또는 SHOW SLAVE STATUS) 명령을 실행하면 복제 관련 정보가 레플리카 서버 MySQL에 등록돼 있는 것을 확인할 수 있다

Replica_IO_Running(또는 Slave_IO_Running)과 Replica_SQL_Running(또는 Slave_SQL_Running) 칼럼 값이 "No"로 되어있다
- 복제 관련 정보가 등록만 된 것이고 동기화는 따로 시작해야 한다
Seconds_Behind_Source(또는 Seconds_Behind_Master) 값이 0이 되면 소스 서버와 레플리카 서버의 데이터가 완전히 동기화됐음을 의미한다

START REPLICA 또는 START SLAVE 명령을 실행하여 동기화 따로 시작

위에 No로 설정된 두 칼럼이 "Yes"로 바뀌고 이 명령어를 11:45에 시작했으면 레플리카 서버는 가능한 10:30(mysqldump 시작 지점) 부터 11:45까지의 데이터 변경사항들을 소스 서버로부터 가져와 적용하게 된다
만약 위에 No로 설정된 두 칼럼이 "Yes"로 바뀌지 않는다면 복제 설정 정보가 잘못 입력됐을 가능성이 상당히 높다
- 소스 서버의 호스트명, MySQL 포트, 레플리카 서버에서 사용하는 복제용 접속 계정과 비밀번호
- 네트워크상의 문제가 없는지도 확인해 보는 것이 좋음

16.3.1.2 바이너리 로그 파일 위치 기반의 복제에서 트랜잭션 건너뛰기

복제로 구성돼 있는 MySQL 서버들을 운영하다 보면 레플리카 서버에서 소스 서버로부터 넘어온 트랜잭션이 제대로 실행되지 못하고 에러가 발생해 복제가 멈추는 현상이 종종 발생함

MySQL 서버의 비정상 종료와 같이 실제 예기치 못한 문제가 있어서 발생할 수도 있음
대부분 사용자의 실수
- 중복 키 에러

레플리카 서버의 데이터를 모두 버리고 처음부터 다시 레플리카 서버를 구축한 뒤 복제를 다시 구성해야 할 수도 있다

복제를 중단시킨 문제가 수동으로 복구가 불가능할 정도로 심각한 문제인 경우

하지만 경우에 따라 레플리카 서버에서 문제되는 소스 서버의 트랜잭션을 무시하고 넘어가도록 처리해도 괜찮을 때가 있다

sql_slave_skip_counter 시스템 변수를 이용해 문제되는 트랜잭션을 건너뛸 수 있다

443p 책 예제는 바이너리 로그 위치 기반 복제가 설정된 레플리카 서버에서 중복된 키로 인해 INSERT 쿼리가 실패한 상태로 복제가 멈춰져 있다고 가정함

다음과 같이 복제를 중단한 후 sql_slave_skip_counter 변수의 값을 1로 지정하고 레플리케이션 SQL 스레드를 재시작하면 레플리카 서버는 에러가 발생한 INSERT 쿼리를 건너뛰고 정상적으로 복제를 재개하게 된다

-- // MySQL 8.0.22 미만 버전
STOP SLAVE SQL_THREAD;
SET GLOBAL sql_slave_skip_counter=1;
START SLAVE SQL_THREAD;

-- // MySQL 8.0.22 이상 버전
STOP REPLICA SQL_THREAD;
SET GLOBAL sql_slave_skip_counter=1;
START REPLICA SQL_THREAD;

sql_slave_skip_counter에는 적용하지 않고 건너뛸 바이너리 로그 이벤트 그룹 수를 지정

실제로 DML 쿼리 문장 하나를 가진 바이너리 로그 이벤트 1개를 무시하는 것이 아닌 현재 이벤트를 포함한 이벤트 그룹을 무시하는 것
트랜잭션을 지원하는 테이블의 경우에는 트랜잭션이 하나의 이벤트 그룹
트랜잭션을 지원하지 않은 테이블에서는 DML 문장 하나하나가 이벤트 그룹이 된다

MySQL 서버에서 실행되는 DML 쿼리들이 단순하게 하나의 트랜잭션에 DML 쿼리가 하나만 실행되는 형태라면

sql_slave_skip_counter는 곧 쿼리의 개수
사용자는 적용이 무시되는 쿼리 개수를 명확하게 알 수 있다
특정 쿼리만 무시하게 할 수도 있을 것이다

그러나 하나의 트랜잭션에 여러 DML 쿼리들이 포함되는 경우에서 에러가 발생한다면

에러가 발생한 쿼리 외에 다른 쿼리들이 예상치 못하게 무시될 수 있다
사용자는 sql_slave_skip_counter 시스템 변수를 사용할 때 위와 같은 점을 주의해서 사용해야 한다

기타 1

https://dev.mysql.com/doc/refman/8.0/en/replication-howto-slaveinit.html

복제에서 unix socket file을 사용할 수 없고 tcp/ip를 사용하여 source server에 연결할 수 있어야 한다

기타 2

https://blog.ex-em.com/1681

select thread_id, name, type from performance_schema.threads;
+-----------+----------------------------------------+------------+
| thread_id | name                                   | type       |
+-----------+----------------------------------------+------------+
|         1 | thread/sql/main                        | BACKGROUND |
|         2 | thread/sql/thread_timer_notifier       | BACKGROUND |
|         3 | thread/innodb/io_ibuf_thread           | BACKGROUND |
|         4 | thread/innodb/io_log_thread            | BACKGROUND |
|         5 | thread/innodb/io_read_thread           | BACKGROUND |
|         6 | thread/innodb/io_read_thread           | BACKGROUND |
|         7 | thread/innodb/io_read_thread           | BACKGROUND |
|         8 | thread/innodb/io_read_thread           | BACKGROUND |
|         9 | thread/innodb/io_write_thread          | BACKGROUND |
|        10 | thread/innodb/io_write_thread          | BACKGROUND |
|        11 | thread/innodb/io_write_thread          | BACKGROUND |
|        12 | thread/innodb/io_write_thread          | BACKGROUND |
|        13 | thread/innodb/page_cleaner_thread      | BACKGROUND |
|        15 | thread/innodb/srv_lock_timeout_thread  | BACKGROUND |
|        16 | thread/innodb/srv_error_monitor_thread | BACKGROUND |
|        17 | thread/innodb/srv_monitor_thread       | BACKGROUND |
|        18 | thread/innodb/srv_master_thread        | BACKGROUND |
|        19 | thread/innodb/srv_purge_thread         | BACKGROUND |
|        20 | thread/innodb/srv_worker_thread        | BACKGROUND |
|        21 | thread/innodb/srv_worker_thread        | BACKGROUND |
|        22 | thread/innodb/srv_worker_thread        | BACKGROUND |
|        23 | thread/innodb/buf_dump_thread          | BACKGROUND |
|        24 | thread/innodb/dict_stats_thread        | BACKGROUND |
|        25 | thread/sql/compress_gtid_table         | FOREGROUND |
|        26 | thread/sql/con_sockets                 | BACKGROUND |
|        27 | thread/sql/one_connection              | FOREGROUND |
+-----------+----------------------------------------+------------+

mojh7 / real-mysql-study