概要

本記事は、PHPフレームワークLaravel入門第2版で学習している中の疑問・つまづきの備忘録です。
今回はMySQLの文字化けを解消する方法について調べたことをまとめます。

環境

Docker
Laravel Sail
MySQL8.0

今回起きた現象

boardsテーブルの２行目のtitleは、本来「こんにちは」と入力されていますが、「?????」と文字化けしています。

mysql> select * from boards;
+----+-----------+--------------+---------+---------------------+---------------------+
| id | person_id | title        | message | created_at          | updated_at          |
+----+-----------+--------------+---------+---------------------+---------------------+
|  1 |         1 | Hello        | xxxxxxx | 2022-01-11 07:32:54 | 2022-01-11 07:32:54 |
|  2 |         2 | ?????        | yyyyy   | 2022-01-11 07:33:24 | 2022-01-11 07:33:24 |
|  3 |         1 | good_morning | xxxxxx  | 2022-01-11 09:32:31 | 2022-01-11 09:32:31 |
+----+-----------+--------------+---------+---------------------+---------------------+

原因

文字セットがどのように設定されているかを確認したところ以下のような状態でした。

mysql> show variables like "chara%";
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

結果としては、

character_set_client
character_set_connection
character_set_results

の３項目でlatin1が設定されています。
これらをutf8mb4に変更する必要があるようです。

character_set_client・・・サーバーは、character_set_client システム変数値を、クライアントが送信するステートメントの文字セットにします。
character_set_connection・・・クライアントによって送信されたステートメントを character_set_client から character_set_connection に変換します。
character_set_results・・・サーバーがクライアントにクエリー結果を返信するときに使用する文字セットを示します。

MySQL 8.0 リファレンスマニュアル

修正方法

まずはmysqlのコンテナに入り、複数ある設定ファイルmy.cnfの読み込み順序を調べます。

MySQL 8.0 リファレンスマニュアル

$ docker exec -it [MySQLのコンテナ名] /bin/bash   ///mysqlのコンテナに入る

bash-4.4# mysql --help
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

今回コンテナ内にはデフォルトで/etc/my.cnf以外のファイルは存在しなかったので、ここを変更していきます。

/etc/my.cnf

[mysqld]
#コメントは省略
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid

今回はDockerを用いて環境構築しているので、以下の順序で修正していきます。
1. /mysql/etc/my.cnfファイルをローカルに作成
2. mysqlコンテナ内の/etc/my.cnfの内容を1.のファイルにコピー
3. ローカルの/mysql/etc/my.cnfに設定を追記
4. docker-compose.ymlを修正

1. /etc/my.cnfファイルをローカルに作成

プロジェクトルート直下にmysqlディレクトリを作成、その配下にetc/my.cnfを作成する。

2. mysqlコンテナ内の/etc/my.cnfの内容を1.のファイルにコピー

今回は、デフォルトの状態は崩さず文字化けを直したいので、先ほど確認した/etc/my.cnfの内容をローカルの/mysql/etc/my.cnfにコピーします。

3. ローカルの/mysql/etc/my.cnfに設定を追記

MySQLのリファレンスには以下のように記載があります。
ここでいうオプションファイルは、my.cnfのことです。したがって、/mysql/etc/my.cnfにdefault-character-setに関する設定を追記します。

mysql クライアントでは、デフォルトとは異なる文字セットを使用するために、サーバーに接続するたびに SET NAMES ステートメントを明示的に実行できます (クライアントプログラム接続文字セット構成を参照)。同じ結果をより簡単に得るには、オプションファイルに文字セットを指定します。たとえば、次のオプションファイル設定では、mysql を起動するたびに、接続関連の 3 つの文字セットシステム変数が koi8r に設定されます:

MySQL 8.0 リファレンスマニュアル

/mysql/etc/my.cnf

[client]
default-character-set=utf8mb4 #clientセクションを追加

[mysqld]
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid

4. docker-compose.ymlを修正

ローカルの/mysql/etc/my.cnfをmysqlコンテナの/etc/my.cnfにマウントします。
これで、ローカルのmy.cnfに加えた変更がmysqlコンテナのmy.cnfにも反映されます。

docker-compose.yml

#mysqlに関する部分のみ抜粋
    mysql:
        image: 'mysql/mysql-server:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3306}:3306'
        environment:
            MYSQL_ROOT_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ROOT_HOST: "%"
            MYSQL_DATABASE: '${DB_DATABASE}'
            MYSQL_USER: '${DB_USERNAME}'
            MYSQL_PASSWORD: '${DB_PASSWORD}'
            MYSQL_ALLOW_EMPTY_PASSWORD: 1
        volumes:
            - 'sailmysql:/var/lib/mysql'
            - './mysql/etc/my.cnf:/etc/my.cnf'  # 追記
        networks:
            - sail
        healthcheck:
            test: ["CMD", "mysqladmin", "ping", "-p${DB_PASSWORD}"]
            retries: 3
            timeout: 5s

コンテナを停止・再作成する

docker-compose.ymlの変更を反映するために、コンテナを停止・再作成します。

もしサービス用のコンテナが存在している場合、かつ、コンテナを作成後にサービスの設定やイメージを変更している場合は、 docker-compose up -d を実行すると、設定を反映するためにコンテナを停止・再作成します（マウントしているボリュームは、そのまま保持します）。
Docker-docs-ja

fkjtmy@mba laravel-practice % docker compose up -d                              
[+] Running 6/6
 ⠿ Container laravel-practice_mailhog_1       Running                                                                                              0.0s
 ⠿ Container laravel-practice_selenium_1      Running                                                                                              0.0s
 ⠿ Container laravel-practice_meilisearch_1   Running                                                                                              0.0s
 ⠿ Container laravel-practice_mysql_1         Running                                                                                              0.0s
 ⠿ Container laravel-practice_redis_1         Running                                                                                              0.0s
 ⠿ Container laravel-practice-laravel.test_1  Running                                                                                              0.0s

設定が反映されたか確認する

mysqlのコンテナに入り、ローカルでの修正内容が反映されているかを確認します。

$ docker exec -it [MySQLのコンテナ名] /bin/bash   ///mysqlのコンテナに入る
bash-4.4# cat /etc/my.cnf

[client]
default-character-set=utf8mb4 #clientセクションを追加


[mysqld]
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid

反映された！！

次はmysqlに接続し、文字セットとテーブルを確認します。

mysqlの文字セットとテーブルの確認

    mysql> show variables like "chara%";
    +--------------------------+--------------------------------+
    | Variable_name            | 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     | utf8mb3                        |
    | character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
    +--------------------------+--------------------------------+
    8 rows in set (0.01 sec)


    mysql> select * from boards;
    +----+-----------+-----------------+---------+---------------------+---------------------+
    | id | person_id | title           | message | created_at          | updated_at          |
    +----+-----------+-----------------+---------+---------------------+---------------------+
    |  1 |         1 | Hello           | xxxxxxx | 2022-01-11 07:32:54 | 2022-01-11 07:32:54 |
    |  2 |         2 | こんにちは        | yyyyy   | 2022-01-11 07:33:24 | 2022-01-11 07:33:24 |
    |  3 |         1 | good_morning    | xxxxxx  | 2022-01-11 09:32:31 | 2022-01-11 09:32:31 |
    +----+-----------+-----------------+---------+---------------------+---------------------+
    3 rows in set (0.01 sec)

解決！！

character_set_client
character_set_connection
character_set_results

の３項目がutf8mb4に変更されています。
また、テーブル２行目のtitleが「こんにちは」とひらがなで正常に表示されています。

まとめ

mysqlで文字化けが起こった際は、
1. /mysql/etc/my.cnfファイルをローカルに作成
2. mysqlコンテナ内の/etc/my.cnfの内容を1.のファイルにコピー
3. ローカルの/mysql/etc/my.cnfに設定を追記
4. docker-compose.ymlを修正

の手順で修正できます。
my.cnfを作成する場所などは開発環境に合わせて適宜変更しても問題ありません。

参考文献

MySQL 8.0 リファレンスマニュアル
10.4 接続文字セットおよび照合順序
 4.2.2.2 オプションファイルの使用

Docker-docs-ja

【MySQL,Docker】MySQLの文字化けを解消する方法

概要

環境