[MySQL] GTID mode 테스트 - MHA 고가용성

hwwwa·2024년 10월 1일
mysql
0
post-custom-banner

이전 포스트 👉 MySQL GTID 모드에서 복제 구성 테스트
이번 포스트에서 다룰 주제는 MHA 고가용성 구조에서의 GTID mode 테스트입니다.

GTID 모드에서 MHA 고가용성 테스트

복제 연결 후 MHA 로그 확인

  • GTID 모드 활성화 후 MHA 정보 및 로그 확인
    -> GTID 활성화 직후에는 MHA Monitor에는 남은 로그가 없으나, 고가용성 오류 발생
    -> 초기 MHA 구동 시 남은 로그를 살펴보면, GTID 모드가 활성화되지 않았다는 로그를 볼 수 있음
    Thu Sep 5 18:25:32 2024 - [info] GTID (with auto-pos) is not supported

    Thu Sep  5 18:25:30 2024 - [info] MHA::MasterMonitor version 0.57.
Thu Sep  5 18:25:32 2024 - [info] GTID failover mode = 0
Thu Sep  5 18:25:32 2024 - [info] Dead Servers:
Thu Sep  5 18:25:32 2024 - [info] Alive Servers:
Thu Sep  5 18:25:32 2024 - [info]   tstr-inhwa-wa810(10.161.229.204:13306)
Thu Sep  5 18:25:32 2024 - [info]   tstr-inhwa-wa809(10.161.229.126:13306)
Thu Sep  5 18:25:32 2024 - [info] Alive Slaves:
Thu Sep  5 18:25:32 2024 - [info]   tstr-inhwa-wa809(10.161.229.126:13306)  Version=8.0.34 (oldest major version between slaves) log-bin:enabled
Thu Sep  5 18:25:32 2024 - [info]     Replicating from 10.161.229.204(10.161.229.204:13306)
Thu Sep  5 18:25:32 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Sep  5 18:25:32 2024 - [info] Current Alive Master: tstr-inhwa-wa810(10.161.229.204:13306)
Thu Sep  5 18:25:32 2024 - [info] Checking slave configurations..
Thu Sep  5 18:25:32 2024 - [warning]  relay_log_purge=0 is not set on slave tstr-inhwa-wa809(10.161.229.126:13306).
Thu Sep  5 18:25:32 2024 - [info] Checking replication filtering settings..
Thu Sep  5 18:25:32 2024 - [info]  binlog_do_db= , binlog_ignore_db= 
Thu Sep  5 18:25:32 2024 - [info]  Replication filtering check ok.
Thu Sep  5 18:25:32 2024 - [info] GTID (with auto-pos) is not supported
Thu Sep  5 18:25:32 2024 - [info] Starting SSH connection tests..
Thu Sep  5 18:25:35 2024 - [info] All SSH connection tests passed successfully.
...
Thu Sep  5 18:25:38 2024 - [info] Set master ping interval 3 seconds.
Thu Sep  5 18:25:38 2024 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Thu Sep  5 18:25:38 2024 - [info] Starting ping health check on tstr-inhwa-wa810(10.161.229.204:13306)..
Thu Sep  5 18:25:38 2024 - [info] Ping(INSERT) succeeded, waiting until MySQL doesn't respond..

  • MHA 재기동 수행 후 MHA Monitor 로그 확인
    -> GTID 모드가 활성화된 것을 확인할 수 있음
    => GTID 모드를 활성화한 후에는 MHA Monitor를 재기동 해주어야만 한다!!
    -> 실제 서비스 환경에서는 고가용성 중지 -> MySQL GTID 활성화 -> 고가용성 시작 순으로 진행해야할 것 같다.
    Sun Sep 8 18:42:30 2024 - [info] GTID failover mode = 1
    Sun Sep 8 18:42:30 2024 - [info] GTID ON
    Sun Sep 8 18:42:30 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.

    Sun Sep  8 18:42:29 2024 - [info] MHA::MasterMonitor version 0.57.
Sun Sep  8 18:42:30 2024 - [info] GTID failover mode = 1
Sun Sep  8 18:42:30 2024 - [info] Dead Servers:
Sun Sep  8 18:42:30 2024 - [info] Alive Servers:
Sun Sep  8 18:42:30 2024 - [info]   tstr-inhwa-wa810(10.161.229.204:13306)
Sun Sep  8 18:42:30 2024 - [info]   tstr-inhwa-wa809(10.161.229.126:13306)
Sun Sep  8 18:42:30 2024 - [info] Alive Slaves:
Sun Sep  8 18:42:30 2024 - [info]   tstr-inhwa-wa809(10.161.229.126:13306)  Version=8.0.34 (oldest major version between slaves) log-bin:enabled
Sun Sep  8 18:42:30 2024 - [info]     GTID ON
Sun Sep  8 18:42:30 2024 - [info]     Replicating from 10.161.229.204(10.161.229.204:13306)
Sun Sep  8 18:42:30 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
Sun Sep  8 18:42:30 2024 - [info] Current Alive Master: tstr-inhwa-wa810(10.161.229.204:13306)
Sun Sep  8 18:42:30 2024 - [info] Checking slave configurations..
Sun Sep  8 18:42:30 2024 - [info] Checking replication filtering settings..
Sun Sep  8 18:42:30 2024 - [info]  binlog_do_db= , binlog_ignore_db= 
Sun Sep  8 18:42:30 2024 - [info]  Replication filtering check ok.
Sun Sep  8 18:42:30 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sun Sep  8 18:42:30 2024 - [info] Checking SSH publickey authentication settings on the current master..
Sun Sep  8 18:42:31 2024 - [info] HealthCheck: SSH to tstr-inhwa-wa810 is reachable.
...
Sun Sep  8 18:42:31 2024 - [info] Starting ping health check on tstr-inhwa-wa810(10.161.229.204:13306)..
Sun Sep  8 18:42:31 2024 - [info] Ping(INSERT) succeeded, waiting until MySQL doesn't respond..

MHA 고가용성 중지 -> GTID 활성화 -> MHA 고가용성 시작

  1. MHA Monitor 중지

    # 로그 확인
Mon Sep  9 17:58:29 2024 - [info] Got terminate signal. Exit.

  2. enforce_gtid_consistency 변경

    [2024-9-09 17:31:50][tstr-inhwa-wa810][(none)]> SET GLOBAL ENFORCE_GTID_CONSISTENCY = WARN;
[2024-9-09 17:36:41][tstr-inhwa-wa809][(none)]> SET GLOBAL ENFORCE_GTID_CONSISTENCY = WARN;

[2024-9-09 17:36:46][tstr-inhwa-wa810][(none)]> SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON;
[2024-9-09 17:36:47][tstr-inhwa-wa809][(none)]> SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON;

  3. gtid_mode 를 OFF_PERMISSIVE 으로 변경

    [2024-9-09 17:37:07][tstr-inhwa-wa809][(none)]> SET GLOBAL GTID_MODE = OFF_PERMISSIVE;
[2024-9-09 17:37:05][tstr-inhwa-wa810][(none)]> SET GLOBAL GTID_MODE = OFF_PERMISSIVE;

  4. gtid_mode 를 ON_PERMISSIVE 으로 변경

    [2024-9-09 17:37:46][tstr-inhwa-wa810][(none)]> SET GLOBAL GTID_MODE = ON_PERMISSIVE;
[2024-9-09 17:37:52][tstr-inhwa-wa809][(none)]> SET GLOBAL GTID_MODE = ON_PERMISSIVE;

  5. 잔여 ANONYMOUS 트랜잭션 확인

    [2024-9-09 17:39:13][tstr-inhwa-wa810][(none)]> SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+

[2024-9-09 17:39:16][tstr-inhwa-wa809][(none)]> SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+

  6. 모든 ANONYMOUS 트랜잭션이 모든 서버에 복제될 때까지 대기

    1. Master 에서 현재 status 확인

      [2024-9-09 17:39:33][tstr-inhwa-wa810][(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                             |
+------------------+----------+--------------+------------------+-----------------------------------------------+
| mysql-bin.000026 |      197 |              |                  | 83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174142 |
+------------------+----------+--------------+------------------+-----------------------------------------------+

    2. 모든 Slave 에서 MASTER_POS_WAIT 값이 0인지 확인

      [2024-9-09 17:39:35][tstr-inhwa-wa809][(none)]> SELECT MASTER_POS_WAIT('mysql-bin.000026',197);
+-----------------------------------------+
| MASTER_POS_WAIT('mysql-bin.000026',197) |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+

  7. gtid_mode 를 ON 으로 변경

    [2024-9-09 17:39:50][tstr-inhwa-wa810][(none)]> SET GLOBAL GTID_MODE = ON;
[2024-9-09 17:40:05][tstr-inhwa-wa809][(none)]> SET GLOBAL GTID_MODE = ON;


[2024-9-09 17:40:47][tstr-inhwa-wa809][(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                            ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...

  8. my.cnf 에 gtid_mode=ON 및 enforce_gtid_consistency=ON 추가

    vi my.cnf

# 아래 내용 추가
gtid_mode=ON
enforce_gtid_consistency=ON

  9. 복제 그룹 내의 모든 Slave 서버에서 GTID 프로토콜 사용을 위해 복제 연결 재설정
    모든 트랜잭션에 GTID가 존재하는 것이 보장되므로 GTID 프로토콜을 통해 자동 장애 조치 수행 가능

    [2024-9-09 17:42:29][tstr-inhwa-wa809][(none)]> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.01 sec)

[2024-9-09 17:42:35][tstr-inhwa-wa809][(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: ***
                  Master_User: ***
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000027
          Read_Master_Log_Pos: 197
               Relay_Log_File: relay-bin.000008
                Relay_Log_Pos: 373
        Relay_Master_Log_File: mysql-bin.000027
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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

[2024-9-09 17:42:39][tstr-inhwa-wa809][(none)]> CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

[2024-9-09 17:43:09][tstr-inhwa-wa809][(none)]> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

[2024-9-09 17:50:41][tstr-inhwa-wa809][(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: ***
                  Master_User: ***
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000027
          Read_Master_Log_Pos: 197
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 4976031
        Relay_Master_Log_File: mysql-bin.000020
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                            ...
           Retrieved_Gtid_Set: 83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-174142
            Executed_Gtid_Set: 83019ac0-6b64-11ef-8bdf-fa163ed63ea8:1-16039
                            ...

  10. MHA Monitor 기동

    # 로그 확인
Mon Sep  9 17:53:57 2024 - [info] MHA::MasterMonitor version 0.57.
Mon Sep  9 17:53:59 2024 - [info] GTID failover mode = 1
Mon Sep  9 17:53:59 2024 - [info] Dead Servers:
Mon Sep  9 17:53:59 2024 - [info] Alive Servers:
Mon Sep  9 17:53:59 2024 - [info]   tstr-inhwa-wa810(10.161.229.204:13306)
Mon Sep  9 17:53:59 2024 - [info]   tstr-inhwa-wa809(10.161.229.221:13306)
Mon Sep  9 17:53:59 2024 - [info] Alive Slaves:
Mon Sep  9 17:53:59 2024 - [info]   tstr-inhwa-wa809(10.161.229.221:13306)  Version=8.0.34 (oldest major version between slaves) log-bin:enabled
Mon Sep  9 17:53:59 2024 - [info]     GTID ON
...
Mon Sep  9 17:53:59 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Sep  9 17:53:59 2024 - [info] Checking SSH publickey authentication settings on the current master..
Mon Sep  9 17:53:59 2024 - [info] HealthCheck: SSH to tstr-inhwa-wa810 is reachable.
...
Mon Sep  9 17:54:00 2024 - [info] Starting ping health check on tstr-inhwa-wa810(10.161.229.204:13306)..
Mon Sep  9 17:54:00 2024 - [info] Ping(INSERT) succeeded, waiting until MySQL doesn't respond..

MHA 롤체인지 테스트

  • MHA 로그 확인
    정상 롤체인지 수행됨

       Mon Sep  9 18:03:03 2024 - [info] MHA::MasterMonitor version 0.57.
   Mon Sep  9 18:03:04 2024 - [info] GTID failover mode = 1
   Mon Sep  9 18:03:04 2024 - [info] Dead Servers:
   Mon Sep  9 18:03:04 2024 - [info] Alive Servers:
   Mon Sep  9 18:03:04 2024 - [info]   tstr-inhwa-wa809(10.161.229.221:13306)
   Mon Sep  9 18:03:04 2024 - [info]   tstr-inhwa-wa810(10.161.229.204:13306)
   Mon Sep  9 18:03:04 2024 - [info] Alive Slaves:
   Mon Sep  9 18:03:04 2024 - [info]   tstr-inhwa-wa810(10.161.229.204:13306)  Version=8.0.34 (oldest major version between slaves) log-bin:enabled
   Mon Sep  9 18:03:04 2024 - [info]     GTID ON
   Mon Sep  9 18:03:04 2024 - [info]     Replicating from 10.161.229.221(10.161.229.221:13306)
   Mon Sep  9 18:03:04 2024 - [info]     Primary candidate for the new Master (candidate_master is set)
   Mon Sep  9 18:03:04 2024 - [info] Current Alive Master: tstr-inhwa-wa809(10.161.229.221:13306)
   Mon Sep  9 18:03:04 2024 - [info] Checking slave configurations..
   Mon Sep  9 18:03:04 2024 - [info] Checking replication filtering settings..
   Mon Sep  9 18:03:04 2024 - [info]  binlog_do_db= , binlog_ignore_db= 
   Mon Sep  9 18:03:04 2024 - [info]  Replication filtering check ok.
   Mon Sep  9 18:03:04 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
   Mon Sep  9 18:03:04 2024 - [info] Checking SSH publickey authentication settings on the current master..
   Mon Sep  9 18:03:05 2024 - [info] HealthCheck: SSH to tstr-inhwa-wa809 is reachable.
   Mon Sep  9 18:03:05 2024 - [info] 
   tstr-inhwa-wa809(10.161.229.221:13306) (current master)
    +--tstr-inhwa-wa810(10.161.229.204:13306)

   Mon Sep  9 18:03:05 2024 - [info] Checking master_ip_failover_script status:
   Mon Sep  9 18:03:05 2024 - [info]   mha/custom_scripts/master_ip_failover --command=status --ssh_user=admin --orig_master_host=tstr-inhwa-wa809 --orig_master_ip=10.161.229.221 --orig_master_port=13306 
   Mon Sep  9 18:03:05 2024 - [info]  OK.
   Mon Sep  9 18:03:05 2024 - [info] Checking shutdown script status:
   Mon Sep  9 18:03:05 2024 - [info]   mha/custom_scripts/power_manager --command=status --ssh_user=admin --host=tstr-inhwa-wa809 --ip=10.161.229.221 
   Mon Sep  9 18:03:05 2024 - [info]  OK.
   Mon Sep  9 18:03:05 2024 - [info] Set master ping interval 3 seconds.
   Mon Sep  9 18:03:05 2024 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
   Mon Sep  9 18:03:05 2024 - [info] Starting ping health check on tstr-inhwa-wa809(10.161.229.221:13306)..
   Mon Sep  9 18:03:05 2024 - [info] Ping(INSERT) succeeded, waiting until MySQL doesn't respond..


다음 포스트에서는 GTID 모드에서의 Xtrabackup 백업 생성 테스트에 대해 다루겠습니다.
profile
hwwwa
이전 포스트

[MySQL] GTID mode 테스트 - 복제 연결

다음 포스트

[MySQL] GTID mode 테스트 - Xtrabackup 백업 생성 및 복원

post-custom-banner

0개의 댓글