MySQL单表ibd文件恢复方法详解_MySQL
bitsCN.com
前言：
随着innodb的普及，innobackup也成为了主流备份方式。物理备份对于新建slave，全库恢复的需求都能从容应对。
但当面临单表数据误删，或者单表误drop的情况，如果使用物理全备进行恢复呢？
下文将进行详细分析。
恢复过程中需要用到的工具，percona data recover tool ： https://launchpad.net/percona-innodb-recovery-tool
情况一：误删部分数据，需要用最近一次备份覆盖
来自同一台机器的ibd恢复覆盖，且备份后table没有被recreate过。
这种情况是最简单的，备份时的ibd文件（后称老ibd）中的space id和index id 与 新ibd的space id 和index id一致。
且和ibdata文件中的space id和index id一致。因此，物理文件可以直接覆盖做恢复。
以下是详细步骤
Step -1 : 物理备份
innobackupex --defaults-file=/usr/local/mysql3321/my.cnf --socket=/xfs/mysql3321/mysql.sock --user=root --password=password /xfs/backup/
Step 0 : apply log
innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf /xfs/backup/2012-10-17_11-29-20/
Step 1 : 备份现在的ibd文件（可选）
cp -a testibd.ibd testibd.bak
Step 2 : 舍弃现在ibd文件
mysql> alter table testibd discard tablespace
Step 3 : 复制备份ibd文件
shell> cp /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd /xfs/mysql3321/test/
shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd
Step 4 : 导入ibd文件
mysql> alter table testibd import tablespace
情况二：误删 table，表结构已经被drop了
这种情况稍复杂，不过恢复过程还是比较容易操作的。由于table被drop后的space id会留空因此备份文件的space id不会被占用。
我们只需要重建表结构，然后把ibdata中该表的space id还原，物理文件可以直接覆盖做恢复了。
Step 1 : 重建表
mysql> create table testibd (UserID int);
Step 2 : 关闭mysql服务（必须）
shell> service mysqld3321 stop
Step 3: 准备ibd文件 apply log
shell> innobackupex --apply-log --defaults-file=/usr/local/mysql3321/my.cnf /xfs/backup/2012-10-17_11-29-20/
Step 4 : 备份现在的ibd文件（可选）
cp -a testibd.ibd testibd.bak
Step 5 : 复制备份ibd文件
shell> cp -a /xfs/backup/2012-10-17_11-29-20/test/testibd.ibd /xfs/mysql3321/test/
shell> chown mysql:mysql /xfs/mysql3321/test/testibd.ibd
Step 6 : 使用percona recovery tool 修改ibdata
shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/ibdconnect -o /xfs/mysql3321/ibdata1 -f /xfs/mysql3321/test/testibd.ibd -d test -t testibd
Initializing table definitions...
Processing table: SYS_TABLES
- total fields: 10
- nullable fields: 6
- minimum header size: 5
- minimum rec size: 21
- maximum rec size: 555
Processing table: SYS_INDEXES
- total fields: 9
- nullable fields: 5
- minimum header size: 5
- minimum rec size: 29
- maximum rec size: 165
Setting SPACE=1 in SYS_TABLE for `test`.`testibd`
Check if space id 1 is already used
Page_id: 8, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0
Db/table: infimum
Space id: 1768842857 (0x696E6669)
Next record at offset: 8D
Record position: 8D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52
Db/table: SYS_FOREIGN
Space id: 0 (0x0)
Next record at offset: D5
Record position: D5
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57
Db/table: SYS_FOREIGN_COLS
Space id: 0 (0x0)
Next record at offset: 122
Record position: 122
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53
Db/table: test/testibd
Space id: 2 (0x2)
Next record at offset: 74
Space id 1 is not used in any of the records in SYS_TABLES
Page_id: 8, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 8 50 3 2 0 0 0 0 0
Db/table: infimum
Space id: 1768842857 (0x696E6669)
Next record at offset: 8D
Record position: 8D
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 11 17 24 32 36 40 48 52 52
Db/table: SYS_FOREIGN
Space id: 0 (0x0)
Next record at offset: D5
Record position: D5
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 16 22 29 37 41 45 53 57 57
Db/table: SYS_FOREIGN_COLS
Space id: 0 (0x0)
Next record at offset: 122
Record position: 122
Checking field lengths for a row (SYS_TABLES): OFFSETS: 16 12 18 25 33 37 41 49 53 53
Db/table: test/testibd
Space id: 2 (0x2)
Updating test/testibd (table_id 17) with id 0x01000000
SYS_TABLES is updated successfully
Initializing table definitions...
Processing table: SYS_TABLES
- total fields: 10
- nullable fields: 6
- minimum header size: 5
- minimum rec size: 21
- maximum rec size: 555
Processing table: SYS_INDEXES
- total fields: 9
- nullable fields: 5
- minimum header size: 5
- minimum rec size: 29
- maximum rec size: 165
Setting SPACE=1 in SYS_INDEXES for TABLE_ID = 17
Page_id: 11, next page_id: 4294967295
Record position: 65
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 50 7 2 0 0 0 0
TABLE_ID: 3798561113125514496
SPACE: 1768842857
Next record at offset: 8C
Record position: 8C
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47
TABLE_ID: 11
SPACE: 0
Next record at offset: CE
Record position: CE
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48
TABLE_ID: 11
SPACE: 0
Next record at offset: 111
Record position: 111
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 36 40 44 48
TABLE_ID: 11
SPACE: 0
Next record at offset: 154
Record position: 154
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 35 39 43 47
TABLE_ID: 12
SPACE: 0
Next record at offset: 22C
Record position: 22C
Checking field lengths for a row (SYS_INDEXES): OFFSETS: 15 8 16 22 29 44 48 52 56
TABLE_ID: 17
SPACE: 2
Updating SPACE(0x00000001 , 0x01000000) for TABLE_ID: 17
sizeof(s)=4
Next record at offset: 74
SYS_INDEXES is updated successfully
Step 7 : 使用percona recovery tool 重新checksum ibdata
重复执行以下命令，直到程序没有输出为止。
shell> /root/install/percona-data-recovery-tool-for-innodb-0.5/innochecksum -f /xfs/mysql3321/ibdata1
page 8 invalid (fails old style checksum)
page 8: old style: calculated = 0xF4AD74CB; recorded = 0xEECB309D
fixing old checksum of page 8
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x6F0C29B4; recorded = 0x3D02308C
fixing new checksum of page 8
page 11 invalid (fails old style checksum)
page 11: old style: calculated = 0x3908087C; recorded = 0xF9E8D30C
fixing old checksum of page 11
page 11 invalid (fails new style checksum)
page 11: new style: calculated = 0xB26CFD77; recorded = 0xDB25D39D
fixing new checksum of page 11
Step 8 : 启动mysql服务
shell> service mysqld3321 startbitsCN.com
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to create a new simulator in the Lightning Simulator - How to create a new simulator in the Lightning Simulator
Mar 05, 2024 pm 03:04 PM
Recently, some users who have just downloaded and contacted the lightning simulator software asked the editor how to create a new simulator in the lightning simulator? The following content brings the method of creating a new simulator in the lightning simulator. Let us take a look below. Find the lightning simulator software. Double-click to open the Thunderbolt multi-opener. Click New/Copy Simulator. Click New Simulator. You can get a new simulator. Find the lightning simulator software, double-click to open the lightning multi-opener, click New/Copy Simulator, click New Simulator, and you will get a new simulator.
How to create a new Word document on your computer
Dec 27, 2023 pm 10:05 PM
There are many ways to create a new word document. We can right-click the desktop and click New Doc Document or Docx Document. The specific methods are almost the same. Let’s take a look at them together. How to create a new word document using a computer: win11: 1. First, we right-click "New". 2. Then select the "docx or doc" file. 3. Finally, the creation can be completed. win10: 1. We right-click and select "New". 2. Then select one of the two documents. 3. Finally, the creation is completed. win7: 1. First, right-click and select "New". 2. Then select the word document. 3. Finally, the new creation can be completed.
How to create a gray version of a new spanned volume on Win10 disk
Jul 02, 2023 am 08:13 AM
What should I do if the newly created spanned volume on Win10 disk is grayed out? Sometimes there are multiple unused spaces in our hard disk. We can merge them and put them in a logical volume, so that the space on multiple hard disks can be used more efficiently. This is a spanned volume. However, some netizens said that their win10 system had problems when creating a new spanned volume. They found that the new spanned volume on the disk was not grayed out. What happened? How to create a new spanned volume? The editor below will teach you the correct method of creating a new cross-zone volume. Gray solution steps for creating a spanned volume on Win10 disk First, as shown in the figure below, the space on the two hard disks can use the same volume, and just create a spanned volume. Then the reason why the spanned volume cannot be grayed out may be due to the following factors: 1. If it contains a span
Solve the problem that there is no 'New' option on the right click of Win10
Jan 05, 2024 am 09:45 AM
When using win10 system, some friends find that there is no new option in the right-click menu when they right-click the mouse on the desktop. In fact, they only need to add an option to it through the registry to right-click and create a new one. What to do if there is no New option when right-clicking on Windows 10: 1. Press the key combination + Open Run, enter and click OK to open the Registry Editor. 2. Then expand the folders:. 3. Then find the folder. If there is no such folder, right-click the folder-- and name it. 4. After opening the file in the new folder, we change this numerical data to. 5. Then restart the computer to solve the problem.
Detailed method for creating a new monthly report on Enterprise WeChat
Mar 25, 2024 pm 05:36 PM
1. Open Enterprise WeChat. 2. Click the [App] icon button on the left. 3. Click the [Report], [New], [Monthly Report] options. 4. Then you can see that you need to enter this month’s work, etc. 5. Enter the content of this month’s work. You may or may not fill in the rest, and click the [Submit] button. 6. Finally, you can see the details of your monthly report.
Step-by-step guide for creating folders on Realme mobile phone
Mar 23, 2024 pm 03:51 PM
Step-by-Step Guide to Creating Folders on Realme Mobile Phones In our daily lives, we use our phones to store a variety of files, including photos, videos, documents, and more. However, as time goes by, the number of files on your phone may increase, causing insufficient memory on your phone or making it difficult to manage files. In order to better organize files and improve the efficiency of mobile phone use, the establishment of mobile phone folders has become particularly important. Realme Mobile is a popular smartphone brand with a powerful system and diverse features. Creating folders on Realme phones can help users better organize files
Win11 new document tutorial
Dec 30, 2023 pm 07:59 PM
In win11, we can add articles or notes by creating a new document, but some friends don’t know how to create a new document in win11. In fact, just right-click on the blank space. How to create a new document in win11: 1. Right-click on the blank space of the desktop or explorer and select "New" 2. Then select "Text Document" 3. After the new creation is completed, you can see your newly created text document. 4. We can also choose to "create" a "doc/docx document" 5. After the creation is completed, double-click the document to open it.
How to create a new sub-account on Feige Customer Service Workbench - How to create a new sub-account on Feige Customer Service Workbench
Mar 04, 2024 pm 04:58 PM
Many people use Feige Customer Service Workbench when working, so do you know how to create a new sub-account on Feige Customer Service Workbench? Below, the editor will bring you how to create a new sub-account on Feige Customer Service Workbench. If you are interested Let’s take a look below. 1. First come to the main page, click on the store on the left, and select the store management option. 2. Then click the sub-account management option in the drop-down option. 3. Finally, in the Account Management Office, click Create a new sub-account.
