For the past day now I have been trying to restore all my websites due to my MySQL installation going screwy, the longest part of this was finding away of getting the data structure out of the .frm files. After much searching I managed to find several website with ways of doing it but want to put the details here so it is easier to find.
Step 1: Don’t Panic, Don’t Get Stressed
It’s easy to panic in this situation but it’s best not to. The brain doesn’t work well when stressed.
Step 2: Make a copy of the Data Folder
MySQL keeps all the databases in the data folder it is located at:
| Operating System | Folder Location |
| Windows | C:\ProgramData\MySQL\MySQL Server [version number]\Data |
| MacOS | /var/lib/mysql |
| Linux | /var/lib/mysql |
Step 3: Re-Install MySQL
Re-Install A fresh copy of MySQL by removing it and using the appropriate installer.
Step 4: Recreate Databases
It is time to manually create the databases, I used PHPMyAdmin to create each of the databases I needed.
Step 5: Download DBSake
Download DBSake from https://dbsake.readthedocs.io/
Step 6: Recreate each Table
For each table use:
$ dbsake frmdump --type-codes [location of tables .frm file]
Copy the resulting SQL query for each table into either MySQL CLI or in my case PHPMyAdmin
Step 7: Discard empty table data
Use the following command to discard the created table data replacing [name of table] with the appropriate table name:
ALTER TABLE [name of table] DISCARD TABLESPACE
STEP 8: Copy Old table data files
The next step is to copy each tables .idb file from the folder you created in Step 2 into the appropriate database folder.
Step 9: Import old table data
You next import each tables data using the following SQL Command again replacing [name of table] with the appropriate table name:
ALTER TABLE [name of table] IMPORT TABLE SPACE
After doing the above steps all your databases should be working again, the fact that you are reading this is proof that this works.
Share this:
