Not so long ago I had a customer who experienced data loss after MySQL restart. It was really puzzling. MySQL was up & running for many months, but after the customer restarted MySQL server all tables have gone. The tables were still visible in SHOW TABLES output, but they were not readable:

To understand what’s happened let make some experiments (WARNING: Don’t do it on production or with valuable data).

Let’s take a healthy MySQL instance with installed sakila database.

While MySQL is running let’s remove ibdata1:

Even though ibdata1 is deleted the tables are readable and writable:

Now let’s put some other ibdata1 instead of the original one. I saved an empty ibdata1 for this purpose.

From MySQL perspective nothing has changed:

MySQL still works correctly, but obviously it won’t when we restart MySQL, because ibdata1 is empty now.

But why does MySQL work after we deleted the ibdata1? Because MySQL keeps ibdata1 open:

Right, MySQL opens ibadat1 at start and never closes it until MySQL stops. You can delete the file but it will be still accessible to processes that have open file descriptors on this file. MySQL can work normally and doesn’t really notice that the file is actually deleted.

There are two ibdata1 after we overwrote the original ibdata1 – one is that MySQL works with and another one is visible to all other processes.

How do you think backups would work

What is interesting, Xtrabackup successfully takes a backup from this instance:

But this backup copy is not usable! How often do you verify your backups, by the way?

Logical backups like mysqldump or mydumper would work fine.

How to prevent problems like this

Percona developed Nagios plugins for MySQL, fortunately they detect this problem:

Lessons learned

You might wonder how the story ended for the customer? Well, he was running MySQL with innodb_file_per_table=OFF, so not only a dictionary was in ibdata1 but the data, too. We took a disk image, found InnoDB pages on it and recovered the tables. I don’t remember if all important data survived, but the database was pretty damaged.

Moral of this story:

  1. Monitor your MySQL.
  2. Take backups regularly and verify them.
  3. Keep MySQL data files on a separate dedicated partition.