Arquivos perdidos do Frm recuperando dados do ibdata

Question

Eu estava trabalhando com uma base de dados innodb do mysql. Quando eu estava trabalhando com mysqlworkbench acidentalmente todos os arquivos .frm foram excluídos da pasta de dados mysql, mas eu ainda tenho o arquivo ibdata na pasta de dados. Eu não estava usando innodb_file_per_table significa que todos os meus dados de tabelas innodb estavam no único arquivo ibdata.

Infelizmente, não tenho um backup para restaurar. Alguém poderia me sugerir como recuperar os dados do arquivo ibdata.

    

Answer 1

Sua pergunta parece ser idêntica a este no Stack Overflow.
Eu reproduzi parte da resposta aceita aqui.

The simple solution is to find your saved copy of the CREATE TABLE SQL, run it on a development instance of MySQL, then copy the generated FRM file to the restored instance.

If you don't have access to the CREATE TABLE statements you can try the following:

1. In your restored database, run create table innodb_table_monitor (a int) ENGINE=InnoDB
2. Watch the MySQL server error file until the table monitor data has been dumped (usually about a minute)
3. Run drop table innodb_table_monitor

4. Stop the restored database

5. Write SQL to match the table monitor output, e.g.:

   TABLE: name db/mylosttable, id 0 7872, flags 1, columns 5, indexes 1, appr.rows 1828
   COLUMNS: id: DATA_MYSQL DATA_NOT_NULL len 12; name: type 12 DATA_NOT_NULL len 45;     
   DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; 
   DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
   INDEX: name GEN_CLUST_INDEX, id 0 17508, fields 0/5, uniq 1, type 1
   root page 3, appr.key vals 1828, leaf pages 9, size pages 10
   FIELDS:  DB_ROW_ID DB_TRX_ID DB_ROLL_PTR id name
   

   can be expressed as:

   drop table if exists mylosttable;
   create table mylosttable (
       id char(12) NOT NULL,
       name varchar(45) NOT NULL
   );
   

   If you are confused about the table monitor output, look at the output for tables with a known schema.

6. Run the above SQL on a development instance of MySQL

7. Copy the FRM files created in the development server to the restored database. You will find them in the MySQL data directory within the subdirectory for the corresponding database.

8. Restart the restored database

   Note you can copy the FRM files from your development system into a live database instance. The reason for stopping the server above is that if you crash the database after making the innodb_table_monitor table it will leave the ibdata file in an inconsistent state, and you'll have to start over from a backup.

9. Test that the tables work using select * statements. If you are wrong you will see:

   ERROR 2013 (HY000): Lost connection to MySQL server during query
   

which means the database has crashed. If this occurs, do create table innodb_table_monitor... on the dev instance and compare the output to the original output from the restored instance. You will likely see you missed a NOT NULL or something small like that.