Recover a crashed MySQL or MariaDB InnoDB database from ibd files

Written by Claudio Kuenzler - 2 comments

Published on December 13th 2017 - Listed in Database DB MySQL MariaDB

---

It happened. Defect power supply. Zap. Dark.

The database (MariaDB 10.0) running on that particular server (Debian Jessie) suffered data corruption and data loss. A million (I'm exhausting this fact but in 10+ years on Linux I have never seen so many filesystem errors) ext4 file system errors were detected and most of them repaired. But for the database all hope was lost. Simply too much corruption occurred to recover the databases after a start of MariaDB.

At least the daily database dumps could be used to restore all databases. But that also meant that all changes since the dump was taken were lost. One particular database contained a lot of changes exactly in that time range - a lot of effort had been done on this one database. So I was looking for a way to get the data from the moment of the crash back online.

This particular database (wordpress) is not heavily used, so there might be a chance that the InnoDB files (idb) are still usable. Most of the Internet's howto's how to recover a crashed MySQL/MariaDB database simply point you to a restore from a previously backed up database dump. Some articles I found hinted that the files can simply be put into /var/lib/mysql/wordpress/ while the database server is stopped and a restart should recover them. That's just bullcrap as a mysqlcheck reveals:

root@ /var/lib/mysql # mysqlcheck --all-databases -p
Enter password:
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos
Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status   : Operation failed
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats
Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
status   : Operation failed
mysql.innodb_table_stats
Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
status   : Operation failed
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
wordpress.np_commentmeta
Error    : Table 'wordpress.np_commentmeta' doesn't exist in engine
status   : Operation failed
wordpress.np_comments
Error    : Table 'wordpress.np_comments' doesn't exist in engine
status   : Operation failed
wordpress.np_layerslider
Error    : Table 'wordpress.np_layerslider' doesn't exist in engine
status   : Operation failed
wordpress.np_layerslider_revisions
Error    : Table 'wordpress.np_layerslider_revisions' doesn't exist in engine
status   : Operation failed
wordpress.np_links
Error    : Table 'wordpress.np_links' doesn't exist in engine
status   : Operation failed
wordpress.np_options
Error    : Table 'wordpress.np_options' doesn't exist in engine
status   : Operation failed
wordpress.np_postmeta
Error    : Table 'wordpress.np_postmeta' doesn't exist in engine
status   : Operation failed
wordpress.np_posts
Error    : Table 'wordpress.np_posts' doesn't exist in engine
status   : Operation failed
wordpress.np_term_relationships
Error    : Table 'wordpress.np_term_relationships' doesn't exist in engine
status   : Operation failed
wordpress.np_term_taxonomy
Error    : Table 'wordpress.np_term_taxonomy' doesn't exist in engine
status   : Operation failed
wordpress.np_termmeta
Error    : Table 'wordpress.np_termmeta' doesn't exist in engine
status   : Operation failed
wordpress.np_terms
Error    : Table 'wordpress.np_terms' doesn't exist in engine
status   : Operation failed
wordpress.np_usermeta
Error    : Table 'wordpress.np_usermeta' doesn't exist in engine
status   : Operation failed
wordpress.np_users
Error    : Table 'wordpress.np_users' doesn't exist in engine
status   : Operation failed
wordpress.np_yoast_seo_links
Error    : Table 'wordpress.np_yoast_seo_links' doesn't exist in engine
status   : Operation failed
wordpress.np_yoast_seo_meta
Error    : Table 'wordpress.np_yoast_seo_meta' doesn't exist in engine
status   : Operation failed

But eventually I came across a very interesting answer on dba.stackexchange.com. The answer came from user "carment" and this is seriously one of the best and most important howto's I ever found on a MySQL/MariaDB topic: She described how to restore the database from the frm and ibd files only.

First of all you need the command "mysqlfrm", which is part of the packagemysql-utilities. Install that package:

root@ ~ # apt-get install mysql-utilities

I placed the database's files from the time of the crash into /tmp/wordpress:

root@ ~ # ll /tmp/wordpress
total 23668
-rw-rw---- 1 mysql mysql       61 Dec  7 10:51 db.opt
-rw-rw---- 1 mysql mysql     3033 Dec  7 10:55 np_commentmeta.frm
-rw-rw---- 1 mysql mysql   131072 Dec 12 10:58 np_commentmeta.ibd
-rw-rw---- 1 mysql mysql     6685 Dec  7 10:55 np_comments.frm
-rw-rw---- 1 mysql mysql   180224 Dec 12 11:02 np_comments.ibd
-rw-rw---- 1 mysql mysql     2047 Dec  7 11:07 np_layerslider.frm
-rw-rw---- 1 mysql mysql    98304 Dec  7 11:07 np_layerslider.ibd
-rw-rw---- 1 mysql mysql     1041 Dec  7 11:07 np_layerslider_revisions.frm
-rw-rw---- 1 mysql mysql    98304 Dec  7 11:07 np_layerslider_revisions.ibd
-rw-rw---- 1 mysql mysql     8105 Dec  7 10:55 np_links.frm
-rw-rw---- 1 mysql mysql   114688 Dec  7 10:55 np_links.ibd
-rw-rw---- 1 mysql mysql     2365 Dec  7 10:55 np_options.frm
-rw-rw---- 1 mysql mysql   507904 Dec 12 17:41 np_options.ibd
-rw-rw---- 1 mysql mysql     3030 Dec  7 10:55 np_postmeta.frm
-rw-rw---- 1 mysql mysql  9437184 Dec 12 17:42 np_postmeta.ibd
-rw-rw---- 1 mysql mysql     7223 Dec  7 10:55 np_posts.frm
-rw-rw---- 1 mysql mysql 12582912 Dec 12 17:42 np_posts.ibd
-rw-rw---- 1 mysql mysql     3030 Dec  7 10:55 np_termmeta.frm
-rw-rw---- 1 mysql mysql   131072 Dec  7 10:55 np_termmeta.ibd
-rw-rw---- 1 mysql mysql     1496 Dec  7 10:55 np_term_relationships.frm
-rw-rw---- 1 mysql mysql   114688 Dec 12 17:03 np_term_relationships.ibd
-rw-rw---- 1 mysql mysql     3592 Dec  7 10:55 np_terms.frm
-rw-rw---- 1 mysql mysql   131072 Dec 12 17:07 np_terms.ibd
-rw-rw---- 1 mysql mysql     2209 Dec  7 10:55 np_term_taxonomy.frm
-rw-rw---- 1 mysql mysql   131072 Dec 12 17:07 np_term_taxonomy.ibd
-rw-rw---- 1 mysql mysql     3031 Dec  7 10:55 np_usermeta.frm
-rw-rw---- 1 mysql mysql   131072 Dec 12 17:07 np_usermeta.ibd
-rw-rw---- 1 mysql mysql     6965 Dec  7 10:55 np_users.frm
-rw-rw---- 1 mysql mysql   147456 Dec  7 17:51 np_users.ibd
-rw-rw---- 1 mysql mysql     2585 Dec  7 11:08 np_yoast_seo_links.frm
-rw-rw---- 1 mysql mysql   114688 Dec 12 17:17 np_yoast_seo_links.ibd
-rw-rw---- 1 mysql mysql     1015 Dec  7 11:08 np_yoast_seo_meta.frm
-rw-rw---- 1 mysql mysql    98304 Dec 12 17:41 np_yoast_seo_meta.ibd

As you can see, the table files are all there. But at the point I didn't know, if I there was any data corruption on the files.
The frm files contain the structure of the table, the ibd files contain the data itself.

By using the mysqlfrm command, a "parallel" MySQL instance is started up reading the information from the frm file (given by the path at the end of the command). Before you do that, make sure you're in a folder the mysql user can write to (e.g. /tmp) because it is advised to not run mysqlfrm's parallel MySQL instance as root user (that's why I added --user to the command):

root@ ~ # cd /tmp/

Now run mysqlfrm and make sure you don't use the "real" MySQL port of the already running server (here I chose 3307):

root@ /tmp # mysqlfrm --user=mysql --server=root:maria@localhost --port=3307 /tmp/wordpress/np_commentmeta.frm -vvv
# Source on localhost: ... connected.
# Checking read access to .frm files
# Creating a temporary datadir = /tmp/c1205314-ceba-464c-b1d9-65e6240dbf21
# Spawning server with --user=mysql.
# Starting the spawned server on port 3307 ...
# Cloning the MySQL server located at /usr.
# Configuring new instance...
# Locating mysql tools...
# Location of files:
#                       mysqld: /usr/sbin/mysqld
#                   mysqladmin: /usr/bin/mysqladmin
#      mysql_system_tables.sql: /usr/share/mysql/mysql_system_tables.sql
# mysql_system_tables_data.sql: /usr/share/mysql/mysql_system_tables_data.sql
# mysql_test_data_timezone.sql: /usr/share/mysql/mysql_test_data_timezone.sql
#         fill_help_tables.sql: /usr/share/mysql/fill_help_tables.sql
# Setting up empty database and mysql tables...
171213  9:03:28 [Note] /usr/sbin/mysqld (mysqld 10.0.30-MariaDB-0+deb8u2) starting as process 3211 ...
171213  9:03:28 [Note] InnoDB: Using mutexes to ref count buffer pool pages
171213  9:03:28 [Note] InnoDB: The InnoDB memory heap is disabled
171213  9:03:28 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
171213  9:03:28 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
171213  9:03:28 [Note] InnoDB: Compressed tables use zlib 1.2.8
171213  9:03:28 [Note] InnoDB: Using Linux native AIO
171213  9:03:28 [Note] InnoDB: Using CPU crc32 instructions
171213  9:03:28 [Note] InnoDB: Initializing buffer pool, size = 128.0M
171213  9:03:28 [Note] InnoDB: Completed initialization of buffer pool
171213  9:03:28 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
171213  9:03:28 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
171213  9:03:28 [Note] InnoDB: Database physically writes the file full: wait...
171213  9:03:28 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
171213  9:03:28 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
171213  9:03:29 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
171213  9:03:29 [Warning] InnoDB: New log files created, LSN=45781
171213  9:03:29 [Note] InnoDB: Doublewrite buffer not found: creating new
171213  9:03:29 [Note] InnoDB: Doublewrite buffer created
171213  9:03:29 [Note] InnoDB: 128 rollback segment(s) are active.
171213  9:03:29 [Warning] InnoDB: Creating foreign key constraint system tables.
171213  9:03:29 [Note] InnoDB: Foreign key constraint system tables created
171213  9:03:29 [Note] InnoDB: Creating tablespace and datafile system tables.
171213  9:03:29 [Note] InnoDB: Tablespace and datafile system tables created.
171213  9:03:29 [Note] InnoDB: Waiting for purge to start
171213  9:03:29 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.35-80.0 started; log sequence number 0
171213  9:03:29 [Note] Plugin 'FEEDBACK' is disabled.
171213  9:03:30 [Note] InnoDB: FTS optimize thread exiting.
171213  9:03:30 [Note] InnoDB: Starting shutdown...
171213  9:03:30 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
171213  9:03:32 [Note] InnoDB: Shutdown completed; log sequence number 1616697
# Starting new instance of the server...
# Startup command for new server:
/usr/sbin/mysqld --no-defaults --datadir=/tmp/c1205314-ceba-464c-b1d9-65e6240dbf21 --tmpdir=/tmp/c1205314-ceba-464c-b1d9-65e6240dbf21 --pid-file=/tmp/c1205314-ceba-464c-b1d9-65e6240dbf21/clone.pid --port=3307 --server-id=101 --basedir=/usr --socket=/tmp/c1205314-ceba-464c-b1d9-65e6240dbf21/mysql.sock --user=mysql
# Testing connection to new instance...
171213  9:03:32 [Note] /usr/sbin/mysqld (mysqld 10.0.30-MariaDB-0+deb8u2) starting as process 3237 ...
171213  9:03:32 [Note] InnoDB: Using mutexes to ref count buffer pool pages
171213  9:03:32 [Note] InnoDB: The InnoDB memory heap is disabled
171213  9:03:32 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
171213  9:03:32 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
171213  9:03:32 [Note] InnoDB: Compressed tables use zlib 1.2.8
171213  9:03:32 [Note] InnoDB: Using Linux native AIO
171213  9:03:32 [Note] InnoDB: Using CPU crc32 instructions
171213  9:03:32 [Note] InnoDB: Initializing buffer pool, size = 128.0M
171213  9:03:32 [Note] InnoDB: Completed initialization of buffer pool
171213  9:03:32 [Note] InnoDB: Highest supported file format is Barracuda.
171213  9:03:32 [Note] InnoDB: 128 rollback segment(s) are active.
171213  9:03:32 [Note] InnoDB: Waiting for purge to start
171213  9:03:32 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.35-80.0 started; log sequence number 1616697
171213  9:03:32 [Note] Plugin 'FEEDBACK' is disabled.
171213  9:03:32 [Note] Server socket created on IP: '::'.
171213  9:03:32 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_host_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_user_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_account_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'host_cache' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'objects_summary_global_by_type' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'setup_actors' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'setup_objects' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_index_usage' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'table_io_waits_summary_by_table' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'table_lock_waits_summary_by_table' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_current' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_history' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_history_long' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_thread_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_account_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_user_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_summary_by_host_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_stages_summary_global_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_current' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_history' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_history_long' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_thread_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_account_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'users' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
171213  9:03:32 [ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
171213  9:03:32 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.0.30-MariaDB-0+deb8u2'  socket: '/tmp/c1205314-ceba-464c-b1d9-65e6240dbf21/mysql.sock'  port: 3307  (Debian)
# trying again...
# Success!
# Setting the root password...
# Connection Information:
#  -uroot -proot --socket=/tmp/c1205314-ceba-464c-b1d9-65e6240dbf21/mysql.sock
#...done.
# Connecting to spawned server
done.
# Reading .frm files
#
# Reading the np_commentmeta.frm file.
# Changing engine for .frm file /tmp/c1205314-ceba-464c-b1d9-65e6240dbf21/wordpress_temp/np_commentmeta.frm:
# Skipping to header at : 2
# General Data from .frm file:
{'IO_SIZE': 86,
 'MYSQL_VERSION_ID': 100030,
 'avg_row_length': 0,
 'charset_low': 0,
 'create_options': 9,
 'db_create_pack': 2,
 'default_charset': 224,
 'default_part_eng': 0,
 'extra_size': 16,
 'frm_file_ver': 5,
 'frm_version': 10,
 'key_block_size': 0,
 'key_info_length': 87,
 'key_length': 1483,
 'legacy_db_type': 'INNODB',
 'length': 3033,
 'max_rows': 0,
 'min_rows': 0,
 'rec_length': 1051,
 'row_type': 0,
 'table_charset': 224,
 'tmp_key_length': 1483}
# Engine string: InnoDB
# Server version in file: 1.0.30
#
# CREATE statement for /tmp/wordpress/np_commentmeta.frm:
#

CREATE TABLE `wordpress`.`np_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `comment_id` (`comment_id`),
  KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

# Shutting down spawned server
# Removing the temporary datadir
171213  9:03:33 [Note] /usr/sbin/mysqld: Normal shutdown

171213  9:03:33 [Note] Event Scheduler: Purging the queue. 0 events
171213  9:03:33 [Note] InnoDB: FTS optimize thread exiting.
171213  9:03:33 [Note] InnoDB: Starting shutdown...
#...done.
171213  9:03:34 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
171213  9:03:35 [Note] InnoDB: Shutdown completed; log sequence number 1616707
171213  9:03:35 [Note] /usr/sbin/mysqld: Shutdown complete

That's obviously a lot of information, but I used -vvv which is debug mode to see what's actually happening in the background. At the end of the command, if it was successful, it returns the table's CREATE command. This command can now be used in the real MySQL server instance right after the new database was created:

MariaDB [(none)]> CREATE DATABASE wordpress;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> CREATE TABLE `wordpress`.`np_commentmeta` (
    ->   `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->   `comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
    ->   `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    ->   `meta_value` longtext COLLATE utf8mb4_unicode_ci,
    ->   PRIMARY KEY (`meta_id`),
    ->   KEY `comment_id` (`comment_id`),
    ->   KEY `meta_key` (`meta_key`(191))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.14 sec)

MariaDB [(none)]> use wordpress;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [wordpress]> show tables;
+----------------------------+
| Tables_in_wordpress |
+----------------------------+
| np_commentmeta             |
+----------------------------+
1 row in set (0.00 sec)

 So far so good - the table was created. But what about the data? And this is where the tricky part comes in: First you need to tell MySQL that this particular table (np_commentmeta) should discard its tablespace:

MariaDB [wordpress]> ALTER TABLE np_commentmeta DISCARD TABLESPACE;
Query OK, 0 rows affected (0.01 sec)

This bascially tells MySQL to forget the .ibd file of that table.

Now on the filesystem, I copied the ibd file of that table from /tmp/wordpress right into the data directory of MySQL:

root@ /tmp # cp -p /tmp/wordpress/np_commentmeta.ibd /var/lib/mysql/wordpress/

The following command does the magic trick: It will import the table's tablespace again (using the now copied ibd file):

MariaDB [wordpress]> ALTER TABLE np_commentmeta IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.21 sec)

There's 1 warning shown, but it can be ignored (phew!).

Now let's check, if we got some data back:

MariaDB [wordpress]> select * from np_commentmeta;
+---------+------------+-----------------------+------------+
| meta_id | comment_id | meta_key              | meta_value |
+---------+------------+-----------------------+------------+
|       1 |          1 | _wp_trash_meta_status | 1          |
|       2 |          1 | _wp_trash_meta_time   | 1513072669 |
|       3 |          2 | _wp_trash_meta_status | 1          |
|       4 |          2 | _wp_trash_meta_time   | 1513072672 |
+---------+------------+-----------------------+------------+
4 rows in set (0.00 sec)

Hurray! The data is back!

I continued these steps for all tables of the wordpress database and was able to successfully recover the whole database with the status right before the crash.

Note: According to user comments on the mentioned stackexchange link, this works in MySQL 5.6 and also MySQL 5.7 (so probably > 5.6). In my case it was, as mentioned, a MariaDB 10.0.

Add a comment

Show form to leave a comment

Comments (newest first)

Laurie McIntosh from New Zealand wrote on Mar 16th, 2019:

Cheers Claudio, really helpful having this recover a set of copied tables after I borked one of my databases. really helpful.

---

Arun from India wrote on May 31st, 2018:

Hi Claudio, thanks for this blog! Helped me recover my database. I'm happy, learned a new thing, much much thanks!!

And yeah, All THE BEST to SWITZERLAND for the World Cup. ( I know you're a football fan, me too. )

---