블로그와 MySQL이 지옥에서 살아 돌아온 이야기

January 28, 2023

지난 주 주간 점검을 아직도 못 올렸는데, 연휴와 일이 겹쳐 정신이 엄청나게 없었기 때문입니다. 오늘 주간 점검을 올리려고 했더니 더 심각한 문제를 발견했는데, 이 블로그를 구동하는 데에 쓰이던 MySQL 데이터베이스가 연휴 사이 폭발했다는 것이었습니다.

정확한 원인은 모르겠습니다. 사실 항상 폭발해 있었던 것 같은데 그동안 어떻게든 버티고 있었던 것 같습니다. 6~7년 전부터 쓰던 옛날 서버의 데이터베이스를 그대로 가져오는 과정에서 분명히 뭔가 심각한 실수를 저질렀던 것 같은데, 정확히 무슨 실수였는지는 역시 모르겠지만, 여기 그 기록을 정리해 놓습니다.

업그레이드가 안됨

저는 업데이트를 한 기억이 없는데 왠지 연휴 동안 MySQL 버전이 8.0.31에서 8.0.32로 올라갔더라고요. 자동 업데이트 같은 거였겠죠. (사실 더 심각한 문제는 제가 지금껏 MySQL이 아니라 MariaDB를 쓰고 있다고 생각했다는 점인데, 어디서부터 헷갈린 건지는 저도 모르겠습니다.) 그러더니 MySQL 서비스가 죽었고, 계속 자동 재시작이 되면서 서버 CPU 사용량이 90% 이상으로 치솟았습니다.

2023-01-28T07:05:31.796959Z 4 [System] [MY-013381] [Server] Server upgrade from '80031' to '80032' started.
2023-01-28T07:06:11.609152Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '# # SQL commands for creating the user in MySQL Server which can be used by the # internal server session service # Notes: # This user is disabled for login # This user has: # Select privileges into performance schema tables the mysql.user table. # SUPER, PERSIST_RO_VARIABLES_ADMIN, SYSTEM_VARIABLES_ADMIN, BACKUP_ADMIN, # CLONE_ADMIN, SHUTDOWN privileges # INSERT IGNORE INTO mysql.user VALUES ('localhost','mysql.session','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'caching_sha2_password','$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED','N',CURRENT_TIMESTAMP,NULL,'Y', 'N', 'N', NULL, NULL, NULL, NULL); ' failed with error code = 1136, error message = 'Column count doesn't match value count at row 1'.
2023-01-28T07:06:11.618560Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
2023-01-28T07:06:11.619583Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-01-28T07:06:13.278967Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32-0buntu0.22.04.1)  (Ubuntu).

user 테이블의 열(column) 개수가 안 맞아서 터진다니, 이전 버전으로 돌리면 되지 않을까요? 하지만 좀 더 간단한 해결책이 있어 보였습니다. 다행히 1월 25일자 자동 백업이 남아있었고, 제가 1월 25일 이후로는 글을 한 번도 안 올렸기 때문에 서버를 롤백하기로 했습니다. (자동 백업의 필요성을 절감한 순간이었습니다) 그리고 알고보니 이전 버전으로 롤백한다고 되는 문제도 아니었습니다.

덤프도 안됨

서버를 롤백하고 나서 다행히 DB가 정상적으로 돌아가는 건 확인했습니다. 업그레이드를 하면 터진다는 걸 확인했으니, DB를 덤프하고 나서 MySQL을 재설치한 다음 다시 덤프를 불러오면 되지 않을까요? 하지만...

~$ mysqldump -u writefreely -p writefreely > writefreely.dump
Enter password:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

그러면 root 유저로 덤프를 하면 되겠죠?

~$ mysqldump -u root -p writefreely > writefreely.dump
Enter password:
mysqldump: Error: 'The user specified as a definer ('mysql.infoschema'@'localhost') does not exist' when trying to dump tablespaces

유저 추가도 안 됨

뭔가 단단히 잘못되면 mysql.infoschema 유저가 사라지는 일이 발생하기도 하는 모양입니다. 그래서 소프트웨어 엔지니어의 친구인 스택 오버플로우에 들어갔죠.

mysql.infoschema@localhost does not exist

답변에는 이렇게 적혀 있습니다.

Create user as create user 'mysql.infoschema'@'localhost' identified by 'password'; And grant it privileges grant all privileges on *.* to 'mysql.infoschema'@'localhost';

하지만...

mysql> create user 'mysql.infoschema'@'localhost' identified by '비밀번호';
ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.user]

mysql 데이터베이스가 InnoDB가 아니라 MyISAM을 쓰고 있다고요? 고등학교 시절의 저와 친구가 행한 수많은 뻘짓들의 흔적이 살아 숨쉬고 있었는지도 모르겠습니다. 그래서 다시 스택 오버플로우로 향했고...

How to fix ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. in Mysql 8.0 after CREATE USER

mysqld --upgrade=FORCE를 실행하라고 되어 있네요. 아까와 달리 원래 쓰던 버전이니까 괜찮지 않을까요?

원래 버전을 써도 업그레이드가 안 됨

아니었습니다. 여전히 같은 곳에서 Column count doesn't match value count at row 1 오류를 내면서 뻗고 있었습니다. user 테이블이 완전히 잘못되었다는 것을 깨달은 순간이었습니다.

위의 스택 오버플로우 링크에는 다른 해결책도 있는데, 물론 그것도 실행해 봤고 실패했습니다.

아마 제 MySQL 데이터베이스는 5.x 버전대의 고대의 유저 테이블을 쓰고 있었고, 서버 마이그레이션을 할 당시 덤프를 가져가는 대신 데이터 디렉토리 자체를 복사했던 모양입니다. (왜 그런 짓을 했을까요?) 제 추측이 맞다면 아마도 그 상태에서 그대로 --upgrade를 실행하지 않고 8.0 버전을 쓰면서 두 달 넘게 문제가 있다는 사실조차 몰랐던 거겠죠.

mysqld --upgrade를 실행하고 나면 MySQL이 다시 켜지지조차 않았기 때문에, 서버를 몇 번 더 롤백해야 했습니다.

유저가 없으면 직접 만들어 보자

유저 테이블을 복구하거나 MySQL을 정상적으로 업그레이드하는 게 어렵다면, 어떻게든 덤프라도 떠야 했습니다. 하지만 덤프를 하려고 하면 'mysql.infoschema'@'localhost' 유저가 없다고 하고, 유저를 만들려고 하면 Storage engine 'MyISAM' does not support system tables 오류가 발생하는 상황이죠.

혹시, 어쩌면 mysql.user 테이블에 직접 mysql.infoschema 유저를 추가하면 되지 않을까요?

그러려면 우선 현재 유저 테이블이 어떻게 생겨먹었는지 알아내야 했습니다.

mysql> use mysql;
mysql> select * from user;

그러고는 출력을 그대로 VS Code에 복붙하고, mysql.infoschema 유저를 만들어서 root 유저의 모든 정보를 그대로 다시 집어넣기로 했습니다.

mysql> use mysql;
mysql> insert into user
    ->  (`host`, `user`, `password`, [...] )
    ->  values
    ->  ('localhost', 'mysql.infoschema', '해싱된 root 비밀번호', [...] );
Query OK, 1 row affected (0.01 sec)

쿼리가 들어갔다는 사실만 해도 감동적이었습니다. 하지만 진짜 이렇게 한다고 해서 덤프가 될까요? 떨리는 마음으로 서비스를 재시작했고...

기적적인 해결

~$ sudo service mysql restart
~$ mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ...                |
| writefreely        |
+--------------------+

주먹구구식으로 집어넣은 mysql.infoschema 유저가 할 일을 하고 있었습니다. 덤프도 정상적으로 진행되었고요. 정말 블로그를 날려먹는 줄 알았는데, 기적적으로 어떻게든 데이터를 살려내는 데 성공한 것입니다. 6시간만의 해결이었습니다.

그 후

지금 이 블로그는 MariaDB 10.6으로 구동되고 있습니다. 이번에는 처음부터 멀쩡히 설치한 다음 덤프를 불러왔습니다. 오늘은 도저히 그렇게 할 체력이 없었지만, 앞으로는 문제가 생길 때를 대비해 각 서비스를 Dockerize하는 편이 좋겠다는 교훈도 얻었습니다. 블로그가 Dockerize되어 있었다면 서버 전체를 롤백하는 대신 도커 컨테이너만 롤백할 수 있었을 테니까요.

제 토요일 오후는 사라졌지만, 나름대로 값진 경험을 얻은 하루였습니다. (값지긴 한데 다시 하고 싶지는 않은 경험이었습니다.)

태그: #MySQL #MariaDB #개발 #문제해결 #삽질

Daniel Soohan Park (@heartade)

Follow this blog at Fediverse: @heartade@blog.heartade.dev

Follow my shorter shoutouts at Fediverse: @heartade@social.silicon.moe