何もわからないまま使うのは危険です！！！

いきなりこんなことをかいてしまいましたが、皆さん、MySQLを安全に使ってますか？
特に金融とか顧客情報とか扱うとこ！あとDBの障害対応めんどくさいな〜ってなってる人も必見です！

MySQLをクラウドでmanagaedなもの(CloudSQLとか、RDSとか)使っている方もいれば、self hostedなサーバを利用している方もいらっしゃるかと思います。

どちらにおいても、必ずこの内容は抑えておかないと危険かもしれないですよという、注意喚起的な記事です。

つまり、マネージドなRDBMSとしてMySQL使ってるあなたも、知っておくべきということです。
RDS を使っているあなたも、 CloudSQLを使っているあなたも、ぜひ読んで確認してください。

InnoDB、ちゃんと気にしていますか？

MySQLを使っていますか？と書き始めましたが、この記事の中ではInnoDBについて9割の話で進めます。
MyISAMはすでに使ってらっしゃらないという前提です。
むしろMyISAMを使い続ける ≒ EoLなMySQLを利用しているわけで、セキュリティ的に最悪です。
今すぐ新しいバージョンのMySQLを利用しましょう。

何を気にしないと危ないか？

※ この記事では、primary = master , replica = slaveと話を進めていきます。

絶対に業務等のレベルでは、RDBMSを利用する場合はreplicationを組むかと思います。読み取り専用のリードレプリカとか。
その中で、レプリケーションって、どういったロジックで行われているのか？それを理解しないまま利用すると、以下のようなことが起こり得ます。

アプリケーション側がreplicaを見に行く際に、
- 実はprimaryでは実行されていないTransactionがreplicaでは実行されてしまっている
- dbがちょっとしたインスタンスのhost errorで、replicationが崩壊し、24h365dでの対応が求められる
- 適切な設定ではないがゆえに、本来はもっと抑えられるコストを抑えられない
  - 理解が足らないまま、replicaのスペックを抑えてしまい、永遠にprimaryのtransactionに追いつけないなんてなったらもっと最悪です…
- etc...(考えたくもない)

要は、replicaとprimaryの間で、本来は全く同じ状態が論理的に担保されているべきレプリケーションが、実は保てていない状態じゃないか？原子性確保できてる？大丈夫？
ってことを言いたいわけです。

※ 原子性: 原子はそれ以上に分割ができないという特徴から。
まとめてポイってCOMMITしちゃうと、障害とか起きたら、分割できるレベル(uniqueじゃない塊)をロストすることになるのでえげつないクリティカルだよね。くらいの意味合いだと認識しています。つまりunique性みたいなニュアンスで考えてもらえればいいかと。

具体的にじゃあ何を気にしないといけないのか、解説していきます。

datadir,socket,logbin...大丈夫だよね流石に…？

まあぶっちゃけ今更感ありますけど、ちゃんとみておきましょう。初歩的なミスはよくあることです。

datadir
- ただたんに、どこにmysqlのデータウェアハウスを置いとく？っていう、ディレクトリpathを定義するだけです。デフォは/var/lib/mysql とかになってるはず。
port
- 3306ですが、セキュリティ的に変えたいとかあれば変えときましょ。
- 開発環境だからケチって、k8sとかで1nodeに何podもmysqlを用意するとかしてやる場合、podごとにport変えたりね。まあいうてmy.cnfでやらんでいいか。port mapすればいいだけだし。
socket
- socket fileのパス。
log_bin
- デフォでONじゃないんで、ONを指定しましょう。じゃないと、replicaがbinlog読みに行けない…＝replicationできるわけないw
- あわせてlog-binで、binlogのファイル名指定しましょう。デフォでbinlogって名前だっけ…そんなファイル名が使われますが、primaryサーバならprimaryってつけるとか、本番ならprdってつけるとか、pj名つけるとか…わかりやすさのため。

sync_binlog

これ、マジで重要です。結論から言うと1にすべきです。(パフォーマンスは落ちます)
実はこれは、binlogをディスクに同期する際の挙動を設定するパラメータです。ソースコードを読むと、binlogのディスクへのfdatasyncをしている処理に関するパラメータであることがわかりますね。
例えば、

sync_binlog=0
と、
sync_binlog={n≦1}
のときの違いですが、

0の場合、MySQLではサーバの ディスクへの同期は行いません。
同期はすべてOSのディスク同期機能に完全に任せるということです。

1以上の場合、指定した1以上の数のトランザクション/文しか失われません。

つまり、本当に堅牢性がある状態でbinlog同期をしたいなら、sync_binlog=1にすることで、起こりうるデータの損失は、最大で1文/トランザクション分しかしない状態にできるわけです。
パフォーマンス重視！ｗということで、ここ、やばそうな値に設定してませんか？

ちょこっと古いけど、わかりやすいかもしれない記事乗っけときますね。
https://yakst.com/ja/posts/1372

innodb_flush_log_at_trx_commit

これもありえないくらい重要ですね。堅牢にしたいなら1にすべきです。(パフォーマンスは落ちます)
これ、もし0にしたらどうなるかわかりますか？
2にしたらどうなるかわかりますか？

まず大前提として、書き込む＝flushしたではないです。書き込むやで！っていっても、それはflushする内容がキュー的(かはわからんけど)積み上がってるだけです。障害起きるとロストしかねません。
解説すると、ACIDプロパティっていう、なんかトランザクションの書き込みしたよねーみたいなのを保存してくれるものがあるんですが、
これにトランザクション単位でディスクへのフラッシュの情報を書き込み、確実にトランザクションが書き込まれたことを保証するきっかけになります。(多分認識あってる)

どういうことかというと、innodb_flush_log_at_trx_commitが

0
- 1secごとにログバッファをディスクに書き込みます。
- 書き込む頻度が1秒ごとなので、もしインスタンスに障害がおき、複数のトランザクションがこのタイミングで失われたら…？
1
- 1 transaction ごとにログバッファをディスクに書き込みます。
- 書き込む頻度がtransactionごとなので、失うとしても1 transactionであり、非常に堅牢ですね。
- データ量とか考えてみたら、普通に考えたら、なかなか1 tansactionレベルを失うって考えにくいですよね。
- というか、ACIDプロパティを利用するのは 1 のときだけであり、必ず1 transationしか失いようがないというのが正しいですね。
2
- 1 sec ごとにログバッファをディスクに書き込みを行います。
- あーまた出た。0 のときと同じ。フラッシュが保証されないですね。
- 0のときと違う点は、コミット時に、あくまで「ログバッファ」については、ディスクに書き込むよ、でもフラッシュまではしないでおくね。(その分書き込みが減るから当然writeパフォーマンス上がるね！)

って感じで変わります。トランザクションをディスクへ(つまりbinlogへ)書き込むまでの、堅牢性が明らかに高いのは1なのがわかります。

AWSでは…？

これ、なんとAWSのRDSの公式では、「2を設定すべき」って言ってますね…まずくね…？？？？
いや確かに

システムの耐久性よりパフォーマンスを優先する場合は

っていう前置きはあるが、その前段階として、

トランザクションの耐久性を維持するには、ログバッファを堅牢なストレージにフラッシュする必要があります

って言ってるんだから、ちょっと分かりにくすぎるしミスリードするんじゃない？

Auroraはどうやら1にしていて、何ならこれ変えられないみたいですね。
パフォーマンス重視するならAuroraにするとI/Oパフォーマンスが落ちるかもしれないですね。

Innodb_read_io_threads、Innodb_write_io_threads

これは、Innodbが利用するI/Oスレッドのパフォーマンスに直結しますね。何スレッド、MySQLのInnoDBが利用するI/Oに使うの？っていうのがこれです。
これデフォルトの4とかにしてませんか？
5兆個あるコアでも、5兆thread使いませんwwww4なのでなんもしないと4コアしか使いません。無駄ですね〜。
(1coreあたり1threadなCPUなマシンとして)

relay_log_recovery

これは、そもそも

binlog
relaylog

ってなんだ？っていうのがわかっていないといけないと思います。
binlogは、いわゆるバイナリログで、実行したトランザクションが記載されます。(primaryサーバに存在)
relaylogは、そのbinlogの内容をそっくりそのまま、記載します。(replicaサーバに存在)

MySQLにおけるReplicationは、

[Primary] binlogにTransactionを吐き出し続けている
↓
([replica] replica I/O threadというthreadが動いている(show processlistみるとわかる) )
↓
[replica] replica I/O threadは、Primaryサーバに対し、binlogを要求する。
↓
[Primary] binlog dump threadというthreadが、要求を受けると立ち上がり、binlogを replicaに送信する。(このthreadもshow processlistで出る)
↓
[replica] replica I/O threadは、この受け取ったbinlogをもとに、relaylogに内容を書き込む
↓
[replica] replica SQL threadは、このrelaylogの中身をもとに、SQLを自分のサーバに対し実行する。(INSERTとかUPDATEとかもろもろの更新系クエリ。それしか元々binlogに吐き出されないので。)

というプロセスを踏みます。

これで、primaryもreplicaもおんなじ状態になったわけです。
(まあこの処理が時間がかかると、replicaサーバは、primaryサーバに対しての遅延がおきるわけですが…いわゆるseconds_behind_primary ですね。replicaサーバでshow replica statusクエリを叩くとでみれます)

で、前提はここまでなんですが、ここで出てきた、relay logですが、もしも、3~6の間に、インスタンス障害で、リレーログが破損したらどうなるでしょうか？
答えは、「replica SQL threadが、自分に対して実行するクエリが不完全になる」可能性が非常に高い、です。
これを解消するのが、relay_log_recoveryです。

障害が起きると、replicaサーバは、mysqlが立ち上がったとき、自動で新規にrelay logを作り直します。
作り直すときは先程示したロジックが実行されます。
ここで、
Q. 障害が起きてreplicaサーバが立ち上がるまでのbinlogの変更内容はどうするのか？
と疑問が湧くかと思います。

これは実は実行されます。
要は、どこまで読んだかを、ファイルと、そのファイルをどこまで読んだか、replicaサーバは常に把握しているからです。(それぞれ、Source_Log_File, Read_Source_Log_Pos)
primaryサーバに更新系クエリが投げられている環境で、replicaにて、show replica statusを見てみるとわかります。

で、さっきの疑問にお答えすると、

replica SQL threadは、新しいリレーログの初期化にあわせ、最初の場所から読み始めようとします。
replica I/O threadは、Source_Log_File, Read_Source_Log_Posをもとに、binlog取得を再開します。
取得したらさっきのロジックの要領で、SQL threadによりSQLが実行されていきます。

そのため、一貫性が保たれた状態が用意できるといえます。

relay_log_purge

relay_log_recoveryが有効でも、新しいrelay logが作られるとき、古いログはどうなるかと言うと、削除されてないままじゃないですか！障害置きたときのrelay logは正しくない状態になっててもおかしくないな〜。いやですね。
このパラメータをOnに設定してやれば、不要な古いrelay logは削除してくれます。こいつのおかげで、セットで使えば、本当のrelay logのinitializeができるって感じです。セットで使いましょう。

relay_log_info_repository

これは、さっき書いた、

要は、どこまで読んだかを、ファイルと、そのファイルをどこまで読んだか、replicaサーバは常に把握しているからです。(それぞれ、Source_Log_File, Read_Source_Log_Pos)

をどこで持っているのかということの答えになりますが、
これ、実はデフォルトだと、ファイルに吐き出されるようになってるんですが、DBのテーブルで管理することもできます。
relay_log_info_repository = TABLE

テーブルで定義すれば、トランザクション内でレプリケーション情報を更新するんで、常に別ファイルとかじゃなくて、トランザクションの他のデータと一緒に、replicaサーバで同期してくれるから比較的安心だよね！っていうことです。
https://yakst.com/ja/posts/57 ここ、余裕あったら読むといいかも。どういうロジックでcrash safeな設定を保てるかみたいなの結構書いてる。この記事読み終えたら理解できるんじゃないかな。

max_connections

なんか難しいロジックの話が沢山あったので一旦閑話休題みたいに簡単な話をしましょう。
max_connections、正しく設定していますか？
これ、もし設定を、受け付けたいだけのconnection数を適切に設定していないままだと…
クエリがめっちゃ重いものが飛ぶようなApplicationがあると、アホ重いクエリが並列でバンバン実行されて大変！！
その時はパラメータを調整…じゃなくて、そのアホ重いクエリを投げてきたところに問題がないか調査しましょう。
アプリケーションのバグや、人間のバグ(ヒューマンエラーで重たいクエリをなげたとか)だったら、直すなり再発防止をすべきです。
なぜなら、connection数なんて雑にめっちゃ設定しちゃっていいんですから。
最大でもどれくらいAPIがアプリケーションで叩かれ、それを元にどれくらいのクエリが飛んできそうか？それを判断して、多めに設定すればいいだけです。

ちょっとGTIDって結構良さそうだよ！見ていきませんか？

GTID(Global Transaction ID)といいます。
こいつマジで優秀です。堅牢性がアホみたいに上がり、障害発生時の対応がほぼ無くなりますw

どういうことかというと、relay logって、その内容を実行する際、SQLthreadが実行すると思うんですけど、
その実行されたかどうかの情報(relay log info)が更新されてない状態だと、また実行しちゃうじゃないですか。
このときに出るエラーって、皆さんご存知、 1062 duplicate entry です。
で、めっちゃリクエストが飛んできてクエリが叩かれるような、すごい高負荷なワークロードにおいては、リレーログが再度実行する内容って、めっちゃ多いと思うんですよ。
それを、何個もskip,skip,skip...しんどいですね。
もうバックアップから復元してbinlogすべて持ってきて対応！とか、別のreplicaがいるからそいつをいい感じに使わせてもらう！とかもあると思うんですけど、それって運用でカバーでしかないし、あんまり賢くないですよね。なんとかしたい。

GTIDはこの問題を解決します。

名前の通り、こいつを使うと、トランザクションごとにuniqueなIDを振ります。
GTIDが使えると、binlog/relaylogには、
そのクラスタ内(マルチprimary-マルチreplica構成でもなんでも、replicationして一緒に動いてるグループ)にて、
クラスタ全体(global)においてuniqueなIDを、トランザクションごとに付与します。

これによって、論理的に考えてみたら、GTIDによってレプリで変わることってなんだと思いますか？

そうです。
relay log、 すべてのbinlogもっかい読み取ってくればいい だけになるじゃん。
relay_log_recoveryしなくても、 replica_io_threadが、binlogを最初から読んでくれば、正しいところから確実に読んでくれるじゃないか。

はい、ということで、GTID使うなら relay_log_recovery は不要になるんですね〜
あっ、当然、なんぼまでならreplicaのイベントをskipする？っていうパラメータである、sql_slave_skip_counterは、当然不要になるのでこいつも使えなくなります(何なら0にしないといけない)。

ただし、マルチスレッドレプリカにおいては、割と最近対応されたものなんで注意がいります。(MySQL8.0.18~)

ということで、GTID使いましょう。簡単なので。
あっ、ちなみに、GTIDつかってると、 show replica statusしたとき、

Retrieved_Gtid_Set
- 受け取ったGTIDのトランザクション番号の最初と最後
Executed_Gtid_Set
- 実行したGTIDのトランザクション番号の最初と最後

がみれます。
あっ、ちなみに、show replica statusを眺めているとね、Source_UUID(Primary サーバのUUIDね)あると思うんですけど、
上記の２つのGtid_Setって、Source_UUIDベースになってません？
そうです。
GTIDのIDって、
"Source_UUID:Transactionについた番号"のフォーマットで付与されます。uniqueなIDっていっても、どうuniqueさを出してるか、わかりやすいですねー。

で、実際に有効にするパラメータとしては、

gtid_mode=ON
enforce-gtid-consistency=ON
この2つです。

それぞれ解説します。

メリット・デメリット整理

メリット
- 一度実行したものに対し、二度と実行しないことで、本当の意味での整合性(Consistency)を担保できるため、ACID特性の要素の大事な部分を担保できる。
  - つまりReplication障害が、どんなhosterror多い環境でも起きなくなるわけです。
- レプリ組むのが楽
  - レプリケーションは、CHANGE MASTER TO ~で設定しますが、今まで↓が面倒でした…
    - ① primaryサーバで、show primary statusで、以下の情報を取得
      - MASTER_LOG_FILE
      - MASTER_LOG_POS
    - ② replicaサーバで、CHANGE MASTER TO ~にて上記を指定
    - ③ start replica
  - GTIDだと、すでに実行したものは実行しないので、、、？
    - ① replicaサーバで、CHANGE MASTER TO ~のとき、MASTER_LOG_FILEとMASTER_LOG_POSのかわりに、MASTER_AUTO_POSITION=1を指定する
    - ② start replica
    - これだけ！！！！！！！！！！！！！！primaryサーバにあれこれ見に行かなくてOK！
デメリット
- GTIDを使うことで、使っちゃいけないオプションが有る可能性。
  - ドキュメントに書いてるんで、特に、レプリケーション系パラメータは調べておいてください。
- mysqldumpがオプション指定が必要になるかも。
  - 今まで通りにdumpすると、dumpの中をみると、GTIDの情報も記載されてしまう。
    - GTIDがついてるから他の環境でリストアができない(グループ外でのリストアとか)とき困る。
    - --set-gtid-purged=OFF指定すれば記載されなくなるんでOK！
    - https://donbulinux.hatenablog.jp/entry/2019/01/17/232419

`gtid_mode=ON`

まんまです。GTID使うってだけです。
ただし、さっきあげた通り、Replicationロジックを考えると、不要になるパラメータがちらほらありますんで、自分の環境で使っているReplication系パラメータを一度見直したほうがいいと思います。
https://dev.mysql.com/doc/refman/8.0/ja/replication-options-replica.html
↑ここあたりみればいいのかな？

`enforce-gtid-consistency=ON`

これ、GTID使うということならONにしてください。
どういうことかというと、GTIDによって、
「一度実行したものが実行されない」
ということで得られる整合性のルールが、

OFFだとルール違反してもよいとなり、無視される
WARNも指定できるが、これは、ルールに違反しても良いが警告を出す

…こんな感じで、つまりGTIDのルールを守るか守れないかを定義するパラメータです。

どう考えてもこれONにしないとあかんでしょ。GTIDによる整合性保ってくれるルールは守ってください、ってことで定義しましょう。

あとがき

実は、友人から、
「整合性とかそこまで気にしないで、というかRDBMSのパラメータとかレプリロジックって全然理解している人がいないよ！」
「てか教えてほしい」
って言われて作った記事ですこれ。
僕は、このくらいはSRE/インフラエンジニアなら、クラウドで用意されるとはいえ、結構大事な部分だから、ACID特性とかみんな知ってると思ってたし、理解しておいたほうがいいと思いました。
特に、金融とか顧客情報扱うとことかは、こういうACID特性に対する意識を持つべきだと思いますし、ここにかいたことなんて一部のパラメータですが、ロジックさえわかればいろんなパラメータの理解なんて楽勝です！！

余談1

僕は、GTIDとか、crashsafeな設定を入れることで、ゲームっていう高トラフィックかつ、高負荷、大規模なDBの障害発生率を著しく下げました。
それ以降は、ほぼhost errorとかで障害なって、DB復旧対応する必要とか発生してないんじゃないですかね。勝手に復旧してくれるので。
少なくとも僕がその会社に在籍しているときは、そんな事象見たことなかったと思います。

余談2

ちなみに、read replicaとして使うなら、sync_binlog=0とか、innodb_flush_log_at_trx_commit=0にするだけで、

別に一貫性なくていい
正直すぐバックアップから戻せばいいレベルの大きさ
壊れて放っておいても良い
つまりどうでもいいDB

なら、めっちゃパフォーマンス上がるんで設定するのを考えるのはありです。それに合わせてインスタンススペック下げてコストダウンするとかね。

【超危険】【クラウドも】そのMySQLパラメータ設定、やべ〜〜〜〜かも