運用中のMySQLに対して書き込み（DML）を制限したいことがあると思います。たとえば、読み取り専用のMySQLを作成したり、バージョンアップのため新しいバージョンのスレーブを用意してマスターと切り替えるときに、マスターに対して書き込みを制限したいなどです。
また、もしあなたがMySQLのHAを提供する仕組みを作ることになったとすると、さまざまなMySQLの機能を使い実装すると思います。
そして、フェールオーバーに失敗し、スプリットブレインが発生する可能性を考慮しなくてはいけません。それを防ぐために、ユーザーからMySQLへの書き込みを禁止する仕組みが必要です。
MySQLには書き込みを禁止するためのいろんなオプションや手段があります。今回はMySQLへの書き込みを制限するさまざまな方法を紹介したいと思います。
1. READ_ONLY オプション
最初に紹介するのはREAD_ONLYオプションです。このオプションは古くからあるオプションのため、知ってる方が多いと思います。SET GLOBAL READ_ONLY=ONを実行すると、MySQLは読み通り専用モードとなり、書き込みを実施するとエラーを返します。
ただし、MySQL 5.7とそれ以前ではSUPER権限、MySQL 8.0ではSUPER権限またはCONNECTION_ADMIN権限をもっているユーザーであれば、READ_ONLY=ONであったとしても書き込み可能です。
2. SUPER_READ_ONLY オプション
次に、SUPER_READ_ONLYオプションです。このオプションはMySQL 5.7から登場したオプションで、使い方や仕様はREAD_ONLYオプションと同じです。SET GLOBAL SUPER_READ_ONLY=ONを実行すると、MySQLは読み通り専用モードになります。
READ_ONLY オプションと異なる点は、MySQL 5.7とそれ以前ではSUPER権限、MySQL 8.0ではSUPER権限またはCONNECTION_ADMIN権限をもっているユーザーであっても、書き込みが禁止されます。よって、READ_ONLY=ONをベースにスレーブを管理している環境ではヒューマンエラーで誤ってスレーブに更新してしまう問題がありましたが、これを防ぐことができます。
シングルプライマリーモードで運用中のInnoDB Cluster環境下のレプリカ群は、すべて書き込みを禁止するために、このオプションがONになっています。
1、2 のオプションの注意点について
1、2で紹介したオプションは共通して、ONにするタイミングで待機する可能性があります。READ_ONLYオプションを例に説明します。
SET GLOBAL READ_ONLY=ONを実行したタイミングで、実行中のSELECTステートメントやアクティブなトランザクションのステートメント間では待機しません。ただし、実行中のDMLがある場合は待機します。これはwait_timeoutオプションを超えるとタイムアウトします。
さらに、SET GLOBAL READ_ONLY=ONの待機中は、後続のDMLもメタデータロックにより待機してランニングスレッドが増えていく可能性もあるので注意してください。よって、SET GLOBAL READ_ONLY=ONをするときは、実行中のDMLがないことを確認してから実施したほうが良いです。
3. innodb_read_only オプション
つぎに、innodb_read_onlyオプションです。InnoDBストレージエンジンで作成されたテーブルを読み取り専用とします。MySQL 8.0からはデータディクショナリテーブルもInnoDBになったため、すべてのストレージエンジンのテーブル作成、削除やCREATE USERやGRANTステートメントもできないようになっています。
このオプションのみオンラインでの変更はできません。MySQL起動時に設定されます。詳しくは 15.8.2 Configuring InnoDB for Read-Only Operation をご確認ください。
ちなみに、Aurora for MySQLではプライマリーかレプリカを判断するのにこのオプションが使用されます。OFFであればプライマリー、ONであればレプリカと判断できるようです。
4. OFFLINE_MODE オプション
つぎに、MySQL 5.7から登場した OFFLINE_MODEオプションです。SET GLOBAL OFFLINE_MODE=ONを実施すると、以降の接続は拒否し、実行中の書き込みもロールバックされ、接続中のスレッドはすべて強制的に切断されます。
MySQL 5.7とそれ以前ではSUPER権限、MySQL 8.0ではSUPER権限またはCONNECTION_ADMIN権限をもっているユーザーは切断されず、接続可能です。このオプションは読み取り専用のMySQLを作成する用途ではなく、MySQLへの接続を制限するオプションです。
注意点としては、OFFLINE_MODE=ONしたMySQLがマスターとして稼働していてスレーブを持っているとすると、そのスレーブが上記の権限を持っていなければIO_THREADも切断されるのでレプリケーションが停止されます。そのことは覚えておいたほうが良いです。
5. 権限を剥奪する方法
最後に、書き込みを制限したいユーザーから書き込み権限をREVOKEステートメント使用して剥奪する方法があります。これは以前の記事 第69回 MySQLの権限レベルについてで説明しましたが、グローバルレベルで書き込み権限が設定されている場合は接続中のスレッドには反映されません。
また、データベースレベルで書き込み権限が設定されている場合は、すでに接続中のスレッドがデータベースを選択していると反映されません。USEコマンドにてデータベースを選択したタイミングで反映されるためです。
みなさんの運用されているMySQLのユーザ権限はグローバルレベルやデータベースレベルでの書き込み権限だと思います。よって、権限剥奪により書き込み制限する方法は接続中のスレッドも加味するのであれば難しいと思います。新たに接続されるスレッドのみ加味するのであれば有効です。
まとめ
今回はいろいろな書き込みを制限する方法を紹介しました。個人的には、OFFLINE_MODEオプションが一番リスクが少なく好きな機能です。また、MySQLをシャットダウンさせる方法もあると思います。これらの方法を覚えておけば、書き込み制限する必要がある案件があった場合、要件に合った選択ができると思います。
