MobageのMySQL userのpassword変更について

こんにちは、IT基盤部のtomoyukiです。
Mobageのインフラ全般を主に担当しています。

今回はMobageのMySQL userのpassword変更を実施することになったので、その流れと注意する点についてお話ししたいと思います。

なぜMobageのMySQL userのpassword変更するのか？
MobageのMySQL userのpassword変更方法と注意点
MobageのMySQL userのpassword変更するにあたっての苦労

なぜMobageのMySQL userのpassword変更するのか？

DeNAではセキュリティの観点から定期的にMySQL userのpasswordの変更を実施しています。退職者や異動してMobageに関係なくなった人もpasswordを知っているからです。

MobageのMySQL userのpassword変更方法と注意点

大まかなpassword変更方法は以下になります。
既存のものとは別のuserを作成し、アプリケーションが新しいuserを使うように切り替えることでpassword変更を行います。
既存のuserのpasswordを変更してしまうと、アプリケーションが保持しているpassword情報を更新するまでの間、新規に接続を張ることが出来なくなるためです。

新しいMySQL userの作成
新しいMySQL userへの切り替え
- 変更したpasswordの設定ファイルの配布
- 各種インフラツールの修正
- アプリの再起動
MySQL userが変更されているか確認
- 全系統 (DeNA ではマスター, スレーブのセットのことを系統と呼んでいます)のDBでtcpdumpを取得し pt-query-digest でMySQL userが変更されているか確認
古いMySQL userの削除

新しいMySQL userの作成

系統によって利用しているMySQLのpasswordのハッシュアルゴリズムが違うため、それを考慮して新しいuserを作成します。
DeNA ではアプリケーションが誤ってスレーブに書き込んでしまうことを防ぐため、スレーブには書き込み可能なuserを作成していません。
そのため、マスターとスレーブには別の DDL を適用する必要があります。 SET sql_log_bin = 0; を利用し、クエリがレプリケーションされないようにし、マスターとスレーブ別々に DDL を適用しました。

dbの役割ごとに分けたsqlファイルを作成しそれを適応する ※ mysql に接続する際、実際には社内製のラッパースクリプトを用意しpasswordの入力を省けるようにしていますが、ここでは便宜上 mysql -u xxx -p xxx と記載しています

for host in `xxxx` ; do echo [$host]; mysql -u xxx -p xxxx -h $host -e "source xxx.master.sql"; done
for host in `xxxx` ; do echo [$host]; mysql -u xxx -p xxxx -h $host -e "source xxx.slave.sql"; done

新しいMySQL userのハッシュアルゴリズムが既存のMySQL userと同じか確認する
DeNA では各 MySQL インスタンスのユーザー設定 (ユーザー名、パスワード、権限) の"あるべき姿"を一つの設定ファイルに記載しています。その設定ファイルと実際に設定されているユーザー情報が一致していることを確かめる監視スクリプトも定常的に動かしています。今回もその監視スクリプトを利用することで、ここまでの作業に漏れやミスがないことを確認しました。

新しいMySQL userへの切り替え

MySQLに新userの追加ができたら、次はアプリケーションが利用するuserを切り替えていきます。
アプリケーションは大きく２つに分けられます。

アプリケーションが利用するパスワード情報ファイルを変更・配付すれば自動的に接続ユーザーが切り替わるもの
パスワード情報ファイルを変更・配付した後に、再起動をする必要があるアプリケーション

前者は fcgi で動いている web サーバーや batch などが該当します。後者は主に daemon です。
ログの収集に用いているものなど、いつでも再起動できるものもありますが、再起動をするとユーザーに影響が出るものもあります。例えばゲームのリアルタイム通信に利用している daemon の場合、再起動時にはユーザーの接続が切断されてしまうケースがあります。そういった daemon についてはサービス運営を行っている部署と協調し再起動を実施します。
各アプリケーションの特性にあわせて、すべてに設定変更を反映させます。新しいMySQL userへの切り替えに伴って、各種インフラツールの修正も実施しました。このツールは上述したユーザー情報が一致していることを確かめる監視スクリプトなどになります。

MySQL userが変更されているか確認

全系統のdbでtcpdumpを取得しpt-query-digestでMySQL userが変更されているか確認しました。

tcpdumpの取得

sudo tcpdump -i xxx -s 65535 -x -nn -q -c 100000 -tttt 'port 3306' > /tmp/xxx.out

pt-query-digestでの確認

pt-query-digest --type=tcpdump --limit 100 /tmp/xxx.out

こちらの方法で確認したところ、一部のクエリのMySQL userが表示されないという問題が発生しました。
具体的には以下の2つのケースで検証し、ケース1では接続元のMySQL userは表示されましたが、ケース2では接続元のMySQL userが表示がされませんでした。

ケース1 -> クエリ発行ユーザーが表示される
- tcpdumpを実行する -> 接続 -> query発行 -> 切断 -> tcpdump終了
ケース2 -> クエリ発行ユーザーが表示されない
- 接続 -> tcpdumpを実行する -> query発行 -> tcpdump終了 -> 切断

ケース2の想定されるケースとして、batch処理などで長時間接続されいてる場合は確認が取れません。このような接続元のMySQL userが表示されない部分の確認に関しては、show processlistなどを組み合わせて確認するようにしました。

古いMySQL userの削除

MySQL userが変更されているか確認をした後に、古いMySQL userを一時的に rename します。いきなり drop するのではなくrenameを行うのは、問題発生時に切り戻しやすくするためです。

rename後にしばらく期間を置き問題がなければ古いユーザをdropする流れになります。

MobageのMySQL userのpassword変更するにあたっての苦労

台数の多さ

MobageのMySQLは約280系統に垂直分割されており、計約1050インスタンスあります。まず一番大変なことは台数が多いことです。作業自体は単純なものではありますが少しのミスで大規模サービスであるMobageが止まってしまうリスクがあります。精神的な負担が非常に大きな作業であります。

各サービスの各系統での違い

サービスAの書き込み系統のMySQL userと権限が、サービスBの書き込み系統のMySQL userと権限と違うのでその確認が大変でした。

再起動するdaemonの洗い出しと各サービス担当との調整

長期間運用してるシステムで担当者が入れ替わっているため各所との調整が大変でした。

事前の洗い出しでアクセスがされていないと思われていたサービスからMobageのdbに接続していた。

大規模なプラットフォームなので、社内でも完全にどのような使われ方をしてるか把握されていなかったので、それを把握するために全系統のdbの接続元をtcpdumpで洗い出しました。全系統のdbでtcpdumpを取得すること自体大変なのですが、アクセスのピーク帯の時間にtcpdumpを取得する際のサーバへの負荷の考慮など含め大変でした。

最後に

細かい点で省略してる部分はありますが、長期間運用している大規模サービスのセキュリティ対応でMySQL userのpassword変更を実施する際の大まかな流れと注意点を記載しました。長期間運用しているサービスではセキュリティ対策でMySQL userのpassword変更を実施する必要がやはり出てくると思います。その際に本内容が何かしらの参考になれば幸いです。