[Mysql] Attempted to open a previously opened tablespace 에러 해결

---

서버 DB를 사용하는 도중에 어느 날 갑자기 Mysql이 다운되었습니다

systemctl status mysql 명령을 통해 상태를 확인해보니 다음과 같은 에러가 발생했습니다

● mariadb.service - MariaDB 10.1.48 database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
  Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
   Active: activating (auto-restart) (Result: signal) since Mon 2022-07-18 17:30:02 KST; 4s ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 4279 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 1841 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=killed, signal=ABRT)
  Process: 1680 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
  Process: 1678 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 1841 (code=killed, signal=ABRT)
   Status: "InnoDB: Error: Attempted to open a previously opened tablespace. Previous tablespace garinsystem/tbl_adm uses space ID: 1 at filepath: ./garinsystem/tbl_adm.ibd. Cannot open tablespace mysql/innodb_table_stats which uses space ID: 1 at filepath: ./mysql/innodb_table_stats.ibd"

Jul 18 17:30:02 vm1533639884855.localdomain systemd[1]: Failed to start MariaDB 10.1.48 database server.
Jul 18 17:30:02 vm1533639884855.localdomain systemd[1]: Unit mariadb.service entered failed state.
Jul 18 17:30:02 vm1533639884855.localdomain systemd[1]: mariadb.service failed.

해당 에러는 몇몇의 테이블을 열지 못했다는 의미로

대부분은 Mysql 구동중에 메모리 오버가 발생할 때 많이 만날 수 있습니다

 

systemctl start mariadb.service

 

해당 명령을 통해 실행을 하지만 실행이 되지 않습니다

구글에서는 mysql-data 폴더에 데이터를 지우라는 말이 많은데

해당 방법을 사용하실 경우 mysql을 다시 설치해야하는 최악의 경우가 발생할 수 있습니다

 

해당 에러는 다음과 같은 방법을 통해 간단하게 해결했습니다

 

vim /etc/my.cnf

# If you run into InnoDB tablespace corruption, setting this to a nonzero
# value will likely help you to dump your tables. Start from value 1 and
# increase it until you're able to dump the table successfully.

innodb_force_recovery=1

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency = 16

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit = 1

# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
#innodb_fast_shutdown

 

innodb_force_recovery=1 이부분이 맨 앞에 #이 있는 게 기본인데 #을 제거한 뒤 설정파일을 저장합니다

그런 뒤에 systemctl restart mysql 명령을 실행하면 에러 없이 실행되는 것을 확인하실 수 있습니다