Everything happens in a day. I realized that my website (yeah! techbiriyani.com) stopped working only when i tried to log in to my website. Everything seems dead, i logged in from hosting account and found that Apache is working fine but, MySQL server is not running.
I tried to restart MySQL but, every time it failed to respond. Now my fear went over my files and my website data. I have no recent backup’s. There’s no way to backup my website data unless MySQL starts running. After a week of research, i can’t find a possible solution, so i copied all files from my MySQL data directory and removed all other files.
What now? while asking this question myself i started searching for guides to recover MySQL data from files. Every guide i found on the internet is not suitable for my needs. So i decided to give a try myself with the available guides.
The solution i did to Recover MySQL databases was not available anywhere on the internet. so I thought that I should make this post to help others who experienced a similar issue.
As my website is powered by wordpress all database tables were created by wordpress itself, so i used wordpress table names in recovering steps. Apart from this one, all other functions were the same for both wordpress sites and normal web sites.
Getting the Structure
Before beginning, you should have a small understanding of MyISAM and InnoDB databases. These are the most commonly used storage engines by MySQL. You can find the difference between MyISAM and InnoDB or read a quick summary of them below:
A MySQL MyISAM table is the combination of three files:
- The FRM file is the table definition.
- The MYD file is where the actual data is stored.
- The MYI file is where the indexes created on the table are stored.
You should be able to restore by copying them in your database folder (In xampp, the default location is
InnoDB stores its tables and indexes in a single tablespace *, which by default consist of the 3 files ibdata1, ib_logfile0, and ib_logfile1. For restoring a database you would also need these files. It is a combination of two files.
- The FRM file is the table definition.
- IBD file is where the actual data is stored.
Recover MySQL database
We use Xampp to Recover MySQL database and export our databases. It is the easiest and convenient way to recover corrupted databases. When i wrote this article i used PHP 7.2 in XAMPP. A small version change may bring troubles. So here i will mention the things i used to recover databases.
- Windows OS
- XAMPP – Download
- PHP version: 7.2.23
- MySQL Data Files
Some tables may show errors. In any case don’t forget to recover the tbale which contains your site data. These are the tables that you shouldn’t miss.
|wp_options||The wp_options table is one of the most important WordPress database table and stores all the settings of a WordPress site like the URL, the title, installed plugins, etc.|
|wp_posts||The most important table in WordPress and contains your site posts.|
|wp_postmeta||Stores any additional information which is connected to a post|
|wp_comments||Stores all your site comments|
|wp_terms|| The categories for both posts and links and the tags for posts are found within the |
|wp_term_relationships||Coordinate and connects all tags to posts, pages, and links.|
|wp_term_taxonomy||It acts like metadata for the table wp_terms|
Download and install XAMPP from above. After the installation completes browse to the folder
C:\XAMPP\mysql\data and copy your database files here. Now open the xampp control panel and start apache and MySQL server.
Now access your PHPMyAdmin via http://localhost/phpmyadmin and your database should be listed there. Upon selecting your database you will be able to see the tables and options in the database. You can also find your MySQL storage engine type in TYPE column. see below image
Recover MyISAM database for WordPress
Recover MySQL database for MyISAM databases are quite simple but at various stages, it can make some troubles. I suggest you do a check on all tables by selecting them individually, so that way we can identify if any table causing an error. If all tables are working fine you can just export the database. To export a database:
- Login to phpMyAdmin
- Select Database
- Choose Export
- Click on Go, your table SQL file will be downloaded.
wp_options table may return you the error given below:
#1932 – table ‘tb.wp_options’ doesn’t exist in engine.
So if you got an error similar to this then try to drop the table. Although the wp_options table is important for wordpress to run, it stores only website settings which we can recreate later, so it will not affect your website data. If deleting the table went success then it’s okay but if it returns an error similar to the image below read further.
1347 ‘tb.wp_options’ is not of type ‘VIEW’
Using Shell Command
If the table
DROP method fails using phpMyAdmin we need to do that using cmd
SHELL in XAMPP. Open SHELL from XAMPP control panel.
Now a command window will appear to you, then type:
mysql -u root
Now, you have logged into MySQL, select your database:
Your database should now be selected, type below code to delete your table;
DROP TABLE wp_options;
Then delete wp_options.frm and wp_options.ibd files from
Next, we want to rebuild our WordPress website structure using the exported tables. For that, download WordPress and extract it to the
After extract, open wordpress folder. Rename
wp-config-sample.php file to
wp-config.php or make a copy. This is the file which we used to enter the credentials for the database connection. Now, open wp-config.php in a text editor and update the following:
You should create a new database and user in order to connect WordPress
// ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define( 'DB_NAME', 'database_name_here' ); /** MySQL database username */ define( 'DB_USER', 'username_here' ); /** MySQL database password */ define( 'DB_PASSWORD', 'password_here' ); /** MySQL hostname */ define( 'DB_HOST', 'localhost' );[
Now try to access the same http://localhost/your_folder_name. If the connection was successful you will be redirected to the install page.
After the installation login with your username and password for wordpress admin. If all went success leave it there come back to phpMyAdmin, then select your newly created database and there you will see a list of default tables created by WordPress itself.
Get the new files
Now select your new database and
EXPORT your preferred table (eg: wp_options) you wish to backup and your table will be downloaded as SQL file.
Now, select your old database, then choose
IMPORT to restore your table.
Now your entire database is repaired. Do this to each table which returns any error given above. Once again select each table in your database and ensure that everything working without any error.
We cannot recover InnoDB databases in this way. It is a bit more complicated than MyISAM tables. However, we have shared a guide on recovering InnoDB databases using FRM and IBD files in WordPress.
This is the guide to Recover MySQL database of MyISAM databases. If you find this post helpful don’t forget to share with your friends.