Bobcares

WeSupport

Call Us! 1-800-383-5193
Call Us! 1-800-383-5193
Call Us! 1-800-383-5193

Need Help?

Emergency Response Time custom

Our experts have had an average response time of 11.06 minutes in March 2021 to fix urgent issues.

We will keep your servers stable, secure and fast at all times for one fixed price.

Recover orphan innodb database from ibd file – How we do it

by | Apr 5, 2021

Are you looking for steps to recover the orphan InnoDB database from the ibd file? We can help you do it.

Here at Bobcares, we have seen several such InnoDB-related queries as part of our Server Management Services for web hosts and online service providers.

Today we will take a look at how to recover the orphan InnoDB database from the .ibd file

 

When Orphan InnoDB database incident occur

Now let’s see when does the Orphan InnoDB database incident happens:

  • If a user accidentally removes the ibdata1 file. (mostly in /var/lib/mysql/ibdata1).
  • If ibdata file is corrupted.
  • Enabling “innodb_file_per_table” in my.cnf beforehand
  • The database folder (/var/lib/mysql/database_name/) and files inside the folder remains untouched.

By default, if the “innodb_file_per_table” is enabled then the InnoDB database is fully dependent on the ibdata1 file.

Note: Losing ibdata1 without “innodb_file_per_table” enabled basically means that we have lost everything.

When “innodb_file_per_table” is enabled in my.cnf, we can see 2 files format in the database folder:

.frm file (internal data dictionary) and .ibd file (database structure information)

If ibdata1 is accidentally removed then the InnoDB database will refuse to start due to its inability to refer to the correct data block in ibdata.

So we need to make the .ibd file and ibdata1 sync again.

 

How to recover orphan InnoDB database from ibd file

Here are the steps to sync the .ibd file and ibdata1.

1. First, we need to recreate the table structure exactly the same as the corrupted one, which is stored in the .frm file.

We need to restart the MySQL service to recreate ibdata1, and then copy the database folder to another directory as a backup.

cp -axvRfp /var/lib/mysql/database_name /home/backup/

2. Next, we login to MySQL using the terminal by running the below command.

[root@localhost /]# mysql -uroot -p

3. Then we create a dummy database with the same name.

mysql> create database database_name;
Query OK, 1 row affected (0.00 sec)

4. After that, we connect to the dummy database

mysql> use database_name

5. Then we create a dummy table with the same name as the corrupted table, with random structure, and enable InnoDB engine.

mysql> create table table_name (id int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

6. We describe the table to check its structure.

mysql> desc table_name;

7. Now, we stop the MySQL service, and copy the .frm file from the backup directory to /var/lib/mysql/database_name/ to replace the .frm file inside. For that, we run the below commands.

/etc/init.d/mysql stop
cp -ap /home/backup/database_name/table_name.frm /var/lib/mysql/database_name/

8. We start the MySQL service, connect to the database, and flush tables.

/etc/init.d/mysql start
mysql -uroot -p

mysql> use database_name
Database changed

flush tables;

9. Then we describe table again and we will be able to see the losing structure of the table

desc table_name;

10. However, the table cannot work without a proper CREATE command by identifying each structure. So to review the CREATE query of the corrupted table, we run the below command.

show create table table_name;

11. The query can now be referring to easily. So we COPY the create table line, drop the dummy table and recreate the table with the recovered structure query.

mysql > drop table table_name;

12. Now to recover the ibd file, we need a innodb recovery tools from here. We download the tools, extract it, and install it.

cd /usr/local
wget http://www.ipfusions.com/setup/percona-data-recovery-tool-for-innodb-0.5.tar.gz
tar xvfz percona-data-recovery-tool-for-innodb-0.5.tar.gz
cd percona-data-recovery-tool-for-innodb-0.5
cd mysql-source
./configure
cd ..
make

13. We then stop mysql service again, copy the .ibd file from backup directory to /var/lib/mysql/database_name/ and replace the existing dummy .ibd file.

/etc/init.d/mysql stop
cp -ap /home/backup/database_name/table_name.ibd /var/lib/mysql/database_name/

14. We run the ibdconnect tools of percona by referring to the query below:

[root@/]# cd /usr/local/percona-data-recovery-tool-for-innodb-0.5/
[root@localhost percona-data-recovery-tool-for-innodb-0.5]# pwd
/usr/local/percona-data-recovery-tool-for-innodb-0.5
[root@localhost percona-data-recovery-tool-for-innodb-0.5]# ls
check_data.c ibdconnect INSTALL print_data.c
constraints_parser ibdconnect.c lib split_dump.pl
constraints_parser.c include Makefile tables_dict.c
create_defs.pl incrementalupdate.c mysql-source
docs innochecksum page_parser
fetch_data.sh innochecksum.c page_parser.c
-o full location of your ibdata file
-f full location of your .ibd file
-d fullname of database name
-t fullname of table name
ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/database_name/table_name.ibd -d database_name -t table_name

15. After the ibd-reconect job, we run a checksum on the ibdata1 using percona checksum tool

innochecksum /var/lib/mysql/ibdata1
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x239090D5; recorded = 0x56764549
[root@localhost include]# /usr/local/percona-data-recovery-tool-for-innodb-0.5/innochecksum /var/lib/mysql/ibdata1
page 8 invalid (fails new style checksum)
page 8: new style: calculated = 0x239090D5; recorded = 0x56764549
[root@localhost include]# /usr/local/percona-data-recovery-tool-for-innodb-0.5/innochecksum /var/lib/mysql/ibdata1
[root@localhost include]

We run it several times until no error message pops out further.

16. Finally, we start MySQL service.

/etc/init.d/mysql start

[Need any further assistance with InnoDB-related queries? – We’re available 24*7]

 

Conclusion

Today, we saw how our Support Engineers recover the orphan InnoDB database from the ibd file.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF