[MySQL] show slave status 알아보기 ( replica status )

 

- 목차

 

들어가며.

SHOW SLAVE STATUS 는 MySQL Replicas 에서 복제 상태를 확인할 수 있는 MySQL 명령어입니다.

이 명령어는 MySQL Replica 에서 Replication 이 정상적으로 작동하고 있는지, 복제 지연이 발생하고 있는지, 또는 복제에 실패한 이유가 무엇인지 등을 파악하는 데 사용됩니다.

show slave status 명령어는 MySQL Replica 에서만 실행 가능하여, 현재의 Replication 상태를 출력합니다.

이와 유사하게 MySQL 의 Primary 서버에서는 show master status 명령어를 사용할 수 있습니다.

 

MySQL Replication 구축.

두개의 MySQL Docker Container 를 통해서 MySQL 의 Replication 구조를 구현해보도록 합니다.

 

먼저 2개의 MySQL Docker Container 를 연결할 Docker Network 를 생성합니다.

 

docker network create mysql

 

MySQL Primary Server 를 실행합니다.

my.cnf 파일과 초기화 SQL 스크립트를 준비 후, MySQL 에 Mount 하여 실행합니다.

 

cat <<'EOF'> /tmp/primary.cnf
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW
enforce_gtid_consistency=ON
gtid_mode=ON
EOF

cat <<'EOF'> /tmp/initial.sql

CREATE USER 'replica_user'@'%' IDENTIFIED BY 'replica_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
FLUSH PRIVILEGES;

EOF


docker run -d --platform linux/amd64 \
  --network mysql \
  --hostname primary --name primary \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -v /tmp/primary.cnf:/etc/mysql/conf.d/primary.cnf \
  -v /tmp/initial.sql:/docker-entrypoint-initdb.d/initial.sql \
  mysql:8.0.30

 

 

MySQL Replica 의 실행 명령어는 아래와 같습니다.

 

cat <<'EOF'> /tmp/replica.cnf
[mysqld]
server-id=2
relay-log=relay-log
read_only=1
enforce_gtid_consistency=ON
gtid_mode=ON

log_bin=mysql-bin
binlog_format=ROW
EOF

cat <<'EOF'> /tmp/replica_initial.sql

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'primary',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'replica_user',
  SOURCE_PASSWORD = 'replica_password',
  SOURCE_AUTO_POSITION = 1;

START SLAVE;

EOF

docker run -d --platform linux/amd64 \
  --network mysql \
  --name replica --hostname replica \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -v /tmp/replica.cnf:/etc/mysql/conf.d/replica.cnf \
  -v /tmp/replica_initial.sql:/docker-entrypoint-initdb.d/initial.sql \
  mysql:8.0.30

 

 

2개의 MySQL Docker Container 를 실행 이후에 Primary - Replica 관계로 이어지며,

Replica 의 Slave Status 초기상태값은 아래와 같습니다.

 

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: primary
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 197
               Relay_Log_File: relay-logf.000005
                Relay_Log_Pos: 413
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 197
              Relay_Log_Space: 1094

 

참고로 Slave_IO_Running 과 Slave_SQL_Running 이 Yes 상태여야지 정상적으로 연결됨을 의미합니다.

Primary - Replica 의 정상 연결 후에 Primary 의 Bin Log 와 Replica 의 Relay Log 가 표시됩니다.

 

 

Primary 에 데이터 생성해보기.

이제 Primary 에 데이터를 추가하여 Replica 에 Replication 이 되는지 확인합니다.

 

먼저 Primary MySQL 에서 "CREATE DATABASE test;" DDL Query 를 실행합니다.

 

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: primary
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 382
               Relay_Log_File: relay-logf.000005
                Relay_Log_Pos: 598
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

그리고 Table 을 생성합니다.

create table test.test_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  value VARCHAR(100)  
);

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: primary
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 637
               Relay_Log_File: relay-logf.000005
                Relay_Log_Pos: 853
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

 

Row 를 생성합니다.

 

INSERT INTO test.test_table (value) VALUES ('1');
INSERT INTO test.test_table (value) VALUES ('2');
INSERT INTO test.test_table (value) VALUES ('3');

show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: primary
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1501
               Relay_Log_File: relay-logf.000005
                Relay_Log_Pos: 1717
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

 

위 예시들에서 확인할 수 있듯이, DDL 또는 DML Query 가 실행되면 Bin Log 와 Relay Log 의 Position 이 변경됩니다.

이 Position 은 단조 증가의 방식으로 계속 증가하게 됩니다.

 

show slave status.

show slave status 의 출력 결과값들에 대해서 알아보도록 하겠습니다.

 

Slave_IO_Running.

Slave_IO_Running 은 Replica 의 IO Thread 가 실행 중인지 그리고 Primary 와 연결되었는지 여부를 알려주는 결과값입니다.

이 값이 Yes 로 설정되어야 IO Thread 가 정상적으로 동작함을 의미합니다.

참고로 IO Thread 는 Primary MySQL 로부터 Bin Log 의 이벤트들을 조회하기 위한 Replica 의 Thread 입니다.

 

Slave_SQL_Running.

Slave_SQL_Running 은 Replica 의 SQL Thread 의 정상 실행 여부를 알려주는 결과값입니다.

Replica MySQL 은 IO Thread 와 SQL Thread 가 독립적으로 실행되면서 데이터 복제가 수행됩니다.

특히 SQL Thread 는 Relay Log 에 기록된 데이터를 실제 Replica MySQL 내부에 생성하는 역할을 수행합니다.

 

Slave_SQL_Running 또한 정상적으로 실행 중이라면 Yes 라고 설정됩니다.

 

Master_Host, Master_User, Master_port.

Master_Host, Master_Post, Master_User 은 지정된 Primary MySQL 의 주소 정보와 User 를 의미합니다.

Replica MySQL 에서 replication source 를 지정함면서 설정된 Primary MySQL 의 메타정보를 의미합니다.

 

 

Connect_Retry.

Connect_Retry 는 Replica MySQL 이 Primary MySQL 와의 연결을 시도할 때 재시도 간격을 나타냅니다.

기본적으로 이 값은 60초로 설정되어 있고 만약 마스터와의 연결이 끊어졌거나 실패하면,

Replica 는 Connect_Retry 초 만큼 대기한 뒤에 다시 연결을 시도하게 됩니다.

 

 

Master_Log_File.

Master_Log_File 은 Replica 의 IO Thread 가 읽고 있는 Primary 의 바이너리 로그 파일 이름을 나타냅니다.

Replica IO Thread 는 변경 이벤트를 가져와 Relay Log File 에 기록하며,

Replica SQL Thread 는 이를 처리하여 Replica 데이터를 Primary 와 동기화합니다.

 

Relay_Log_File.

Relay_Log_File 은 Replica 서버가 처리 중인 Relay Log File 을 의미합니다.

구체적으로 SQL Thread 가 복제 중인 이벤트가 저장된 Relay Log File 을 가리킵니다.

 

Relay_Master_Log_File.

Relay_Master_Log_File 은 현재 처리 중인 Relay Log File 이 Primary 의 어떤 Bin Log FIle 의 복제본이지를 의미합니다.

그래서 현재 처리 중인 Relay Log File 의 원본 Bin Log File 을 의미한다고 생각하시면 됩니다.