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