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@localhostmysql]# rm -f /var/lib/mysql/ibdata1
[Root@localhostmysql]#
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@localhostmysql]# cp ibdata1.empty /var/lib/mysql/ibdata1
[Root@localhostmysql]#
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@localhostmysql]# ll /proc/`pidof mysqld`/fd | grep ibdata
Lrwx------. 1 mysql mysql 64 Feb 13 18:42 3 -> /var/lib/mysql/ibdata1 (deleted)
[Root@localhostmysql]#
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@localhost2016-02-13_18-51-40]# grep sakila ibdata1
[Root@localhost2016-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
Monitor your MySQL.
Take backups regularly and verify them.
Keep MySQL datafiles on a separate dedicated partition.
DBRECOVER Recovery Options
For Oracle incidents, start with the DBRECOVER for Oracle trial to verify table visibility, row previews, and export readiness on copied datafiles. For MySQL and InnoDB incidents, DBRECOVER for MySQL is free software and can inspect.ibd files, ibdata1, and database directories locally.
When the case is urgent, preserve the original files first, work from copies, and contact paid emergency support with the database version, platform, error messages, file list, and recovery objective.