レガシーサービスのMySQLバージョンアップ（5.1 -> 5.7）とRDS化

レガシーサービスのMySQLバージョンアップ（5.1 -> 5.7）とRDS化 @teckl 2020-12-11 MyNA(日本MySQLユーザ会) 望年LT大会2020＠オンライン

はじめに初めての参加で大変緊張しています。。今回は外部非公開イベントということで、間違った内容などを言ってしまっても安心出来そうというのがあり、急遽思い立って参加させていただきました。知見のある皆様の意見・ツッコミなどいただけたらと思いまして、、

自己紹介 teckl (てっくる)と言います。いわゆるDBAではなく、サーバサイドエンジニアです主にPerlを書いていました DevOps 普段はラーメンの画像ばかりシェアして飯テロをしていますｗ MySQLは昔から使っていましたが、本業はアプリケーションエンジニアをやっており、インフラ・MySQLはあまり知識が無くお恥ずかしい点も多いですが、よろしくお願いします。。

最近やった某サービスのMySQL 5.1 からの 5.7 バージョンアップについて紹介します

最近やった某サービスのMySQL 5.1 からの 5.7 バージョンアップについて紹介します某オンプレで稼働中のサービスで行ったMySQLのバージョンアップについて、自分の知見やハマリポイントを軽く紹介します。あくまでも個人的な範囲としておこなっておりますので、「具体的な数値」や「資料のスクショ」などは表（twitter）には非公開とさせてください。
（テキストでのシェアでしたらまったく問題ございませんｗ）

某サービスについて古いオンプレのサービスがありまして、歴史的な経緯により、2009年当時にMySQL 4.0から 5.1にバージョンアップした後、この10年ほどずっとMySQL 5.1で運用していました。様々な事情により、AWS RDSに持っていく必要が出てきました。が、MySQL
5.1のままだと、Amazon RDSに入れることが出来ません。。（RDSはMySQL 5.6以上をサポート）

AWS移行にあたっての大きな壁 DB以外にもNFS対応などいろいろあったのですが今回は割愛します MySQL 5.1をなんとかして5.6以上に上げないとAWSに持っていけない。。なぜ8.0ではなく5.7？当初はAurora MySQLも検討していました。 Auroraが8.0に対応していないことや、MyISAMの問題もあり5.7に決めました。

5.1 から5.7にどうやってバージョンアップするか？

某サービスのMySQL 5.1の特徴ストレージエンジンはMyISAM 深遠な理由により、ずっとMyISAMとなっていました今回のバージョンアップを機に、InnoDBに変更しました文字コードは5.1 のUTF8 utf8mb4ではないが、utf8mb4の絵文字「」「
」が入っている状態当時の何かしらの理由により、範囲外の文字列を入れられるパッチを当てていた模様。。

某サービスのMySQL 5.1の特徴日付型の扱いがゆるふわテーブルのdatetime型のデフォルト値はだいたい 0000-00-00 00:00 などとなっていたこの5.1 DBとは別ですが、別の機能で使っているDBは未だにMySQL
4.0のMyISAM マージテーブルなども現役で動いている

5.7移行にあたっての前提条件関係者が多く、長時間の停止メンテナンスは難しい… 具体的には丸一日停止して閲覧が全部止まるようなのはNG 書き込み停止は長くても数時間程度まで書き込みメンテナンス中も閲覧だけは極力続けたいせっかく 5.1 -> 5.7 に上げるのであれば
sql_mode なども厳格にしたい 5.1 -> 5.5 -> 5.6 -> 5.7 のようにレプリケーションのチェーンをするオンプレのサーバリソースはない… せっかくなので、 5.1で3つに分かれていたDBを1つのDBに集約したい

MySQL 5.1から5.7化の基本戦略 Percona XtraBackupなどのツールもあるが、前述のように様々な制約があることから、シンプルに mysqldump を使って行う事にまずは全テーブルの件数とサイズ等を調査移行元DBは約XXTB。何も考えずに mysqldump
& restore すると、丸約2日くらいはかかってしまう・・・

MySQL 5.1から5.7化の基本戦略悩みまくったあげく、いわゆるダブルライト方式(のようなもの)を採用更新のあったすべてのレコードをロギングして、人力でバイナリログ的な実装を行って更新データを両方に書き込むようにする感じダブルライト方式がうまく行けばmysqldumpとレストアをメンテナンス中にやるのではなく、待機系のレプリケーションDBを使ってサービス稼働中にゆっくりと差分に追いつかせることができる、はず…（理論上はｗ）

ダブルライト方式（人力バイナリログ機能）の実装キモはこのあたりアプリケーション側でバイナリログのような更新ログを吐くようにしました

ダブルライト方式（人力バイナリログ機能）の実装システムでは基本的にPerlのORマッパー（ Class::DBI::mysql ）を使っているので、親クラス側ですべてのレコード更新をロギング出来るはず・・

ログDB用のレプリケーションフィルター設定追加自分はレガシーなMySQL 5.1脳だったため、人力バイナリログ用のDBをレプリケーションフィルターに追加するにはmysqldの再起動が必須なので、再起動をするだけのメンテナンスを短時間でも取らないと駄目かなと思っていたのですが、それはなるべく避けたかったため調べてみると以下の記事を発見しました MySQL 5.7 : レプリケーションフィルターをオンラインで変更する
https://yakst.com/ja/posts/3486

ダブルライト方式のためのログDB用のレプリケーションフィルター設定追加 MySQL 5.7からの新機能として、再起動不要でオンラインでレプリケーションする対象DBが増やせるということを知り、無駄なメンテナンスを取らずに済みました…。助かりました。。 mysql> CHANGE REPLICATION FILTER
REPLICATE_DO_DB = (service_article, service_replication); Query OK, 0 rows affected (0.00 sec) たぶん知ってればなんてことは無かったのですが・・・

ダブルライト方式（人力バイナリログ機能）を実際に開始してみて人力バイナリログからの手動レプリケーションですが、実際に開始してみると思ったよりも想定外の不整合が出たり、いろいろと問題がありました。。更新量が異常に多くて全然レプリが追いつかなかったり例の 0000-00-00 00:00 の日付の問題が出てきたり ORマッパーを使っておらず、直でSQLを打ち込んでる実装箇所があったり…

ダブルライト方式（人力バイナリログ機能）の追従開始手動レプリの不整合の解決をして、落ち着いてきた段階でこんな感じで1時間単位で手動でレプリ同期していきました for x in $(seq -w 0 23)
do TABLE_NAME=replication20201012$x echo === $x === echo /var/hoge/src/service/bin/bl.scripts exec replication_do $TABLE_NAME sleep 10; /var/hoge/src/service/bin/bl.scripts exec replication_do $TABLE_NAME done;

5.1 mysqldumpを 5.7へインポートする際の工夫 5.1のmysqldumpを 5.7 にインポートする際には、明示的に --default-character- set=utf8mb4 を付ける必要があった $
/tmp/20200928/20200928/service_new$ for x in `ls *.gz` ;do echo === $x ===; zcat $x | mysql --default-character-set=utf8mb4 -uroot service_new ; done; これをしないと「」「」などがすべて ? になって壊れてしまう

MySQL 5.1 と 5.7のデータ比較人力レプリという事もあり、どうしても不整合が出る不安はあったため以下のようにレコード件数チェックスクリプトを作ったりしました。 MySQL 5.1と5.7のレコード件数のdiffを出すやつ（主要なテーブルのみ）

5.7 の重いクエリの負荷テスト 5.1のMyISAMから5.7のInnoDBになるため、これまで何でも無かったクエリでもインデックスの使われ方が変わってスローログが出てしまう可能性がある。実際のサービスの生ログを元に、アクセスパターンを試して簡易的な負荷テストを行いました。 ltsvの生ログを元にcurlのリクエストを作って実施 ONLY_FULL_GROUP_BY問題の発生

負荷テストで見つかったslow_logの一部ファイル名検索でインデックスが効かずにフルインデックススキャンになってしまう問題の修正など -- before mysql> explain SELECT id, cache_height_thumbnail,
member_id, filename, cache_width, object_id, timestamp, createstamp, cache_size, cache_media_type, title, type, service_id, cache_height, cache_width_thumbnail FROM upload WHERE service_id = '6945629' AND filename LIKE '%html%' ORDER BY id DESC LIMIT 0, 1000; +----+-------------+--------+------------+-------+------------------------------------------------------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+------------------------------------------------------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | upload | NULL | index | service_id__id,service_id,service_id__createstamp,service_id__filename | PRIMARY | 4 | NULL | 125684 | 0.09 | Using where | +----+-------------+--------+------------+-------+------------------------------------------------------------+---------+---------+------+--------+----------+-------------+ -- after mysql> explain SELECT id, service_id, cache_height_thumbnail, member_id, filename, cache_width, object_id, timestamp, createstamp, cache_size, cache_media_type, title, type, service_id, cache_height, cache_width_thumbnail FROM upload WHERE service_id = '6945629' AND filename LIKE '%html%' ORDER BY createstamp DESC LIMIT 0, 1000; +----+-------------+--------+------+------------------------------------------+----------------------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------------------------------+----------------------+---------+-------+--------+-------------+ | 1 | SIMPLE | upload | ref | service_id__id,service_id,service_id__createstamp | service_id__createstamp | 4 | const | 294272 | Using where | +----+-------------+--------+------+------------------------------------------+----------------------+---------+-------+--------+-------------+

MySQL 5.7のパラメータの調整（バッファプール調整など）恥ずかしながら MySQL 5.1 & MyISAM 脳でほとんどInnoDB運用の知見がないため、バッファプールサイズなど何が最適なのかの知見が無く、
いろいろ調査して以下のものに行き着きました。いろいろと調査していたが決め手に欠けていたが、よさげなツールを発見 MySQLTuner https://github.com/major/MySQLTuner-perl

MySQL 5.7のパラメータの調整（バッファプール調整など） MySQLTunerの教えに従って innodb_buffer_pool_size innodb_buffer_pool_instances などを設定、切り替え後のバッファプール、メモリ量は良い感じのところで落ち着いてくれました。。

そして、MySQL 5.1 -> 5.7の切り替えメンテナンス実施いろいろありましたが、人力バイナリログの仕組みによって、5.1 から 5.7へのデータ追従・書き込み停止時間も最小限にすることが出来まして、大きなトラブルも無くMySQL 5.7への切り替えが出来ました。。

5.7化後のオンプレからAWS RDSへ移行無事5.7に切り替わったので、オンプレ5.7からAWS RDS 5.7への移行準備が整いました。現在オンプレからインターネット越しにsshでトンネルを作って、レプリケーションを繋いで切り替えの準備をしているところとなります。。（現在進行中）

ということで、RDS化移行はもう少しでいけそうです。。

まとめ歴史の長いサービスだけに、停止時間を短くしてのDBの載せ替えはなかなかしんどかった逆に言うと長時間停止して良いのであれば手順自体は非常にシンプルになるパッケージインストールではなく、自前でパッチを当ててビルドなどしていると闇が生まれやすい当時はパッチを当てなければならない理由があったのだと思いますが、今となっては当時の担当者もおらず、その理由が不明だったり

まとめ人力レプリケーション（ダブルライト方式？）は結構大変だったが、このバージョンアップ以外にもいろいろと応用が出来そうかも？こういったレガシーMySQLのアップグレードで、もし他に良い方法があれば教えていただけると助かります。。

おわりご静聴、ありがとうございました！（ここをもっとこうした方が良かったとか、同じような経験をされた方がおられましたらぜひツッコミいただきたいです）

レガシーサービスのMySQLバージョンアップ（5.1 -> 5.7）とRDS化

レガシーサービスのMySQLバージョンアップ（5.1 -> 5.7）とRDS化

Shigeki SUGAI

Transcript