If WordPress cannot connect to the database, it displays a public error message: Error Establishing a Database Connection.

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform related WordPress queries.

In this context, we shall look into methods to fix database issues.

Nature of Error Establishing a Database Connection: Vultr

This error can occur due to low RAM or disk space, database corruption, plugin errors, misbehaving themes, incorrect password, MySQL errors, and more.

Before getting to the solution of this problem, we need to check the server console at Vultr. If we find an "out of memory" error on the screen, we reboot the instance. This can often temporarily fix the problem.

How to Make Backups ?

First and foremost, it is vital to have a reliable backup.

You can easily Take a Snapshot

Vultr snapshots allow us to restore an exact point in time backup in case something goes wrong.

How to Verify MySQL ?

It is possible for the MySQL daemon to stop. We verify it is running with ps and grep. It will return us a one-line with 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

How to Review MySQL Logs ?

Then we review the log files for information. We check for messages regarding incorrect passwords, corrupt tables, memory or disk space issues, etc.

This information is beneficial for diagnosing the root cause of the database problem.

To do so, we use tail. It will give the most recent 50 entries in error.log:

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

How to perform Backup of the Database ?

Prior to making any database changes, we make a backup. In addition, this process verifies that WordPress has the correct database credentials:

1. Initially, we connect to the server as root with SSH or the Vultr Console.

2. Then we locate the database credentials with grep. This returns the WordPress connection information. We ensure 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. To back up, we use mysqldump. In the MySQL prompt, we provide the database password. We can choose a location. For security, we ensure the backup is outside the /var/www directory.

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

4. We create the backup file in a reasonable size, larger than a few KB. It is a text file, we 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

How to Reset the Database Password ?

We can skip this part if we succeed with the mysqldump backup.

We may fail to create a backup using the credentials from wp-config.php. In such a case, we reset the database password. Only do it if the WordPress information is incorrect and you do not know the correct password.

1. Edit wp-config.php.

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

2. We search for the line:

define( 'DB_PASSWORD', 'example_password' );

Enter a new, strong password. Do not change any of the other database information.

3. Eventually, we save the file and exit the editor.

4. We verify the database information in the file and 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. Then we 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. We launch mysqld_safe with the –skip-grant-tables parameter and fork it into the background with &.

# mysqld_safe --skip-grant-tables &

8. Hit ENTER to regain the prompt. Log into MySQL as root:

# mysql -u root

9. Then to set the new password, we run:

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. Finally, we restart the VPS to verify MySQL starts properly at boot:

# reboot

How to Repair the WordPress Database ?

Another possible method for database repair is to add a directive to wp-config.php.

1. Initially, we edit wp-config.php:

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

2. Then we insert the WP_ALLOW_REPAIR directive just above the line, "That's all, stop editing! Happy blogging":

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

3. Eventually, we save the file. Then exit the editor.

4. Visit the following URL:

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

5. We can 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. Finally, we remove the WP_ALLOW_REPAIR directive from wp-config.php to prevent unauthorized use.

[Need help with WordPress troubleshooting? We are here for you. ]