Recovering MySQL database from *.frm and *.idb files

The general assumption is that something bad happened to your mysql database, but the *ibd and *frm files stayed preserved in some mysql_bak directory.  Drop what remains of the database from mysql, and then do

 mysqlfrm --basedir=/usr/bin/ /usr/mysql_bak/db_to_recover/*.frm --port=3333 --user=root > ~/scratch/db_to_recover_structure.sql

Mysqlfrm can be found in mysql-utilities distributed by Oracle.  /usr/bin/ is the directory where the mysql and mysqladmin executables reside. Note: this leaves a mysqld process hanging.

Unfortunately, the CREATE TABLE statements do not end with ‘;’, therefore

sed 's/CHARSET=latin1/CHARSET=latin1;/g' ~/scratch/db_to_recover_structure.sql -i

mysql -u root -p -e "create database db_to_recover"

Then, to be allowed to input a Innodb file, need to change /etc/mysql/mysql.conf.d/mysqld.cnf (or whatever its location) to contain:

[mysqld]
innodb_file_per_table=1
innodb_force_recovery=1

so we can do

mysql -u root -p db_to_recover < db_to_recover_structure.sql

Now,  ‘create table’ created *idb files (in a place like /var/lib/mysql/db_to_recover). These are something that innodb people refer to as ‘tablespaces,’ in binary format. What we do now is we want to drop all these tablespaces, and switch in the original file from the database we are trying to recover.

If we have many tables, we want to use a script to do that. Go to /var/lib/mysql and run

ls *frm | sed 's/'.frm'//g' | awk '{printf "alter table db_to_recover.%s discard tablespace;\n", $1}' > ~/scratch/discard_tablespace.sql

Then run

 mysql -u root -p db_to_recover < ~/scratch/discard_tablespace.sql

You should notice that the *ibd files are gone from /var/lib/mysql/db_to_recover. Stop the mysql server. Copy the *ibd files from the bkp directory to /var/lib/mysql/db_to_recover.

Actually, copy both *frm and ibd files at this point. Change the ownership of *ibd files to mysql:mysql.

chown -R mysql:mysql /var/lib/mysql/db_to_recover

Restart the server.

Repeat the trick of generating the sql commandfile, this time to import the tablespace:

ls *frm | sed 's/'.frm'//g' | awk '{printf "alter table db_to_recover.%s import tablespace;\n", $1}' > ~/scratch/import_tablespace.sql
mysql -u root -p db_to_recover < ~/scratch/import_tablespace.sql

At this point you should have your database working more or less  as before. The table with passwords did not work for me anymore. Luckily, I work with a small number of passwords :}

Also I am nut sure what happens with indices in this procedure. I’ll investigate some other time.

Btw, for the MyISAM tables, all you need to do is copy all  related files from bak to the current mysql directory and restart the server.