복제

Table of Contents

1 소개

Real Mysql 2권의 16장 복제에 대해서 다룬다.

1.1 용어

  • 바이너리 로그(Binary Log) : MySQL 서버에서 발생하는 모든 변경 사항은 별도의 로그 파일에 순서대로 기록된다. 바이너리 로그에는 데이터의 변경 내역뿐만 아니라 데이터베이스나 테이블의 구조 변경과 계정과 권한의 변경 정보까지 모두 저장.
  • 이벤트(Event) : 바이너리 로그에 기록된 각 변경 정보들.
  • 릴레이 로그(Relay Log) : 레플리카 서버에서 소스 서버의 바이너리 로그를 읽어 들여 따로 로컬 디스크에 저장해준 파일.

1.2 복제타입

  • 바이너리 로그 파일 위치 기반 복제(Binary Log File Position Based Replication)
  • 글로벌 트랜잭션 ID 기반 복제(Global Transaction Identifiers Based Replication)

1.2.1 바이너리 로그 파일 위치 기반 복제

레플리카 서버에서 소스 서버의 binlog 파일명과 위치(offset, position)로 개별 바이너리 로그 이벤트를 식별해서 복제. 바이너리 로그 파일 위치 기반 복제는 MySQL 서버들이 모두 고유한 server_id 값을 가져야한다. 동일한 server_id 값을 가지는 경우 레플리카에서는 해당 이벤트를 적용하지 않고 무시한다. 그래서 고유한 server_id 를 설정해야한다.

  1. 설정 준비

    소스 서버 설정

    [mysqld]
    server_id=1
    log_bin=/binary-log-dir-path/binary-log-name
    sync_binlog=1
    binlog_cache_size=5M
    max_binlog_size=512M
    binlog_expire_logs_seconds=1209600
    

    소스서버에서 바이너리 로그가 정상적으로 기록되는지 다음 명령을 실행한다.

    mysql> SHOW MASTER STATUS;
    

    레플리카 서버 설정

    [mysqld]
    server_id=2
    ;; 레플리카 서버에서 복제를 위해 생성하는 릴레이 로그 파일도 복제 설정 시 기본적으로
    ;; 데이터 디렉터리 밑에 자동으로 생성
    relay_log=/relay-log-dir-path/relay-log-name 
    relay_log_purge=ON ;; 릴레이 로그 파일 자동 삭제
    read_only  ;; 레플리카는 대게 read_only이다. 
    log_slave_updates ;; 추후 소스 서버장애로 이 서버가 승격될 수도 있으므로 이 설정을 넣어 놓는다.
    
  2. 복제 계정 준비

    복제용 계정은 시작하려면 REPLICATION SLAVE 권한을 가져야 한다.

    CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_user_password';
    GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
    
  3. 데이터복사

    mysqldump 등과 같은 툴을 이용해 소스 서버에서 데이터를 내려받아 레플리카 서버로 복사하면 된다.

    mysqldump 는 "–single-transaction", "–master-data" 라는 두 옵션을 반드시 사용해야한다.

    "–single-transaction" 은 하나의 트랜잭션을 사용해 덤프가 진행되게 해서 mysqldump 가 테이블이나 레코드에 잠금을 걸지 않고 InnoDB 테이블에 대해 일관된 데이터를 덤프받을 수 있게 한다.

    "–master-data" 는 덤프 시작 시점의 소스 서버의 바이너리 로그 파일명과 위치 정보를 포함하는 복제 설정 구문(CHANGE REPLICATION SOURCE TO 또는 CHANGE MASTER TO)이 덤프 파일 헤더에 기록되는 옵션 복제 연결을 위해 반드시 필요하다.

    다음은 예시다

    > mysqldump -uroot -p --single-transaction --master-data=2 \
      --opt --routines --triggers --hex-blob --all-databases > source_data.sql
    

    데이터 덤프가 완료되면 source_data.sql 파일을 레플리카 서버로 옮겨 데이터 적재를 진행한다. /tmp 파일에 옮겨 놓았다고 해보자.

    ;; Mysql 서버에 직접 접속해 데이터 적재 명령 실행
    > SOURCE /tmp/master_data.sql
    
    ;; 둘중 하나 사용. 데이터 적재 명령 실행중.
    > mysql -uroot -p < /tmp/source_data.sql
    > cat /tmp/source_data.sql | mysql -root -p
    

    장기간 실행 중인 쿼리가 있다면 문제가 생길 수 있다. 실행 중/후에 글로벌 락을 모니터링하자.

  4. 복제시작

    소스 서버와 레플리카 간의 복제 설정을 하자. CHANGE REPLICATION SOURCE TO(또는 CHANGE MASTER TO) 명령으로, mysqldump 로 백업받은 파일의 헤더 부분에서 해당 명령어를 참조할 수 있다.

    > less /tmp/source_data.sql
    ...
    -- Position to start replication or point-in-time recovery from
    --
    -- CHANGE MASTER TO MASTER_LOG_FILE='binary-log.000002', MASTER_LOG_POS=2708
    

    이걸로 복제 명령을 준비한다.

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

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

    ...
    message: Authentication plugin 'caching_sha2_password' reported error: Authentication
    

    MySQL 인증 플러그인이 mysql_native_password 에서 caching_sha2_password 로 변경됨. 별도의 인증플러그인을 지정하지 않으면 디폴트가 수행됨. caching_sha2_password 로 설정되면 MySQL 접속할 때 반드시 SSL로 하거나 RSA키로 패스워드를 교환해야한다.

    잘되면 아래 명령어로 상태를 보자

    -- MySQL 8.0.22 미만
    > SHOW SLAVE STATUS \G
    -- MySQL 8.0.22 이상
    > SHOW REPLICA STATUS \G
    
  5. 트랜잭션 건너뛰기

    대게 사용자가 실수하여 PK가 겹치는 문제를 만드는 경우 등으로 복제중에 실패하는 경우가 있다.

    > SHOW REPLICA STATUS \G
    ...
    Last_Error: Error 'Duplicate entry '15' for key 'tb1.PRIMARY'' on query.
    Default database: 'test'.Query: 'insert into tb1 values(15)'
    

    sql_slave_skip_counter 를 1로 지정해서 INSERT쿼리를 건너뛰고 복제를 재개하게 할 수 있다.

    # Mysql 8.0.22 이상
    > STOP REPLICA SQL_THREAD;
    > SET GLOBAL sql_slave_skip_counter=1;
    > START REPLICA SQL_THREAD;
    

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

WIP

Date: 2022-01-03 Mon 00:00

Author: 남영환

Created: 2022-09-14 Wed 01:26

Emacs 27.2 (Org mode 9.4.4)

Validate