mysql中truncate刪除記錄恢復筆記

truncate命令是mysql中刪除記錄一個命令它可以一次性刪除當前表中所有記錄並且不留任何日志了，同時這個表的ID就自動初化從1開始，今天我就來給大家嘗試一個利用truncate清除記錄之後恢復過程，希望給各位朋友解決你的問題。

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

測試環境： Percona-Server-5.6.16
日志格式： mixed 沒起用gtid

表結構如下：

 代碼如下 復制代碼 CREATETABLE`tb_wubx`(`id`INT(11)NOTNULLAUTO_INCREMENT,`name`VARCHAR(32)DEFAULTNULL,PRIMARYKEY(`id`)) ENGINE=InnoDB AUTO_INCREMENT=2DEFAULT 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| 老趙趙 |+----+-----------+1ROWINSET(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 （相當於恢復了備份，過程省略）
作者：吳炳錫　來源：http://www.mysqlsupport.cn/ 聯系方式： wubingxi#gmail.com 轉載請注明作/譯者和出處，並且不能用於商業用途，違者必究.

 代碼如下 復制代碼 mysql>SHOWBINARY logs;
+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 |143|| mysql-bin.000002 |261|| mysql-bin.000003 |562|| mysql-bin.000004 |1144|+------------------+-----------+4ROWSINSET(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`; TRUNCATETABLE 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`; TRUNCATETABLE 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 */|+------------------+------+-------------+-----------+-------------+----------------------------------------------------+19ROWSINSET(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

恢復結果如下：

 代碼如下 復制代碼 mysql -S /tmp/mysql.sock re_wubx;
mysql>SELECTCOUNT(*)FROM tb_wubx;
+----------+|COUNT(*)|+----------+|3|+----------+1ROWINSET(0.02 sec)
 
mysql>SELECT*FROM tb_wubx;
+----+--------------+| id | name |+----+--------------+|1| 張三 ||2| 李四 ||3| 隔壁老王 |+----+--------------+3ROWSINSET(0.00 sec)
 
mysql>INSERTINTO tb_wubx(name)SELECT name FROM wubx.tb_wubx;
Query OK,1ROW affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
 
mysql>RENAMETABLE wubx.tb_wubx TO wubx.bak_tb_wubx;
Query OK,0ROWS affected (0.04 sec)
 
mysql>RENAMETABLE re_wubx.tb_wubx TO wubx.tb_wubx;
Query OK,0ROWS affected (0.03 sec)
 
mysql>SELECT*FROM wubx.tb_wubx;
+----+--------------+| id | name |+----+--------------+|1| 張三 ||2| 李四 ||3| 隔壁老王 ||4| 老趙趙 |+----+--------------+4ROWSINSET(0.00 sec)

恢復完成。
想一想，如果我跳過那個truncate繼續執行那些binlog會怎麼樣

總結：從這次數據丟失給刪除之後得到一些感想了我們還是要經常對數據庫進行備份了，這樣可以保證我們的數據不給丟失了，同時也可以保證我們網站數據安全。