這篇文章主要介紹了MySQL中truncate誤操作后的數據恢復案例,主要是要從日志中定位到truncate操作的地方然后備份之前丟失的數據,需要的朋友可以參考下

實際線上的場景比較復雜，當時涉及了truncate, delete 兩個操作，經確認丟數據差不多7萬多行，等停下來時，差不多又有共計1萬多行數據寫入。 這里為了簡單說明，只拿弄一個簡單的業務場景舉例。

測試環境： Percona-Server-5.6.16

日志格式： mixed 沒起用gtid

表結構如下：

1. CREATE TABLE `tb_wubx` ( 
2. `id` int(11) NOT NULL AUTO_INCREMENT, 
3. `name` varchar(32) DEFAULT NULL, 
4. PRIMARY KEY (`id`) 
5. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 
6.  
7. CREATE TABLE `tb_wubx` ( 
8. `id` int(11) NOT NULL AUTO_INCREMENT, 
9. `name` varchar(32) DEFAULT NULL, 
10. PRIMARY KEY (`id`) 
11. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 

基于某個時間點有一個備份或是有全量的binlog是能恢復數據的一個唯一保證。 例如我們的備份就是一個表結構創建語句,binlog pos相關信息： mysql-bin.000004 , 4，然后進行了如下：

–t1時間 程序寫入：

1. insert into tb_wubx(name) values(‘張三'),(‘李四'); 
2. insert into tb_wubx(name) values(‘隔壁老王'); 

–t2時間 某個人員失誤

1. truncate table tb_wubx; 

–t3時間 程序寫入

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

現在表里的數據情況：

1. mysql>select * from tb_wubx; 
2. +----+-----------+ 
3. | id | name | 
4. +----+-----------+ 
5. | 1 | 老趙趙 | 
6. +----+-----------+ 
7. 1 row in set (0.00 sec) 
8.  
9. mysql>select * from tb_wubx; 
10. +----+-----------+ 
11. | id | name | 
12. +----+-----------+ 
13. | 1 | 老趙趙 | 
14. +----+-----------+ 
15. 1 row in set (0.00 sec) 

可以見truncate table操作后，表的自增id又變更為從1開始，原來寫入的數據應該是：

1. +—-+———–+ 
2. | id | name | 
3. +—-+———–+ 
4. | 1 | 張三 | 
5. +—-+———–+ 
6. | 2 | 李四 | 
7. +—-+———–+ 
8. | 3 | 隔壁老王 | 
9. +—-+———–+ 

如果沒生truncate table操作，實際的數據應該為：

1. +—-+———–+ 
2. | id | name | 
3. +—-+———–+ 
4. | 1 | 張三 | 
5. +—-+———–+ 
6. | 2 | 李四 | 
7. +—-+———–+ 
8. | 3 | 隔壁老王 | 
9. +—-+———–+ 
10. | 4 | 老趙趙 | 
11. +—-+———–+ 

而且線上的恢復那個表時和序序開發人員了解才知道，原來那個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 (相當于恢復了備份，過程省略)

1. mysql> show binary logs; 
2. +------------------+-----------+ 
3. | Log_name | File_size | 
4. +------------------+-----------+ 
5. | mysql-bin.000001 | 143 | 
6. | mysql-bin.000002 | 261 | 
7. | mysql-bin.000003 | 562 | 
8. | mysql-bin.000004 | 1144 | 
9. +------------------+-----------+ 
10. 4 rows in set (0.00 sec) 
11.  
12. mysql> show binary logs; 
13. +------------------+-----------+ 
14. | Log_name | File_size | 
15. +------------------+-----------+ 
16. | mysql-bin.000001 | 143 | 
17. | mysql-bin.000002 | 261 | 
18. | mysql-bin.000003 | 562 | 
19. | mysql-bin.000004 | 1144 | 
20. +------------------+-----------+ 
21. 4 rows in set (0.00 sec) 

我這里有一個備份文件就是那個創建表的sql語句，位置是mysql-bin.000004 , 4

在這個案例里我只用cover住mysql-bin.000004這個文件。

1. mysql>show binlog events in 'mysql-bin.000004'; 
2. +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 
3. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 
4. +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 
5. | mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 | 
6. | mysql-bin.000004 | 120 | Query | 753306 | 209 | use `wubx`; truncate table tb_wubx | 
7. | mysql-bin.000004 | 209 | Query | 753306 | 281 | BEGIN | 
8. | mysql-bin.000004 | 281 | Table_map | 753306 | 334 | table_id: 91 (wubx.tb_wubx) | 
9. | mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F | 
10. | mysql-bin.000004 | 393 | Xid | 753306 | 424 | COMMIT /* xid=1073 */ | 
11. | mysql-bin.000004 | 424 | Query | 753306 | 496 | BEGIN | 
12. | mysql-bin.000004 | 496 | Table_map | 753306 | 549 | table_id: 91 (wubx.tb_wubx) | 
13. | mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F | 
14. | mysql-bin.000004 | 602 | Xid | 753306 | 633 | COMMIT /* xid=1074 */ | 
15. | mysql-bin.000004 | 633 | Query | 753306 | 722 | use `wubx`; truncate table tb_wubx | 
16. | mysql-bin.000004 | 722 | Query | 753306 | 794 | BEGIN | 
17. | mysql-bin.000004 | 794 | Table_map | 753306 | 847 | table_id: 92 (wubx.tb_wubx) | 
18. | mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F | 
19. | mysql-bin.000004 | 894 | Xid | 753306 | 925 | COMMIT /* xid=1081 */ | 
20. | mysql-bin.000004 | 925 | Query | 753306 | 997 | BEGIN | 
21. | mysql-bin.000004 | 997 | Table_map | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) | 
22. | mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F | 
23. | mysql-bin.000004 | 1113 | Xid | 753306 | 1144 | COMMIT /* xid=1084 */ | 
24. +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 
25. 19 rows in set (0.00 sec) 
26.  
27. mysql>show binlog events in 'mysql-bin.000004'; 
28. +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 
29. | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | 
30. +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 
31. | mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 | 
32. | mysql-bin.000004 | 120 | Query | 753306 | 209 | use `wubx`; truncate table tb_wubx | 
33. | mysql-bin.000004 | 209 | Query | 753306 | 281 | BEGIN | 
34. | mysql-bin.000004 | 281 | Table_map | 753306 | 334 | table_id: 91 (wubx.tb_wubx) | 
35. | mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F | 
36. | mysql-bin.000004 | 393 | Xid | 753306 | 424 | COMMIT /* xid=1073 */ | 
37. | mysql-bin.000004 | 424 | Query | 753306 | 496 | BEGIN | 
38. | mysql-bin.000004 | 496 | Table_map | 753306 | 549 | table_id: 91 (wubx.tb_wubx) | 
39. | mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F | 
40. | mysql-bin.000004 | 602 | Xid | 753306 | 633 | COMMIT /* xid=1074 */ | 
41. | mysql-bin.000004 | 633 | Query | 753306 | 722 | use `wubx`; truncate table tb_wubx | 
42. | mysql-bin.000004 | 722 | Query | 753306 | 794 | BEGIN | 
43. | mysql-bin.000004 | 794 | Table_map | 753306 | 847 | table_id: 92 (wubx.tb_wubx) | 
44. | mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F | 
45. | mysql-bin.000004 | 894 | Xid | 753306 | 925 | COMMIT /* xid=1081 */ | 
46. | mysql-bin.000004 | 925 | Query | 753306 | 997 | BEGIN | 
47. | mysql-bin.000004 | 997 | Table_map | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) | 
48. | mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F | 
49. | mysql-bin.000004 | 1113 | Xid | 753306 | 1144 | COMMIT /* xid=1084 */ | 
50. +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 
51. 19 rows in set (0.00 sec) 

看到這個表剛開始就發生一次truncate, 那其實也可以說明我就恢復剛開始那個truncate到后來那個誤操作的truncate table的語句之間的數據就是丟失的數據。

這個恢復可以從mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即：

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

恢復結果如下：

1. mysql -S /tmp/mysql.sock re_wubx; 
2. mysql>select count(*) from tb_wubx; 
3. +----------+ 
4. | count(*) | 
5. +----------+ 
6. | 3 | 
7. +----------+ 
8. 1 row in set (0.02 sec) 
9.  
10. mysql>select * from tb_wubx; 
11. +----+--------------+ 
12. | id | name | 
13. +----+--------------+ 
14. | 1 | 張三 | 
15. | 2 | 李四 | 
16. | 3 | 隔壁老王 | 
17. +----+--------------+ 
18. 3 rows in set (0.00 sec) 
19.  
20. mysql>insert into tb_wubx(name) select name from wubx.tb_wubx; 
21. Query OK, 1 row affected (0.00 sec) 
22. Records: 1 Duplicates: 0 Warnings: 0 
23.  
24. mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx; 
25. Query OK, 0 rows affected (0.04 sec) 
26.  
27. mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx; 
28. Query OK, 0 rows affected (0.03 sec) 
29.  
30. mysql> select * from wubx.tb_wubx; 
31. +----+--------------+ 
32. | id | name | 
33. +----+--------------+ 
34. | 1 | 張三 | 
35. | 2 | 李四 | 
36. | 3 | 隔壁老王 | 
37. | 4 | 老趙趙 | 
38. +----+--------------+ 
39. 4 rows in set (0.00 sec) 
40.  
41. mysql -S /tmp/mysql.sock re_wubx; 
42. mysql>select count(*) from tb_wubx; 
43. +----------+ 
44. | count(*) | 
45. +----------+ 
46. | 3 | 
47. +----------+ 
48. 1 row in set (0.02 sec) 
49.  
50. mysql>select * from tb_wubx; 
51. +----+--------------+ 
52. | id | name | 
53. +----+--------------+ 
54. | 1 | 張三 | 
55. | 2 | 李四 | 
56. | 3 | 隔壁老王 | 
57. +----+--------------+ 
58. 3 rows in set (0.00 sec) 
59.  
60. mysql>insert into tb_wubx(name) select name from wubx.tb_wubx; 
61. Query OK, 1 row affected (0.00 sec) 
62. Records: 1 Duplicates: 0 Warnings: 0 
63.  
64. mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx; 
65. Query OK, 0 rows affected (0.04 sec) 
66.  
67. mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx; 
68. Query OK, 0 rows affected (0.03 sec) 
69.  
70. mysql> select * from wubx.tb_wubx; 
71. +----+--------------+ 
72. | id | name | 
73. +----+--------------+ 
74. | 1 | 張三 | 
75. | 2 | 李四 | 
76. | 3 | 隔壁老王 | 
77. | 4 | 老趙趙 | 
78. +----+--------------+ 
79. 4 rows in set (0.00 sec) 

恢復完成。