mysql資料恢復

書接上文，把未刪除表資料也恢復出來：
http://blog.sina.com.cn/s/blog_5037eacb0102vq45.html 
這裡沒有使用獨立表空間，使用獨立表空間也是一樣的道理。
只是多了一個 ./stream_parser  .ibd檔案的過程。  然後在指定資料頁的時候 換一個.ibd的路徑。






[root@10-4-1-104 ]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql |grep song1
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp//dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
000000055333    A900000D7D0110  SYS_TABLES      "song1/song1"   228     2       1       0       0       ""      0


--檢視228頁
[root@10-4-1-104 ]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql |grep 228
000000055333    A900000D7D014D  SYS_INDEXES     228     423     "GEN\_CLUST\_INDEX"     0       1       0       630
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp//dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);
000000055333    A900000D7D014D  SYS_INDEXES     228     423     "GEN\_CLUST\_INDEX"     0       1       0       630
-- 主鍵索引在 423頁


--把上節得到的表定義語句放好
[root@10-4-1-104 ]# cd song1
[root@10-4-1-104 song1]# ll
total 4
-rw-r--r-- 1 root root 130 Jul 16 10:53 song1.sql
[root@10-4-1-104 song1]# cat song1.sql 
CREATE TABLE `song1`(
        `id` INT,
        `name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci') ENGINE=InnoDB;
[root@10-4-1-104 song1]# cd ..


--抽資料。  如果是使用獨立表空間，這裡的資料頁路徑要換一換
[root@10-4-1-104 ]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000423.page -t song1/song1.sql > dumps/default/song1 2> dumps/default/song1_load.sql
[root@10-4-1-104 ]# cat dumps/default/song1
-- Page id: 630, Format: COMPACT, Records list: Valid, Expected records: (1 1)
000000000700    000000055334    AA00000D7E0110  song1   8       "song"
-- Page id: 630, Found records: 1, Lost records: NO, Leaf page: YES
-- Page id: 630, Format: COMPACT, Records list: Valid, Expected records: (0 0)
-- Page id: 630, Found records: 0, Lost records: NO, Leaf page: YES
-- Page id: 630, Format: COMPACT, Records list: Valid, Expected records: (1 1)
000000000700    000000055334    AA00000D7E0110  song1   8       "song"
-- Page id: 630, Found records: 1, Lost records: NO, Leaf page: YES
[root@10-4-1-104 ]# cat dumps/default/song1_load.sql 
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp//dumps/default/song1' REPLACE INTO TABLE `song1` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'song1\t' (`id`, `name`);


--準備把資料放到庫中
[root@10-4-1-104 ~]# mysql -u root -S /data/mysqld.sock  -ptest --local-infile=1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.5.24-ucloudrel1-log Source distribution


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d1                 |
| db_song_recover    |
| mysql              |
| performance_schema |
| song1              |
| song1_recover      |
| t                  |
| test               |
| ttt                |
+--------------------+
10 rows in set (0.00 sec)


mysql> use song1_recover;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> show tables;
+-------------------------+
| Tables_in_song1_recover |
+-------------------------+
| sys_columns             |
| sys_fields              |
| sys_indexes             |
| sys_tables              |
+-------------------------+
4 rows in set (0.00 sec)


mysql> CREATE TABLE `song1`(
    ->         `id` INT,
    ->         `name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci') ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)


mysql> source /tmp//dumps/default/song1_load.sql
Query OK, 0 rows affected (0.00 sec)


Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from song1;
+------+------+
| id   | name |
+------+------+
|    8 | song |
|    8 | song |
+------+------+
2 rows in set (0.00 sec)


這裡取出來 兩條記錄， 看來沒有人為指定主鍵，還是有些問題。不過想資料回來，也是萬幸了。




轉載請註明源出處
QQ 273002188  歡迎一起學習

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1735940/，如需轉載，請註明出處，否則將追究法律責任。