MySQL資料災難挽救之truncate table
場景:工作人員誤操作對其整表誤刪除且用了truncate table語句,且無備份、未開啟BinLog日誌的情況。
思路:資料在truncate刪除不會刪除表結構只清除資料,在清除之前資料已經記錄到磁碟了,在這個情況下不再對磁碟進行重新整理和覆蓋,可以嘗試透過undrop-for-innodb工具掃描磁碟進行恢復資料。
說明:innodb_file_per_table=on,因為透過掃描磁碟頁來找回資料的,為防止磁碟頁由於大量的IO操作,導致磁碟被重用無法找回資料。建議在誤操作之後剛磁碟掛載為只讀模式或直接停止業務,避免資料頁被重新整理。資料庫資料目錄要單獨掛載一個盤,不然解析可能會出現問題。
一、安裝undrop-for-innodb工具
shell> git clone git://github.com/twindb/undrop-for-innodb.git shell> yum install make gcc flex bison -y shell> cd undrop-for-innodb shell> make shell> gcc `$basedir/bin/mysql_config --cflags` `$basedir/bin/mysql_config --libs` -o sys_parser sys_parser.c 注:$basedir使用實際路徑替換
二、手動建立模擬環境
1、手動建立模擬表
mysql> create database test; mysql> use test mysql> CREATE TABLE `test` ( `test_id` bigint NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`test_id`), KEY `idx_test_last_name` (`last_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> DELIMITER // mysql> create function rand_string (n int) returns varchar(255) -> begin -> declare chars_str varchar(100) default 'ABCDEFGHIGKLMNOPQRSTUVWXYZ'; -> declare return_str varchar(255) default ''; -> declare i int default 0; -> while (i < n) do -> set return_str = concat(return_str,substring(chars_str,floor(1+rand()*26),1)); -> set i = i+1; -> end while; -> return return_str; -> END -> // mysql> CREATE PROCEDURE mytest() -> begin -> declare i bigint; -> set i=1; -> while i<=50000 do -> insert into thx(first_name, last_name) value (rand_string(3),rand_string(3)); -> set i = i+1 ; -> end while; -> commit; -> end -> // mysql> DELIMITER ; mysql> call mytest();
2、查詢當前表資訊
mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 50000 | +----------+
3、手動刪除表
mysql> truncate table test;
4、查詢需要恢復的表的index_id資訊
mysql> select t.name, t.table_id, i.index_id,i.NAME,i.page_no from information_schema.INNODB_SYS_TABLES t join information_schema.INNODB_SYS_INDEXES i on t.table_id=i.table_id and t.name='test/test'; +-----------+----------+----------+--------------------+---------+ | name | table_id | index_id | NAME | page_no | +-----------+----------+----------+--------------------+---------+ | test/test | 49 | 52 | PRIMARY | 3 | | test/test | 49 | 53 | idx_test_last_name | 4 | +-----------+----------+----------+--------------------+---------+ 2 rows in set (0.01 sec)
三、恢復資料
1、掃描邏輯卷
shell> df -h Filesystem Size Used Avail Use% Mounted on devtmpfs 895M 0 895M 0% /dev tmpfs 910M 0 910M 0% /dev/shm tmpfs 910M 11M 900M 2% /run tmpfs 910M 0 910M 0% /sys/fs/cgroup /dev/sda3 28G 13G 16G 45% / /dev/sda1 297M 165M 133M 56% /boot tmpfs 182M 12K 182M 1% /run/user/42 tmpfs 182M 0 182M 0% /run/user/0 shell> /root/undrop-for-innodb/stream_parser -f /dev/sda3 -s 1G -t 28G Disk cache: 1073741824 (1.000 GiB) Opening file: /dev/sda3 File information: ID of device containing file: 5 inode number: 9937 protection: 60660 (block device) number of hard links: 1 user ID of owner: 0 group ID of owner: 6 device ID (if special file): 2051 blocksize for filesystem I/O: 4096 number of blocks allocated: 0 time of last access: 1637735653 Tue Nov 23 22:34:13 2021 time of last modification: 1637735650 Tue Nov 23 22:34:10 2021 time of last status change: 1637735650 Tue Nov 23 22:34:10 2021 total size, in bytes: 0 (0.000 exp(+0)) Size to process: 30064771072 (28.000 GiB) Worker(0): 7.14% done. 2021-11-23 22:47:55 ETA(in 00:03:02). Processing speed: 146.283 MiB/sec Worker(0): 10.71% done. 2021-11-23 22:45:45 ETA(in 00:00:50). Processing speed: 512.000 MiB/sec Worker(0): 14.29% done. 2021-11-23 22:45:45 ETA(in 00:00:48). Processing speed: 512.000 MiB/sec Worker(0): 17.86% done. 2021-11-23 22:45:45 ETA(in 00:00:46). Processing speed: 512.000 MiB/sec Worker(0): 21.43% done. 2021-11-23 22:45:45 ETA(in 00:00:44). Processing speed: 512.000 MiB/sec Worker(0): 25.00% done. 2021-11-23 22:46:51 ETA(in 00:01:45). Processing speed: 204.800 MiB/sec Worker(0): 28.57% done. 2021-11-23 22:46:09 ETA(in 00:01:00). Processing speed: 341.328 MiB/sec Worker(0): 32.14% done. 2021-11-23 22:45:49 ETA(in 00:00:38). Processing speed: 511.992 MiB/sec Worker(0): 35.71% done. 2021-11-23 22:45:49 ETA(in 00:00:36). Processing speed: 512.000 MiB/sec Worker(0): 39.29% done. 2021-11-23 22:45:49 ETA(in 00:00:34). Processing speed: 512.000 MiB/sec Worker(0): 42.86% done. 2021-11-23 22:45:32 ETA(in 00:00:16). Processing speed: 1024.000 MiB/sec Worker(0): 46.43% done. 2021-11-23 22:46:04 ETA(in 00:00:45). Processing speed: 341.333 MiB/sec Worker(0): 50.00% done. 2021-11-23 22:46:49 ETA(in 00:01:24). Processing speed: 170.667 MiB/sec Worker(0): 53.57% done. 2021-11-23 22:46:35 ETA(in 00:01:05). Processing speed: 204.797 MiB/sec Worker(0): 57.14% done. 2021-11-23 22:47:01 ETA(in 00:01:24). Processing speed: 146.283 MiB/sec Worker(0): 60.71% done. 2021-11-23 22:46:37 ETA(in 00:00:55). Processing speed: 204.797 MiB/sec Worker(0): 64.29% done. 2021-11-23 22:46:04 ETA(in 00:00:20). Processing speed: 511.992 MiB/sec Worker(0): 67.86% done. 2021-11-23 22:46:04 ETA(in 00:00:18). Processing speed: 512.000 MiB/sec Worker(0): 71.43% done. 2021-11-23 22:46:13 ETA(in 00:00:24). Processing speed: 341.333 MiB/sec Worker(0): 75.00% done. 2021-11-23 22:46:37 ETA(in 00:00:42). Processing speed: 170.667 MiB/sec Worker(0): 78.57% done. 2021-11-23 22:46:37 ETA(in 00:00:36). Processing speed: 170.664 MiB/sec Worker(0): 82.14% done. 2021-11-23 22:46:13 ETA(in 00:00:10). Processing speed: 511.992 MiB/sec Worker(0): 85.71% done. 2021-11-23 22:46:13 ETA(in 00:00:08). Processing speed: 512.000 MiB/sec Worker(0): 89.29% done. 2021-11-23 22:46:13 ETA(in 00:00:06). Processing speed: 512.000 MiB/sec Worker(0): 92.86% done. 2021-11-23 22:46:13 ETA(in 00:00:04). Processing speed: 512.000 MiB/sec Worker(0): 96.43% done. 2021-11-23 22:46:11 ETA(in 00:00:01). Processing speed: 1024.000 MiB/sec All workers finished in 95 sec
2、檢視誤刪的表結構
mysql> show create table test; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `test_id` bigint(20) NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`test_id`), KEY `idx_test_last_name` (`last_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec) shell> vi test.sql CREATE TABLE `test` ( `test_id` bigint(20) NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`test_id`), KEY `idx_test_last_name` (`last_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3、解析聚集索引頁
shell> mkdir -p dumps/default shell> /root/undrop-for-innodb/c_parser -6f pages-sda3/FIL_PAGE_INDEX/0000000000000052.page -t test.sql > dumps/default/test 2> dumps/default/test.sql
4、恢復資料
shell> mysql -uroot -pHzmc321# test < /root/undrop-for-innodb/dumps/default/test.sql mysql: [Warning] Using a password on the command line interface can be insecure. mysql> select count(*) from test; +----------+ | count(*) | +----------+ | 50000 | +----------+ 1 row in set (0.01 sec)
資料恢復成功,沒有資料丟失!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30310891/viewspace-2844565/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL資料災難挽救之drop tableMySql
- MySQL資料災難挽救之Delete\UpdateMySqldelete
- oracle資料庫災難挽救應急方案之DDL誤操作恢復(truncate)Oracle資料庫
- MySQL資料災難挽救之ibdata檔案誤刪恢復MySql
- oracle資料庫災難挽救應急方案之DML誤操作恢復Oracle資料庫
- oracle資料庫災難挽救應急方案之DDL誤操作恢復(drop)Oracle資料庫
- oracle logminer恢復truncate table的資料Oracle
- truncate table 誤刪除資料後的恢復
- 恢復被執行truncate table的表資料
- Oralce 資料庫的災難恢復(轉)資料庫
- 【資料庫資料恢復】Oracle資料庫誤truncate table的資料恢復案例資料庫資料恢復Oracle
- 【資料庫資料恢復】oracle資料庫誤truncate table怎麼恢復資料?資料庫資料恢復Oracle
- 【資料庫資料恢復】誤truncate table的Oracle資料庫資料恢復方案資料庫資料恢復Oracle
- Mysql truncate table時解決外來鍵關聯MySql
- 一次truncate table 後的資料恢復[轉帖]資料恢復
- PostgreSQL資料檔案災難恢復-解析與資料dumpSQL
- drop table和truncate table的區別
- Oracle中truncate table後的資料恢復(Oracle資料恢復工具-ODU)Oracle資料恢復
- mysql資料庫之間、table增量匯入指令碼MySql資料庫指令碼
- partition table中truncate應用
- 【北亞資料恢復】oracle資料庫執行truncate table命令怎麼恢復資料?資料恢復Oracle資料庫
- Xtrabackup實現資料庫備份和災難恢復資料庫
- 資料備份方法及災難恢復探討(轉)
- 【資料庫資料恢復】ORACLE常見資料災難&資料恢復可能性資料庫資料恢復Oracle
- oracle truncate table recover(oracle 如何拯救誤操作truncate的表)Oracle
- 禁止客戶端 誤 truncate table客戶端
- TRUNCATE資料恢復資料恢復
- MySQL truncate原理MySql
- truncate table執行很慢的原因分析
- MySQL Shell import_table資料匯入MySqlImport
- 基於UNIX系統,邏輯故障的資料災難解讀
- 解決資料災難需要回答的十個問題
- mysql之 OPTIMIZE TABLE整理碎片MySql
- delete table 和 truncate table - 型別安全的心 - 部落格園delete型別
- 使用閃回挽救我們的資料
- SQL Server災難恢復SQLServer
- 細數基於ORACLE 資料庫環境的常見資料災難解決方式Oracle資料庫
- 在儲存過程中寫truncate table儲存過程