Upon installing WordPress on your website, a database is automatically created. The database is an important part of your website, and one can access the database from the site’s web hosts account (more on this later). Think of it like a storage cupboard where comments, posts, site name, user details – basically every information on your website is stored. In the database, information is stored in a structured manner.

Every time a visitor tries to access your website, information from the database is fetched and presented. Naturally, if the connection between the database and the website is severed, visitors will be looking at an empty website, and most likely never return.

The database is infamous for tables crash from time to time. When the database has broken, missing or has corrupted tables, visitors to your website will see database errors. They are likely to come across error establishing messages like “Cannot establish the database connection in WordPress” or “Warning: failed to open stream” etc.

error establishing a database connection in wordpress

 

What Happened to the WordPress Database?

When these database connection error messages appear because the database is corrupted, broken or missing. And the database can be corrupted, broken or even gone missing if any of the following misfortunes may have occurred:

  • Entry of incorrect credentials in the wp-config.php file.
  • The web host server crashes.
  • You’ve reached PHP memory limit, and that causes server interruption.
  • The file permissions are not set up properly.
  • The external firewall service that you are using could be blocking your website or at least some parts of it.
  • Third party WordPress plugins are causing file paths to break or delete content.
  • You have been hacked, and someone can access your site and MYSQL database, etc.

However, fixing the error is possible. all these misfortunes can be prevented to the minimum by optimizing the database.

But before you begin the repair process, it is advisable that you back up your WordPress database. Without the WordPress backups, you’ll end up losing your website content should anything happen to your website. With a back up you’ll have the confidence to make changes in the database. If something goes wrong, you can simply restore the backup, and your website will be up and running within minutes.

In a few steps, you can backup or even download the database and store it in your system. But if you want to download only a specific file or files, you’ll need to take a couple of more steps. Here’s how to take backups of your WordPress database:

 

Backup WordPress Database:

Step 1: You’ll need to access your web host account. Choose phpMyAdmin. When the phpMyAdmin page opens, you can see the database tables. All WordPress databases have 12 tables by default. As your website grows and you, more and more tables are created.

The default tables are as follows –
1: wp_commentmeta
2: wp_comments
3: wp_links
4: wp_options
5: wp_postmeta
6: wp_posts
7: wp_terms
8: wp_termmeta
9: wp_term_relationships
10: wp_term_taxonomy
11: wp_usermeta
12: wp_users

database tables

Step 2: On the page, you should be able to see a tab called Export on the top of the screen. Click on it, and it’ll reveal two options:

  • Quick – Display only the minimal options
  • Custom – Display all possible options

database export

If you choose Quick, you’ll end up exporting the entire database.

But if you choose Custom, a few more options kick in. From a drop-down menu, you can select a specific table or tables to export.

Step 3: Next, you need to choose the format of the backup. You can go for the default option which is SQL or any other format that you feel is convenient for you. After having selected the format, click on GO.

Depending on the size of the entire database or the specific file, it’ll take a few minutes before the entire backup is downloaded on your system.

Now that, you have the backup of your database secure and within your reach, here’s how you can repair and optimize the database. There are two ways of doing this. WordPress (version 2.9 and above) now comes with a database repair feature. Built for people without technical expertise or knowledge, the features allows users to fix corrupted file and database in just a few steps. If this doesn’t work, you can try the phpMyAdmin. We’ll guide you through both these methods.

 

WordPress Database Repair Using WP_ALLOW_REPAIR:

You’ll need to access your web host account. Go to File Manager and then Select public_html from the option available in the left side of the screen. Now search for wp-config.php. Right click on the wpconfig file and select Edit.

In the text-editor, insert the following line:

define( 'WP_ALLOW_REPAIR', true );

Make sure you are inserting this line above the "if ( !defined('ABSPATH') )" line.

add code to wpconfig file

Now save the changes. Do not make any other changes on the website, unless you know what you are doing.

After saving, open this URL: http://yoursite.com/wp-admin/maint/repair.php
(Be sure to replace *yoursite* with the actual name of your WordPress blog) Note that you need to be logged in to view this page.

Upon opening the website, you should see a WordPress page with two buttons that say:

  • Repair Database
  • Repair and Optimize Database

repair and optimize wordpress database

You can click on either of the buttons, and they’ll repair the broken database. But we recommend choosing the second option to repair your WordPress database because it optimizes the database as well.

After WordPress repairs your website, you should see the following message on the screen.

wordpress database repair complete

Now, the only thing left to do is remove the line“define( 'WP_ALLOW_REPAIR', true );” that you added in the config file.

If you fail to use make this method work, there is another way of repairing and optimizing the WordPress database table.

 

Repairing MySQL Database Tables

  1. You’ll need to access your web host account and then select phpMyAdmin. On the phpMyAdmin page, you should be able to view the table in the database.
  2. On the left side of each of the tables, there will be a box. Check the boxes for the tables you need to repair.
  3. There should be an option called With Selected. If you choose that, a drop-down menu will appear.
  4. From the drop-down menu, select Repair Table.

The tables will automatically repair. And then a page will appear with the result of the repair.

repair mysql database table

 

Optimizing MySQL Database Tables

  1. On the left side of each of the tables, there will be a box. Check the boxes for the tables you need to optimize.
  2. There should be an option called With Selected. If you choose that, a drop-down menu will appear.
  3. From the drop-down menu, select Optimize table.

The tables will automatically repair. And then a page will appear with the result of the repair.

optimize mysql database tables

 

Conclusion

The database connection problem is not uncommon. And after you fix the problem, there’s no guarantee that it won’t occur again. While some database issue is beyond your control (like when the web host crashes), other issues can be prevented (like protecting WordPress websites from hackers by using a WordPress website security services).

We hope you found the guide helpful and were able to repair corrupted database files and fix WordPress sites to get them running again.


Tweet