Zenn
👾

Amazon Aurora Mysql の文字コード設定

2022/05/16に公開
AWS
MySQL
RDS
aurora
idea

root134-systemの'K.D'です。

はじめに

先日、Mysqlで動いているWEBシステムをそのままAWSへ移設するお仕事があり、
AWSのAuroraのパラメータ設定でハマったのでMemoとして残します。

事象と原因

mysql5.6からAurora mysql5.7へのDumpでの移行において移行データは問題ないが、
新規登録の際文字化けが発生。
原因は、AuroraDBでのパラメータ設定漏れによりCreateDB（コマンドで設定）のパラメータ指定の設定が反映されず（上書き）utf8mb4⇒Latain1に戻っていた事が原因）

移行方式

移行は全体で2Gもなかったので、mysqldumpのFULL-DUMPで一発移行。
尚、バージョンはmysql5.6からAWS Aurora Mysql5.7への移行。
文字コードは’utf8mb4'又,DBでのファイル格納はしていない事が前提。

1.現行システムからFullDumpを取得
2.AuroraMysqlへCreateDB
3.リストア実施
4.アプリケーションデプロイし稼働確認。

$ mysql -uXXX -P3306 -p -hxxx.rds.amazonaws.com hogehoge --ssl-mode=Disable
mysql> show databases; ＃Database確認
mysql> CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
#作成後文字コード、照合順序確認。
mysql> use hogehoge
mysql> show variables like 'character_set%';
	+--------------------------+---------------------------+
	| 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     | utf8
	| character_sets_dir       | hogehoge|
	+--------------------------+-------------------------+
	8 rows in set (0.01 sec)

mysql> show variables like '%collation%';
	+----------------------+--------------------+
	| Variable_name        | Value              |
	+----------------------+--------------------+
	| collation_connection | utf8mb4_general_ci |
	| collation_database   | utf8mb4_general_ci |
	| collation_server     | utf8mb4_general_ci |
	+----------------------+--------------------+
	3 rows in set (0.01 sec)
mysql> quit

問題ない為、現行からDump取得しauroraへリストア。

#Dump取得
$ mysqldump --skip-column-statistics -uXXX.XXX.XXX.100 -p hogeDetabase --ssl-mode=disabled --routines --events > hogehoge_dump.sql
	# オプションについて
	# １．ストアドも持ってきたいので --events付与。
	# ２．--skip-column-statisticsは　クライアントがmysql８のdumpで5.7のdumpを取得する際にエラー回避するオプション
	# ３．dumpのパラメータは　公式サイトにて
	# https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html

#auroraへリストア
pv hogehoge_dump.sql | mysql -uXXXX -p -P3306 -hxxxxxx.ap-northeast-1.rds.amazonaws.com hogehoge --ssl_mode=disabled
	# pvを使うと進捗確認しながら実行できる。

上記実行後、アプリケーションデプロイ（DBの向き先を変更し忘れずに）
とここまでは順調に作業終了。

文字化け発生

アプリケーションデプロイしテスト実施。無事ログイン成功した結果、
一部文字が？？？で文字化け発生。移行してきた値は問題なく表示されているが、
監査ログ（アクセス証跡）のような、AuroraDBで登録されたものは文字化けが発生している。
又、検索機能での漢字カタカナ類、検索が引っかからない事判明。
mysqlへログインし再度文字コードを確認した所,latin1になっていた。

mysql> show variables like 'character_set%';
+--------------------------+---------+
| Variable_name            | Value  |
+--------------------------+---------+
| character_set_client     | utf8   |
| character_set_connection | utf8   |
| character_set_database   | latin1 |
| character_set_filesystem | binary |
| character_set_results    | utf8   |
| character_set_server     | latin1 |
| character_set_system     | utf8   |                                         
+--------------------------+---------+

よって、一度　’Drop database' で削除し、auroraのパラメータを下記の項目をすべて設定してから再度、crate Database ⇒　dumpからのリストア実施を行った文字化が解消された。
尚、パラメータを設定した後、DBの再起動が必要です。

纏め※RDS（Aurora)のパラメータ設定

  • 上記の結果からDefaultの設定から独自設定した項目は以下。
    　※collation_connectionの設定を忘れないように。
パラメータ mcpvc default.aurora-mysql5.7
character_set_client utf8mb4 <engine-default>
character_set_connection utf8mb4 <engine-default>
character_set_database utf8mb4 <engine-default>
character_set_results utf8mb4 <engine-default>
character_set_server utf8mb4 <engine-default>
collation_connection utf8mb4_general_ci <engine-default>
skip-character-set-client-handshake 1 <engine-default>
time_zone Asia/Tokyo <engine-default>

その他 Excelアップロードからの一括登録処理にてmax_allowed_packet Error

  • 確認テスト時Mysqlの設定関連でエラーが発生。WEBアプリケーションでExcelアップロードからの一括登録機能があり（1回の登録で6000件ぐらい）balkuInsertを実行しているのだが、本番で仕様したExcelシートをアップロードした所、max_allowed_packet_ErrorでABEND。
    どうも、sql実行のsqlサイズがオーバーしているとの事で以下でサイズ確認。
mysql> show variables like 'max_allowed_packet';
max_allowed_packet	193792

約200kのサイズが上限となっていた。
現行システムのMysqlの値を同コマンドで確認し、AWSコンソールよりパラメータ変更し再起動。
再起動後反映されているか確認。

mysql> show variables like 'max_allowed_packet';
max_allowed_packet	5193792 #現行に合わせて5MGSET

サイド同じアップロードテスト実行。
正常終了確認し問題解決。

Discussion