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:

mysql> show tables like 'actor';
+--------------------------+
| Tables_in_sakila (actor) |
+--------------------------+
| actor                    |
+--------------------------+
1 row in set (0.00 sec)

mysql> select * from actor;
ERROR 1146 (42S02): Table 'sakila.actor' doesn't exist
mysql>

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:

[root@localhost mysql]# rm -f /var/lib/mysql/ibdata1
[root@localhost mysql]#

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

mysql> select * from actor limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> insert into actor(first_name, last_name) values('Aleksandr', 'Kuzminsky');
Query OK, 1 row affected (0.00 sec)

mysql>

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

[root@localhost mysql]# cp ibdata1.empty /var/lib/mysql/ibdata1
[root@localhost mysql]#

From MySQL perspective nothing has changed:

mysql> insert into actor(first_name, last_name) values('Ovais', 'Tariq');
Query OK, 1 row affected (0.00 sec)
mysql> select * from actor order by actor_id desc limit 4;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      202 | Ovais      | Tariq     | 2016-02-13 18:37:56 |
|      201 | Aleksandr  | Kuzminsky | 2016-02-13 18:35:31 |
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
|      199 | JULIA      | FAWCETT   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
4 rows in set (0.00 sec)

mysql>

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:

[root@localhost mysql]# ll /proc/`pidof mysqld`/fd | grep ibdata
lrwx------. 1 mysql mysql 64 Feb 13 18:42 3 -> /var/lib/mysql/ibdata1 (deleted)
[root@localhost mysql]#

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:

[root@localhost ~]# innobackupex .
...
[01] Copying ./sakila/actor.ibd to /root/2016-02-13_18-51-40/sakila/actor.ibd
[01]        ...done
[01] Copying ./sakila/address.ibd to /root/2016-02-13_18-51-40/sakila/address.ibd
[01]        ...done
...

xtrabackup: Creating suspend file '/root/2016-02-13_18-51-40/xtrabackup_log_copied' with pid '18223'
xtrabackup: Transaction log of lsn (1600949) to (1600949) was copied.
160213 18:51:44  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/root/2016-02-13_18-51-40'
160213 18:51:44  innobackupex: Connection to database server closed
160213 18:51:44  innobackupex: completed OK!

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

[root@localhost 2016-02-13_18-51-40]# grep sakila ibdata1
[root@localhost 2016-02-13_18-51-40]#

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:

[root@localhost ~]# /usr/lib64/nagios/plugins/pmp-check-mysql-deleted-files
CRIT open but deleted files: /var/lib/mysql/ibdata1

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.

The post Data loss after MySQL restart appeared first on Backup and Data Recovery for MySQL.