Having to troubleshoot and repair the WordPress database is something that happens to many of us at some time.
If you’re seeing an error message telling you that you can’t connect to your database or maybe that some files are corrupted, then there’s a good chance that you got a problem with your WordPress database.
But how do you repair the WordPress database?
In this article, we’ll show you how to diagnose problems with your WordPress database and how to fix them. You’ll also learn how to prevent your WordPress database problems from happening again in the future.
Check out our video guide to fixing WordPress Database and PHP Errors:
How To Know if Your WordPress Database Needs Repairing
As a WordPress user, you probably come across your share of WordPress errors. It’s almost impossible to run a website and not occasionally come up against a problem. But how do you know it’s the database that’s the issue?
Here are some clues that will tell you that you have a problem with your database:
- You’re seeing the ‘Error establishing database connection’ error message.
- Images are missing on your site.
- Content is missing from your site.
- You’re seeing the “No Update Required Your WordPress Database is Already Up-to-Date” error.
The ‘error establishing a database connection message’ is probably one of the most common error messages you’ll see on your WordPress site. It’s almost as scary as the so-called white screen of death you get when there’s a problem with the code.
Fixing it can be quite simple, though.
Let’s take a look at the reasons for database problems before we get into the various ways you might need to fix the problem.
6 Common Reasons Why Your Database Needs to Be Repaired
Here are some of the most common causes of database problems. For some, you’ll see a specific warning message. For others, you’ll get the generic establishing database connection message.
Let’s have a look at the most common scenarios and then how to repair your WordPress database.
1. Incorrect Database Credentials
This is possibly the most common cause of a WordPress database connection problem and, luckily, it’s the easiest to fix.
You’ll just need to make a simple amendment to your wp-config.php file or use a plugin to make the fix if you don’t want to edit the code yourself. Here’s how to check your database credentials in MyKinsta.
2. Connection Problems
One of the causes of an inaccessible database is a problem connecting to your server. If you’re using cheap hosting, you might find that your server is slow or unresponsive. A good hosting provider will give you an uptime guarantee, meaning that this shouldn’t be the cause of your problem.
But it’s worth checking with your hosting provider whether there are any issues on the server.
3. PHP Memory Limit Exceeded
This is an error that in my experience crops up most frequently when you’re uploading media to your site.
It means you’ve overloaded the memory limit that’s been set for your site. Fixing it is a case of editing some code, changing some settings with your hosting provider or using a plugin.
4. Corrupted Database Tables
If your database tables have become corrupted, you’ll need to clean up your database. If your credentials are correct, there are no problems with the connection, and you haven’t exceeded your PHP memory limit, a corrupt database is the next most likely culprit.
You can fix corrupt database tables in phpMyAdmin (we’ll see how).
5. Corrupt Files
If you have files in your WordPress installation that have become corrupted, this could cause problems in your database. This could be due to an issue with the plugin whose files are corrupted or with an edit that you one of the developers made (here’s where to find the best WordPress developers for hire).
We’ll take a look at the options you have for fixing corrupt files shortly.
6. Your Site or Database Has Been Hacked
We deliberately left this one last because it’s the one most likely to frighten you: your WordPress site or database might have been hacked. However, if your database is experiencing problems because of the hack, fixing it isn’t as difficult as you might think.
You can either get help from your hosting company (Kinsta will help with all instances of hacking) or you can fix it your self.
Now, it’s time to get cracking on some database fixing!
How to Repair the WordPress Database
Since you know now the common causes of database problems, you’re going be asking this question: how do I repair my WordPress database?
The exact steps you need to take will depend on the nature of the problem. Let’s go through the simplest steps first, as these will solve the most common problems.
If you already identified that your database is experiencing a more severe problem, skip to the relevant part of this section.
Start by Backing Up
Whichever steps you’ll be taking to repair your database, you should always start by taking a backup of your site.
Use a backup plugin or go to your hosting provider interface and take a manual backup there. Kinsta automatically backs up WordPress sites every day, but you can also take a manual backup or increase the frequency if you need to.
How To Use a Database Log File to Diagnose WordPress Database Errors
The first step is to view your error logs and identify what the problem is. There are three options for doing this:
- View the raw WordPress logs via FTP.
- Enable error logs in wp-config.php.
- Access the logs via MyKinsta (image below).
This is how easy you can access the Log viewer in MyKinsta:
The logs will tell you what’s been happening on your site and what errors have occurred. You can use these to diagnose the database connection problem.
Once you’ve done that, use the relevant fix below. Or if you aren’t able to do that, work through these fixes in order, as they start with the simplest and most common.
How To Edit Your WordPress Database Credentials
The most common cause of an error establishing a database connection is incorrect database credentials.
To edit your database credentials, make a backup copy of your wp-config.php file then open the original one. Taking a backup is essential in case you make a mistake that makes things worse.
You’ll find the wp-config.php file in the root directory of your WordPress installation. To access it, you will want to connect to your site via SFTP.
The file contains four pieces of information that need to be correct if your site is going to correctly connect to your database. These are:
- Database name.
- MySQL database username.
- MySQL database password.
- MySQL hostname.
In your wp-config.php file, these four pieces of information will look like this:
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'xxxxxx');
/** MySQL database username */
define('DB_USER', 'xxxxxx');
/** MySQL database password */
define('DB_PASSWORD', 'xxxxxx');
/** MySQL hostname */
define('DB_HOST', 'xxxxxx');To find the correct database credentials, you’ll need to either check on your admin screens with your hosting provider or find the email you originally received when you set up your hosting account.
If you’re a Kinsta user, you can easily find the database credentials in MyKinsta. Specifically, go to Sites and then select the site you want to work with. After that, scroll down to the Database access section to find details related to your database.
You can also find your database credentials in phpMyAdmin, which you can access via cPanel if that’s the interface you get with your hosting provider.
Once you’ve updated correctly the database credentials, save your “new” wp-config.php file, close it down, and try accessing your site again.
If you still can’t access the database, read on for more solutions.
How To Use WP_ALLOW_REPAIR to Repair the WordPress Database
The next option is to instruct WordPress to fix any issues with your database. You do this by adding a line in your wp-config.php file.
Open the file and find this line:
/* That's all, stop editing! Happy blogging. */Add this line immediately above that line:
define ('WP_ALLOW_REPAIR', true);Now, save your wp-config.php file and close it.
Again, make sure you create a backup copy of your wp-config.php file first, just in case.
The next step is to visit the database repair page. You’ll find this at http://your-site.com/wp-admin/maint/repair.php. Replace your-site.com with the domain of your website.
This will take you to the database repair page:
You have two options here:
- Repair the database
- Repair and optimize the database
If you choose to optimize it as well, WordPress will run through your database tables and optimize them to make your site run more efficiently.
Click whichever of those two buttons you want, and wait for WordPress to fix your database. Now, your site should be working correctly.
Once you’re finished, don’t forget to remove the line that you just added to your wp-config.php file.
But what if this doesn’t work either? The next step is to go to phpMyAdmin and attempt to repair your database there.
How To Repair the WordPress Database in phpMyAdmin
So you’ve tried the options above and your database still isn’t connecting. Let’s try fixing it via phpMyAdmin.
You’re not comfortable accessing your database directly, you might prefer to use a plugin for this. Some of the plugins that you can use to repair the WordPress database are listed later in this article.
You can access phpMyAdmin via your hosting provider’s admin screens. In MyKinsta, you get to it by clicking on Sites, selecting the site you want to work with, and clicking the Open phpMyAdmin link.
Once you’re logged into phpMyAdmin, click on the database you need to repair from the list of databases on the left. The tables in your database will load on the right:
Scroll down and tick the Check All option. Then, in the With selected box, select Repair table.
The system will then work through your database tables and make repairs.
But what if one of these options doesn’t work or you want to access your site differently? Let’s try another method.
How To Repair the WordPress Database via WP-CLI
If you’re using the command line to access your WordPress installation, you can use WP-CLI to repair the database.
To do this, use the wp db repair command. This can be the quickest way to repair the database.
How To Increase PHP Memory Limit
If you’re having problems accessing the database because your PHP memory limit has been exceeded, you can take steps to increase this limit.
In the wp-config.php file, find this line:
define( 'WP_MEMORY_LIMIT', '256M' );The 256M part relates to how much memory you have available to use. If it’s a lower number, edit it to make it higher. 256M is normally enough for a WordPress site. Note that the number may be different in your file, as it represents your site’s current memory limit. You may also not find this line at all.
Note that if you’re on a hosting plan that doesn’t allow you to access more memory, you may need to upgrade your plan to make this possible. Or you might prefer to switch to a provider who gives you more memory.
How To Repair the WordPress Database Using Database Repair Plugins
If you aren’t comfortable repairing the database using any of the options above, you can use a database plugin to fix it instead. This will only work if you have admin access to your site.
Plugins that let you repair the database include:
How To Optimize Your WordPress Database
Once you’ve repaired your database issues (or before it develops them in the first place), it’s a good idea to take steps to avoid problems in the future. Here are some tips to help you manage your WordPress database and protect it from future issues.
Kinsta’s Automatic Database Optimization
At Kinsta, we perform automatic weekly database optimizations for all of our customers’ WordPress sites. This means you’re much less likely to encounter problems if your site is hosted at Kinsta.
Database Optimization Plugins
If your hosting provider doesn’t optimize your databases for you, you can use a plugin to take care of this. Plugins that do this include:
As a reminder: if you are with Kinsta, you won’t need database optimization plugins as they will interfere with the automated optimization process we automatically have in place.
Add MySQL Index to WordPress wp_options Table
Adding the MySQL index to your wp_options table (or removing it from that table) can speed up the database connection. Use the tips in this guide to work out which option will make your site run faster.
Database Management and Security Good Practice
Good practice for managing your database is similar to good WordPress website management:
- Keep Regular Backups. If you should need to fix your database again, knowing that you have regular automated backups will make it easier and give you some peace of mind. Use a backup plugin or, if you’re with Kinsta, relax in the knowledge that your site is being backed up every day without you having to lift a finger.
- Make sure your database is always optimized and ensure it runs smoothly. Do this either via a database optimization plugin or choose a hosting provider that takes care of that for you.
- Never give out your database password unless you trust the recipient. It’s unusual to need a database password to work on a site so if you are asked for it, ask why.
- If someone else is given access to your database, change the credentials after they have finished working on it and update the wp-config.php file afterward.
- Only use secure passwords for your WordPress admin, your hosting account and your FTP/SFTP access. Use SFTP in preference to FTP when on a public connection.
- If you write a plugin that adds tables or records to the database, make sure to remove these when the plugin is deleted. Clean up your database after deleting plugins that have added their own tables.
- Take measures to keep your site secure and protect it from hacks.
Summary
Having a problem connecting to your WordPress database is a headache and can be daunting. But if you follow the steps above, you should be able to repair it and get access again quickly.
Make sure you follow the guidance to prevent database problems occurring in the future and, hopefully, you won’t have to repair your database a second time.
