[MySQL & MariaDB - Relay Log 변경 시 replication 복제 에러 해결방법]

 발생 에러

MariaDB [(none)]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log


## 에러 로그
2023-12-20 16:42:39 0 [ERROR] Failed to open the relay log './kjh-relay-bin.000008' (relay_log_pos 633)
2023-12-20 16:42:39 0 [ERROR] Could not find target log during relay log initialization
2023-12-20 16:42:39 0 [ERROR] Failed to initialize the master info structure

 

 

 발생 원인

relay log 형식이 지정이 안되어있어서 relay log가 호스트이름으로 파일이 생성됩니다. 

slave에서 이를 인식하지 못하여 start slave 불가하는 현상 발생.

 

기본적으로 릴레이 로그에는 서버의 호스트 이름을 참조하는 이름과 #nnnnnn이 host_name-relay-bin.nnnnnn지정 됩니다.

 

복제본의 호스트 이름이 변경되면 문제가 발생 Failed to open the relay log하고 오류가 반환됩니다.

오류 : Could not find target log during relay log initialization

## 마지막으로 읽은 binlog 파일과 pos번호 필수 확인!
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 192.168.152.130
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql.000003
           Read_Master_Log_Pos: 338
                Relay_Log_File: kjh-relay-bin.000008
                 Relay_Log_Pos: 633
         Relay_Master_Log_File: mysql.000003
              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: 338
               Relay_Log_Space: 0
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 0
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State:
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

 

 

 

 해결 방법

■  Relay_log 및 Relay_log_index 시스템 변수를 설정하여 릴레이 로그 파일 이름을 지정

## my.cnf
relay_log=mariadb-relay-bin
relay_log_index=mariadb-relay-bin

 

■ 복제가 이미 진행 중인 동안 이 문제를 해결해야 하는 경우 복제본을 중지하고 이전 릴레이 로그 인덱스 파일을 새 릴레이 로그 인덱스 파일 앞에 추가한 다음 복제본을 다시 시작할 수 있습니다.

## 현재 kjh-relay log 사용
MariaDB [(none)]> show relaylog events;
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| Log_name             | Pos | Event_type  | Server_id | End_log_pos | Info                                           |
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| kjh-relay-bin.000007 |   4 | Format_desc |         2 |         256 | Server ver: 10.3.22-MariaDB, Binlog ver: 4     |
| kjh-relay-bin.000007 | 256 | Rotate      |         1 |           0 | mysql.000002;pos=12077486                      |
| kjh-relay-bin.000007 | 299 | Format_desc |         1 |           0 | Server ver: 10.3.22-MariaDB-log, Binlog ver: 4 |
| kjh-relay-bin.000007 | 551 | Rotate      |         2 |         602 | kjh-relay-bin.000008;pos=4                     |
+----------------------+-----+-------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.000 sec)


## hostname이 jh로 변경될 경우(db재기동) 아래와 같이 jh-relay log 파일이 생성됨
-rw-rw----. 1 mariadb mariadb         6 12월 21 10:11 jh.pid
-rw-rw----. 1 mariadb mariadb        66 12월 21 10:11 jh-relay-bin.index
-rw-rw----. 1 mariadb mariadb       279 12월 21 10:11 jh-relay-bin.000005


## slave를 실행하면 아래와 같은 에러 발생
MariaDB [(none)]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log



[해결 방법 1]
ㅁ 형식
shell> cat NEW_relay_log_name.index >> OLD_relay_log_name.index
shell> mv OLD_relay_log_name.index NEW_relay_log_name.index

## DB중지
[root@jh datadir]# mysqladmin -uroot -proot shutdown

## relay log index 수정
[root@jh datadir]# cat jh-relay-bin.index >> kjh-relay-bin.index
[root@jh datadir]# mv kjh-relay-bin.index jh-relay-bin.index

## DB 기동
[root@jh datadir]# sh +x /mariadb/mariadb/startup.sh

## slave status 확인 -> 정상



[해결 방법 2]
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.008 sec)


## master 정보를 다시 지정해줄땐 stop slave이 후 show slave status;를 통해 반영된 binlog와 pos값을 확인해 해당 시점부터 복제가 이루어질수 있도록 재설정이 필요하다.
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.152.130', MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_LOG_FILE='mysql.000003', MASTER_LOG_POS=338,MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.014 sec)


MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)


MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.152.130
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 10
               Master_Log_File: mysql.000003
           Read_Master_Log_Pos: 338
                Relay_Log_File: jh-relay-bin.000007
                 Relay_Log_Pos: 551
         Relay_Master_Log_File: mysql.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: 338
               Relay_Log_Space: 857
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.001 sec)

 

 

참고

Docs : https://mariadb.com/kb/en/relay-log/

Relay Log