Restoring MySQL Tables Without a Database Backup

There may be no worse feeling than needing a database backup and not having one. It ranks right up there with running a DELETE statement and missing the WHERE clause. God help you if you if you suffer both of those together. If you come across that situation with a MySQL database, you might be able to recover what you need.

When Life Gives You Lemons

Let’s imagine a situation where we need a MySQL database table restored but don’t have a backup. While we don’t have a database backup, what if we have a file system backup that includes the IBD file for the table? We can make that work to get our database up and running.

We can’t just copy and paste the IBD file in and carry on. We’ll need to take a few extra steps. For this example, our table name will be film_category in the sakila sample database.

Our table that should have data is empty:

Before doing work with the backup IBD file, we’ll first want to discard the tablespace for film_inventory by running this statement against our database:

ALTER TABLE film_category DISCARD TABLESPACE;

Then, we can copy our “good” IBD file into our MySQL data directory. In my case, it’s “C:\ProgramData\MySQL\MySQL Server 8.0\Data\sakila”

Finally, our last step will be to change “DISCARD” to “IMPORT” from our previous statement and run the following:

ALTER TABLE film_category IMPORT TABLESPACE;

We can run our SELECT again and see the data:

It’ll Do

It feels dirty to do, but keep these steps in mind in case you’re ever in this type of situation. It’s obviously not as good as having a database backup to restore from, but it’s better than nothing.

On a side note, I hope everyone has recovered from the CrowdStrike incident (or better yet, not been affected). I’ve had a string of bad luck when it comes to updates causing me problems, but didn’t have any issues this time around. When the first thing I saw that morning was a text from my uncle, who doesn’t own a computer, asking, “Is the computer problem today having any effect on you?” I knew it must be significant.

Thanks for reading!