1. Data Loss After MySQL Restart

Data Loss After MySQL Restart


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

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

Let’s take a healthy MySQL instance with an 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 the 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 the beginning 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 – the first one is that MySQL works with, and the another one is visible to all other processes.

How Do You Think Backups Would Work

What is interesting, Xtrabackup successfully takes a backup copy 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  that 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 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.
Previous Post Next Post