About restoring an InnoDB MySQL Database by “just copying files across”

Restoring a database was never going to be fun. When you’re working with MySQL you need quite a bit of background before you can even begin.

I’m listing some things to understand and to help you rate your chances of success.

To start, let’s be clear: If you don’t use mysqldump, don’t think just restoring the files is going to work. You were supposed to back up your data using the standard tool, not some “clever way”. So spoiler alert, chances of recovery and recovery speed if you’ve used MySQL dump is very good. If not, set aside a few hours or more to make scant progress.

On one very rare occasion I’ve noticed that mysqldump didn’t backup the entire database. It backed up tables from A to M, but then when I tried to restore I didn’t see anything from N to Z. I still remember this so vividly even though it’s more than a decade ago.

Of course, this was an anomaly, but the pros in the backup space will let you know no plan is complete without restore testing. So if you’re working at the bank or your data is valuable, remember restore testing, okay?

Now, more homework when restoring:

Determine your storage engine. This isn’t as easy as it sounds, but InnoDB is used by default these dates. The other option is MyISAM. They have different recovery procedures. In the interest of time and to keep things simple, this article is just about restoring InnoDB.

Why can’t I just restore the files?

Here is the scenario. You look in the directory, and you only see one files, e.g. migrations.idb. You gut says just stop MySQL, copy all the files across, and start up MySQL again.

Here is why this won’t work:

This is classic server admin confusion. The problem is that you can’t just copy .ibd files back and expect them to work – there’s more to MySQL’s storage system.

Here’s what’s happening:

InnoDB uses two key components:

1. .ibd files – These contain the actual table data (one per table)
2. InnoDB system tablespace and data dictionary – This tracks metadata about all tables

When you just copy .ibd files back, MySQL doesn’t know they exist because the data dictionary wasn’t updated.

Okay, so moving on from there, what will happen if you try?

The approach will be different depending on your backup type. If you only have physical files, we’ll need to use ALTER TABLE ... IMPORT TABLESPACE or potentially restore the entire data directory properly.

So here would be the output if you just tried copying the files across:

tail -f /var/log/mysql/error.log

2025-09-06T09:19:52.014068Z 13 [ERROR] [MY-012095] [InnoDB] [FATAL] Tablespace id is 529 in the data dictionary but in file ./database-name/migrations.ibd it is 52! 2025-09-06T09:19:52.014195Z 13 [ERROR] [MY-013183] [InnoDB] Assertion failure: fil0fil.cc:2549:ib::fatal triggered thread 130757059286720

This is a tablespace ID mismatch error.

What’s happening:

• MySQL’s data dictionary expects the migrations table to have tablespace ID 529
• But the actual .ibd file contains tablespace ID 52
• This mismatch causes MySQL to crash when trying to access the table

This confirms that you can’t just copy .ibd files between MySQL instances – the tablespace IDs must match between the data dictionary and the physical files.

We have two main options to fix this:

Option 1: Full data directory restore (Recommended – cleanest approach)

• Replace the entire /var/lib/mysql directory with your backup
• This ensures data dictionary and tablespace IDs are consistent

Option 2: Import individual tablespaces (More complex, table by table)

• Drop and recreate each table structure, then import the tablespace

I’m stopping this article here. In our case we opted to restore the whole of MySQL. It’s a radical way to get things going but a better price to pay in terms of time when the user never bothered to do a proper backup.

To the authors of MySQL / MariaDB from an old school sysadmin:

Wouldn’t it have been nice when this error is encountered, instead of a server violation, some sane instructions are printed out or some sane easy to use recovery tool is written to match up mismatched IDs again? Surely this will make things a bit more logical?