We work on data recoveries quite often. In many cases, we recover table structures from the .frm files because there was no backup available. There is already a great blog post by my colleague Miguel Ángel Nieto about how we can recover structures from .frm files using MySQL utilities.
The frmdump command makes the recovery process easy and faster. We can easily script this and recover the structure of a large number of tables. For example, if we need to recover the structure of all tables from a world database, we can do following:
The dbsake command makes it easier to recover table structures from .frm files. We can also filter and transform the mysqldump output easily without writing a complex awk or sed script. There are some more useful features of this tool that you can read about in the manual.
Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.
4) Copy your *.ibd files (MySQL Or MariaDB) to MariaDB's data pathFirst i try to use MySQL 5.5 and 5.6 to restrore, but database crashes and immediately stops about tablespace id broken error. (ERROR 1030 (HY000): Got error -1 from storage engine) After i have used MariaDB 10.1.8, and i have succesfully recovered my data.
I just want to add one more thing for macos El Capitan users. MySQL utilities is not supported for this version, so mysqlfrm command is not helpful. What I did is recovering my table structures with dbsake as shown in this link: -table-structure-from-frm-files-using-dbsake/
There are tools to get the CREATE TABLE statement just using the .frm file. I wrote a post about this as well : How can extract the table schema from just the .frm file?. In that post, I copied a .frm file to a Windows machine from a Linux box, ran the Windows tool and got the CREATE TABLE statement.
One way to cut some time from testing or executing mysql_upgrade is to combine it with mysqlcheck. This identifies tables that need to be rebuilt or repaired. The first step is to capture the output of this process: 2b1af7f3a8