MySQLレプリケーション復旧　テーブルロックをせず特定テーブルのみコピーする　その1

今回は、レプリケーションの問題発生時の復旧で、テーブルロックをせずに特定テーブルのみ再コピー(dumpとインポート)する方法について書きたいと思います。テーブルロックするとその間マスターサーバーの機能を制限してしまうの運用時にはなるべく避けたいものです。(この記事は、innoDBのデータベースやテーブルが対象です。)

初回のレプリケーションセットアップ

よくあるレプリケーションのセットアップ手順は

マスターでサーバーIDとバイナリログを有効にする
スレーブがアクセスするためのレプリケーションユーザーをマスターに作成する
マスターでFLUSH TABLES WITH READ LOCKを開始する
マスターでSHOW MASTER STATUSを実行し、binlogファイル名とポジションをメモする
mysqldumpでマスターデータをダンプする
マスターでUNLOCK TABLESを実行してロックを解放する
スレーブでダンプしたデータをインポートする
スレーブでCHANGE MASTER TOを実行してマスターホストとメモしたバイナリログポジションを登録する
スレーブでSTART SLAVEを実行してレプリケーションを開始する

といった内容で、具体的には以下のようになります。

//my.cnfの設定 
server-id = 1
log-bin = mysqld-bin
binlog-format = ROW

//ユーザー作成、ロック、バイナリログポジション取得
master> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'password_here';
master> FLUSH TABLES WITH READ LOCK;
master> SHOW MASTER STATUS;  //--> File: mysqld-bin.000001 Position: 1101983 

//マスターデータダンプ
$> mysqldump -uroot -p --databases databaseA databaseB databaseC > ./master_dump.sql

//ロック開放
master> UNLOCK TABLES;

// -----   ここからスレーブで ----------------
//スレーブでのデータインポート
$> mysql -uroot -p --default-character-set=utf8 < ./master_dump.sql

//スレーブにマスター登録とレプリケーションの開始
slave> STOP SLAVE;
slave> RESET SLAVE;
slave> CHANGE MASTER TO MASTER_HOST = '192.168.0.2',MASTER_USER='replication_user',MASTER_PASSWORD='password_here',MASTER_LOG_FILE='mysqld-bin.000001',MASTER_LOG_POS=1101983;
slave> START SLAVE;

レプリケーションの不具合

運用中に何らかの問題でスレーブのSQLスレッドが書き込みエラーを起こすと、マスターとスレーブにデータの違いが出てしまいます。最初に試みる修復は、エラーの内容を見てスレーブのレコードを削除したり、SQL_SLAVE_SKIP_COUNTER変数を使ってイベントをスキップすることで行います。これで解消できればよいですが、データに矛盾が無いかどうか確認しにくかったり、複雑で手に負えないこともあります。そのような場合、マスターからコピーし直せば確実です。

しかし、初回と同じ手順で再度セットアップすると、機能制限の時間が問題になります。

マスターの書き込み機能を、FLUSH TABLES WITH READ LOCKの間停止しなければならない。
1つのマスターに複数のデータベースがある場合、必要なすべてのデータベースをダンプとインポートしなければならない。特にデータサイズが大きい場合は多くの時間がかかる。

テーブルロックフリーな部分コピーによる復旧

マスターの機能制限の時間が最小限になるよう、テーブルロックフリーでの部分コピーによる修復方法を考えてみたいと思います。スレーブは壊れた状態なので、復旧担当以外アクセスできないようになっていることが前提です。

ログポジションの問題

大抵の場合、問題は特定のデータベースやテーブルに限定できることが多いと思います。そこで、全部のデータベースではなく一部のみのダンプとインポートを行うとどうでしょうか。
初回セットアップ時と同じ手順で、テーブルロックなしに部分的にコピーしようとすると、下記の問題が生じます。

スレーブを停止せずに行うと、SHOW MASTER STATUS以降の変更が重複してスレーブに適用されてしまう。（ログポジションをSHOW MASTER STATUS時点に戻すため）
事前にスレーブを停止すると、停止時のログポジションからSHOW MASTER STATUSするまでの変更ログが無くなる。（ログポジションをSHOW MASTER STATUS時点に進めてしまうため）

部分コピーを行うには、データに矛盾を発生させないように、スレーブ停止時とダンプ時のログポジションが一致する、すなわち、SHOW MASTER STATUSで確認したログポジションまでスレーブに適用された状態でスレーブを停止する必要があることがわかります。

START SLAVE UNTIL

START SLAVEにはUNTILというオプションがあります。UNTIL master_log_file='xxxx', master_log_pos=xxxのように使用します。UNTILはSQLスレッドが指定したログポジションまで達したら停止してくれます。これを利用して以下の手順を加えます。

STOP SLAVEを実行します。
マスターでSHOW MASTER STATUSを実行し、スレーブでそのポジションをUNTILに加えてSTART SLAVEします。
問題が発生した場合は、SQL_SLAVE_SKIP_COUNTERに1を指定してから、再度START SLAVE UNTILを実行し、問題のあるイベントをスキップします。これを繰り返して問題をすべてスキップします。
SHOW SLAVE STATUSを何度か実行して、指定したポジションまでログが適用されSQLスレッドが停止するのを待ちます。
スレーブでインポートします。
スレーブでRESET SLAVE、マスター再登録とレプリケーションの開始を行います。

これで、サーバーの一部のデータベースのみ矛盾なくコピーし直すことができます。ただし、SHOW MASTER STATUS時点のコピー元データが必要です。これを現在のMySQLで実現するには２つの方法があります。

事前にFLUSH TABLES WITH READ LOCKしてダンプする。
事前にFLUSH TABLES WITH READ LOCKしてその間にスナップショットを開始しダンプする。

ちなみに、START SLAVE UNTILは MySQL 5.5～5.7、MariaDB 5.5～10.1 で使用できます。

テーブルロック時間を最小限でダンプする

mysqldumpには、バイナリログポジションを同時に取得する--master-dataオプションがあります。これを使うと、自分でFLUSH TABLES WITH READ LOCKを発行する必要はありません。しかしながら、--master-dataオプションは内部でこのコマンドを使っていますので、完全にテーブルロックなしにダンプすることはできません。以下はその部分を示すmysqldumpのソースコードの抜粋です。

// mysql-5.6.20 mysqldump.c : 5797
  if ((opt_lock_all_tables || opt_master_data ||
       (opt_single_transaction && flush_logs)) &&
      do_flush_tables_read_lock(mysql))
    goto err;

--single-transactionオプションを使うと、ロック開始後にスナップショットを取り（start_transaction）、SHOW MASTER STATUSしてすぐにロックを開放します。これによりロック時間をほんのわずかな時間にすることができます。

// mysql-5.6.20 mysqldump.c : 5827 
  if (opt_single_transaction && start_transaction(mysql))
    goto err;

  /* Add 'STOP SLAVE to beginning of dump */
  if (opt_slave_apply && add_stop_slave())
    goto err;


  /* Process opt_set_gtid_purged and add SET @@GLOBAL.GTID_PURGED if required. */
  if (process_set_gtid_purged(mysql))
    goto err;


  if (opt_master_data && do_show_master_status(mysql))
    goto err;
  if (opt_slave_data && do_show_slave_status(mysql))
    goto err;
  if (opt_single_transaction && do_unlock_tables(mysql)) /* unlock but no commit! */
    goto err;

　// ...このあとダンプ開始

初回の方法よりはテーブルロックを十分短い時間にできるので、

$>mysqldump -uroot -p --master-data --single-transaction databaseA tableA tableB > ./master_dump.sql

のようにダンプしましょう。
しかし、START SLAVE UNTILを実行するためには、ダンプファイルを開いてログ名とポジションを自分で読み取らなければなりません。少々面倒ですね。

ここまでの方法は、mysqlクライアントとmysqldumpを使って行うことができます。後述のまとめに手順一式を書いておきます。

テーブルロックをせずコピーする

ここまで見てきた通り、mysqldumpでは、完全にテーブルロックをせずスナップショットとその時点のログポジションを取得することはできません。
しかし、Transactdのnsdatabase::beginSnapshot メソッドを使えば、テーブルロックをせずにスナップショットを開始し、その時点でのログポジションを取得できます。

なぜテーブルロックフリーで取得可能なのでしょうか。
MySQLはXAトランザクションを使ってバイナリログとinnoDBの処理結果に矛盾が無いようにしています。XAのコミットは、最初にcommitロックを取得してシリアライズされます。Transactdのnsdatabase::beginSnapshotは、このcommitロックを使って以下のように処理します。

commitロックを取得（他の書き込みブロック）
ログポジションを取得
スナップショットを開始
commitロックを開放

commitロックは通常の書き込み処理と同様のロックなので、テーブルをロックすることはありません。他の処理を止めることなくスムーズに処理されます。
あとは、このスナップショット中にマスターデータを読み取ってスレーブにコピーします。
これで完全にテーブルロックフリーでコピーできます。マスターはほんのわずかな機能停止もありません。
具体的なコードは次回改めて詳しく書きたいと思います。

まとめ

mysqlクライアントとmysqldumpを使って、可能な限りロックを短くしたレプリケーションの部分コピーを行う方法は以下のようになります。
例としてdatabaseAのtableAとtableBのみコピーします。「障害はtableAとtableBでのみ発生している」と特定できていることが条件です。

/*
 databaseA の tableA と tableB のみ再コピーするサンプル
 master> はマスターのmysqlクライアントでの処理
 slave>  はスレーブのmysqlクライアントでの処理
*/
//スレーブ停止
slave> STOP SLAVE;
//マスターデータダンプ (ロック、スナップショット、binlog posの取得)
$> mysqldump -uroot -p --master-data --single-transaction --add-drop-table databaseA tableA tableB > ./master_dump.sql
// ./master_dump.sqlからbinlogファイル名とポジションを読み取ってメモしておく
slave> START SLAVE UNTIL master_log_file='xxxx', master_log_pos=xxx; //ファイル名とポジションは先ほどメモした値
// -- ここから エラーが発生した場合の処理 --
slave> STOP SLAVE;
slave> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
slave> START SLAVE UNTIL master_log_file='xxxx', master_log_pos=xxx; //ファイル名とポジションは先ほどメモした値
// -- エラーが発生した場合の処理 ここまで --
//    以上をエラーが発生するたびに繰り返し、エラーをすべてスキップする
slave> SHOW SLAVE STAUS;　　// Exec_Master_Log_Posが指定したポジションになってSQLスレッドが停止するまで待つ
//スレーブ停止　ダンプしたデータと同じログポジションまで適用されている状態
slave> STOP SLAVE;
//スレーブでのデータインポート
$> mysql -uroot -p --default-character-set=utf8 < ./master_dump.sql
//スレーブへのマスター登録とレプリケーションの開始
slave> RESET SLAVE;
slave> CHANGE MASTER TO MASTER_HOST = '192.168.0.2',MASTER_USER='replication_user',MASTER_PASSWORD='password_here',MASTER_LOG_FILE='xxxx',MASTER_LOG_POS=xxx;
slave> START SLAVE;

今回はタイトルにある「テーブルロックをせず」まで至っていません。次回はTrasnactdを使って完全にテーブルロックしない方法でのスクリプトを紹介します。エラーのスキップも簡単にできてとても便利なスクリプトです。

追記 (2016/05/27) その２を公開しました。
bizstation.hatenablog.com

BizStationブログ

ビズステーション株式会社の公式ブログです。