You are Here:Home>>Code>>WordPress>>Learn how to repair and optimize the WordPress database

Learn how to repair and optimize the WordPress database

Carlos Pinho
By | 2017-09-07T12:44:11+00:00 Jul 20, 2017|WordPress|

Time to time anyone with a website is challenged with some issues in the WordPress database. This is something that happens to the majority of websites, and if you have never faced this problem than it is a question of time until you will get it. Since this is a regular issue, we decided to publish this article to explain some steps and alternatives to repair and optimize the WordPress database.

Reasons behind a corrupted WordPress database or a problem with the connection

There are several reasons that can make your database is corrupted or broken. The most common are:

  • Server crashed;
  • Server hardware problem;
  • WordPress Plugins, Themes, MySQL bugs or even PHP version;
  • Memory problems;
  • Website was hacked;
  • wp-config.php database credentials misconfigured;

How to know if the WordPress database is corrupted or you are having issues with memory

The most common trigger that lets you know you have an issue with your database is when you start to get the screen with an error establishing a database connection.

Error Establishing database connection WordPress

You can also check your MySQL error log file on the server and try to find out some logs such as in the below image. To do so, use an FTP client such as FileZilla or an SSH client such PuTTY.

Using Filezilla, go to:

/var/log/mysql

and open error.log file.

search for corrupted tables like the following;

mysql error log file

 

If you prefer to use the command line, then use instead, an editor like nano using the following command line:

sudo nano /var/log/mysql/error.log

Again, check for a registry like the above example.

Once we have noticed there are problems, then we can try to repair the WordPress database.

Besides this kind of error, you should check for memory issues, as this can be the cause of the problem, being the corrupted files or the connection error the result.

php memory error mmap() failed: [12] Cannot allocate memory

php memory error PHP Fatal error: Out of memory

Options to Repair and Optimize the WordPress database

There are several ways to try to fix the WordPress tables corruption problems. We are going to cover some of the most popular solutions to do that.

Backup and Restore

This is the easiest one and the most effective. But you need to have a backup, right? Most of us, forget this essential tool. Backup regularly your website. Most of the host service providers have this kind of service available for free.

For the following alternatives, we strongly advise you to create a backup of the whole website before proceeding.

Fixing the “Error Establishing Database Connection” for WordPress

WP-config.php file is one of the most important files in your WordPress blog. This file, contains all the details need to connect your site to the database. If for some reason you have changed the username or root password, you must update wp-config.php accordingly.

define('DB_NAME', 'database-name');
define('DB_USER', 'database-username');
define('DB_PASSWORD', 'database-password');
define('DB_HOST', 'localhost');

So, the first step is to check whether we can connect with the WordPress database. For that, lets create a connection_db_test.php file using the details of wp-config.php

<?php
$link = mysql_connect('localhost', 'root', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Now save the file in the WordPress installation directory. Once it is saved, in the browser check the url of the file:

http://yourdomain.com/connection_db_test.php

If the connection is successful then you might have corrupted tables in the database. Pls, proceed for the next steps.

If the connection was not working, then the issue can be related to wrong credentials or a firewall blocking the connection.

Go to phpMyAdmin and go to user accounts. choose your WordPress username and make sure the password matches with the one in the wp-config.php file.

If you don’t remember, just create a new username and a new password in both files.

Test the connection again. Working? Check the website if it loads correctly.

For a firewall issue:

In case you have the chance, check your firewall log and notice if there are any warnings / blocked connections. If so, check with your hosting company in order to solve this. If you don’t use a managed hosting, you must do by your own.

In case you have the chance, check your firewall log and notice if there are any warnings / blocked connections. If so, check with your hosting company in order to solve this. If you don’t use a managed hosting, you must do by your own.

Repair and Optimize the WordPress Database using WordPress AutoRepair option

One of the first solutions you should try is to use the WordPress Automatic repairing tool by using the WP_ALLOW_REPAIR.

In order to use this, you will need to edit your wp-gonfig.php file in the WordPress directory.

To do so, add the following code just before the line showing “/** Absolute path to the WordPress directory. */“:

define('WP_ALLOW_REPAIR', true);

your code should look like this at the bottom of the file:

WordPress repair code WP_ALLOW_REPAIR

Save and close the file and then visit the following page:

http://yourdomain.com/wp-admin/maint/repair.php

You should get a screen like the below showing two options, repair WordPress database only, or repair and optimize WordPress database.

wordpress repair database screen

After you complete the repairing procedure, edit back the file, and delete the line you have added, since this functionality doesn’t need the user to be logged in to use it.

Repair the WordPress database with phpMyAdmin

If your web host has a cpanel or similar, open the phpMyAdmin module. In case your web host doesn’t provide you with it, and most likely you don’t have it installed, check this tutorial showing how to install and secure phpMyAdmin.

 

phpMyAdmin Repair Tables

Select the WordPress database on the left sidebar, then on the main screen, go to the bottom and select all tables. In the dropdown box, select repair tables. You should then get a screen showing the result.

Go back to your blog and check if it works fine.

You can also optimize the database selecting the Optimize Table option in the same dropdown box.

Memory problems

Offen, the problems with MySQL come from memory issues. While many of the tasks on the server side are need to be implemented once, optimizing MySQL is a regular task, that you need to care frequently in order to guarantee the best performance.

MySQLTuner is a tool written in Perl which helps you to optimize the performance of your MySQL database server.

Basing the install in a Linux based server, in order to install the MySQLTuner, write the following command lines, using your SSH Client.

sudo apt-get update

sudo apt-get install mysqltuner

Now to run MySQLTuner, execute the following command:

sudo mysqltuner

After some time, MySQLTuner will provide you a final report. Be aware that in order to MySQLTuner get the most reliable insights, you should run it only after 24hours since the last MySQL restart.

The report is split into 5 parts:

  • General statistics
  • Storage Engine Statistics
  • Security Recommendations
  • Performance Metrics
  • MyISAM Metrics
  • InnoDB Metrics
  • Recommendations
    • General Recommendations
    • Variables to adjust

In order to follow the MySQLTuner recommendations, you will need to change the mentioned variables in the MySQL config file, which should be located at /etc/mysql/my.cnf

In order to edit the file from the command line, execute the following command:

sudo nano /etc/mysql/my.cnf

After you have finished the changes, save the file and restart the MySQL server executing the following command:

sudo service mysql restart

Some points you should bear in mind, in order to don’t get further issues with the changes in the MySQL config file:

  • Consider the recommendations of MySQLTuner only if you run it at least 24hours or more, since the last restart, as the recommendation settings might be inaccurate;
  • In the case is recommended to change the values of tmp_table_size and max_heap_table_size, be sure that you change both and keep them with equal values;
  • If it is asked to change the join_buffer_size, be aware that this value is multiplied by the value of max_connections. Therefore is recommended you make small increases.
  • In case it is asked to increase the innodb_buffer_pool_size, do it for a large value as it should be enough to accommodate all innodb databases. In case your server is short on RAM, consider to increase it. Some web hosts plans like at DigitalOcean are scalable which is awesome for these kinds of situations.
  • Be sure to look carefully to the Performance Metrics section at the MySQLTuner report. If possible don’t use more than 50% of Maximum possible memory usage.
  • Every time you change the MySQL config settings, run again the MySQLTuner after restarting the server, just to make sure you haven’t set too high values on the maximum possible memory usage.
[OK] Maximum reached memory usage: 204.8M (20.63% of installed RAM)
[OK] Maximum possible memory usage: 352.4M (35.52% of installed RAM)
[OK] Slow queries: 0% (0/471K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Aborted connections: 0.02% (1/4015)

Where to go from here

If you want to learn more about repair and optimize MySQL database, you can check mysql official reference manual. We raise two main sections which could give you important insights about it, such as optimizing the MySQL server performance, you can consult its Reference Manual and also additional information about Backup and Recovery, such as database Backup and Recovery Types using mysqldump.

To proceed with further optimization, you can also check how to optimize the pagespeed of your WordPress site using gulp and optimizing it with the use of some plugins.

You might want also to check the best WordPress themes, with faster google pagespeed scores and giving you unlimited options to customize your website with drag and drop page builders.

If you have faced any issue and used a different solution, feel free to share it with the community. We will be glad to know it.

Have you enjoyed this tutorial? If so, supports us by sharing this article or Become a Patron!

About the Author:

Carlos Pinho
A father, a husband and a geek... Carlos was the founder of projects like The Tech Labs and Flash Enabled Blog. He is the founder of TekTuts He is passionate about technologies. Their main skills are in analytics, transport & logistics, business administration. He also writes about programming resources, trends, strategy and web development.