MySQL道普請便り

第71回データの差分を修正しよう

第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をつけて実行してみます。

$ pt-table-sync --dry-run --replicate=mysql.pt_tcs --sync-to-master --databases d1 h=127.0.0.1,u=root,P=3307,p=*****
# NOTE: --dry-run does not show if data needs to be synced because it
#       does not access, compare or sync data.  --dry-run only shows
#       the work that would be done.
# Syncing via replication P=3307,h=127.0.0.1,p=...,u=root in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 Chunk     03:19:49 03:19:49 0    d1.t2
#      0       0      0      0 Chunk     03:19:49 03:19:49 0    d1.t3

差分対象のデータベースがd1.t2とd1.t3であることがわかりました。

実際に差分の修正を実行する時は--dry-run--executeに変更して実行します。

$ pt-table-sync --execute --replicate=mysql.pt_tcs --sync-to-master --databases d1 h=127.0.0.1,u=root,P=3307,p=*****

データを修正後、再度マスターに対してpt-table-checksumを実行してみます。

$ pt-table-checksum  --host 127.0.0.1 --port 3306 --user root --password ***** --recursion-method="dsn=D=mysql,t=dsns" --databases d1
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
05-08T03:26:10      0      0        3       1       0   0.031 d1.t1
05-08T03:26:10      0      0      100       1       0   0.012 d1.t2
05-08T03:26:10      0      0        0       1       0   0.012 d1.t3

前回の記事にあったデータの差分がなくなりました。

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つ紹介しました。データの差分が発生しないような環境作りが大事ですが、もしも発生した時はどのような方法で修正すべきか検討し、データの修正を行ってください。

おすすめ記事

記事・ニュース一覧