gihyo.jpサイトのロゴ

MySQL道普請便り

第62回MySQLのクライアントプログラムいろいろ［その2］

2018-01-09

MySQLのクライアントプログラム

MySQLのパッケージにはMySQLサーバーであるmysqldの他に、いくつかのクライアントプログラムが付いてき（依存関係でインストールされ）ます。

今回は、第51回　MySQLのクライアントプログラムいろいろ［その1］の続きとして、mysqldumpコマンドについて説明します。

デモ用の環境はCentOS 7.3のサーバー上に以下のコマンドで作成しました。

$ yum install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
$ yum install mysql-community-server
$ rpm -qa | grep mysql
mysql57-community-release-el7-11.noarch
mysql-community-common-5.7.20-1.el7.x86_64
mysql-community-client-5.7.20-1.el7.x86_64
mysql-community-server-5.7.20-1.el7.x86_64
mysql-community-libs-5.7.20-1.el7.x86_64

mysqldump

mysqldumpはMySQLの論理バックアップを取得するためのプログラムです。簡単な使い方は第15回　mysqldumpを使ってバックアップするにまとまっていますので、すぐに試してみたい方はこちらを参照してください。

また、公式ドキュメントは以下のURLにあります。

mysqldumpの論理バックアップ

mysqldumpの論理バックアップは「SQL（SELECT * FROM ..ステートメント）でバックアップを取得」し、「⁠全データに対してSQL（INSERT INTO ..ステートメントまたはLOAD DATA,LOAD XMLなどのステートメント）を再実行してリストア」します。

このことを知っておくと、mysqldumpのトラブルとして挙げられる「バックアップ中に負荷が上がってレスポンスタイムが悪くなる」ことは全テーブルをスキャンするためのバッファプールや、それを全転送するための帯域が足りなくなることで説明ができます（逆に、バッファプールや帯域がデータ量に対して十分あればこれは問題になりません）し、「⁠バックアップに比べてリストアが非常に遅い」ことは全データをSQLとして（シングルスレッドで）再実行するためにかかってくるオーバーヘッドですので、バルクロードを最適化するためのパラメーターをチューニングすることでリストアの速度を上げられることがわかります。

--single-transactionオプションと--lock-all-tablesオプション

mysqldumpのオプションのうちもっとも大切なものはこの2つです。この2つは同時に指定することができませんのでどちらかを選択して使用することになります。 デフォルトではどちらもOFFです。

ドキュメント上の記載は以下のURLを参照してください。

--single-transactionがONの場合

--single-transactionオプションが有効な場合、mysqldumpはバックアップを取得するために実行する全てのSELECT * FROM ..ステートメントを1つのトランザクションとして実行します。具体的には概ね以下のようなSQLが実行されることになります。

mysql> FLUSH TABLES WITH READ LOCK; データベース全体に読み取りロックを設定する
mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; トランザクション分離レベルをREPEATABLE READに変更
mysql> SHOW CREATE TABLE ..;  1つめのテーブルの定義情報を読み取る
mysql> SELECT * FROM ..;  1テーブルずつデータを読み取る
mysql> SHOW CREATE TABLE ..;  次のテーブルへ
mysql> SELECT * FROM ..;  次のテーブルへ
..
mysql> ROLLBACK;  トランザクションを終了

トランザクション分離レベルにREPEATABLE READを指定することで、最初のテーブルに対して実行したSELECTも2つ目のテーブルに対して実行したSELECTも「同じタイミングのデータ」が取得できます。最初のテーブルをバックアップしている最中に2つ目以降のテーブルに対してINSERTやDELETEが発生しても「それらの更新が無かったかのように、トランザクションが開始された時点のデータまでさかのぼって」データを取得するということです。

これにより、親子関係のあるようなテーブル（たとえば1つ目のテーブルがブログの記事情報、2つ目のテーブルが記事に対するコメント情報だったとして）においても整合性のあるバックアップを取得することができますが、トランザクションに対応していないストレージエンジン（もっとも多いパターンはMyISAMストレージエンジンです）を利用（トランザクション対応のInnoDBストレージエンジンとMyISAMストレージエンジンが混在している場合を含む）している場合、--single-transactionオプションではデータの一貫性を保つことができません。後述の--lock-all-tablesオプションを利用することになります。

MySQL 5.7現在、システム用のmysqlスキーマには多くのMyISAMストレージエンジンのテーブルが含まれているため、--single-transactionを利用した場合ではそれらのテーブルとデータの整合性は保証することができません。が、多くの場合mysqldumpでバックアップを取得している最中にmysqlスキーマが更新される（CREATE USERで新しいユーザーを作成する、GRANTでユーザーに権限を割り当てる、CREATE PROCEDUREでストアドプロシージャを作成する、INSTALL PLUGINでプラグインをインストールする…など）ことは容易に避けられるため、あまり問題になることはありません。

--lock-all-tablesがONの場合

--lock-all-tablesオプションが有効な場合、mysqldumpはバックアップ実行中にデータベース全体に読み取りロックをかけ続けます。先程と同じようにSQLで例示すると以下のようになります。

mysql> FLUSH TABLES WITH READ LOCK; データベース全体に読み取りロックを設定する
mysql> SHOW CREATE TABLE ..; 1つめのテーブルの定義情報を読み取る
mysql> SELECT * FROM ..; 1テーブルずつデータを読み取る
mysql> SHOW CREATE TABLE ..; 次のテーブルへ
mysql> SELECT * FROM ..; 次のテーブルへ
..
/* 読み取りロックはバックアップ終了時のコネクション切断で暗黙に解除される */

mysqldumpの実行中に全ての書き込みをブロックすることで、トランザクション非対応のストレージエンジン間でもデータの整合性を保証することができます。裏を返すと、mysqldumpの実行中に全ての書き込みをブロックしない限りはトランザクション非対応のストレージエンジン間でデータの整合性を保証することは できません 。

しかしながら、稼働しているデータベースの全てのテーブルに対する書き込みを停止してバックアップを取得することは困難なため、この方法でバックアップを取る場合はバックアップ用のスレーブサーバーを作成し、そのサーバーに対してmysqldumpを実行することになります。

--single-transaction,--lock-all-tablesがいずれもOFFの場合

これら--single-transactionおよび--lock-all-tablesがいずれもOFFの場合はどうなるでしょうか。非常に残念なことに、以下のSQLのようになります。

mysql> LOCK TABLES .. READ; 同一スキーマ内の全てのテーブルに読み取りロックをかける
mysql> SHOW CREATE TABLE ..; 1つめのテーブルの定義情報を読み取る
mysql> SELECT * FROM ..; 1テーブルずつデータを読み取る
mysql> SHOW CREATE TABLE ..; 次のテーブルへ
mysql> SELECT * FROM ..; 次のテーブルへ
..
mysql> UNLOCK TABLES; スキーマ内の読み取りロックを解除する
mysql> LOCK TABLES .. READ; 次のスキーマの全てのテーブルに読み取りロックをかける
..

スキーマ単位でテーブルロックを取得して、同一スキーマ内のテーブル間においては整合性を維持しようとします（この動作は--lock-tablesオプションで制御できます⁠）⁠。スキーマをまたいだデータの整合性は保証されませんので、これをフルバックアップとするには大きな問題があります。

残念ながらMySQL 5.7.20現在、--single-transactionまたは--lock-all-tablesを明示的に指定しなかった場合はこの動作となります。

注意事項

「--single-transactionオプションを指定した場合、FLUSH TABLES WITH READ LOCKが一瞬だけ走る」という話を聞いたことはないでしょうか。本番環境においてmysqldumpでバックアップを取得する多くの場合、これは正です（FLUSH TABLES WITH READ LOCKが走ります⁠）⁠。

ただしこれは--single-transactionオプションに起因するものではなく、--master-data または--flush-logs オプション（あるいは両方）を指定した場合にトリガーされます（本番環境で取るバックアップには多くの場合--master-dataを指定していると思います⁠）⁠。これらを指定しない場合、--single-transactionではFLUSH TABLES WITH READ LOCKは実行されません。

これらのオプションと--single-transactionを同時に指定することで、「⁠FLUSH TABLES WITH READ LOCK後にトランザクションの開始およびバイナリーログの操作が終われば、このロックはすぐに解放（UNLOCK TABLES⁠）⁠」されます。ロックを解放するためのステートメントが--single-transactionオプションに紐付いているため、--single-transactionが指定されていない場合は、FLUSH TABLES WITH READ LOCKはmysqldumpが終了するまで解放されません。--lock-all-tablesを有効にしている状態ではもともとバックアップ終了までロックが継続することを期待しているので問題ありませんが、どちらも指定しない場合は少し想定と違う動作になるかも知れません。

まとめ

mysqldumpはMySQLの論理バックアップを取るためのプログラムですが、--single-transactionまたは--lock-all-tablesを指定しない場合、ダンプは不完全な（データの整合性が取れていない可能性がある）ものとなります。

mysqlスキーマを除いた全てのテーブルがInnoDBストレージエンジンである場合は--single-transactionオプションを、それ以外の場合は--lock-all-tablesオプションを利用する必要があります。

--single-transactionにおいてFLUSH TABLES WITH READ LOCKが実行されるのは--master-dataまたは--flush-logsが同時に指定されている場合です。

おすすめ記事

記事・ニュース一覧