InnoDB Recovery during Directory-Loss (ibdata)

Today I learned a big lesson about backup, recovery and handling databases propery. When modifying my MariaDB for supporting larger scale-sets, due to integrating a ticket-system for my services, I needed to remove several files from the database, as the required configuration changes are not reflected otherwise:

• ibdata
  Part of the system table space, containing the data dictionary, UNDO space, rollback segments (having undo slots), insert buffers (for changes to secondary non-unique indexes) and the double write buffer.

Most documentation only reference the undo space, which was not important for my actions. However, the data dictionary is. The regular structure in mariadb is as followed:

/var/lib/
└── mysql/                  # database data-folder
    ├── mysql/              # system database
    ├── performance_schema/ 
    ├── test/               # example database
    └── <your_database>/
        └── user.frm        # table structure file
        └── user.idb        # table data file, if configured (current)
              OR
        └── user.MYD        # table data file, if configured (legacy)
        └── user.MYI        # table data file, if configured (legacy)

*.MYD and *.MYI (MyISAM tables) are legacy and have been replaced by .idb with MariaDB 10.1.0 in october 2025. Nowadays you can activate it by setting innodb_file_per_table = 0 in your [mysqld] setting. However, that’s legacy. Nowadays innodb_file_per_table = 1 is set by default, having idb files in place.

Following procedure will only work with innodb_file_per_table = 1

The problem

When removing mysql/ibdata, the data directory is removed, causing InnoDB unable to read any of your databases, even though all of your data is still physically available, but not readable. As data is stored in binary-format inside the files, you also cannot extract them from there. How to address that?

The idea

As the data directory is missing, you can recovery the data, once the directory is re-created. This gives multiple options:

1. Use your backup
2. Cre-create the table structure

While option 1 is easy, let’s have a look for option 2, e.g. if you don’t have automated backups yet and miss some data-sets. That’s what you want to avoid at all costs.

The approach

1. Create a backup of your affected databases
   You don’t want to lose any data because of following copy-paste activities in your terminal. These kind of issues happen far too quickly. For instances copy your database from /var/lib/mysql/myDatabase to /opt/backup/myDatabase.
2. Drop the current database and re-create it
   You still have your backup, but current files are unaccessable and references of the data directory should be recovered to known locations.
3. Re-Create the schema and table structure
   You might use your init-scripts for re-creating the table structure. If you use standard-software, a fresh installation might generate the required structure automatically.
4. Export the table structure and remove foreign keys and constraints
   At first we want to access the data-sets. Therefore, any foreign keys and constraints will block data-restore due to dependencies. Therefore, we need to remove these from the table structure elements.
5. Clear the schema
   Once you have the structure, clear the schema for keeping it empty. You can also re-create the schema a second time for a fresh start of the schema.
6. Re-Create the table structure in the cleared schema
   Re-create the table structure from your structure export, having no foreign keys, constraints etc.
7. Recover your data (here comes the magic)
   • Execute a following statement for each table (only works without foreign keys and constraints):
     ALTER TABLE myDatabase.<tableName> DISCARD TABLESPACE;
   • Copy your backup *.idb files (e.g. /opt/backup/myDatabase) back into the database folder /var/lib/mysql/myDatabase. Do not copy the .frm files, as the backups structure is broken.
   • Execute a following statement for each table:
     ALTER TABLE myDatabase.<tableName> IMPORT TABLESPACE;
8. Create a recovery schema
   Create a new schema and copy (structure and data) into the new schema.
9. Clear the schema
   Once again, clear the original schema. You can also re-create the schema a third time for a fresh start.
10. Re-Create the schema and table-structure (like ③)
    Use your init-scripts or standard-software for re-creating the table structure, incl. foreign keys, constraints etc.
11. Remove access from other sources to the schema
    Any other software or user must not have access for the next steps for having a proper restore.
12. Delete all data inside the tables
    e.g. by using the empty-function in phpMyAdmin.
13. Copy the data from your recovery schema
    Now you have the proper structure and current data restored.

This way you can fully recovery your data, and – more important – think about proper backups! We do offer database services as part of our portfolio, which includes regular backups. Contact us, in case you’re interested in our solutions!