BizStationブログ

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

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

前回は、レプリケーション復旧においてMySQLのコマンドを使って最もロック時間を短くコピーし直す方法を紹介しました。
今回は、全くテーブルをロックせずに特定のテーブルやデータベースをコピーし直す方法を紹介します。

Nonlocking Replcopy

Nonlocking Replcopyは前回の記事の内容を実装したPHPスクリプトです。ブログのタイトル通り、テーブルロックをせず特定テーブルのみコピーできます。
スクリプトGitHubからダウンロードできます。実行環境や使い方の概要はreadmeをご覧ください。

github.com

また、テスト用のTransactdサーバー環境を手元のWindowsマシンに3分でセットアップできるTransactdTestSrvSetupもあります。是非活用してみてください。

Nonlocking Replcopyは以下のことができます。

それでは、このスクリプトを使った具体的な新規セットアップと、エラー別の修復方法を説明します。どの例もサーバーは稼働中のままでOKです。

新規にレプリケーションをセットアップする

設定はすべて設定ファイルに記述します。スクリプトコマンドラインパラメータには設定ファイル名を渡します。

$php replcopy.php repl_config.ini

具体的な新規セットアップ条件は以下の内容とします。

サーバーの種類MySQL 5.6
対象データサーバー全体
マスターホスト名server1
スレーブホスト名server2
以下は上記条件で新規レプリケーションをセットアップしたときの実行結果です。

[replcopy]# php dreplcpy.php ./repl_config_centOS.ini
Nonlocking replcopy version 1.0.0
--- Start replication setup  ---
Open slave database ...  done!
Stop slave ...  done!
Open master database ...  done!
Open master tables ...  done!
Begin snapshot on master ...  done!
Wait for stop slave until binlog pos ...  done!
Copying tables ...
  [database : test_v3]
    table : fieldtest ...  done!
    table : groups ...  done!
    table : nullkey ...  done!
    table : nullvalue ...  done!
    table : packrecord_test ...  done!
    table : scores ...  done!
    table : setenumbit ...  done!
    table : test ...  done!
    table : timetest ...  done!
    table : users ...  done!
Reset slave ...  done!
Change master ...
        set global gtid_purged='d30d02d6-3fe4-11e5-97db-00ffa4dbde57:1-8';
        change master to master_host='server1', master_port=3306, master_user='replication_user', master_password='123', master_auto_position = 1;  done!
Start slave ...  done!
Slave_IO_Running = Yes
Slave_SQL_Running = Yes
--- Replication setup has been completed ---
[replcopy]#

こんな感じで実行されます。どのような処理をしているかは、この実行結果の通りです。成功/失敗は最後のSlave_IO_Running = YesSlave_SQL_Running = Yesで判断しています。

設定のポイント

設定ファイルは以下のような内容です。

[master]
host=server1
repl_port=3306
repl_user=replication_user
repl_passwd=123

databases=
tables=
ignore_tables=

[slave]
host=server2
master_resettable=1
log_bin=0

[gtid]
using_mysql_gtid=1
type=2
  • databasestablesに何も指定しないとホスト全体をコピーします。
  • GTIDを使った新規セットアップはRESET MASTERの実行は必須です。master_resettable=1をセットして有効にします。
  • 新規セットアップなのでコピー処理のログは不要です。log-bin=0を指定してOFFにします。
  • MySQL 5.6のGTIDを使用するので、using_mysql_gtid=1をセットします。
  • MySQL 5.6のGTIDによるポジション指定でレプリケーションをセットアップするので、type=2を指定します。(0は非GTID、1はMariaDBのGTID)

mysql システムデータベース

mysqlという名前のデフォルトのデータベースがあります。このデータベースのテーブルはほとんどがMyISAMのテーブルです。MyISAMはMVCCをサポートしておらず、スナップショットが取れません。そのためコピー中にマスターで変更を加えると不整合を起こす場合があります。

Nonlocking Replcopyでは、ホスト全体を指定してもこのmysqlデータベースは含まれません。ホスト全体コピーの前にdatabases=mysqlとして実行し、その後ホスト全体を指定してコピーする分割方式で、完全な全体コピーを行うことができます。ただし、mysqlデータベースのコピー中はマスターでこのデータベースを変更しないことが条件です。

レプリケーションエラーを修復する

レプリケーションの修復を行う理由として

  • SQLスレッドエラー
  • I/Oスレッドエラー
  • データの不一致

などがあります。
それぞれのパターンごとに修復の仕方を説明します。

データの不一致を修復

これは最もシンプルです。データのコンペアツールやユーザーからの申告などでデータの不整合に気付いた場合です。SQLスレッドは運良くまだエラーにはなっていない場合です。
この場合は不整合のあるテーブルのみ列挙してコピーします。

不一致のあるデータベース名test
不一致のあるテーブル名tabel_a
table_b

設定ファイル上で、新規セットアップと異なる点は以下の部分です。

[master]
...
databases=test
tables=tabel_a,table_b
...

この設定で実行すれば、スムーズにコピーして終わります。

SQLスレッドのエラーを修復

設定内容は、上記の「データの不一致を修復」と同じです。ただし、SQLスレッドエラーを起こしているテーブルを事前にある程度調べる必要があります。そしてそのテーブルをコピー対象に設定します。もし、エラーの中に想定外のテーブルが出現した場合は、キャンセルして設定ファイルにそのテーブルを加えて再実行します。

このスクリプトは、START SLAVE UNTILを使って指定したポジションまでSQLスレッドが進むのを待機します。ところがSQLスレッドエラーが発生してしまってはそこまで進むことができません。そこで、待機時にSQLスレッドエラーがある場合、その内容を表示しスキップするかどうかを問い合わせします。

-------------------------
SQL thread has error(s).
-------------------------
Could not execute Update_rows_v1 event on table test_v3.fieldtest; Can't find record in 'fieldtest', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysqld-bin.000016, end_log_pos 2424
Do you want skip only this error ?
Y: Skip this error | A: Skip all error | C: Cancel replication

選択肢は以下の3つです。

  • Y : このエラーのみスキップする
  • A : RESET SLAVEしてすべてのエラーをスキップする
  • C : レプリケーション修復をキャンセルする

あらゆる条件で矛盾なく進められるのは、Yの「このエラーのみスキップする」を選択し、かつこのエラーの起きているデータベースまたはテーブルが今回のコピー対象に含まれている場合です。
エラーのスキップはすべてのエラーがなくなるまで繰り返し行う必要があります。

エラーメッセージには、対象のデータベース名やテーブル名が含まれているので、必ず確認するようにします。

Aの「RESET SLAVEしてすべてのエラーをスキップする」では容易にコピーを開始することができますが、リレーログにコピー対象に含まれないトランザクションがあるとそれがスレーブに適用されず矛盾が生じます。ホスト全体が対象であったり、コピー対象外のデータがあっても問題ない場合やコピー対象外のデータは無いとわかっている場合はこれを選択できます。

もし、コピー対象外で問題のあるSQLスレッドエラーが出現したらCの「キャンセル」をして、そのテーブルやデータベースを設定に加えて再度実行してください。ここでのキャンセルは追加の問題を発生させたりはしません。

このスキップを通過すれば、あとは新規セットアップと同じようにテーブルがコピーされレプリケーションが再開されます。

I/Oスレッドのエラーを修復

I/Oスレッドエラーの原因は

  • レプリケーション用のアカウントが無効になった
  • ファイアウォール設定が変更されブロックされた
  • マスターが停止している
  • マスターで RESET MASTERなどが実行され、スレーブの要求するログがない

など多岐に渡ります。また修復方法も原因ごとに異なります。Nonlocking Replcopyはこれらを回復するためのものではありませんが、再セットアップしてしまいたい場合には有用です。

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

この設定は新規セットアップと何も変わりありません。
ただ、既にスレーブが設定済みのため、現状の状態にI/Oスレッドエラーやバイナリログの矛盾あったりします。その場合、それらを表示して継続するかどうか問い合わせします。

-------------------------
IO thread has error(s).
-------------------------
Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-1-234, which is not in the master's binlog'
Do you want stop and reset slave ?
 (Y/N) ?

Yを選択すると、I/Oエラー自体を無視して再セットアップを行います。開発やテストなどでレプリケーションを行う場合、この再セットアップはとても頻繁に使用されます。

GTIDへの対応

Nonlocking ReplcopyMySQLMariaDBの両方のGTIDに対応しています。GTIDを使用している場合でも、修復コピーの考え方は前回の記事と同様です。
また、GTIDを使ったレプリケーションについては以下の記事も是非ご覧ください。
bizstation.hatenablog.com

エラーのスキップ

エラーのスキップについては、MySQL 5.6以降でgtid_mode=onの場合、空のトランザクションに代替する方法でスキップします。それ以外のサーバーはsql_slave_skip_counter=1を使ってスキップします。

ポジション指定

type=0とした場合、従来のバイナリログ名とポジションを使ってログポジションの指定をします。MySQLgtid_mode=onの場合でもこの指定は可能です。
type=1とした場合、MariaDBmaster_use_gtid = slave_posを使ってログポジションの指定をします。
type=2とした場合、MySQLmaster_auto_position = 1を使ってログポジションの指定をします。

フェイルオーバーでの注意点

GTIDを使ったマスターと複数のスレーブによるフェイルオーバー構成をしているときの注意点を説明します。
スレーブはフェイルオーバーによってマスターになる可能性があります。(ここでは以降、スレーブから昇格したマスターを新マスターと呼びます。)
新マスターは、遅れのあるスレーブに自身のバイナリログからトランザクションを転送しなければなりません。転送する可能性のあるのは、遅れスレーブとの差分です。

意図的に遅らせたスレーブがなければ、遅れはごくわずかな時間(例えば1秒)です。
例えば、あるスレーブを修復コピーしバイナリログを消去しても、それから1秒以上経てば、新マスターになって問題ありません。修復してから1秒ですから現実的にはほとんど問題ないでしょう。
しかし、意図的に12時間遅らせる等の設定をしたスレーブがある場合、バイナリログを消去したスレーブは、それから12時間以上経たないと新マスターになる資格がありません。フェイルオーバースクリプトなどを用意し、ある時間までは昇格できないと制御する必要があります。

Nonlocking Replcopyでバイナリログに関連するパラメータは以下の2つです。

  • log_bin
  • master_resettable

MySQLのGTIDとそれ以外の場合ではこれらに対する対処が異なるので、分けて説明します。

MySQL GTID

MySQLのGTIDはマスターとGTIDセットで比較するので、スレーブ上で余計なトランザクションを実行すると、新マスターになった時にそれが転送されてしまいます。ですのでmaster_resettable=1を指定して、そのスレーブのバイナリログをリセットした方がうまくいきます。ただ、リセットするとマスターになったときに遅延スレーブからのログ転送要求に答えることができません。遅延させたスレーブがある場合は、その遅延時間が経過するまで、修復したスレーブはマスターになれないとマークすることが必要です。

MariaDB GTID

MariaDBのGTIDの場合、log_bin=0としてコピーの内容をバイナリログに記録しないようにしましょう。遅れスレーブに対しても問題ありません。

もし、この修復したスレーブが多段構成でマスターでもある場合は、log_bin=1の必要が出てきます。この場合、MySQLの場合と同じように、遅延させたスレーブがある場合は、その遅延時間が経過するまで、修復したスレーブはマスターになれないとマークすることが必要です。さらに、修復直後にマスター上で何らかのトランザクションを発生せて、それが遅延スレーブに反映されるようにしましょう。そうでないと、遅延スレーブがコピーの内容以降のポジションを要求してくれません。

まとめ

2回に渡ってレプリケーションの復旧コピーについて説明しました。また、間にGTIDの詳細の記事も書きました。この3つの記事で、レプリケーションの復旧についてはだいたい網羅できたかと思います。

今回、パターン別修復方法の記事を書いたことで、自分の中でも修復時の問題と対処をうまく整理できました。

Nonlocking Replcopyは開発環境などではとても便利です。(開発時はあれやこれやですぐにSQLスレッドが止まってたりしますので)設定ファイルさえ作っておけば、コマンド一発でレプリケーションの修復ができます。

これがTransactdを使うきっかけになってくれれば嬉しいです。コメントも大歓迎! です。