최근 운영중인 백업 서버(replication slave)에서 Disk full 이슈로 인해 Replication이 중단된 사례가 있었습니다.
DB는 항상 우리를 간쫄리게 합니다... DBA 분들은 항상 존경스러워요. 🤣
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.0
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000057
Read_Master_Log_Pos: 247673142
Relay_Log_File: mysql:PR_DB-relay-bin.000041
Relay_Log_Pos: 5537565
Relay_Master_Log_File: mysql-bin.000044
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: RDD1,RDD2,RDDX
Replicate_Ignore_DB: information_schema,mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1594
Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
Skip_Counter: 0
Exec_Master_Log_Pos: 25338679
Relay_Log_Space: 2190669763
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: 1594
Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)
1. MySQL Error 로그 확인하기
먼저, 백업 서버 내의 데이터가 몇월 며칠까지 저장됐는지 확인 후, 해당 날짜의 로그를 확인합니다.
확인이 됐으면, 해당 날짜의 mysql error log를 확인해줍니다.
가장 하단의 라인에서 볼 수 있듯이, mysql-bin.000044 데이터의 25338679 position에서 동기화를 시도하다가 실패되었음을 알 수 있습니다.
즉, 우리는 mysql-bin.000044의 25338679 포지션에서부터 replication을 다시 시작하면 되는 겁니다.
2023-08-09T02:33:07.247674+09:00 1 [ERROR] Error in Log_event::read_log_event(): 'read error', data_len: 286, event_type: 31
2023-08-09T02:33:07.247734+09:00 1 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O error
2023-08-09T02:33:07.247756+09:00 1 [ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
2023-08-09T02:33:07.247772+09:00 1 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000044' position 25338679.
2. MySQL SLAVE 중단 및 재시작
mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.52 sec)
mysql> change master to MASTER_LOG_FILE='mysql-bin.000044', MASTER_LOG_POS=25338679;
Query OK, 0 rows affected (0.49 sec)
mysql> start slave;3. 결과 확인
SHOW SLAVE STATUS \G 명령어를 통해 슬레이브 상태를 확인하면, 최초 error에 대한 정보가 사라지고, mysql-bin 파일 및 포지션이 실시간으로 달라지는 것을 볼 수 있습니다.
이후, 데이터를 확인할 경우 순차적으로 최신의 데이터로 갱신됩니다.
'데이터베이스 > MySQL' 카테고리의 다른 글
| mysql-bin 파일이 뭐야? (0) | 2023.05.24 |
|---|
댓글