20210619 MySQL

1. 구성

• MySQL 8.0 2EA, MHA 이중화 구성 (Master / Standby)

2. 장애 사항

• MySQL Master Down & Failover

3. 에러 로그

[System] [MY-010931] [Server] /data/mysql_engine/engine/bin/mysqld: ready for connections. Version: '8.0.23'  socket: '/data/mysql_engine/engine/mysql.sock'  port: 13306  MySQL Community Server - GPL.
[ERROR] [MY-010826] [Server] While rebuilding index file /data/mysql_data/log/mysql-bin.index: Failed to delete the existing index file. It could be that file is being used by some other process.
[ERROR] [MY-010838] [Server] MYSQL_BIN_LOG::add_log_to_index failed to move crash safe index file to index file.
[ERROR] [MY-011072] [Server] Binary logging not possible. Message: Either disk is full, file system is read only or there was an encryption error while opening the binlog. Aborting the server..
mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f7d8c0008c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f869c4dfce0 thread_stack 0x46000
/data/mysql_engine/engine/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x2066f1e]
/data/mysql_engine/engine/bin/mysqld(handle_fatal_signal+0x323) [0x1032cc3]
/lib64/libpthread.so.0() [0x31b140f7e0]
/lib64/libc.so.6(gsignal+0x35) [0x31b1032495]
/lib64/libc.so.6(abort+0x175) [0x31b1033c75]
/data/mysql_engine/engine/bin/mysqld() [0xd52c3d]
/data/mysql_engine/engine/bin/mysqld(MYSQL_BIN_LOG::open_binlog(char const*, char const*, unsigned long, bool, bool, bool, Format_description_log_event*, unsigned int)+0x863) [0x1c2a743]
/data/mysql_engine/engine/bin/mysqld(MYSQL_BIN_LOG::new_file_impl(bool, Format_description_log_event*)+0x7b1) [0x1c2b7a1]
/data/mysql_engine/engine/bin/mysqld(MYSQL_BIN_LOG::rotate(bool, bool*)+0x35) [0x1c2bb65]
/data/mysql_engine/engine/bin/mysqld(MYSQL_BIN_LOG::rotate_and_purge(THD*, bool)+0x52) [0x1c37d22]
/data/mysql_engine/engine/bin/mysqld(handle_reload_request(THD*, unsigned long, TABLE_LIST*, int*)+0x51f) [0xf30e9f]
/data/mysql_engine/engine/bin/mysqld(mysql_execute_command(THD*, bool)+0x140c) [0xefd26c]
/data/mysql_engine/engine/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x3f8) [0xf010f8]
/data/mysql_engine/engine/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x1693) [0xf02c13]
/data/mysql_engine/engine/bin/mysqld(do_command(THD*)+0x174) [0xf04234]
/data/mysql_engine/engine/bin/mysqld() [0x10246c8]
/data/mysql_engine/engine/bin/mysqld() [0x25973dc]
/lib64/libpthread.so.0() [0x31b1407aa1]
/lib64/libc.so.6(clone+0x6d) [0x31b10e8bbd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f7d8c0e3228): FLUSH LOGS
Connection ID (thread ID): 348
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
mysqld_safe Number of processes running now: 0
mysqld_safe mysqld restarted
mysqld: Error on delete of '/data/mysql_data/data/mysqld_tmp_file_case_insensitive_test.lower-test' (OS errno 13 - Permission denied)
mysqld: Error on delete of '/data/mysql_data/data/mysqld_tmp_file_case_insensitive_test.lower-test' (OS errno 13 - Permission denied)

4. 원인 분석

• 시스템 관리자가 OS 계정의 권한 변경
• Binary log rotate 동작 과정 중 FLUSH LOGS 명령어로 인하여 signal 6 발생