How can I restore a partitioned mysql InnoDB table from just the .ibd files of the form TableName#P#pname.ibd ? Chris Calendar's article here http://www.chriscalender.com/?p=28 works for non-partitioned tables with a single .ibd file, but the "discard" and "import" steps result in a "storage engine doesn't have this option" error for partitioned tables.
Wes Smith in one of the comments at the link above suggests a manual procedure to import one partition at a time, but that did not work for me. If I try to follow his approach by creating a non-partitioned table, moving the first .ibd file renamed as TableName.ibd, and doing an import, the import succeeds, but there are zero rows in the table. The subsequent suggested step of "add back in the partition", which I tried as "alter table TableName partition by range ... (partition pname1 values less than (...) ENGINE=InnoDB)"shockingly replaces the TableName.ibd file (corresponding to the first partition) with a fresh TableName#P#pname1.ibd of a trivial size. I lost one partition's worth of data trying this. I have about 150 partitions to recover.
Any advice on how to recover the data from the .ibd files? Thanks.
So, it turns out that the approach suggested in the comment at the link in the original post does work after all. The deletion of the partition I mentioned in the "alter table..." step above was legitimate as it had been intentionally deleted in the original table before the loss of the ibdata and log files (but mysql leaves the .ibd files undeleted anyway). The recovery process is painfully slow, but I have managed to script it up and hope to let it run to completion over the next several days.
Here are some tips if you find yourself in a similar situation with many partitions to recover. Suppose you had a table TableName with 100 partitions. Typically, the 100 partitions would have consecutive innodb IDs if they were created via a "create table" statement, but in general this may not be the case as partitions may have been added after creation. So, here are the steps:
1) Find out the innodb ID corresponding to each partition using Calendar's method in the blog above as follows. Note that this step does not need a restart of the mysql server. Just create a table like TableName without any partitions and discard its tablespace. Then move the first partition's .ibd file (named something like TableName#P#pname1.ibd) to the database directory as TableName.ibd and try to import it. Look into the mysql error.log (typically /var/log/mysql/error.log) to see what that partition's innodb ID is. Repeat this step for each partition (or as needed) until you have the 2-tuples (innodb_ID_i, partition_boundary_i) for all partitions in a file.
2) Start with an empty innodb state (stop the server, delete ibdata and ib_logfile*, restart server). For each innodb_ID entry in the file in step 1 above, create a table TableName_i like TableName. E.g., if the 100 partitions correspond to the IDs 321, 322,..., 370, 415, 416,...464 (two blocks of 50 contiguous IDs each), then write a script to create 320 dummy tables, 50 tables like TableName, 45 dummy tables, and 50 tables like TableName.
3) For each TableName_i table created above,
--do
(i) rename table TableName_i to TableName
(ii) alter table TableName discard tablespace // important to do this step before the next one
(iii) mv TableName#P#pname_i.ibd TableName.ibd // with the appropriate directory prefixes
(iv) alter table TableName import tablespace
(v) alter table partition by range (partition_field) (partition pname_i values less than (partition_boundary_i)) // This is the most and only time consuming step
(vi) rename table TableName to TableName_i // or some other name or just dump it to a file
--repeat
Note that all of the above steps are scriptable and do not require restarting the server at any point except at the beginning of step 2 to start with an empty innodb state. Be careful to introduce checks for the success of each sub-step in step 3 before moving to the next, otherwise successive steps may fail and/or .ibd files may get overwritten. If feasible, use a copy in step 3(iii) instead of mv.
A final note: There might be a slightly easier alternative using percona's recovery toolkit using hex editing, but this did not work for my case of partitioned tables. I ran into the same, seemingly unresolved issue, with partitioned tables as noted in one of the comments at http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/ . Your mileage may vary though. If there were a way to avoid recreating partitions (like in step 3(v) above), that would be real nice and quick, but I am not sure if there is one.