程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中truncate誤操作後的數據恢復案例

MySQL中truncate誤操作後的數據恢復案例

編輯：MySQL綜合教程

MySQL中truncate誤操作後的數據恢復案例。本站提示廣大學習愛好者：（MySQL中truncate誤操作後的數據恢復案例）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL中truncate誤操作後的數據恢復案例正文

現實線上的場景比擬龐雜，其時觸及了truncate, delete 兩個操作，經確認丟數據差不多7萬多行，等停上去時，差不多又有合計1萬多行數據寫入。這裡為了簡略解釋，只拿弄一個簡略的營業場景舉例。

測試情況： Percona-Server-5.6.16
日記格局： mixed 沒升引gtid

表構造以下：

CREATE TABLE `tb_wubx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
 
CREATE TABLE `tb_wubx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

基於某個時光點有一個備份或是有全量的binlog是能恢單數據的一個獨一包管。例如我們的備份就是一個表構造創立語句,binlog pos相干信息： mysql-bin.000004 , 4，然落後行了以下：

–t1時光法式寫入：

insert into tb_wubx(name) values(‘張三'),(‘李四');
insert into tb_wubx(name) values(‘近鄰老王');

–t2時光某小我員掉誤

truncate table tb_wubx;

–t3時光法式寫入

insert into tb_wubx(name) values(‘老趙');
update tb_wubx set name='老趙趙' where id=1;

如今內外的數據情形：

mysql>select * from tb_wubx;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 老趙趙 |
+----+-----------+
1 row in set (0.00 sec)
 
mysql>select * from tb_wubx;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 老趙趙 |
+----+-----------+
1 row in set (0.00 sec)

可以見truncate table操作後，表的自增id又變革為從1開端，本來寫入的數據應當是：

+—-+———–+
| id | name |
+—-+———–+
| 1 | 張三 |
+—-+———–+
| 2 | 李四 |
+—-+———–+
| 3 | 近鄰老王 |
+—-+———–+

假如沒生truncate table操作，現實的數據應當為：

+—-+———–+
| id | name |
+—-+———–+
| 1 | 張三 |
+—-+———–+
| 2 | 李四 |
+—-+———–+
| 3 | 近鄰老王 |
+—-+———–+
| 4 | 老趙趙 |
+—-+———–+

並且線上的恢復誰人表時和序序開辟人員懂得才曉得，本來誰人id懈弛存及其它處所有依附，由於id亂了，也會形成法式紊亂。這個時光修復id在法式層紊亂的事，留給開辟人員了關建是給他們疏解白恢復的成果是甚麼樣，我們的關建義務是把數據恢復出來。好，接上去的任務是開端從binlog中恢單數據。
應用: show binary logs; 檢查當的log文件散布，然後應用show binlog events in ‘binary log文件'; 檢查log文件的內容，目標是找到truncate產生的日記地位。
別的由於基於備份（由log的啟始地位）或是從量log, 假如基於備份有log的肇端地位，我們須要處置的log文件是啟始地位到產生truncate的日值（前面的數據處置不了，會產生主建抵觸的毛病形成truncate後的數據不克不及恢復），
假如是全量日記，須要從創立完mysql後庫後的日記行止理到以後的產生truncate的地位（前面數據會由於主建抵觸寫不出來）
恢復預備任務，創立一個庫用於恢單數據，這裡創立了一個re_wubx, 及原構造的表: tb_wubx （相當於恢復了備份，進程省略）

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 261 |
| mysql-bin.000003 | 562 |
| mysql-bin.000004 | 1144 |
+------------------+-----------+
4 rows in set (0.00 sec)
 
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 261 |
| mysql-bin.000003 | 562 |
| mysql-bin.000004 | 1144 |
+------------------+-----------+
4 rows in set (0.00 sec)

我這裡有一個備份文件就是誰人創立表的sql語句，地位是mysql-bin.000004 , 4
在這個案例裡我只用cover住mysql-bin.000004這個文件。

mysql>show binlog events in 'mysql-bin.000004';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| Log_name   | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query   | 753306 | 209 | use `wubx`; truncate table tb_wubx |
| mysql-bin.000004 | 209 | Query   | 753306 | 281 | BEGIN |
| mysql-bin.000004 | 281 | Table_map  | 753306 | 334 | table_id: 91 (wubx.tb_wubx) |
| mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F |
| mysql-bin.000004 | 393 | Xid   | 753306 | 424 | COMMIT /* xid=1073 */ |
| mysql-bin.000004 | 424 | Query   | 753306 | 496 | BEGIN |
| mysql-bin.000004 | 496 | Table_map  | 753306 | 549 | table_id: 91 (wubx.tb_wubx) |
| mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F |
| mysql-bin.000004 | 602 | Xid   | 753306 | 633 | COMMIT /* xid=1074 */ |
| mysql-bin.000004 | 633 | Query   | 753306 | 722 | use `wubx`; truncate table tb_wubx |
| mysql-bin.000004 | 722 | Query   | 753306 | 794 | BEGIN |
| mysql-bin.000004 | 794 | Table_map  | 753306 | 847 | table_id: 92 (wubx.tb_wubx) |
| mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F |
| mysql-bin.000004 | 894 | Xid   | 753306 | 925 | COMMIT /* xid=1081 */ |
| mysql-bin.000004 | 925 | Query   | 753306 | 997 | BEGIN |
| mysql-bin.000004 | 997 | Table_map  | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) |
| mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F |
| mysql-bin.000004 | 1113 | Xid   | 753306 | 1144 | COMMIT /* xid=1084 */ |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
19 rows in set (0.00 sec)
 
mysql>show binlog events in 'mysql-bin.000004';
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| Log_name   | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 |
| mysql-bin.000004 | 120 | Query   | 753306 | 209 | use `wubx`; truncate table tb_wubx |
| mysql-bin.000004 | 209 | Query   | 753306 | 281 | BEGIN |
| mysql-bin.000004 | 281 | Table_map  | 753306 | 334 | table_id: 91 (wubx.tb_wubx) |
| mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F |
| mysql-bin.000004 | 393 | Xid   | 753306 | 424 | COMMIT /* xid=1073 */ |
| mysql-bin.000004 | 424 | Query   | 753306 | 496 | BEGIN |
| mysql-bin.000004 | 496 | Table_map  | 753306 | 549 | table_id: 91 (wubx.tb_wubx) |
| mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F |
| mysql-bin.000004 | 602 | Xid   | 753306 | 633 | COMMIT /* xid=1074 */ |
| mysql-bin.000004 | 633 | Query   | 753306 | 722 | use `wubx`; truncate table tb_wubx |
| mysql-bin.000004 | 722 | Query   | 753306 | 794 | BEGIN |
| mysql-bin.000004 | 794 | Table_map  | 753306 | 847 | table_id: 92 (wubx.tb_wubx) |
| mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F |
| mysql-bin.000004 | 894 | Xid   | 753306 | 925 | COMMIT /* xid=1081 */ |
| mysql-bin.000004 | 925 | Query   | 753306 | 997 | BEGIN |
| mysql-bin.000004 | 997 | Table_map  | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) |
| mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F |
| mysql-bin.000004 | 1113 | Xid   | 753306 | 1144 | COMMIT /* xid=1084 */ |
+------------------+------+-------------+-----------+-------------+----------------------------------------------------+
19 rows in set (0.00 sec)

看到這個表剛開端就產生一次truncate, 那其實也能夠解釋我就恢復剛開端誰人truncate到後來誰人誤操作的truncate table的語句之間的數據就是喪失的數據。
這個恢復可以從mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即：

mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx


mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx

恢復成果以下：

mysql -S /tmp/mysql.sock re_wubx;
mysql>select count(*) from tb_wubx;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.02 sec)

mysql>select * from tb_wubx;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 近鄰老王 |
+----+--------------+
3 rows in set (0.00 sec)

mysql>insert into tb_wubx(name) select name from wubx.tb_wubx;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx;
Query OK, 0 rows affected (0.04 sec)

mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from wubx.tb_wubx;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 近鄰老王 |
| 4 | 老趙趙 |
+----+--------------+
4 rows in set (0.00 sec)
 
mysql -S /tmp/mysql.sock re_wubx;
mysql>select count(*) from tb_wubx;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.02 sec)
 
mysql>select * from tb_wubx;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 近鄰老王 |
+----+--------------+
3 rows in set (0.00 sec)
 
mysql>insert into tb_wubx(name) select name from wubx.tb_wubx;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
 
mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx;
Query OK, 0 rows affected (0.04 sec)
 
mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx;
Query OK, 0 rows affected (0.03 sec)
 
mysql> select * from wubx.tb_wubx;
+----+--------------+
| id | name |
+----+--------------+
| 1 | 張三 |
| 2 | 李四 |
| 3 | 近鄰老王 |
| 4 | 老趙趙 |
+----+--------------+
4 rows in set (0.00 sec)

恢復完成。