MySQL 데이터베이스의 깨진 한글을 고치기 위한 모험
작성자: Wesley 작성일:
Tool-Box.info 웹사이트는 아파치 - PHP - MySQL (APM) 솔루션 기반으로 지난 13년 간 운영되어 왔습니다. 각 부분은 해마다 지속적으로 갱신하여 왔고, 최근에 MySQL을 5.7에서 8.0으로 올리게 되었습니다. 그런데 새 버전으로 데이터베이스를 이전시키고 나니 웹사이트 내의 한글 내용이 모두 깨지는 것을 발견했습니다. 이는 문자 세트가 서로 맞지 않아 생기는 증상이기에, 정확한 원인을 찾아내 보기로 했습니다.
먼저 5.7 버전으로 되돌린 뒤 다음 SQL 질의를 통해 어떤 문자 세트가 사용되고 있는지 확인했습니다.
역시나 "character_set_database"와 "character_set_server"가 "latin1"으로 설정되어 있었습니다. 웹사이트 데이터를 담고 있는 데이터베이스와 테이블의 문자 세트를 확인해보니 기본 설정인 "latin1_swedish_ci"로 되어 있었고요. 초창기부터 모든 한글 입력 내용이 데이터베이스에 Latin1 형태로 저장되고 있었던 것입니다. 출력이 될 때 UTF-8로 변환이 되어서 웹 페이지에서는 정상적으로 보였을 뿐입니다. 데이터베이스를 직접 들여다보면 깨져 보이는데 말이지요. MySQL 8.0은 저장된 형태 그대로 출력을 하는 바람에 문제가 발생했던 듯 합니다.
비슷한 문제를 다룬 여러 한국 블로그에서 제안한 해결책은 문제가 되는 데이터베이스와 테이블의 문자 세트를 다음과 같이 바꿔주는 것이었습니다.
또한, MySQL 설정 파일(my.cnf)의 [mysqld] 구분 아래에 다음 내용을 삽입하라고 안내했습니다.
안타깝게도, 이 모든 내용이 하나도 도움이 되지 않았습니다. 더 면밀하게 분석해본 결과 문자 세트 설정을 바꾼다고 하더라도 이미 저장된 데이터는 여전히 "깨진" 상태로 남아있다는 결론을 내렸습니다. 데이터 자체가 UTF-8 문자 세트로 다시 작성되어야 한다는 것입니다. 그래서 데이터베이스 내용을 추출한 뒤 올바른 문자 세트로 복원시키는 과정이 필요했습니다. 먼저 다음과 같이 추출했습니다.
원래 저장된 문자 세트대로 데이터가 추출되도록 "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부터 비로소 지원되었지요.
추출 파일 내에 적힌 데이터베이스 종류와 문자 세트를 모두 바꾸고서 다음과 같이 복원 작업을 했습니다.
이제 비로소 데이터베이스 내에 한글 내용이 정상적으로 나타나게 되더군요. 앞서 언급한 my.cnf 변경사항을 유지해 놓으면 웹사이트 쪽에서도 문제 없이 표시되었습니다. 마지막으로, MySQL 8.0으로 다시금 데이터베이스를 이전시키고 "mysql_upgrade" 명령을 실행했습니다. 드디어 모두 원하던 대로 정상 작동을 하더군요. 그리고 my.cnf의 변경사항은 필요가 없게 되어서 그 부분은 삭제했습니다.
간단히 요약을 하자면, 13년 전 초기에 설정했던 데이터베이스 설정 때문에 새 MySQL 버전으로 업그레이드를 하지 못할 뻔 했지만 모두 해결을 하는데 성공했습니다.
먼저 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 버전으로 업그레이드를 하지 못할 뻔 했지만 모두 해결을 하는데 성공했습니다.