MySQL8.0から5.7へのレプリケーションがutf8mb4_0900_ai_ciのエラーで停止してしまう原因と対策
Created
2023/2/22 6:47
技術本部 サービスリライアビリティグループ（SRG）の鬼海（@fat47）です。
#SRG（Service Reliability Group）は、主に弊社メディアサービスのインフラ周りを横断的にサポートしており、既存サービスの改善や新規立ち上げ、OSS貢献などを行っているグループです。
なにかの役に立てば幸いです。
概要事象回避方法skip_character_set_client_handshakeを指定するダメだった方法とその理由スキーマやテーブル作成時にCollationを指定するdefault_collation_for_utf8mb4を指定する参考URL終わりに
概要
RDSのMySQL5.7から8.0にアップグレードする過程で、切り戻し環境用にMySQL5.7のレプリカを作成しておきたいが、レプリケーションエラーが発生して停止してしまうという相談がありました。
下記の2つの記事でも事例を紹介していますが、他にも回避する手段がないか確認します。
事象
| インスタンス名 | MySQL Version | 役割 |
| DB1 | MySQL8.0 | ソース |
| DB2 | MySQL5.7 | レプリカ |
変更しているパラメータ
Bash
ソースがMySQL8.0でレプリカがMySQL5.7という構成を組むと、下記のようなエラーがでてレプリケーションが停止します。
Bash
これはMySQL8.0から
default collationのデフォルト値がutf8mb4_0900_ai_ciに変更になったためで、MySQL5.7には存在しない値のためエラーになってしまうというものです。回避方法
skip_character_set_client_handshakeを指定する
上記ブログ記事にて紹介した方法がこちらです。
DB1(MySQL8.0)のパラメータグループで下記を有効化します。
Bash
collation_serverでutf8mb4_general_ciを指定しているから大丈夫だと思わせぶりですが、MySQL8.0のClientはスキーマ作成時やテーブル作成の実行時、Collationの指定がないとデフォルトで
utf8mb4_0900_ai_ciを指定してきます。ですので、そのClientからの指定を無視する
skip_character_set_client_handshakeを有効にする必要があるというものでした。Collationを指定すれば良いのかというとそういうことでもなく、それについては下記で解説します。
ダメだった方法とその理由
スキーマやテーブル作成時にCollationを指定する
デフォルトの指定がまずいならCollation指定したら行けるだろうというと思いますが、
なんとだめなのです。
COLLATEを指定してスキーマを作成してみます。
SQL
DB2側のslave statusを確認してみると、レプリケーションがエラーで停止しています。
SQL
DB1側のスキーマはちゃんと指定したCOLLATEで作成されています。
SQL
default_collation_for_utf8mb4を指定する
下位互換のために
default_collation_for_utf8mb4という設定項目があります。my.cnfでは設定できず、SET GLOBALかパラメータ永続化のSET PERSISTで設定する必要があります。
SET PERSISTの場合、RDSの管理者権限では実行権限が足りないため別途付与する必要があります。
SQL
RDSのパラメータグループの項目にある
default_collation_for_utf8mb4の値を変更すると、SET GLOBALで設定したのと同じ状態になりRDSインスタンスを再起動すると値は初期化されます。
パラメータ永続化のSET PERSISTで設定してもRDSインスタンスを再起動すると初期値のutf8mb4_0900_ai_ciにリセットされるRDSの仕様は注意が必要です。
RDSでこの値を永続化に近いかたちで運用するには、RDSのDBパラメータグループの
init_connectに下記設定を入れ、クライアントセッション開始時に設定を反映する方法があります。
SET SESSION default_collation_for_utf8mb4=utf8mb4_general_ci;この2つの方法両方ともいえることなのですが、
DB1(MySQL8.0)側のbinlogの中身を見てみると下記のようになっています。
SQL
CREATE DATABASEが実行される前に注目してみます。
SQL
ここでMySQL5.7に存在しないID 255のものが指定されており、レプリカがエラーで停止してしまう原因となっています。
このID 255が何者かは下記で確認できます。
utf8mb4_0900_ai_ciですね。SQL
ちなみに成功している
skip_character_set_client_handshakeの場合のbinlogの中身は以下の通りです。SQL
CREATE DATABASE実行前に注目してみると255が指定されておらず、45になっています。
SQL
ID 45を確認してみますと、
utf8mb4_general_ciです。SQL
参考URL
終わりに
切り戻し用にMySQL5.7をレプリカとする構成にするとハマる罠について解説しました。
今後MySQL5.7がEOLを迎える2023年10月に向けてMySQL8.0バージョンアップの話が増えてくると思いますので、この記事がお役にたてば幸いです。
SRG では一緒に働く仲間を募集しています。
ご興味ありましたらぜひこちらからご連絡ください。
