Open in app

Sign in

Write

Sign in

[MariaDB] Setting utf8mb4 Character Set

김정환(John.Kim)
OldbeeDev
Published in
13 min readMar 5, 2020
Image by Pixaline from Pixabay

utf8mb4 등장

UTF-8 인코딩은 4바이트 가변 길이 인코딩 방식입니다. 그런데 MySQL에서는 utf8을 3바이트로 구현했었습니다. 그러다가 MySQL 5.5.3 (2010년 3월 24일)에 4바이트짜리 character set인 utf8mb4을 추가했습니다.

유니코드 U+10000 이상인 문자들을 UTF-8로 인코딩하려면 4바이트가 필요한데 이에 해당하는 대표적인 문자들은 다음과 같은 Emoji 입니다.

출처 : http://www.isthisthingon.org/unicode/allchars1.php

utf8mb4 설정하기

MySQL 및 MariaDB에서 utf8mb4를 사용하려면 my.cnf (리눅스) 나 my.ini (윈도우즈) 설정 파일을 다음과 같이 수정합니다.

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake

위의 설정은 MySQL 5.5, MariaDB 5.5 이후 버전에서 사용할 수 있습니다. 이제 DB 서버를 재시작하고 mysql 클라이언트에서 아래의 명령으로 상태를 확인해봅니다.

show variables like 'c%';

character_set_server

DB 서버의 기본 문자셋으로서 설정 파일에 명시한 대로 utf8mb4 로 설정되어 있습니다.

character_set_database

이 변수 값은 현재 사용 중인 database에 따라 다르게 표시될 수 있습니다. database=none인 상태에서는 서버 기본 문자셋인 utf8mb4로 보이지만, 문자셋이 latin1인 database 안에 들어가면 character_set_database=latin1 이라고 나옵니다.

character_set_client, character_set_connection, character_set_results

클라이언트가 DB 서버와 connection을 맺을 때 handshake 과정이 진행됩니다. 이때 클라이언트는 자신이 사용할 문자셋을 알려줍니다. 서버에서는 이 값으로 character_set_client, character_set_connection, character_set_results를 설정합니다.

그러나 설정 파일에서 skip-character-set-client-handshake 옵션을 사용하면 클라이언트에서 알려준 문자셋을 무시하고 character_set_server 값으로 이 세 변수 값이 설정됩니다.

collation_server

설정 파일에 따라 utf8mb4_unicode_ci로 설정되어 있습니다. collation은 문자열 정렬 규칙입니다. 자세한 설명은 라엘님 블로그를 참고 바랍니다.

collation_database

이 변수 값은 현재 사용 중인 database에 따라 다르게 표시될 수 있습니다. database=none인 상태에서는 collation_server 값이 표시되지만 문자셋이 latin1인 database 안에 들어가면 collation_database=latin1_swedish_ci 라고 나옵니다.

collation_connection

명시적인 설정이 없다면 character_set_connection 설정에 따라 기본 collation이 선택됩니다. 예를 들어 latin1이면 latin1_swedish_ci, utf8mb4이면 utf8mb4_general_ci가 기본 collation입니다.

여기서는 skip-character-set-client-handshake 옵션에 따라 collation_server가 대신 사용되어 utf8mb4_unicode_ci가 표시됩니다.

옵션 변경 시도

위의 옵션을 조금 변경해 보면 결과가 어떻게 달라지는지 알아보았습니다.

skip-character-set-client-handshake 옵션

만약 이 옵션을 사용하지 않으면 어떨게 될까요?

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

위의 그림과 같이 character_set_client, character_set_connection, character_set_results에는 클라이언트 측 문자셋이 euckr이 적용되고, collation_connection 역시 euckr_korean_ci로 설정된 것을 확인할 수 있습니다.

init-connect 옵션

QnA 검색을 하다보면 아래와 같이 설정하면 된다는 내용도 나옵니다.

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect=SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

init-connect 옵션은 DB 서버의 init-connect 변수에 저장되는 값입니다.

DB 서버는 클라이언트와 connection이 맺어지면 init-connect 변수에 있는 명령어 스크립트를 자동으로 수행합니다.

SET NAMES는 character_set_client, character_set_connection, character_set_results, collation_connection 을 한번에 설정하는 명령어입니다.

수행 결과는 아래와 같습니다.

잘 됩니다!

그런데 한가지 문제가 있는데, init-connect 옵션은 DB 접속 계정의 권한이 SUPER privilege 일 때, 즉 root 유저일 때는 수행되지 않는다는 점입니다.

이는 DB 복구 시나리오 때문입니다. MySQL은 init-connect 에 명시된 명령어에 syntax error가 있으면 클라이언트와의 연결을 끊어버린다고 합니다. 그런데 만약 root 유저마저도 이런 이유로 접속이 막힌다면 DB를 복구할 방법이 없어져 버리는 겁니다.

mysql 클라이언트에서 root 계정으로 DB에 로그인해보면 다음과 같이 euckr이 보입니다.

따라서 init-connnect 옵션은 사용자를 햇갈리게 할 수 도 있으니 안 쓰는게 좋겠다는 생각이 듭니다.

그러나 다른 한 편으로는 평상시에 root 권한으로 DB에 접속하는 것은 권장할 것이 아니기 때문에, 위와 같은 불일치 정도는 개발이나 서비스 운용시 큰 문제가 아니다라는 생각도 듭니다.

꼭 필요하면 root 로 로그인한 다음에 SET NAMES utf8mb4; 명령어 한 번만 치면 되니까요.

init-connect 옵션을 2개?

가끔 init-connect 옵션을 2개 이상 쓰신 분도 봤습니다.

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect=SET NAMES 'utf8mb4'
init-connect=SET collation_connection='utf8mb4_unicode_ci'

이렇게 하면 둘 다 적용되는게 아니라, 마지막 명령어만 서버의 init-connect 변수에 저장됩니다. 만약 2개 이상의 명령어를 수행하고 싶으면 세미콜론 (;)을 구분자로 하여 명령어를 이어 적으면 됩니다.

수행 결과는 아래와 같습니다.

일반 권한으로 접속

character_set_client, character_set_results는 euckr로 설정되고, collation_connection=utf8mb4_unicode_ci가 되면서 이에 대응하는 character_set_connection도 utf8mb4로 설정되었습니다.

아마도 이 설정을 했던 분이 원하던 결과는 아니었을 것입니다.

Client Library로 접속시 차이점

MariaDB 접속을 위한 C# library로 MySqlConnector 를 사용하고 있습니다. 지금까지는 mysql 클라이언트로만 character set 설정 상태를 확인했는데요, 제가 짠 프로그램과 DB 세션 간에는 어떻게 설정되는지 궁금해졌습니다.

my.ini 설정은 아래와 같습니다.

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake

테스트 프로그램은 아래처럼 간단합니다.

private static void TestConnection()
{
    var builder = new MySqlConnectionStringBuilder
    {
        Server = DbServer,
        Port = 3306,
        Database = MainDbName,
        UserID = Uid,
        Password = Pwd
    };    using (var conn = new MySqlConnection(builder.ConnectionString))
    {
        var result = conn.Query("SHOW VARIABLES LIKE 'c%'");
        foreach (var row in result)
        {
            Debug.WriteLine($"{row.Variable_name}, {row.Value}");
        }
    }
}

결과는 이랬습니다.

character_set_client, utf8mb4
character_set_connection, utf8mb4
character_set_database, utf8mb4
character_set_filesystem, binary
character_set_results, utf8mb4
character_set_server, utf8mb4
character_set_system, utf8
character_sets_dir, C:\Program Files\MariaDB 10.2\share\charsets\
check_constraint_checks, ON
collation_connection, utf8mb4_general_ci
collation_database, utf8mb4_unicode_ci
collation_server, utf8mb4_unicode_ci
completion_type, NO_CHAIN
concurrent_insert, AUTO
connect_timeout, 10

collation_connection=utf8mb4_general_ci가 이상합니다. 설정 파일을 아무리 변경해도 위의 결과는 바뀌지 않았습니다.

결국 MySqlConnector 소스를 뒤져보니 이상한 부분이 발견되었습니다.

MySqlConnector는 접속 절차를 다 거친 후 마지막에 SET NAMES utf8mb4; 명령어을 전송하고 있군요. 이제야 이해가 되었습니다.

그러고 나서 문서를 더 찾아보니 이런 내용이 나옵니다.

출처 : https://mysqlconnector.net/connection-options/

놀랍게도 utf8mb4 / utf8mb4_general_ci 고정인 라이브러리였습니다. 사실 MySQL 8.0의 기본 문자셋이 utf8mb4로 변경된 것을 보면 놀랄 일도 아니긴 합니다만… 😅

사실 이 모든 문제의 시작은 HeidiSQL 때문이었습니다.

설정 파일을 아무리 바꿔도 HeidiSQL에서는 상태가 바뀌지 않았습니다. 이제와서 생각해보니 HeidiSQL이 DB 서버에 접속하면서 character set 을 변경시킨 것이 아닐까 조심스레 추측해봅니다.

그리고 이렇게 collation_connection이 다르면 실행시 어떤 문제가 발생할 수 있는지도 알아봐야 할텐데요, 그건 다음에 다시 살펴볼려고 합니다. ( 살짝 조사해 봤을 때는 큰 문제는 없을 것 같았기에 … 😏 )

References

Utf8
MySQL
Coding
How To

--

--

김정환(John.Kim)
OldbeeDev

Written by 김정환(John.Kim)

49 Followers
Editor for

석유.고인물.개발자 and Randonneur

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams