MySQL 데이터베이스의 깨진 한글을 고치기 위한 모험

Tool-Box.info 웹사이트는 아파치 - PHP - MySQL (APM) 솔루션 기반으로 지난 13년 간 운영되어 왔습니다. 각 부분은 해마다 지속적으로 갱신하여 왔고, 최근에 MySQL을 5.7에서 8.0으로 올리게 되었습니다. 그런데 새 버전으로 데이터베이스를 이전시키고 나니 웹사이트 내의 한글 내용이 모두 깨지는 것을 발견했습니다. 이는 문자 세트가 서로 맞지 않아 생기는 증상이기에, 정확한 원인을 찾아내 보기로 했습니다.

먼저 5.7 버전으로 되돌린 뒤 다음 SQL 질의를 통해 어떤 문자 세트가 사용되고 있는지 확인했습니다.
show variables like 'char%';

역시나 "character_set_database"와 "character_set_server"가 "latin1"으로 설정되어 있었습니다. 웹사이트 데이터를 담고 있는 데이터베이스와 테이블의 문자 세트를 확인해보니 기본 설정인 "latin1_swedish_ci"로 되어 있었고요. 초창기부터 모든 한글 입력 내용이 데이터베이스에 Latin1 형태로 저장되고 있었던 것입니다. 출력이 될 때 UTF-8로 변환이 되어서 웹 페이지에서는 정상적으로 보였을 뿐입니다. 데이터베이스를 직접 들여다보면 깨져 보이는데 말이지요. MySQL 8.0은 저장된 형태 그대로 출력을 하는 바람에 문제가 발생했던 듯 합니다.

비슷한 문제를 다룬 여러 한국 블로그에서 제안한 해결책은 문제가 되는 데이터베이스와 테이블의 문자 세트를 다음과 같이 바꿔주는 것이었습니다.
ALTER DATABASE data_database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE data_table DEFAULT CHARSET=utf8mb4;

ALTER TABLE data_table MODIFY COLUMN title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

또한, MySQL 설정 파일(my.cnf)의 [mysqld] 구분 아래에 다음 내용을 삽입하라고 안내했습니다.
collation-server = utf8_unicode_ci
character-set-server = utf8

안타깝게도, 이 모든 내용이 하나도 도움이 되지 않았습니다. 더 면밀하게 분석해본 결과 문자 세트 설정을 바꾼다고 하더라도 이미 저장된 데이터는 여전히 "깨진" 상태로 남아있다는 결론을 내렸습니다. 데이터 자체가 UTF-8 문자 세트로 다시 작성되어야 한다는 것입니다. 그래서 데이터베이스 내용을 추출한 뒤 올바른 문자 세트로 복원시키는 과정이 필요했습니다. 먼저 다음과 같이 추출했습니다.
mysqldump -u root -p --default-character-set=latin1 data_database > dump.sql

원래 저장된 문자 세트대로 데이터가 추출되도록 "default-character-set" 플래그를 "latin1"으로 해두었습니다. 추출된 파일 내에 "latin1"으로 나오는 문자열은 모두 "utf8mb4"로 바꾸었습니다.

이제 복원만 하면 되었을텐데, 도중에 "지정된 키가 너무 깁니다. 최대 키 길이는 1000 바이트입니다." 에러가 발생하면서 일부 테이블이 복원되지 않았습니다. 문제를 추적해 보니 MyISAM 데이터베이스 종류의 한계가 원인인 것으로 밝혀졌습니다. 테이블 열(column)에 쓰인 "VARCHAR" 데이터 종류가 UTF-8 설정에서 차지하는 공간은 Latin1의 3배가 되므로 문자 세트가 바뀌면서 키의 길이가 1000 바이트 한계를 초과하게 된 셈입니다. InnoDB 데이터베이스 타입의 경우 MySQL 5.7.7 부터 기본적으로 최대 길이가 3072 바이트인 것과 대조됩니다.

이렇다 보니 추출 파일에 언급되는 데이터베이스 종류를 모두 MyISAM에서 InnoDB로 변경했습니다. 그럼 왜 애초에 MyISAM을 썼던 것일까요? 데이터베이스 생성 당시에 InnoDB에 전문 검색 인덱스(Full-text Index) 지원이 안 되었기 때문입니다. 2011년 MySQL 5.6부터 비로소 지원되었지요.

추출 파일 내에 적힌 데이터베이스 종류와 문자 세트를 모두 바꾸고서 다음과 같이 복원 작업을 했습니다.
mysql -u root -p --default-character-set=utf8mb4 data_database < dump.sql

이제 비로소 데이터베이스 내에 한글 내용이 정상적으로 나타나게 되더군요. 앞서 언급한 my.cnf 변경사항을 유지해 놓으면 웹사이트 쪽에서도 문제 없이 표시되었습니다. 마지막으로, MySQL 8.0으로 다시금 데이터베이스를 이전시키고 "mysql_upgrade" 명령을 실행했습니다. 드디어 모두 원하던 대로 정상 작동을 하더군요. 그리고 my.cnf의 변경사항은 필요가 없게 되어서 그 부분은 삭제했습니다.

간단히 요약을 하자면, 13년 전 초기에 설정했던 데이터베이스 설정 때문에 새 MySQL 버전으로 업그레이드를 하지 못할 뻔 했지만 모두 해결을 하는데 성공했습니다.
이 글에 대한 태그: , ,

트랙백

이 글에 대한 트랙백 전용 URI

이 링크는 클릭하기 위한 것이 아닙니다. 본문의 트랙백 URI을 담고 있습니다. 이 URI을 통해서 여러분의 블로그에서 이 블로그로 핑 및 트랙백을 보낼 수 있습니다. 링크를 복사하려면 오른쪽 클릭을 한 뒤 인터넷 익스플로러에서는 "바로가기 복사"를, 모질라에서는 "링크 위치 복사"를 선택하십시오.

트랙백 없음

덧글

덧글 표시 방식: 나열 형태 | 엮은 형태

정영만 작성일: :

역시 애 많이 쓰셨네요.
자랑스런 모습. ✌

덧글 추가

전자우편 주소는 보여지지 않으며 전자우편으로 통보를 할 때만 사용됩니다.
*단어* 식으로 단어를 별표로 둘러싸면 진하게 표시되며 밑줄을 치려면 _단어_ 식으로 적으면 됩니다.
:-) 이나 ;-) 와 같은 표준 이모티콘은 그림으로 바뀝니다.

(C) 1996-2018. 이 웹사이트의 저작권 및 권한은 정우덕에게 있습니다.