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.
>
> I understand that INNO tables are different because different tables share a common table space. The MySQL documentation refers to a “cold backup,” where you copy the separate files after a “slow shutdown.”  It doesn’t tell you what to do with them after you’ve put them in a “safe place.”
The recommendation here is that if your DB is in an inconsistent state, 
the last thing you want to do is break it even further. The flat OS 
files copy of the DD simply gives us a fallback should our recover 
operations on the DD make matters worse, eg via innodb_force_recovery etc
>
> In my case, I can reproduce Time machine backups of data directories at varying times. At one point I was able to replace the non-working installation with an earlier installation, but then it failed unpredictably.
The steps you took and an associated error log would assist me in 
assisting you.
Also accurate information on the version helps, 5.5.22? 5.1.22 ? as 
mentioned there is no 5.22.

In general this approach can be taken;

If the DB successfully started , then your first port of call is to 
perform this action again with the same DD snapshot and attempt a 
logical dump of any tables you wish to recover.
Without knowing what state the db is in , I would recommend starting with

[mysqld]
innodb_force_recovery = 1
read_only
skip_slave_start

https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only
https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_skip-slave-start

and seeing if you can access / dump the tables in question.
If not , iterate through 2,3,4,5,6 ( in order! ) to see if that assists.

If this doesn't work, try the latest version of the current tree you are 
on. ie  if 5.5
https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-53.html

failing that, hail mary it with latest 5.7, you may get lucky with TT

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_transportable_tablespace


GL
>
> Are the Inno tables on Time Machine useless, or can I rescue data from them?
>
> I’ll be grateful for help

-- 
Regards

Ronan McGlue
MySQL Support