MySQL innodb recovery from datafiles

Have been struggling with a broken MySQL/MariaDB database for days and finally found something that works, based on this tutorial. In my case, I saw errors like:

#1932 - Table 'phpmyadmin.pma__recent' doesn't exist in engine ERROR 1030 (HY000): Got error 194 "Tablespace is missing for a table" from storage engine InnoDB ERROR 1146 (42S02): Table 'my_database.my_table' doesn't exist ERROR 1813 (HY000): Tablespace for table '`my_database`.`my_table`' exists. Please DISCARD the tablespace before IMPORT 

Disclaimer: The solution described below seems rather risky to me, but unfortunately I had no current SQL dump backups (I will make more often in the future and recommend this to you too!). I see it more as a “last resort”. If you have a recent SQL dump, it might be worth using it instead.

Assumptions:

• You have root access via sudo.
• Your MySQL server is running.
• Your MySQL/MariaDB database directory is in /var/lib/mysql.
• Your MySQLuser name is mysql.
• You backed up ALL files in your MySQL database directory in case you mess up. (We will drop tables in the following!)
• You have a temporary working directory into which the mysql user can copy files (I will use /home/mysql/tmp_bck in the following).
• You set innodb_force_recovery=0 in your MySQL configuration (/etc/mysql/my.cnf) or it is not set/changed at all (otherwise you can't write).
• You have the mysqlfrm tool installed. If you don't have it yet, install it with sudo apt-get install mysql-utilities.

Make sure the things above are fulfilled before continuing.

For each database that we want to recover, create a temporary working directory. Let's assume the database is called my_database, then execute:

sudo -u mysql mkdir /home/mysql/tmp_bck/my_database 

For each table (e.g. my_table) in this database that we want to recover:

1. In your bash/SSH console, create a temporary backup of the database structure/format (.frm) and data (.ibd). Then use mysqlfrm to log in to your MySQL server (here: with the user "root", password "my_password" (replace), and port 3306 (default)) and crate a temporary dummy server on another port (here: 3310).

sudo -u mysql cp /var/lib/mysql/my_database/my_table.frm /home/mysql/tmp_bck/my_database/my_table.frm sudo -u mysql cp /var/lib/mysql/my_database/my_table.ibd /home/mysql/tmp_bck/my_database/my_table.ibd cd /var/lib/mysql/my_database sudo -u mysql mysqlfrm --server=root:my_password@localhost:3306 my_table.frm --port=3310 

mysqlfrm will give you a CREATE TABLE command, for example:

CREATE TABLE `wp_options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`), KEY `autoload` (`autoload`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

Copy this , add a ; at the end, and keep it for step 4!

2. In your MySQl command line (start a second bash/SSH window and run mysql -u root -p), select your database and drop/delete the table. This will result in data loss if you didn't do a backup of the .frm/.ibd files before starting or if the above copying failed!:

USE my_database; DROP TABLE my_table; 

3. Back in your regular bash, stop the server, delete any remaining .frm/.ibd file (at least the .ibd file was still present in my case), and restart the server:

sudo systemctl stop mariadb sudo -u mysql rm /var/lib/mysql/my_database/my_table.frm sudo -u mysql rm /var/lib/mysql/my_database/my_table.ibd sudo systemctl start mariadb 

4. In your MySQl command line, create the table with the CREATE TABLE command from step 1, for example:

CREATE TABLE `wp_options` ( `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL, `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes', PRIMARY KEY (`option_id`), UNIQUE KEY `option_name` (`option_name`), KEY `autoload` (`autoload`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 

Then, delete the .ibd data tablespace with:

ALTER TABLE my_table DISCARD TABLESPACE; 

5. Back in your regular bash, copy the .ibd file from your temporary backup:

sudo -u mysql cp -f /home/mysql/tmp_bck/my_database/my_table.ibd /var/lib/mysql/my_database/my_table.ibd sudo chmod 660 /var/lib/mysql/my_database/my_table.ibd 

6. Finally, in MySQL, import the .ibd file that was just copied from the backup:

ALTER TABLE my_table IMPORT TABLESPACE; 

Done! Make sure this succeeds (e.g., using phpmyadmin, you shouldn't see an error message but instead see a table with data, or at least an empty table without an error message). Repeat the steps above for each database/table. Good luck!