There are chances of losing databases of websites. I too faced the same situation with this website. One day my website is not accessible, after some research i found that my server MySQL service is not loaded. I tried to start MySQL service, but my MySQL was never started.
I did a little trick to get my data back and to restore my database. This trick will only work with WordPress Databases. So I am sharing this trick to help others who experienced a similar issue.
This method will only work with WordPress Websites.
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.
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. We have published a guide on recovering MyISAM database from files recently.
InnoDB is a common storage engine used by MySQL to store its data. Unlike other storage engines, InnoDB databases are more complicated to handle but good at performance. Follow below steps to recover InnoDB database for your wordpress website.
There are two table files for an InnoDB database. If wp_option is the table then there will be two files named wp_options.frm and wp_options.ibd.
- Windows OS
- XAMPP – Download
- PHP version: 7.2.23
- MySQL Data Files
Recover InnoDB Database for WordPress
Download and install XAMPP from above or ignore if you have already installed. Now open the xampp control panel and start apache and MySQL server.
Place your old MySQL data files somewhere, we need this later.
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:
If you select any table then PhpMyAdmin will return you the error given below:
#1932 – table ‘tb.wp_options’ doesn’t exist in engine.
This is common for InnoDB databases. So read the following steps to backup your databases tables.
Download WordPress to rebuild your database files. Download WordPress then extract it to C:\xampp\htdocs folder.
Now we need to create a connection between WordPress and localhost. For that, we need to enter credentials in wp-config.php in WordPress package.
Create a DATABASE, DATABASE_USERNAME and PASSWORD using PhpMyAdmin.
Type your desired Database Name and click Create
Select Privileges tab on top and will able to see the privileged users in first column. Click on Add user account to create a new user.
Enter your user account name and Generate a password for the account. Grant all privileges by checking the check all option.
Don’t forget to save the password somewhere else.
Connect with WordPress
wp-config-sample.php file to
wp-config.php (located in the root install-directory).
Edit the following DB_NAME, DB_USER and DB_PASSWORD with your own.
// ** 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.
Using Shell Command
Now, we need to open the 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 by:
Your database should now be selected, type below code to discard your tablespace
ALTER TABLE _yourtable_name_ DISCARD TABLESPACE;
Then replace your old wp_options.frm and wp_options.ibd files with new files.
e.g. replace old wp_options.frm and wp_options.ibd files with new files.
Now, import tablespace by:
ALTER TABLE _yourtable_name_ IMPORT TABLESPACE;
Wait for a few seconds for the success message saying tablespace imported successfully. You can now exit from the command prompt and check whether your table imported successfully.
In this way, you can Recover InnoDB database for WordPress websites. If this guide helped you suggest to your friends whom you know about experiencing the same problem.