Restore Innodb database from files

The post explains how to restore Innodb database from file system. There are two cases mentioned before and after MySQL v.5.6. The examples are related to a Debian based system but they can be adapted to any distribution. Case 1 - restore MySQL >= v.5.6 database. First of all create a new database (in this case named "example2"). Then restore the database structure from .frm files:

mysqlfrm --diagnostic /your_backup_files_location/*.frm > ~/recover-structure.sql
mysql -uroot -p example2 < ~/recover-structure.sql

Restart the MySQL server and check if the database structure is OK:

/etc/init.d/mysql restart

Disable the tablespace:

SELECT CONCAT('ALTER TABLE ',table_name,' DISCARD TABLESPACE;') FROM information_schema.tables WHERE table_schema = 'example2'

Check if the .ibd files are removed from the new database directory:

ls -l /var/lib/mysql/example2/

Copy the backup .ibd files to the new database directory:

cp -R /your_backup_files_location/*.ibd /var/lib/mysql/example2/

Change the .ibd files permissions and owner to mysql user:

chown mysql:mysql /var/lib/mysql/example2/*.ibd
chmod 660 -R /var/lib/mysql/example2/

Enable tablespace:

SELECT CONCAT('ALTER TABLE ',table_name,' IMPORT TABLESPACE;') FROM information_schema.tables WHERE table_schema = 'example2'

If the ROW_FORMAT is different in the new MySQL version you may adapt it:

SELECT CONCAT('ALTER TABLE ',table_name,' ROW_FORMAT=COMPACT;') FROM information_schema.tables WHERE table_schema = 'example2'

Case 2 - restore MySQL < v.5.6 database. I couldn't find a way to restore Innodb database from MySQL 5.5 to server that operates MySQL 5.6 without damage on some of the Unicode characters and datetime fields. It seems to be a common issue https://bugs.mysql.com/bug.php?id=85722 . So here's my workaround: Make sure that you don't use the system tablespace on your new MySQL server:

Disable "innodb_file_per_table=1" in /etc/mysql/my.cnf

Create a new virtual machine and install MySQL 5.5 or the old version that have been used. Stop the MySQL server and copy the content of the following directories from the backup:

/var/lib/mysql/ibdata1
/var/lib/mysql/example2

Set the permissions to 660 and owner and group to mysql user and start the MySQL server. This should work as charm. Than you may export the database and import it to the new server.