Web lists-archives.com

Re: rescue Inno tables from an abandoned data directory?

Hi Martin,

On 4/12/2016 07:23, Martin Mueller wrote:
I abandoned a MySQL 5.22 database that quite suddenly andthat I wasn’t able to start up again. The data directory consists of a mix of ISAM and Inno tables.  I  was able to copy the ISAM tables into a new 5.6 version, and they work.

Assuming you mean 5.5.22 or 5.6.22, then sometimes you can recover a table without partitions with its own .ibd file (file-per-table) using the transportable tablespace features:

1. Install a fresh copy of 5.6
2. Create the table (using a normal CREATE TABLE statement). If you don't know the table definition use mysqlfrm from MySQL Utilities (https://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqlfrm.html)
3. Discard the tablespace (ALTER TABLE <table_name> DISCARD TABLESPACE)
4. Copy the .ibd file (make sure you work with a copy) into the new 5.6 instance (e.g. for the table db1.t1 copy to <datadir>/db1/t1.ibd)
5. Import the tablespace (ALTER TABLE <table_name> IMPORT TABLESPACE)

There is also an example in https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html

The import in step 5. will complain that there is no .cfg file from a proper tablespace copy, but InnoDB will do a best effort to import it, and I don't think I've seen it fail if the tablespace has been valid.

Best regards,
Jesper Krogh
MySQL Support

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql