MySQLデータベースの操作とメンテナンスのデータ復旧方法

これまでの 3 つの記事では、論理バックアップと物理バックアップを含む、MySQL データベースの一般的なバックアップ方法を紹介しました。この記事では、MySQL データベースのデータ復旧に関連する内容をまとめます。これらのデータ復旧ソリューションは、以前のバックアップコンテンツで紹介されました。ここでは、復旧ソリューションをまとめ、データベースのバイナリログと組み合わせたデータ復旧を実演します。

1. 復旧計画

1. データ量がそれほど大きくない場合は、mysql client コマンドまたは source コマンドを使用して、mysqldump コマンドでバックアップしたデータを復元できます。
2. Xtrabackup を使用してデータベースの物理バックアップとリカバリを完了します。その間、データベースサービスを再起動する必要があります。
3. LVM スナップショットボリュームを使用してデータベースの物理バックアップとリカバリを完了します。その間、データベースサービスを再起動する必要があります。

2. ポイントインタイムリカバリにmysqlbinlogを使用する

1. はじめに

mysqlbinlog はバイナリログからステートメントを読み取るツールで、インストール後に mysql に付属します。

2. バイナリログリカバリの原理

mysqldump を使用してデータベースをバックアップすると、生成されたバックアップファイルには、データベース DML 操作の時点とバックアップ時のバイナリログ位置情報が含まれます。単一データベースの場合は、特定の時点から開始してポイントインタイムリカバリを実行できます。マスタースレーブアーキテクチャの場合は、バックアップ中に --master-data=2 および --single-transaction に従って、時点または位置ポイントに基づいてリカバリを完了できます。

3. バイナリログリカバリの例

（１）単一データベースのリカバリ例

データベースを作成し、テストデータを挿入する

mysql> SHOW CREATE DATABASE test_db;
mysql> テーブル `student` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `age` tinyint(4) デフォルト NULL,
 主キー (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 デフォルト CHARSET=utf8;
mysql> INSERT INTO 学生 (名前、年齢) VALUES('Jack',23),('Tomcat',24),('XiaoHong',22),('ZhangFei',29);

mysqldumpを使用してフルバックアップを実行し、バックアップ時にログをロールし、バイナリログファイル名とログの場所を記憶します。

[root@WB-BLOG ~]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --single-transaction --triggers --routines --flush-logs --events > /tmp/test_db.sql
[root@WB-BLOG ~]# mysql -e "バイナリログを表示" > bin_pos_`date +%F`.out

この時点で、バイナリログファイル名とログポイントの場所を次のように表示します。

mysql> バイナリログを表示します。
+------------------+-----------+
| ログ名 | ファイルサイズ |
+------------------+-----------+
|mysql-bin.000001 | 1497 |
|mysql-bin.000002 | 397 |
+------------------+-----------+
セット内の 2 行 (0.00 秒)

しばらく使用した後、誤って次のステートメントを実行し、データベース内のすべてのデータを変更しました。

mysql> UPDATE STUDENT SET name = 'admin';

しばらくして、おそらく数分か数時間後、誰かがウェブサイトのログインに問題があると報告しました。確認したところ、多くのデータが誤って変更されたことがわかりました。この期間中、次の新しいレコードなどの書き込み操作がまだありました。

mysql> 学生にINSERT INTO(名前、年齢) VALUES('Hbase',23),('BlackHole',30);

このとき、データを復元する必要があります。まず、データが書き込まれないように、テーブルをロックし、書き込みサービスを一時停止し、ユーザーにシステムメンテナンスを通知してから、次の操作を実行します。

#データベースにログインし、テーブルをロックします。この時点では、テーブルは読み取りのみ可能で、書き込みはできません。mysql> USE test_db;
mysql> LOCK TABLE 学生の読み取り;
#次に、セッション ウィンドウを再度開きます (再度開くことに注意してください)。そうしないと、セッションが終了した後にロックが解除されます。次に、既存のデータとバイナリ ログ ファイルを圧縮してバックアップします [root@WB-BLOG mysql_logs]# tar zcvf mysql_data.tar.gz /mysql_data/*
[root@WB-BLOG mysql_logs]# tar zcvf mysql_bin.tar.gz /mysql_logs/*
#最新のフルバックアップデータをインポートします [root@WB-BLOG ~]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/test_db.sql 

# フルバックアップ中のバイナリログファイルとログポイントを表示します [root@WB-BLOG ~]# cat bin_pos_2018-06-24.out 
  ログ名 ファイルサイズ
  mysql-bin.000001 1497
  mysql-bin.000002 397
#ポイント 861 以降のバイナリ ログ ファイルを SQL ファイルに変換します [root@WB-BLOG bin]# ./mysqlbinlog /mysql_logs/mysql-bin.000002 --start-position=397 > /tmp/tmp.sql
# vim エディタを使用してこの sql ファイルを編集し、無条件 UPDATE ステートメントを見つけて削除し、UPDATE ステートメントを削除した後の sql スクリプトの内容をデータベース [root@WB-BLOG bin] にインポートします。# vim /tmp/tmp.sql
  `test_db`/*!*/ を使用します。
  タイムスタンプを 1522088753/*!*/ に設定します。
  update student set name = 'admin' #この文を削除 [root@WB-BLOG bin]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql
#データベースにログインして、データが復元されたかどうかを確認します。誤って変更されたデータが復元されたかどうかを確認し、テーブルのロックを解除してデータを再度準備することができます。mysql> UNLOCK TABLES;

（２）マスタースレーブアーキテクチャのデータ復旧例

環境

メインデータベース: 192.168.199.10 (node01)
ライブラリから: 192.168.199.11 (node02)

まず、スレーブデータベースの SQL スレッドを停止し、スレーブデータベース上のすべてのデータをバックアップして、バックアップファイルに「SHOW SLAVE STATUS」情報を入力します。「SHOW SLAVE STATUS」の出力情報には、マスターデータベースへの現在のアプリケーションの情報が記録されます。

#スレーブ データベースにログインし、SQL スレッドをシャットダウンします。mysql> STOP SLAVE SQL_THREAD;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
#次に、現在スレーブライブラリに適用されているマスターライブラリのバイナリログファイル情報を記録します [root@node02 mysql_data]# mysql -e "SHOW SLAVE STATUS \G" > slave_`date +%F`.info
[root@node02 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction > /tmp/mysql_test_db_`date +%F`.sql

スレーブでバックアップが完了したら、スレーブの SQL スレッドを再起動します。

mysql> スレーブ SQL_THREAD を開始します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

SQL スレッドが開始されると、バックアップ期間中のマスターデータベース上の DML 操作がスレーブデータベースに再同期されます。マスターデータベースでエラーが発生し、条件を追加せずに学生テーブルのすべてのデータを更新すると、テーブル内のすべてのデータが変更されます。このとき、同期操作により、スレーブデータベースも変更されます。

# メイン データベースにログインし、データベースの外部ユーザーを一時的にサービスを提供しないように変更してから、ログをロールします。 mysql> UPDATE mysql.user SET Host = '127.0.0.1' WHERE User='tomcat';
クエリは正常、1 行が影響を受けました (0.00 秒)
#権限テーブルを更新しますmysql> FLUSH PRIVILEGES;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
#ローリング logmysql> FLUSH LOGS;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
#スレーブデータベースのバックアップデータとバックアップ時のスレーブデータベースのスレーブ情報をマスターデータベース [root@node02 mysql_data] に転送します# scp /tmp/mysql_test_db_2018-06-24.sql 192.168.199.10:/root/
[root@node02 mysql_data]# scp スレーブ_2018-06-24.info node01:/root/

マスターライブラリのデータディレクトリとバイナリログファイルディレクトリをバックアップします

[root@node01 mysql_logs]# tar zcvf mysql_master_data.tar.gz /mysql_data/*
[root@node01 mysql_logs]# tar zcvf mysql_logs.tar.gz /mysql_logs/*

データベースの最新のバックアップからデータをインポートする

[root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /root/mysql_test_db_2018-03-26.sql 
#注: 上記の操作ではメイン データベースのテーブルをロックできません。そうしないと、完全バックアップ データをインポートできません。

バックアップ時にスレーブデータベースから適用されたマスターデータベースのバイナリログファイル名と場所を表示します。

[root@node01 mysql_logs]# cat /root/slave_2018-03-26.info
  Master_Log_File: master-bin.000002 #バックアップ中に適用されるマスターバイナリログファイルの名前 Read_Master_Log_Pos: 395 #バックアップ中に適用されるマスターバイナリログファイルの場所

このログファイルとログポイントから開始して、ログポイント 395 以降のログファイルを SQL スクリプトに変換します。バイナリログファイルが複数ある場合は、次に示すように、それらを同時に SQL スクリプトに変換できます。

[root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.000002 --start-position=395 > /tmp/tmp.sql
#master-bin.000003、master-bin.000004、master-bin.000005 を /tmp.sql ファイルにマージします [root@node01 mysql_logs]# mysqlbinlog /mysql_logs/master-bin.00000{3,4,5} --start-position=395 > /tmp/tmp.sql

間違った更新ステートメントを見つけて削除し、増分SQLスクリプトをデータベースにインポートします。

[root@node01 mysql_logs]# vim /tmp/tmp.sql
  `test_db`/*!*/ を使用します。
  学生セット名を 'admin' に更新します #この文を削除します [root@node01 mysql_logs]# mysql -uroot -proot -h127.0.0.1 -P3306 < /tmp/tmp.sql

データベースにログインして、データが正常かどうか、誤って変更されたデータが復元されているかどうかを確認します。復元されている場合は、マスターデータベースのデータをバックアップし、スレーブデータベースに転送して、スレーブデータベースのリカバリを完了します。

[root@node01 mysql_data]# mysqldump -uroot -proot -h127.0.0.1 -P3306 --databases test_db --routines --triggers --single-transaction --master-date=1 > /tmp/master_test_db_`date +%F`.sql
[root@node01 mysql_data]# scp /tmp/master_test_db_2018-06-24.sql node01:/root/
#スレーブ データベースが読み取り専用に設定されている場合、最初に読み取り専用制限を削除する必要があります。mysql> SET GLOBAL read_only = OFF;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
#データベース [root@node02 mysql_logs] からデータをインポートします。# mysql -uroot -proot -h127.0.0.1 -P3306 < /root/master_test_db_2018-06-24.sql
# 読み取り専用スレーブを有効にする mysql> SET GLOBAL read_only = ON;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

マスターデータベースにバックアップするときに --master-date=1 パラメータが追加されたため、データベースからインポートした後にマスター変更操作を再実行する必要はありません。

スレーブデータベースにログインし、SHOW SLAVE STATUS 情報が正常かどうかを確認します。正常であれば、マスターデータベースにログインし、再度認証テーブルを変更してから、外部にサービスを提供します。

mysql> UPDATE mysql.user で Host = '192.168.0.%' を設定し、 User = 'tomcat' とします。
mysql> 権限をフラッシュします。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

実行が完了すると、マスタースレーブデータが復元されます。

ここまでで、データ復旧の紹介は完了です。上記では、フルバックアップとバイナリログを使用した、シングルインスタンスデータベースとマスタースレーブデータベースのデータ復旧プロセスを紹介しました。ご質問がある場合は、コメントしてご指摘ください。皆様も123WORDPRESS.COMを応援して頂ければ幸いです。

以下もご興味があるかもしれません: