MySQL で置換操作を使用したときにデータ損失が発生する問題の解決策

序文

同社の開発者は、データの更新時に replace into ステートメントを使用していました。不適切な使用により、大量のデータが失われました。この記事では、データ損失がどのように発生したかを分析します。

1. 問題の説明

同社の開発者は、データの更新時に replace into ステートメントを使用していました。不適切な使用により、大量のデータが失われました。以下は、データ損失が発生した原因の分析です。

2. 問題分析

a. REPLACE原則

REPLACE INTO 原則の公式な説明は次のとおりです。

REPLACE は INSERT とまったく同じように動作しますが、テーブル内の古い行の値が PRIMARY KEY または UNIQUE インデックスの新しい行の値と同じである場合、新しい行が挿入される前に古い行が削除される点が異なります。

新しく挿入された行の主キーまたは一意キーがテーブル内に既に存在する場合、元のレコードは削除され、新しい行が挿入されます。テーブル内に存在しない場合は、直接挿入されます。

アドレス: https://dev.mysql.com/doc/refman/5.6/en/replace.html

b. 問題の症状

欠損データのテーブル構造は次のとおりです。

テーブル `active_items` を作成します (

実行される置換ステートメントは次のとおりです (複数)。

active_items(ad_id,score) VALUES('XXXXXXX', 1800) に置き換えます。

binlog をクエリして実行記録を検索すると、次のような結果が表示されます。

### `items`.`active_items` を更新します
### @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */

操作対象のad_idは既に存在するため、一旦削除してから挿入します。指定したad_idとスコア以外はデフォルト値に変更され、元のデータが失われていることがわかります（ログでは更新に変換されていますが）

c. 比較テスト

次に、以下のテストを実施しました。

• MySQL 置換操作中のデータ損失
• 左側では REPLACE ステートメントを使用し、右側では DELETE + INSERT ステートメントを使用します。最終結果はまったく同じです。
• 元の主キーIDが1の行は削除され、新しく挿入された行の主キーIDは4に更新され、フィールドcには指定された内容なしでデフォルト値が挿入されます。
• REPLACE を使用してデータ行が更新され、MySQL は影響を受ける行の数が 2 行であると表示しました。

要約すると、行が削除され、行が挿入されることを意味します。

3. データ復旧

データの損失またはデータ エラーが発生した場合、回復する方法はいくつかあります。

1. ビジネス側は回復のための独自のスクリプトを作成する
2. MySQL binlog を使用して不正な SQL を見つけ出し、データ回復用の逆 SQL を生成します (SQL データ量が小さい場合に適しています)
3. 履歴バックアップファイルと増分バイナリログを使用して、誤った操作が発生する前の時点にデータの状態を復元します。

4. 問題の拡張

上記の分析から、REPLACE は古い行を削除して新しい行を挿入しますが、binlog はそれを更新の形式で記録するため、別の問題が発生することがわかります。

スレーブデータベースの自己成長値はマスターデータベースの自己成長値よりも小さい

1. テスト

a. マスタースレーブ一貫性:

メインライブラリ:

mysql> テーブル tG の作成を表示

ライブラリから:

mysql> テーブル tG の作成を表示

b. メインライブラリの置換:

メインライブラリ:

mysql> t (a,b)values(1,7) に置き換えます。

ライブラリから:

mysql> テーブル tG の作成を表示

マスターテーブルとスレーブテーブルの AUTO_INCREMENT 値が異なっていることに注意してください。

c. スレーブからマスターへのアップグレードをシミュレートし、スレーブ データベースで INSERT を実行します。

mysql> t に (a,b,c) 値 (4,4,4) を挿入します。

データベースから挿入する場合、エラーが報告され、主キーが重複します。エラーが報告された後、AUTO_INCREMENT が +1 されるため、再度実行すれば挿入が成功します。

2. 結論

この問題は通常の状況では影響はありませんが、

マスター データベースが定期的に大量の REPLACE ステートメントを使用し、スレーブ データベースの AUTO_INCREMENT 値がマスター データベースより大幅に遅れている場合、マスターとスレーブの切り替えが発生すると、データが再度挿入されるときに新しいマスター データベースに多数の主キー重複エラーが発生し、データの挿入が失敗します。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。