Troubleshooting WordPress Database Errors

Introduction

WordPress is a popular open-source web publishing platform. It's written in PHP and uses a MySQL database to store dynamic content. If WordPress cannot connect to the database, it displays a public error message: Error Establishing a Database Connection.

This condition can happen for a variety of reasons: low RAM or disk space, database corruption, plugin errors, misbehaving themes, incorrect password, MySQL errors, and more. This troubleshooting guide has useful steps to resolve many database issues. This guide uses values such as example_DB_name, example_password, and www.example.com. Replace these values with your site information.

First Steps

Before performing any of the steps below, check the server console at https://my.vultr.com. If there are any "out of memory" errors on the screen, reboot the instance. This will often temporarily correct the problem. If the problem recurs, you may need to upgrade your server with more memory or disk space.

Make Backups

Before performing any troubleshooting, it is vital to have a reliable backup as a starting point. Please do not skip these steps.

Take a Snapshot

Take a snapshot of your instance. Vultr snapshots allow you to restore an exact point in time backup in case something goes wrong. Navigate to https://my.vultr.com/snapshots/ to make your snapshot. See our Snapshot Quickstart guide for more details.

Verify MySQL is Running

The MySQL daemon may have stopped. Verify it is running with ps and grep. This should return one line showing the process ID and command-line.

# ps -ax | grep '[m]ysqld'
 1342 ?        Sl     0:01 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Review MySQL Logs

Review the log files for information. Look for messages regarding incorrect passwords, corrupt tables, memory or disk space issues, etc. The log information is beneficial when diagnosing the root cause of the database problem. Use tail to view the most recent 50 entries in error.log.

# tail -n50 /var/log/mysql/error.log

Backup the Database

Before making any database changes, make a backup. In addition to backing up the database, this process verifies that WordPress has the correct database credentials.

1. Connect to the server as root with SSH or the Vultr Console.

2. Locate the database credentials with grep. This command returns the WordPress connection information. Verify that DB_HOST is 'localhost'.

    # grep 'DB_USER\|DB_NAME\|DB_PASSWORD\|DB_HOST' /var/www/html/wp-config.php
   
    define( 'DB_NAME', 'example_DB_name' );
    define( 'DB_USER', 'example_username' );
    define( 'DB_PASSWORD', 'example_password' );
    define( 'DB_HOST', 'localhost' );

3. Use mysqldump to back up the database. Enter the database password when prompted by MySQL. This example creates a full database backup in /root/mysqlbak.sql. You can choose a different location. For security, make sure the backup is outside the /var/www directory.

    # mysqldump -u example_username -p example_DB_name > /root/mysqlbak.sql

4. Verify the backup file was created and is a reasonable size, larger than a few KB. It is a text file, you can also inspect it with less.

    # ls -l /root/mysqlbak.sql
    # less /root/mysqlbak.sql        

   If the password is incorrect, mysqldump will report the error.

    mysqldump: Got error: 1045: Access denied for user 'example_username'@'localhost' (using password: YES) when trying to connect

Reset your Database Password

NOTE: If the mysqldump backup succeeded, skip this section.

If you cannot make a backup using the credentials from wp-config.php, you need to reset the database password. Only reset the database password if the WordPress information is incorrect and you do not know the correct password. Make sure you have a server snapshot before proceeding.

1. Edit wp-config.php.

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

2. Look for this line.

    define( 'DB_PASSWORD', 'example_password' );

   Enter a new, strong password in place of the example_password in the line above. Do not change any of the other database information.

3. Save the file and exit the editor.

4. Verify the database information in the file. Make sure the DB_HOST is localhost.

    # grep 'DB_USER\|DB_NAME\|DB_PASSWORD\|DB_HOST' /var/www/html/wp-config.php
   
    define( 'DB_NAME', 'example_DB_name' );
    define( 'DB_USER', 'example_username' );
    define( 'DB_PASSWORD', 'example_password' );
    define( 'DB_HOST', 'localhost' );

5. Stop MySQL.

    # service mysql stop 

6. Create a folder for the runtime socket and grant access to the mysql user.

    # mkdir -p /var/run/mysqld
    # chown mysql:mysql /var/run/mysqld

7. Launch mysqld_safe with the --skip-grant-tables parameter and fork it into the background with &.

    # mysqld_safe --skip-grant-tables &

8. You may need to hit Enter to regain your prompt. Log into MySQL as root.

    # mysql -u root

9. Enter the following commands to set the new password.

    mysql> use example_DB_name;
    mysql> FLUSH PRIVILEGES;
    mysql> GRANT ALL PRIVILEGES ON example_DB_name.* TO "example_username"@"localhost" IDENTIFIED BY "new_example_password";
    mysql> FLUSH PRIVILEGES;
    mysql> EXIT

10. Restart the VPS to verify MySQL starts properly at boot.

    # reboot

Repair the WordPress Database

You can attempt a database repair by adding a directive to wp-config.php.

1. Edit wp-config.php

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

2. Insert the WP_ALLOW_REPAIR directive just above the line that says "That's all, stop editing! Happy blogging". You will find that line near the end of the file.

    define( 'WP_ALLOW_REPAIR', true );
    /* That's all, stop editing! Happy blogging. */     

3. Save the file, exit the editor.

4. Visit the following URL, replacing the example site name with your name.

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

5. Select either Repair Database or Repair and Optimize Database. Both options repair the database. Optimization also removes deleted rows from tables, defragments, and compresses the database to improve performance.

   

6. When finished, remove the WP_ALLOW_REPAIR directive from wp-config.php to prevent unauthorized use.