How to recover a database in Mysql

Asked

Viewed 490 times

-1

I had a problem with a client whose operating system stopped working. I was able to access the disk and there is a Mysql database inside the folder C:\ProgramData\MySQL\MySQL Server 5.7\Data\BDados containing the files .FRM and .IDB.

How do I recover that database?

I’ve copied the files from that folder into my computer folder but I can’t access it. When I enter Mysql Workbench the tables appear but I cannot access the content. Always gives the error:

Error Code: 1812. Tablespace is Missing for table bdados.clientes

I would like your help to resolve this situation.

mysql

1 answer

0

There’s a hint on this one website that can help you:

First you create a new database with the tables.

Then delete newly created files using the DISCARD statement

ALTER TABLE newdb.table1 DISCARD TABLESPACE;

Then copy all the . ibd and . frm files from the backup to the database folder, then access the database.

Ask Mysql to accept the new files using the IMPORT statement.

ALTER TABLE newdb.table1 IMPORT TABLESPACE;

This can help to recover the tables from the database.

  • Thank you for the reply. How do I assign the mysql:mysql property?

  • I edited the answer, in case you have to access your database to make the following changes. I hope this can help you.

  • What are the changes you suggest? I have already done what you told me to ALTER TABLE. Then you gave me the following error: Error Code: 1815. Internal error: Cannot reset Lsns in table bdados.clientes : Unsupported 0.157 sec

  • I tried everything and got nothing. The PC where the data was has installed Mysql 8. I fixed another PC and installed Mysql 8 as well. I followed all the instructions. When I run the instruction ALTER TABLE mewdb.table1 IMPORT TABLESPACE; it gives the following error: ALTER TABLE newdb.table1 IMPORT TABLESPACE Error Code: 1034. Incorrect key file for table 'table1'; Try to Repair it. Can help me with this error?

  • I really need your help in this situation. I have tried it with several parameters and always give this

  • Are you using any programs? Or are you going straight through the terminal/DOS?

  • I am using Mysql Workbench

  • Ok, so what I understood is that when you create a new database to recover the data from . frm and . ibd you need to find the table ID that Mysql is currently configured in, as well as the table ID that needs to be recovered. Take a look at this Linq: http://www.chriscalender.com/tag/innodb-error-table-id-in-file/

  • The error in recovery is happening because the ID of the current Mysql table did not match the id of the preexisting ibd table.

  • I did what the link says but at point 2.And the error it gives me is not "ERROR 1030 (HY000): Got error -1 from Storage engine" but rather the error "ERROR 1812 (HY000): Tablespace is Missing for table test1.product." I couldn’t get to the point where you know which ID to then "hit"

  • So it’s because there’s this table in the database that you want to recover, I haven’t had to do that yet, and I’m running out of time to test, but you know the name of all the tables that you have in this database that you want to recover? If you have any creation script, you mount a new database with the same database structure to be recovered, hence you will be able to recover this data by following the steps after building the database.

Show 6 more comments

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.