restore-mysql-database.jpg

Restore a MySQL database from raw data files

Posted by Simon on Fri, Jan 22 2021

If you have ever lost your MySQL database, such as a server failure, and only have the raw data files, you can still restore the database realitvely easily. We have tried and tested this process (because we had to) and it worked.

It's quite a straight forward process, and we found that following this process, we retreived all the data. First and foremost, you need to ensure you have exactly the same version of the MySQL server installed. We tested this on a linuz platform, and you can either complete the process with all the tables or one table at a time.

  1. Create the database
    Using something like phpmyadmin, or command line, issue the commend to create the database.
  2. Create the same database structure
    You need to make sure that you create the tables in the database and name them correctly.
  3. Copy across the idb files
    Copy your raw database .idb files to your MySQL data folder
  4. Log into MySQL at command line level
    We found it easier to do this at command line level rather than using PHPMYADMIN to execute the comments to alter the tablespace with the following commands

    ALTER TABLE ‘your_table_name’ DISCARD TABLESPACE;
    ALTER TABLE ‘your_table_name’ IMPORT TABLESPACE;