毎日の仕事では、常にあるため、手の震え、間違った条件を書き込み、間違ったテーブル名を書き込み、誤ってライブラリテーブルとデータのものの間違った削除によって引き起こされる本番ライブラリに接続が発生します。まあ、もしデータも回復することはできませんが、どのようなより多くのDBA。
はじめに
データ復旧の前提条件は、バックアップをしっかり取り、binlogを行の形式で開くことです。バックアップファイルがない場合、ライブラリテーブルを削除した後、本当に削除されてしまい、lsofにレコードが残っていれば、ファイルの一部を復旧できるかもしれません。しかし、データベースにテーブルファイルが開かれていなければ、逃げるしかありません。binlogが有効でない場合、データをリストアした後、バックアップ時点のデータは消えてしまいます。binlog形式が行でない場合、データを誤用した後にフラッシュバック操作を行う方法はありません。
ダイレクト・リカバリ
ダイレクト・リカバリとは、バックアップ・ファイルを使用して完全なリカバリを行うことで、最も一般的なシナリオです。
mysqldump バックアップ・フルリストア
mysqldump ファイルを使用したデータの復元は非常に簡単で、直接解凍して実行します：
gzip -d backup.sql.gz | mysql -u<user> -h<host> -P<port> -p
xtrabackup バックアップ・フルリストア
回復プロセス：
# ステップ1:
innobackupex --decompress <バックアップファイルがあるディレクトリ>
# ステップ2：ログを適用する
innobackupex --apply-log <バックアップファイルがあるディレクトリ>
# ステップ3：バックアップ・ファイルをデータ・ディレクトリにコピーする
innobackupex --datadir=<MySQLデータ・カタログ> --copy-back <バックアップファイルがあるディレクトリ>
ポイント・イン・タイム・ベースのリカバリ
ポイントインタイムベースのリカバリはビンログログログに依存するので、バックアップポイントからリカバリポイントまでのビンログからすべてのログを見つけて適用する必要があります。テストしてください。
新しいテストテーブル：
chengqm-3306>>show create table mytest.mytest \G;
*************************** 1\. row ***************************
Table: mytest
Create Table: CREATE TABLE `mytest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ctime` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1秒間に1つのデータを挿入します：
[mysql@mysql-test ~]$ while true; do mysql -S /tmp/mysql.sock -e 'insert into mytest.mytest(ctime)values(now())';date;sleep 1;done
バックアップ
[mysql@mysql-test ~]$ mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql
バックアップ時のログの場所を確認します：
[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.', 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=' ' /data/mysql_log/mysql_test/mysql-bin. > backup_inc.sql
[mysql@mysql-test-83 ~]$ tail -n 20 backup_inc.sql
......
### INSERT INTO `mytest`.`mytest`
### SET
### @1=161 /* INT meta=0 nullable=0 is_null=0 */
### @2=' ' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
......
現在のデータエントリ数：
-- のデータ・エントリの数は
chengqm-3306>>select count(*) from mytest.mytest where ctime < ' ';
+----------+
| count(*) |
+----------+
| 161 |
+----------+
1 row in set (0.00 sec)
-- すべてのデータ・エントリ
chengqm-3306>>select count(*) from mytest.mytest;
+----------+
| count(*) |
+----------+
| 180 |
+----------+
1 row in set (0.00 sec)
その後、リカバリーを実行します：
# フル・リカバリ
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql
# インクリメンタルロギングを適用する
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc.sql
データを確認してください：
chengqm-3306>>select count(*) from mytest.mytest;
+----------+
| count(*) |
+----------+
| 161 |
+----------+
1 row in set (0.00 sec)
chengqm-3306>>select * from mytest.mytest order by id desc limit 5;
+-----+---------------------+
| id | ctime |
+-----+---------------------+
| -:01:53 |
| -:01:52 |
| -:01:51 |
| -:01:50 |
| -:01:49 |
+-----+---------------------+
5 rows in set (0.00 sec)
2019-08-09 11:01:54の時点に復旧しました。
テーブルをリストアするには
mysqldumpバックアップからのテーブルのリストア
リカバリするテーブルがmytest.mytestだとします：
# ライブラリからすべてのデータを取り出す
sed -n '/^-- Current Database: `mytest`/,/^-- Current Database:/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
xtrabackup バックアップからのテーブルの復元
./backup_xtra_fullディレクトリが、解凍後にロギングが適用されたバックアップ・ファイルであると仮定します。
MyISAM
mytest.t_myisamテーブルがバックアップ・ファイルからリカバリされたと仮定して、バックアップ・ファイルからt_myisam.frm、t_myisam.MYD、t_myisam.MYIの3つのファイルを探し出し、対応するデータ・ディレクトリにコピーし、以下の手順でオーソライズします。
MySQLにアクセスし、テーブルの状況を確認してください：
chengqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| mytest |
| t_myisam |
+------------------+
2 rows in set (0.00 sec)
chengqm-3306>>check table t_myisam;
+-----------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| mytest.t_myisam | check | status | OK |
+-----------------+-------+----------+----------+
1 row in set (0.00 sec)
Innodb
テーブルmytest.t_innodbがバックアップファイルからリストアされ、リストア時にinnodb_file_per_table = onが設定されていると仮定したとします：
- 新しいインスタンスを開始します；
- バックアップファイルからt_innodb.ibdファイルを見つけ、対応するデータディレクトリにコピーし、認証します；
- 実行 alter table t_innodb IMPORT tablespace; load tablespace；
- フラッシュテーブル t_innodb; チェックテーブル t_innodb; チェックテーブルを実行します；
- フラッシュテーブルtを実行_innodb;check table t_innodb; テーブルをチェック
注目してください：
- 新しいインスタンスでリカバリし、それをダンプするのは、リスクを避けるためです。 テスト中であれば、ステップ2～6を元のリポジトリで直接行うことができます；
- 8.0より前のバージョンでのみ利用可能です。
SQL の誤使用をスキップ
スキップ誤操作SQLは一般に、ドロップ・テーブルのようなフラッシュ・バックできない操作を実行するときに使用します。
バックアップファイルを使ったスキップの復元
GTID を有効にしないでください。
バックアップファイルからのリカバリは、ビンログ検索操作が追加されることを除けば、時点からのリカバリとほとんど同じです。例えば、2つのテーブルaとbを作成し、1分ごとにデータを挿入した後、フルバックアップを行い、テーブルbを削除しました。
テーブル b を削除した後のデータベースの状態：
chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
+------------------+
1 row in set (0.00 sec)
1.バックアップ時のログの場所を確認します。
[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.', MASTER_LOG_POS=38414;
2.ドロップテーブル文が実行されたposの場所を見つけます。
[mysql@mysql-test mysql_test]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin. | grep -i -B 3 'drop table `b`';
# at :30 server id 83 end_log_pos CRCdd6ab2a Query thread_id=29488 exec_time=0 error_code=0
SET TIMESTAMP=/*!*/;
結果から、120629から始まり120747で終わることがわかります。
3.このステートメントをスキップする他のレコードをbinglogから抽出します。
# 最初のエントリの開始位置はバックアップファイルのpos位置で、停止位置はドロップ文の開始位置である。
mysqlbinlog -vv --start-position=38414 --stop-position= /data/mysql_log/mysql_test/mysql-bin. > backup_inc_1.sql
# 2行目の開始位置はドロップ文の終了位置である
mysqlbinlog -vv --start-position= /data/mysql_log/mysql_test/mysql-bin. > backup_inc_2.sql
4.バックアップファイルの復元
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql
完全復帰後の状況
chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+
2 rows in set (0.00 sec)
chgnqm-3306>>select count(*) from a;
+----------+
| count(*) |
+----------+
| 71 |
+----------+
1 row in set (0.00 sec)
5.増分データのリカバリ
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_1.sql
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_2.sql
状態を復元すると、ドロップ文がスキップされていることがわかります：
chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+
2 rows in set (0.00 sec)
chgnqm-3306>>select count(*) from a;
+----------+
| count(*) |
+----------+
| 274 |
+----------+
1 row in set (0.00 sec)
GTIDの有効化
間違ったSQLを直接スキップするにはGTIDを使用します：
1.バックアップ時のログの場所を確認します；
2. ドロップ・テーブル 文が実行されたGTID値を見つけます；
3.ログの場所をバックアップ時の最新のbinglogログにエクスポートします；
4.バックアップファイルを復元します；
5.このGTIDをスキップします；
SET SESSION GTID_NEXT='対応するGTID値';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
6.ステップ3で取得したインクリメンタルビンログログを適用します。
遅延ライブラリを使用して
GTID を有効にしないでください。
遅延ライブラリのリカバリを使用するための重要な操作は 、スレーブを開始するまでです。 テスト環境に2つのMySQLノードをセットアップし、ノード2は600秒遅延させ、2つの新しいテーブルaとbを作成し、ビジネスデータの挿入をシミュレートするために1秒間に1つのデータを挿入します。
localhost:3306 -> localhost:3307(delay 600)
現在のノード2の状態：
chengqm-3307>>show slave status \G;
...
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.
Read_Master_Log_Pos: 15524
Relay_Log_File: mysql-relay-bin.
Relay_Log_Pos: 22845
Relay_Master_Log_File: mysql-bin.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 600
...
現在のノード2テーブル：
chengqm-3307>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+
ノード1のテーブルbを削除します：
chengqm-3306>>drop table b;
Query OK, 0 rows affected (0.00 sec)
chengqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
+------------------+
1 row in set (0.00 sec)
次のステップは、このSQLをスキップすることです。
1.遅延ライブラリ停止同期
stop slave;
2.ドロップテーブル文が実行された前文のposの位置を求めます。
[mysql@mysql-test ~]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin. | grep -i -B 10 'drop table `b`';
...
# at :25 server id 83 end_log_pos 35199 CRC Anonymous_GTID last_committed=132 sequence_number=133 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at :25 server id 83 end_log_pos 35317 CRCa018aa Query thread_id=37155 exec_time=0 error_code=0
use `mytest`/*!*/;
SET TIMESTAMP=/*!*/;
DROP TABLE `b` /* generated by server */
結果からわかるように、drop文の前の文は35134から始まっているので、35134に同期しています。
3.ライブラリの同期をスキップする前のSQLに遅らせます。
change master to master_delay=0;
start slave until master_log_file='mysql-bin.',master_log_pos=35134;
ステータスを確認し、対応するノードに同期されていることを確認します：
chengqm-3307>>show slave status \G;
...
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.
Read_Master_Log_Pos: 65792
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
Exec_Master_Log_Pos: 35134
...
Until_Log_File: mysql-bin.
Until_Log_Pos: 35134
4.SQLをスキップして同期を開始します。
set global sql_slave_skip_counter=1;
start slave;
同期ステータスをチェックしてください。 テーブルbを削除するステートメントがスキップされました：
chengqm-3307>>show slave status \G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)
chengqm-3307>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a |
| b |
+------------------+
2 rows in set (0.00 sec)
GTIDの有効化
スキップしたいSQLと同じGTIDのトランザクションを実行するだけです。
1.同期を停止します；
2.ドロップテーブル文が実行されたGTIDを特定します；
3.このGTIDトランザクションを実行します；
SET SESSION GTID_NEXT='対応するGTID値';
BEGIN; COMMIT;
SET SESSION GTID_NEXT = AUTOMATIC;
4.同期の継続
フラッシュバックします。
フラッシュバック操作とは、例えば、 id = 1のaからdeleteを実行すると、フラッシュバックは対応するinsert操作をinsert into a values(1,...)に実行するという逆の操作で、データの誤用に使用され、DML文に対してのみ有効で、binlog形式をROWに設定する必要があります。この章では、データの誤操作に使用でき、DML文でのみ有効で、binlog形式をROWに設定する必要がある、2つの優れたオープンソースツールを紹介します。
binlog2sql
binlog2sqlはbinlogを解析するための人気のあるオープンソースで、フラッシュバックステートメントを生成するために使用することができます。
インストール
wget https://.////.ip -O binlog2sql.zip
unzip binlog2sql.zip
cd binlog2sql-master/
# 依存関係をインストールする
pip install -r requirements.txt
5.1.2 ロールバックSQLの生成
python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name>\
--start-file='<binlog_file>' \
--start-datetime='<start_time>' \
--stop-datetime='<stop_time>' > ./flashback.sql
python binlog2sql/binlog2sql.py --flashback \
-h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \
--start-file='<binlog_file>' \
--start-position=<start_pos> \
--stop-position=<stop_pos> > ./flashback.sql
MyFlash
MyFlashは、Meituan-Dianpingの技術エンジニアリング部門、プロジェクトリンクMyFlashによって開発・保守されているDML操作のロールバックツールです。
制限事項
- binlogのフォーマットはrowで、binlog_row_image=fullでなければなりません；
- 5.6と5.7のみ対応しています；
- 5 でのみサポート.6 .7
インストール
# に応じて
yum install gcc* pkg-config glib2 libgnomeui-devel -y
#
wget https://./-///.ip -O MyFlash.zip
unzip MyFlash.zip
cd MyFlash-master
# コンパイルしてインストールする
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
mv binary /usr/local/MyFlash
ln -s /usr/local/MyFlash/flashback /usr/bin/flashback
ロールバック文は
ロールバックステートメントを生成します：
flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<start_pos> --stop-position=<stop_pos>
mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p
