mysql db가 깨졌습니다. ㅠㅠ 0 0 1,645

by 뜨디 [MySQL] [2021.03.24 00:06:00]


현재 mysql 4대를 운영중입니다 1번서버가 마스터 서버이고 2,3,4번 서버는 슬레이브 입니다.

금일 에러가 발생하며 3번서버거 down 되었습니다. 아래 에러로그 첨부합니다.

 

2021-03-13 06:54:57 4847 [Warning] Aborted connection 1808176 to db: 'unconnected' user: 'root' host: '10.49.8.113' (Got an error reading communication packets)
2021-03-18 13:39:00 4847 [Warning] Aborted connection 1872102 to db: 'unconnected' user: 'root' host: '10.49.8.113' (Got an error reading communication packets)
2021-03-23 14:42:51 4847 [ERROR] InnoDB: Tried to read 16384 bytes at offset 90832896. Was only able to read 0.
2021-03-23 14:42:51 4847 [ERROR] InnoDB: File (unknown): 'read' returned OS error 0. Cannot continue operation
210323 14:42:51 mysqld_safe Number of processes running now: 0
210323 14:42:51 mysqld_safe mysqld restarted
2021-03-23 14:42:52 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-03-23 14:42:52 0 [Note] /home/lfcorp/pkg/mysql-5.6.29/bin/mysqld (mysqld 5.6.29-log) starting as process 9841 ...
2021-03-23 14:42:52 9841 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2021-03-23 14:42:52 9841 [Note] Plugin 'FEDERATED' is disabled.
2021-03-23 14:42:52 7f413c333740 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2021-03-23 14:42:52 9841 [Note] InnoDB: Using atomics to ref count buffer pool pages
2021-03-23 14:42:52 9841 [Note] InnoDB: The InnoDB memory heap is disabled
2021-03-23 14:42:52 9841 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2021-03-23 14:42:52 9841 [Note] InnoDB: Memory barrier is not used
2021-03-23 14:42:52 9841 [Note] InnoDB: Compressed tables use zlib 1.2.3
2021-03-23 14:42:52 9841 [Note] InnoDB: Using CPU crc32 instructions
2021-03-23 14:42:52 9841 [Note] InnoDB: Initializing buffer pool, size = 64.0G
2021-03-23 14:42:54 9841 [Note] InnoDB: Completed initialization of buffer pool
2021-03-23 14:42:54 9841 [Note] InnoDB: Highest supported file format is Barracuda.
2021-03-23 14:42:54 9841 [Note] InnoDB: Log scan progressed past the checkpoint lsn 24918740476746
2021-03-23 14:42:54 9841 [Note] InnoDB: Database was not shutdown normally!
2021-03-23 14:42:54 9841 [Note] InnoDB: Starting crash recovery.
2021-03-23 14:42:54 9841 [Note] InnoDB: Reading tablespace information from the .ibd files...
2021-03-23 14:42:54 9841 [Note] InnoDB: Restoring possible half-written data pages
2021-03-23 14:42:54 9841 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 24918745719296
InnoDB: Doing recovery: scanned up to log sequence number 24918750962176
InnoDB: Doing recovery: scanned up to log sequence number 24918756205056
InnoDB: Doing recovery: scanned up to log sequence number 24918761447936
InnoDB: Doing recovery: scanned up to log sequence number 24918766690816
InnoDB: Doing recovery: scanned up to log sequence number 24918771933696
InnoDB: Doing recovery: scanned up to log sequence number 24918777176576
InnoDB: Doing recovery: scanned up to log sequence number 24918782419456
InnoDB: Doing recovery: scanned up to log sequence number 24918787662336
InnoDB: Doing recovery: scanned up to log sequence number 24918792905216
InnoDB: Doing recovery: scanned up to log sequence number 24918798148096
InnoDB: Doing recovery: scanned up to log sequence number 24918803390976
InnoDB: Doing recovery: scanned up to log sequence number 24918808633856
InnoDB: Doing recovery: scanned up to log sequence number 24918813876736
InnoDB: Doing recovery: scanned up to log sequence number 24918819119616
InnoDB: Doing recovery: scanned up to log sequence number 24918824362496
InnoDB: Doing recovery: scanned up to log sequence number 24918829605376
InnoDB: Doing recovery: scanned up to log sequence number 24918834848256
InnoDB: Doing recovery: scanned up to log sequence number 24918840091136
InnoDB: Doing recovery: scanned up to log sequence number 24918845334016
InnoDB: Doing recovery: scanned up to log sequence number 24918850576896
InnoDB: Doing recovery: scanned up to log sequence number 24918855819776
InnoDB: Doing recovery: scanned up to log sequence number 24918861062656
InnoDB: Doing recovery: scanned up to log sequence number 24918866305536
InnoDB: Doing recovery: scanned up to log sequence number 24918871548416
InnoDB: Doing recovery: scanned up to log sequence number 24918876791296
InnoDB: Doing recovery: scanned up to log sequence number 24918882034176
InnoDB: Doing recovery: scanned up to log sequence number 24918887277056
InnoDB: Doing recovery: scanned up to log sequence number 24918892519936
InnoDB: Doing recovery: scanned up to log sequence number 24918897762816
InnoDB: Doing recovery: scanned up to log sequence number 24918903005696
InnoDB: Doing recovery: scanned up to log sequence number 24918908248576
InnoDB: Doing recovery: scanned up to log sequence number 24918913491456
InnoDB: Doing recovery: scanned up to log sequence number 24918914559506
2021-03-23 14:42:58 9841 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 51893401, file name binary_log.000897
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_SEARCH_COMMENT in the InnoDB data dictionary has tablespace id 84764, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_SEASONOFF_MEM in the InnoDB data dictionary has tablespace id 84771, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_SEASONOFF_MEM_RESV in the InnoDB data dictionary has tablespace id 84772, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_SOCIAL_QNA in the InnoDB data dictionary has tablespace id 84795, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_SEASONOFF_MEM_RESV in the InnoDB data dictionary has tablespace id 84772, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_SOCIAL_QNA in the InnoDB data dictionary has tablespace id 84795, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_STATEMENT_RETURN in the InnoDB data dictionary has tablespace id 84802, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_STATEMENT_RETURN_DTL in the InnoDB data dictionary has tablespace id 84803, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_SUPPLY_IF_FAIL in the InnoDB data dictionary has tablespace id 84836, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
oDB data dictionary has tablespace id 84836, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_TB_CUSTOMER_INTEG in the InnoDB data dictionary has tablespace id 217384, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_TM_MASSMAIL_RESULT in the InnoDB data dictionary has tablespace id 84843, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/LST_ZCM021 in the InnoDB data dictionary has tablespace id 84878, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2021-03-23 14:43:06 9841 [ERROR] InnoDB: Table lfmall/ProductStandardCategory in the InnoDB data dictionary has tablespace id 107095, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
@
@

 

이런 상황일 시 가장 최적의 조치방안 부탁드립니다!!

 

-------------------- 추가로

 

디비가 내려가기 전에

 

[lfcorp@lf.mall.mdb03.real /data/mysql-data/lfmall]$  ls -ltr *txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:15 LST_TB_CUSTOMER_INTEG.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:18 LST_SEARCH_COMMENT.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:18 LST_SOCIAL_QNA.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:19 LST_SEASONOFF_MEM.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:19 LST_SEASONOFF_MEM_RESV.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:19 LST_STATEMENT_RETURN.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:20 LST_STATEMENT_RETURN_DTL.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:20 LST_SUPPLY_IF_FAIL.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:21 LST_TM_MASSMAIL_RESULT.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:22 LST_ZCM021.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:22 STB_BULLETIN_BODY.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:40 ProductStandardCategory.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:40 STB_ADDRESS.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:40 STB_COUNSEL.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:40 STB_COUPON.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:40 STB_DELIVERY_ADDR.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:41 STB_DELIVERY_ADDR_HST.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:41 STB_DELIVERY_LOC.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:41 STB_DELIVERY_LOC_HST.ibd.txt
-rw-rw---- 1 lfcorp 68 Mar 23 14:42 STB_INVOICE_UPLOAD.ibd.txt

 

idb파일이 txt파일로 변경이 되었습니다.... 해당 시각에 서버접근한사람도 db접근한사람도 없습니다.

원인분석 어렵네요 ㅠㅠㅠㅠㅠ

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입