Close

May 7, 2017

How to restore mysql database from .frm and ibd files

1. COPY THE .FRM FILES OF WP22 TO C:\WP22
2. RUN THIS BATCH FILE A.BAT. TWO FILES ARE CREATED NAMELY tables_structure AND tablespan.
3. OPEN THE TABLE STRUCTURE FILE AND ADD THIS PHRASE AT THE END OF EACH CREATE TABLE STATEMENT
row_format=compact; REMOVE EXTRA ; IF ANY.
4. ADD THIS LINE AT THE TOP OF FILE IE. SET sql_mode = ”;
5. OPEN PHPMYADMIN CREATE NEW DATABASE WP22.. CLICK THE SQL TAB. COPY PASTE THE COMPLETE SQL STRUCTURE OF LINE 3 ABOVE HERE. THEN CLICK OK TO RUN THE SQL COMMANDS.
6. ON SUCCESSFULL EXECUTION. COPY THE IBD FILES FROM ORIGINALOLD FOLDER ie
F:\wamp\bin\mysql-old\mysql5.6.17\data\wp22 TO NEW DATABASE STRUCTURE DIRECTORY F:\wamp64\bin\mysql\mysql5.7.14\data\wp22
7. RUN THE SECOND FILE COMMANDS IEĀ  tablespan IN SQL AREA OF PHPMYADMIN .
ON SUCCESSFULL RUNNING OF ALL COMMANDS. EXPORT THE DATBASE WP22 SQL FILE.
8. COPY THE WP22 APPLICATION FROM OLD WWW FOLDER TO WWW NEW APPLICATION FOLDER. LOAD THE APPLICATION IN BROWSER IE LOCALHOST/WP22
Note I WAS USING MYISAM ENGINEE AND MY NEW WAMP HAS NEW ENGINE=InnoDB. SO THE LINE row_format=compact.

  • Ashok

    bat file

    :: Creating temporary folder for storing all the tables structure SQL files
    set structureFolder=tables_structure_0123456789
    md “%structureFolder%”
    :: This is the code that restores the tables structure from the *.frm files
    for %%f in (*.frm) do (
    mysqlfrm –server=root:@localhost:3306 “%%~nf.frm” > “%structureFolder%\%%~nf.sql” –port 3310 -q
    )
    :: Creating SQL file for storing all the tables structure
    set structureSQLFile=%structureFolder%.sql
    type NUL > %structureSQLFile%
    :: Concatenating the entire tables structure SQL files into one
    for %%f in (%structureFolder%\*.sql) do (
    more +1 “%structureFolder%\%%~nf.sql” >> %structureSQLFile%
    echo ; >> %structureSQLFile%
    )
    :: Deleting the temporary structure folder
    rmdir /s /q %structureFolder%
    :: Creating the discard tablespan SQL commands for each table
    for %%f in (*.frm) do (
    echo ALTER TABLE %%~nf DISCARD TABLESPACE; >> %structureSQLFile%
    )
    :: Creating the import tablespan SQL commands for each table
    for %%f in (*.frm) do (
    echo ALTER TABLE %%~nf IMPORT TABLESPACE; >> tables_import_tablespan_0123456789.sql
    )