How to debug “Database connection error” error in WordPress

Introduction

WordPress is one of the most popular open source content management systems in the world. Although it initially focused on blogging, over the years it has evolved into a more flexible platform for websites in general. After almost fifteen years of development, it is quite polished and reliable, but problems still arise.

If you’ve recently tried to load a WordPress site and instead saw the message “Error establishing database connection», The reason is most often one of the following:

• The database has collapsed, often due to insufficient memory on the server.
• Database login credentials are incorrect in WordPress config
• Tables in WordPress database have been corrupted

Let’s go through these questions one at a time to determine how they affect you and how to fix them.

Prerequisites

This tutorial assumes the following:

• You start WordPress on a machine, you have a command line and sudo Linux access
• The database runs on the same server as WordPress (typical for setting up your own server for WordPress, less typical for shared WordPress hosting)
• You know the database username, password and database name created for WordPress. This information should be created during the initial setup of your WordPress installation.

Step 1 – Check Server Memory Resources

A good first step to debugging this problem is to try to log into the server to see that the system is up and MySQL is running.

Login to the server via SSH:

ssh [email protected]_server_ip

If you need help logging into the server, see How to connect to Linux using SSH.

Note: If you are confident that your fittings are correct, but you are still having trouble logging in, it could be that your server is out of memory or under very heavy load. This could be due to a sudden spike in traffic to your site and could explain the WordPress error. You may need to reboot the server before you can login.

Now that we have logged in successfully, let’s check that our MySQL server is running:

sudo netstat -plt

Команда netstat displays information about the network system of our server. In this case, we need the names of the programs ( -p) listens for connections ( -l) to a TCP socket ( -t). Check the output on the screen mysqldare highlighted below:

Conclusion

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 localhost:mysql         *:*                     LISTEN      1958/mysqld
tcp        0      0 *:ssh                   *:*                     LISTEN      2205/sshd
tcp        0      0 localhost:smtp          *:*                     LISTEN      2361/master
tcp6       0      0 [::]:http               [::]:*                  LISTEN      16091/apache2
tcp6       0      0 [::]:ssh                [::]:*                  LISTEN      2205/sshd
tcp6       0      0 ip6-localhost:smtp      [::]:*                  LISTEN      2361/master

If your output is similar, we know MySQL is running and listening for a connection. If you don’t see MySQL listed, try starting MySQL manually. On most systems, it will look like this:

sudo systemctl start mysql

Some Linux distributions (CentOS in particular) use mysqld instead of service mysql… Replace depending on the system.

MySQL should work. To check, repeat the command netstat which we used above and check the output for the process mysqld…

MySQL and WordPress need a good amount of memory to run properly. If MySQL crashes due to a low memory situation, we should see evidence of this in our error logs. Let’s see:

zgrep -a "allocate memory" /var/log/mysql/error.log*

zgrep will search through the log – files, including old files that were archived as files with the extension .tar.gz… We are looking for a string containing allocate memory, in any file error.log* in the catalog /var/log/mysql/…

Conclusion

2017-04-11T17:38:22.604644Z 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool

If you see one or more lines like above, the MySQL server crashed and became unavailable. If it is only one line, then you may temporarily experience unusual traffic. If there are many error lines, the server is regularly running out of memory. In any case, the best solution is to migrate to the server with more available memory. With most cloud providers, it is a simple matter to upgrade your existing server with minimal downtime.

If you don’t see any output after running the command zgrep, your server is down not because of memory. If your site still throws errors, move on to the next step where we will look at our WordPress configuration and make sure the MySQL login information is correct.

Step 2 – Verifying the Login Database Credentials

If you’ve just moved your WordPress and chose between servers or hosting provider, you may need to update your database connection details. They are stored on the server in a PHP file named wp-config.php…

First, let’s find our file wp-config.php:

sudo find / -name "wp-config.php"

This search will go through everything in the root directory ( /), and will find a file named wp-config.php… If such a file exists, then the full path will appear in the output:

Conclusion

/var/www/html/wp-config.php

Now use your favorite text editor to open the config file. We will use the editor here nano:

sudo nano /var/www/html/wp-config.php

This will open a text file full of configuration variables and explanatory text. At the beginning of our database connection information:

wp-config.php

/** The name of the database for WordPress */
define('DB_NAME', 'database_name');

/** MySQL database username */
define('DB_USER', 'database_username');

/** MySQL database password */
define('DB_PASSWORD', 'database_password');

Make sure these three variables are correct based on your entries. If they don’t look right, update as needed, save and exit ( CTRL-O, and then, CTRL-X if you use nano). Even if the information looked correct, it is worth trying to connect to the database from the command line just to be sure. Copy and paste the details directly from the config file into the following command:

mysqlshow -u database_username -p

You will be prompted for a password. Paste it in and click ENTER… If you get the error Access Denied, your username or password is incorrect. Otherwise the command mysqlshow will show all databases that the specified user has access to:

Conclusion

+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| database_name      |
+--------------------+

Make sure one of the databases exactly matches the one in your WordPress config file. If this happens, you have verified that your configuration is correct and that WordPress should be able to log into the database successfully. Go to your website to see that the error is gone.

Still not working? The next thing to try is to repair the database.

Step 3 – Restoring the WordPress Database

Sometimes, due to a failed update, a database crash or a faulty plugin, your WordPress database can become corrupted. This problem can present itself as a database connection error, so if your problem is not the MySQL server or configuration file, try restoring your database.

WordPress provides a built-in database recovery utility. It is disabled by default because it has no access control and could be a security issue. We will enable this feature, make repairs, and then disable it.

Open the file wp-config.php again:

sudo nano /var/www/html/wp-config.php

On any blank line, insert the following:

wp-config.php

define('WP_ALLOW_REPAIR', true);

This defines a variable that specifies in WordPress that the restore feature should be enabled.

Save and close the file. Switch to your browser and load the following URL, making sure to replace your site’s domain or IP address for the dedicated site:

http://www.example.ru/wp-admin/maint/repair.php

To load the database recovery page:

Click the button Database Repairand you will be taken to the results page where you can see receipts and repairs in real time:

After completing the process, make sure by opening the file wp-config.php again, and delete the line we inserted.

Have you noticed any renovation work being done? Try your site again and check if the error is gone. If a non-repairable problem was found, you may need to restore the database from a backup if you have a database backup available. Please see our tutorial on how to import and export databases in MySQL for an addition to our article on how to do it.

If the problems with the database are not resolved, then we still haven’t found the problem. These may be temporary problems, see another tutorial here to fix this problem.

Conclusion

Most of the “Error establishing database connection” problems should have been resolved with the three steps above. However, there could be no more elusive questions that continue to present themselves in this way. Here are a few more articles that may be helpful in catching and neutralizing the cause of this error:

• A frequent source of high traffic (and thus reduced performance and errors) is a brute force attack on a common WordPress installation. You can neutralize the attack by following the guide on how to protect WordPress against XML-RPC attacks.
• You can save server resources by implementing caching on your WordPress installation. There are many caching plugins in WordPress. Our tutorial on how to set up Redis caching to speed up WordPress will show you how to set up a particularly performant Redis cache.