第68回の「MySQL道普請便り」では、「MySQLにおけるデータの比較」ということで、MySQLの2つのサーバーにおけるデータの比較方法を紹介しました。データを比較して差分がある場合には、必要があればデータの修正を行う必要があります。
今回は、そんな時にどのようにしてデータの差分を修正していくのか、いくつかの方法を紹介したいと思います。
なお、使用するMySQLは5.7.22とします。
mysqldbcompareからのSQLによる修正
第68回の「mysqldbcompareによるテーブルの比較」という項目にて、2つのデータベースの比較ツールとしてMySQL Utilitiesのmysqldbcompareを紹介しました。
もし、更新がなかったり、一時的に止めても問題がない場合は、mysqldbcompareの--difftype=sql
のオプションを利用して修正に必要なSQLを取得することができます。使い方に関しては第68回 MySQLにおけるデータの比較の「mysqldbcompareによるテーブルの比較」の項目をご覧ください。
pt-table-sync
Percona Toolkitの中にpt-table-syncというツールがあります。こちらのツールはpt-table-checksumと同様にステートメントベースのレプリケーションをを前提としたツールで、マスターを更新するデータを流し、レプリケーションを通してスレーブにも反映させてデータの差分をなくというツールになります。
pt-table-syncの使い方はpt-table-sync [option] [dsn]
となっています。dsnはoption=valueをカンマ区切りで入力していきます。
今回は、第68回でpt-table-checksumにて実際に差分を出したデータを使ってpt-table-syncを利用します。
今、pt-table-checksumで得た差分情報はmysql.pt_tcsに存在しています。この差分情報を使ってデータを修正するには--replicate
オプションで差分テーブルを指定して利用します。
--sync-to-master
を追記してDSNを記述すると、そのデータベースをスレーブとして扱い、マスターのデータを同期させます。
まずは--dry-run
をつけて実行してみます。
差分対象のデータベースがd1.t2とd1.t3であることがわかりました。
実際に差分の修正を実行する時は--dry-run
を--execute
に変更して実行します。
データを修正後、再度マスターに対してpt-table-checksumを実行してみます。
前回の記事にあったデータの差分がなくなりました。
pt-table-syncには他にもさまざまなオプションがあります。詳細はPercona-Toolkitのpt-table-syncのドキュメントをご確認ください。
リストアによる修正
もし、データに差分が出た原因や時間がわかっている場合で、かつMySQLのバイナリログが残っている場合、コールドバックアップを利用してレプリケーションを再作成することでも差分をなくすことが可能かもしれません。その場合はレプリケーション情報が残っていれば、MySQLのdatadirをバックアップのものに置き換えて起動すると、以前バックアップを取得したところからレプリケーションを再開し直すため、通信障害等、MySQL起因で発生していない場合には差分がなくなるかもしれません。
また、マスター以外のデータに全て差分があるような場合は、mysqldumpコマンドを利用してマスターから--master-data
オプションを利用してダンプを取得し直して再構築するやり方や、Percona XtraBackupを使うのも良いかもしれません。Percona XtraBackupについては第59回 Percona XtraBackupを使ってみようをご覧ください。
データの差分を修正する前に確認しておきたいこと
実際にデータの差分を見つけた場合は、差分を修正する前に以下のことに気にする必要があります。
- なぜ差分が発生したのか原因を突き止める
- 原因をつきとめておかないと、データの修正を行っても再度差分が発生してしまうおそれがあります。マスター-スレーブ間の設定の違いなのか、Slaveに直接クエリをながされてしまったのか等原因を突き止めて、修正前に改修しておく必要があります。
- 差分のあるデータベースが独自のDDLや権限を持っているか事前に確認する
- マスターのデータベースと比較してDDLが同じなのかどうか確認しておく必要があります。もしリストアなどでマスターのデータを使ってデータを修正した場合に、そのデータベースに存在していたVIEWやINDEXが消えてしまう可能性があるためです。
- マスター、スレーブの構成を確認する
- レプリケーションの構成を把握して、どのデータベースに差分を修正するクエリを流すべきなのか把握する必要があります。マルチソースレプリケーション構成が存在するのか、マスターからクエリを流して他データベースにどのような影響があるのか等を把握して実行する必要があります。
- データの修正前はバックアップを取得する
- データの修正前にはバックアップを取得して、不測の事態に備えておきましょう。
まとめ
今回はデータの差分を修正する方法を3つ紹介しました。データの差分が発生しないような環境作りが大事ですが、もしも発生した時はどのような方法で修正すべきか検討し、データの修正を行ってください。