MySQLデータ復旧のさまざまな方法の概要

MySQLデータ復旧のさまざまな方法の概要

1. はじめに

データ復旧の前提は、適切なバックアップを作成し、行形式の binlog を有効にすることです。バックアップ ファイルがない場合、ライブラリ テーブルを削除すると、実際に削除されます。lsof にレコードが残っている場合は、一部のファイルを復元できます。しかし、データベースがこのテーブル ファイルを開かない場合は、逃げることしかできません。 binlog が有効になっていない場合、データの復元後にバックアップ時点のすべてのデータが失われます。 binlog 形式が行でない場合、データに対して誤った操作を行った後にフラッシュバック操作を実行する方法はなく、バックアップとリカバリのプロセスを実行することしかできません。

2. 直接回復

直接リカバリは、バックアップ ファイルを使用して完全なリカバリを実行することであり、これが最も一般的なシナリオです。

2.1 mysqldumpバックアップの完全リカバリ

mysqldump ファイルを使用したデータの復元は非常に簡単です。解凍して次のコマンドを実行するだけです。

gzip -d backup.sql.gz | mysql -u<ユーザー> -h<ホスト> -P<ポート> -p

2.2 xtrabackup バックアップ 完全リカバリ

回復プロセス:

# ステップ 1: 解凍 (圧縮されていない場合は、このステップは無視できます)
innobackupex --decompress <バックアップファイルディレクトリ>

# ステップ 2: ログを適用します。nobackupex --apply-log <バックアップ ファイル ディレクトリ>

 # ステップ 3: バックアップ ファイルをデータ ディレクトリにコピーします innobackupex --datadir=<MySQL データ ディレクトリ> --copy-back <バックアップ ファイル ディレクトリ>

2.3 特定の時点に基づく回復

ポイントインタイムリカバリは、バイナリログログに依存します。バイナリログでバックアップポイントからリカバリポイントまでのすべてのログを検索し、適用する必要があります。テストしてみましょう。

新しいテスト テーブルを作成します。

chengqm-3306>>show テーブル mytest.mytest \G を作成します。
************************** 1. 行 ****************************
       テーブル: mytest
テーブルの作成: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ctime` 日時 デフォルト NULL、
  主キー (`id`)
) エンジン=InnoDB デフォルト文字セット=utf8

1 秒あたり 1 つのデータを挿入します。

[mysql@mysql-test ~]$ が true の場合、mysql -S /tmp/mysql.sock -e 'insert in を実行します

バックアップ:

[mysql@mysql-test ~]$ mysqldump --opt --single-transaction --master-data=2 --defa

バックアップ時のログの場所を確認します。

[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'MASTERをMASTER_LOG_FILEに変更'
-- MASTER を MASTER_LOG_FILE='mysql-bin.000032'、MASTER_LOG_POS=39654 に変更します。

2019-08-09 11:01:54 の時点に復元したいと仮定すると、binlog で 39654 から 019-08-09 11:01:54 までのログを検索します。

[mysql@mysql-test ~]$ mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql
[mysql@mysql-test-83 ~]$ tail -n 20 backup_inc.sql
......
### `mytest`.`mytest` に挿入します
### セット
### @1=161 /* INT meta=0 nullable=0 is_null=0 */
### @2='2019-08-09 11:01:53' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
......

現在のデータエントリ数:

-- 2019-08-09 11:01:54 より前のデータの数chengqm-3306>>select count(*) from mytest.mytest where ctime < '2019-08-09 11:01:54';
+----------+
| カウント(*) |
+----------+
| 161 |
+----------+
セット内の 1 行 (0.00 秒)

すべてのデータ項目

chengqm-3306>>mytest.mytestからcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 180 |
+----------+
セット内の 1 行 (0.00 秒)

次に復元を実行します。

# 完全リカバリ [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql

 # 増分ログを適用 [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc.sql

データを確認してください:

chengqm-3306>>mytest.mytestからcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 161 |
+----------+
セット内の 1 行 (0.00 秒)

chengqm-3306>>mytest.mytestから*を選択し、id desc limit 5で順序付けします。
+-----+---------------------+
| id | ctime |
+-----+---------------------+
| 161 | 2019-08-09 11:01:53 |
| 160 | 2019-08-09 11:01:52 |
| 159 | 2019-08-09 11:01:51 |
| 158 | 2019-08-09 11:01:50 |
| 157 | 2019-08-09 11:01:49 |
+-----+---------------------+
セット内の行数は 5 です (0.00 秒)

2019-08-09 11:01:54時点に復元されました。

3. テーブルを復元する

3.1 mysqldumpバックアップからテーブルを復元する

復元するテーブルが mytest.mytest であると仮定します。

# データベースからすべてのデータを抽出します sed -n '/^-- 現在のデータベース: `mytest`/,/^-- 現在のデータベース:/p' backup.sql > backup_mytest.sql

# ライブラリのバックアップファイルからテーブル作成ステートメントを抽出します sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `mytest`/!d;q' backup_mytest.sql > mytest_table_create.sql

# ライブラリバックアップファイルから挿入データステートメントを抽出します。grep -i 'INSERT INTO `mytest`' backup_mytest.sql > mytest_table_insert.sql

# テーブル構造を mytest ライブラリに復元します。mysql -u<user> -p mytest < mytest_table_create.sql

# テーブルデータを mytest.mytest テーブルに復元しますmysql -u<user> -p mytest < mytest_table_insert.sql

3.2 xtrabackupバックアップからテーブルを復元する

./backup_xtra_full ディレクトリには、解凍後にログが適用されたバックアップ ファイルが含まれていると想定します。

3.2.1 MyISAM テーブル テーブル mytest.t_myisam がバックアップ ファイルから復元されると仮定します。バックアップファイルからt_myisam.frm、t_myisam.MYD、t_myisam.MYIの3つのファイルを探し、対応するデータディレクトリにコピーして認証します。

MySQL を入力します。チェックリストの状態:

chengqm-3306>>テーブルを表示;
+------------------+
| テーブル_in_mytest |
+------------------+
| マイテスト |
| t_myisam |
+------------------+
セット内の 2 行 (0.00 秒)

chengqm-3306>>テーブルt_myisamをチェックします。
+-----------------+-------+----------+-----------+
| テーブル | Op | メッセージ タイプ | メッセージ テキスト |
+-----------------+-------+----------+-----------+
| mytest.t_myisam | チェック | ステータス | OK |
+-----------------+-------+----------+-----------+
セット内の 1 行 (0.00 秒)

3.2.2 Innodb テーブル テーブル mytest.t_innodb がバックアップ ファイルから復元されると仮定します。復元の前提として、innodb_file_per_table = on が設定されている必要があります。

  • 新しいインスタンスを開始します。
  • 元のテーブルとまったく同じテーブルをインスタンス上に作成します。
  • テーブルスペースを削除するには、alter table t_innodb discard tablespace; を実行します。この操作により、t_innodb.ibd が削除されます。
  • バックアップ ファイルから t_innodb.ibd ファイルを見つけて、対応するデータ ディレクトリにコピーし、承認します。
  • テーブルスペースをロードするには、alter table t_innodb IMPORT tablespace; を実行します。
  • テーブルをチェックするには、flush table t_innodb;check table t_innodb; を実行します。
  • mysqldump を使用してデータをエクスポートし、復元するデータベースにインポートします。

知らせ:

新しいインスタンスに復元してからダンプするのは、リスクを回避するためです。テストの場合は、元のデータベースで手順 2 ~ 6 を直接実行できます。
8.0 より前のバージョンでのみ有効です。

4. 不正なSQL操作をスキップする

不正な SQL のスキップは、通常、drop table\database など、フラッシュバックできない操作を実行するために使用されます。

4.1 バックアップファイルを使用してスキップを復元する

4.1.1 GTIDを無効にする

バックアップ ファイルを使用して復元する手順は、追加の binlog 検索操作があることを除いて、ポイントインタイム リカバリの手順と似ています。たとえば、テーブル a と b の 2 つのテーブルを作成し、1 分ごとにデータを挿入し、完全バックアップを作成してから、テーブル b を削除しました。ここで、この SQL をスキップします。

テーブル b を削除した後のデータベースの状態:

chgnqm-3306>>テーブルを表示;
+------------------+
| テーブル_in_mytest |
+------------------+
| ア |
+------------------+
セット内の 1 行 (0.00 秒)

バックアップ時のログの場所を確認する

[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'MASTERをMASTER_LOG_FILEに変更'
-- MASTER を MASTER_LOG_FILE='mysql-bin.000034'、MASTER_LOG_POS=38414 に変更します。

ドロップテーブルステートメントが実行された位置を見つける

[mysql@mysql-test mysql_test]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'テーブル `b` を削除します';
# 120629 で
#190818 19:48:30 サーバー ID 83 end_log_pos 120747 CRC32 0x6dd6ab2a クエリ thread_id=29488 exec_time=0 error_code=0
タイムスタンプを 1566128910/*!*/ に設定します。
DROP TABLE `b` /* サーバーによって生成されました */

結果から、ドロップ ステートメントの開始位置は 120629 で、終了位置は 120747 であることがわかります。

このステートメントをスキップする他のレコードをbinglogから抽出します

# 最初の行の開始位置はバックアップファイルの pos 位置であり、停止位置はドロップステートメントの開始位置です。 mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql

# 2行目の開始位置はドロップステートメントの終了位置です。mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.00003

バックアップファイルの復元

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < バックアップ.sql

完全回復後の状態:

chgnqm-3306>>テーブルを表示;
+------------------+
| テーブル_in_mytest |
+------------------+
| ア |
| バ |
+------------------+
セット内の 2 行 (0.00 秒)

chgnqm-3306>>count(*) を a から選択します。
+----------+
| カウント(*) |
+----------+
| 71 |
+----------+
セット内の 1 行 (0.00 秒)

増分データを復元する

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_1.sql
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_2.sql

復元後、drop ステートメントがスキップされたことがわかります。

chgnqm-3306>>テーブルを表示;
+------------------+
| テーブル_in_mytest |
+------------------+
| ア |
| バ |
+------------------+
セット内の 2 行 (0.00 秒)

chgnqm-3306>>count(*) を a から選択します。
+----------+
| カウント(*) |
+----------+
| 274 |
+----------+
セット内の 1 行 (0.00 秒)

4.1.2 GTIDを有効にする
GTID を使用すると、間違った SQL を直接スキップできます。

  • バックアップ時のログの場所を確認します。
  • drop table ステートメントを実行した GTID 値を見つけます。
  • バックアップをエクスポートすると、ログの場所が最新の binglog ログに移動されます。
  • バックアップファイルを復元します。
  • この GTID をスキップします。
SET SESSION GTID_NEXT='対応するGTID値';
開始; コミット;
セッションGTID_NEXTをAUTOMATICに設定します。

手順 3 で取得した増分 binlog を適用します。

4.2 遅延ライブラリを使用したスキップ

4.2.1 GTIDを無効にする

遅延ライブラリ回復を使用する際の主要な操作は、スレーブを起動することです。テスト環境に 2 つの MySQL ノードをセットアップしました。ノード 2 は 600 秒間遅延されました。2 つのテーブル (a と b) が作成されました。ビジネス データの挿入をシミュレートするために、1 秒ごとに 1 つのデータが挿入されました。

localhost:3306 -> localhost:3307 (遅延 600)

現在のノード 2 のステータス:

chengqm-3307>>スレーブステータスを表示 \G;
...
                  マスターポート: 3306
                接続再試行: 60
              マスターログファイル:mysql-bin.000039
          読み取りマスターログ位置: 15524
               リレーログファイル:mysql-relay-bin.000002
                リレーログ位置: 22845
        リレーマスターログファイル: mysql-bin.000038
             スレーブIO実行中: はい
            スレーブSQL実行中: はい
...
        マスターより遅れている秒数: 600
...

現在のノード 2 テーブル:

chengqm-3307>>テーブルを表示;
+------------------+
| テーブル_in_mytest |
+------------------+
| ア |
| バ |
+------------------+

ノード1のテーブルbを削除します。

chengqm-3306>>テーブルbを削除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

chengqm-3306>>テーブルを表示;
+------------------+
| テーブル_in_mytest |
+------------------+
| ア |
+------------------+
セット内の 1 行 (0.00 秒)

次のステップは、SQL ステートメントをスキップすることです。

ライブラリ停止同期を遅延する

奴隷を停止します。

ドロップテーブルステートメントが実行される前のステートメントのPOS位置を見つける

[mysql@mysql-test ~]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000039 | grep -i -B 10 'テーブル `b` を削除します';
...
# 35134 で
#190819 11:40:25 サーバー ID 83 end_log_pos 35199 CRC32 0x02771167 Anonymous_GTID last_committed=132 シーケンス番号=133 rbr_only=no
@@SESSION.GTID_NEXT を 'ANONYMOUS'/*!*/ に設定します。
# 35199 で
#190819 11:40:25 サーバー ID 83 end_log_pos 35317 CRC32 0x50a018aa クエリ thread_id=37155 exec_time=0 error_code=0
`mytest`/*!*/ を使用します。
タイムスタンプを 1566186025/*!*/ に設定します。
DROP TABLE `b` /* サーバーによって生成されました */

結果から、ドロップ ステートメントの前のステートメントの開始位置は 35134 であることがわかります。そのため、35134 に同期します (間違った選択をしないでください)。

スキップするSQLへのデータベース同期を遅延する前

マスターをmaster_delay=0に変更します。
master_log_file='mysql-bin.000039'、master_log_pos=35134 になるまでスレーブを起動します。

ステータスをチェックして、対応するノードに同期されていることを確認します。

chengqm-3307>>スレーブステータスを表示 \G;
...
                  マスターポート: 3306
                接続再試行: 60
              マスターログファイル:mysql-bin.000039
          読み取りマスターログ位置: 65792
...
             スレーブIO実行中: はい
            スレーブSQL実行中: いいえ
          実行マスターログポジション: 35134
...
               Until_Log_File: mysql-bin.000039
                ログ位置まで: 35134

SQL文をスキップした後に同期を開始する

グローバル sql_slave_skip_counter を 1 に設定します。
スレーブを起動します。

同期ステータスを確認し、テーブル b を削除するステートメントがスキップされていることを確認します。

chengqm-3307>>スレーブステータスを表示 \G;
...
             スレーブIO実行中: はい
            スレーブSQL実行中: はい
...
セット内の 1 行 (0.00 秒)

chengqm-3307>>テーブルを表示;
+------------------+
| テーブル_in_mytest |
+------------------+
| ア |
| バ |
+------------------+
セット内の 2 行 (0.00 秒)

4.2.2 GTIDを有効にする
GTID を使用してスキップする手順ははるかに簡単です。スキップする SQL と同じ GTID を持つトランザクションを実行するだけです。

  • 同期を停止します。
  • drop table ステートメントを実行した GTID を見つけます。
  • この GTID のトランザクションを実行します。
SET SESSION GTID_NEXT='対応するGTID値';
開始; コミット;
セッションGTID_NEXTをAUTOMATICに設定します。
  • 同期を続行します。

5. フラッシュバック。

フラッシュバック操作は逆の操作です。たとえば、delete from a where id=1 が実行されると、フラッシュバックは対応する挿入操作 insert into a (id,...) values(1,...) を実行します。これは、誤ったデータ操作を修正するために使用されます。これは DML ステートメントにのみ有効で、binlog 形式を ROW に設定する必要があります。この章では、比較的便利な 2 つのオープン ソース ツールを紹介します。

5.1 バイナリログ2SQL

binlog2sql は、Dianping.com が binlog を解析するために開発したオープン ソース ツールです。フラッシュバック ステートメントを生成するために使用できます。プロジェクト アドレスは binlog2sql です。

5.1.1 インストール

https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip を実行します。
binlog2sql.zip を解凍します。
cd binlog2sql-master/

# 依存関係をインストールします pip install -r requirements.txt

5.1.2 ロールバックSQLの生成

python binlog2sql/binlog2sql.py --flashback \
-h<ホスト> -P<ポート> -u<ユーザー> -p'<パスワード>' -d<データベース名> -t<テーブル名>\
--start-file='<binlog_file>' \
--start-datetime='<開始時刻>' \
--stop-datetime='<停止時刻>' > ./flashback.sql

python binlog2sql/binlog2sql.py --flashback \
-h<ホスト> -P<ポート> -u<ユーザー> -p'<パスワード>' -d<データベース名> -t<テーブル名> \
--start-file='<binlog_file>' \
--start-position=<開始位置> \
--stop-position=<停止位置> > ./flashback.sql

5.2 マイフラッシュ

MyFlash は、Meituan Dianping の技術エンジニアリング部門によって開発および保守されている DML 操作をロールバックするためのツールです。プロジェクト リンクは MyFlash です。

制限:

  • binlog 形式は行であり、binlog_row_image=full である必要があります。
  • 5.6 と 5.7 のみをサポートします。
  • ロールバックできるのは DML (追加、削除、変更) のみです。

5.2.1 インストール

# 依存関係 (centos)
yum install gcc* pkg-config glib2 libgnomeui-devel -y

# ファイルをダウンロードします wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip -O MyFlash.zip
MyFlash.zip を解凍します。
cd MyFlashマスター

# コンパイルしてインストールする gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
mv バイナリ /usr/local/MyFlash
ln -s /usr/local/MyFlash/flashback /usr/bin/flashback

5.2.2 生成されたロールバックステートメントを使用します。

flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<s

実行後、binlog_output_base.flashback ファイルが生成されますが、使用する前に mysqlbinlog で解析する必要があります。

mysqlbinlog -vv binlog_output_base.flashback | mysql -u<ユーザー> -p

上記は、MySQL データ復旧のさまざまな方法の詳細な内容です。MySQL データ復旧の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません:
  • mysql5.7でbinlogを使用してデータを復元する方法
  • MySQLはbinlogを通じてデータを復元する
  • MySQLはテーブルデータを復元するためにfrmファイルとibdファイルを使用します
  • MySQLはデータ復旧を実装するためにbinlogログを使用する
  • 誤って削除されたデータを復元するための mysqlbinlog コマンドを使用した mysql の実装
  • MySQLデータを復元する2つの方法
  • MySQL データベースのバックアップとリカバリの実装コード
  • MySQLはmysqldump+binlogを使用して、削除されたデータベースの原理分析を完全に復元します。
  • MySQLデータのバックアップとリカバリの実装方法の分析
  • MySQL バイナリログデータ復旧: 誤ってデータベースを削除した場合の詳細な説明

<<:  Docker+jenkins+python3環境を使用して非常に詳細なチュートリアルを構築する

>>:  選択/フォーカス時にすべてのオプションをリストする現在のより良い方法

推薦する

CSSアニメーションによるテーブルスクロールカルーセル効果の実装

前回の CSS 回転灯と同じ内容の CSS アニメーションの応用です。これは単なる別のアプリケーショ...

Win10 での MySQL 8.0.20 のインストールと設定のチュートリアル

Win10 システムでの MySQL 8.0.20 のインストールと設定の超詳細なチュートリアルMy...

MySQL ストアド プロシージャ、カーソル、トランザクションの例の詳細な説明

MySQL ストアド プロシージャ、カーソル、トランザクションの例の詳細な説明以下は私が作成した M...

未来志向の総合的なウェブデザイン:プログレッシブエンハンスメント

<br />原文: プログレッシブエンハンスメントを理解するアーロン・ガスタフソン翻訳:...

JDBCデータベースリンクと関連メソッドのカプセル化の詳細な説明

JDBCデータベースリンクと関連メソッドのカプセル化の詳細な説明MySQL データベースを使用して、...

Node.js の TCP 接続処理のコア プロセス

数日前、友人と Node.js の epoll とリクエスト処理に関する知識を交換しました。今日は、...

Windows 10 での MySQL 5.7.17 のインストールと設定方法のグラフィック チュートリアル

この記事では、最新バージョンの MySQL データベース、つまり MySQL 5.7.17 圧縮バー...

MySQL 8.0.17 のインストールと設定方法のグラフィックチュートリアル

この記事では、MySQL 8.0.17のインストールと設定方法を参考までに紹介します。具体的な内容は...

数字当てゲームを実装するための純粋なJavaScript

100 以内の自然数をランダムに選択し、プレイヤーに 10 ラウンド以内にその数を推測させる数字推...

ウェブページの画像の回転を実現するjs

この記事では、Webページの画像の回転を実現するためのjsの具体的なコードを参考までに共有します。具...

MySQL マスタースレーブスイッチチャネルの問題の解決策

VIP を設定した後、アクティブ/スタンバイの切り替え中に表示されるエラー メッセージは次のとおりで...

MySQL複合インデックスの概要

目次1. 背景2. 複合インデックスを理解する3. 左端一致原則4. フィールド順序の影響5. 単一...

MySQL でコマンドを使用して階層検索を実現する方法の詳細な説明

序文この記事は主にMySQLコマンド階層検索ヘルプの使用に関する内容を紹介します。この記事のサンプル...

Vueでパスワードの表示と非表示機能を実装するアイデアを詳しく解説

効果: アイデア:入力タイプ属性を使用して、タイプ値がテキストの場合はパスワードを表示し、タイプ値が...

Vueコンポーネント登録方法の解釈

目次概要1. グローバル登録2. 現地登録3. モジュールシステムへのローカル登録概要コンポーネント...