Repair and Optimize WP-Database with WP-CLI

Repair and optimize wordpress database without a plugin using WP-CLI

The wordpress database has a central role in terms of site speed. If your site is loading slower, you may need to take a look inside your database. You can look inside the tables included in your database to find out the issue and if tables have become too large and overloaded with obsolete data. There are several situations where you may need to optimize or repair your database. Regular maintenance of your wp database also helps keep your site fast.

There are more than one ways to do this and one of them is to access your database through phpMyAdmin and then repair and optimize tables. You can also perform these tasks through the cpanel. Another method is to use a plugin for repairing database or optimizing tables. There are both free and premium plugins to achieve the task. You can also maintain your database without using a plugin, whether you want to backup your database, repair and optimize tables or clear transients.

The WP-CLI also provides an easy method to repair or optimize the wordpress database, without having to login to cpanel or phpMyAdmin. You do not need to change anything inside the wp-config.pho file either. However, to perform it you will need to have the WP-Cli installed and configured.

Repair and optimize database using phpMyAdmin

To repair your database or optimize the tables, you will need to login through phpMyAdmin. Select the database you want to optimize from the left sidebar. Click to show the tables included in the database. Now, select all the tables and from the dropdown menu at the bottom select repair table or optimize table as you want. The system will automatically repair the tables once you have clicked on ‘repair table’.

Repair or Optimize database tables using the wp-cli

The WP-CLI or the wordpress Command LIne Interface is an excellent tool to manage your wordpress installation including the database. However, to manage your website using the CLI, you will first need to install it on your server and then you can start managing your blog using WP-CLI commands.

Install WP-CLI

You will first need to install the wp-cli on your server if you have not installed and configured it.

First you can download the wp-cli.phar using wget or curl

$ curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar

Now, verify that it works:

$ php wp-cli.phar –-info

Now, you need to make the file excutable and move it somewhere in your PATH so you can run the cli commands by typing wp like – ‘wp plugin install’. To do that run the following commands:

$ chmod +x wp-cli.phar

$ sudo mv wp-cli.phar /usr/local/bin/wp

Now, run ‘wp –info’ to verify it works:

$ wp --info

You will receive an output similar to the following showing wp-cli has been installed:

OS: Linux 5.15.0-1028-aws #32-Ubuntu SMP Mon Jan 9 12:28:07 UTC 2023 x86_64

Shell: /bin/bash

PHP binary: /usr/bin/php8.1

PHP version: 8.1.2-1ubuntu2.11

php.ini used: /etc/php/8.1/cli/php.ini

MySQL binary: /usr/bin/mysql

MySQL version: mysql Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper

SQL modes:

WP-CLI root dir: phar://wp-cli.phar/vendor/wp-cli/wp-cli

WP-CLI vendor dir: phar://wp-cli.phar/vendor

WP_CLI phar path: /var/www/aim-blog/html

WP-CLI packages dir:

WP-CLI cache dir: /home/ubuntu/.wp-cli/cache

WP-CLI global config:

WP-CLI project config:

WP-CLI version: 2.7.1

Now, you can use the wp-cli on your server to carry out various tasks related to wordpress and wordpress database.

Repair database using WP-CLI

To repair the database using the wp_CLI, all you need to do is to run the wp db repair command. The WP-CLI uses the credentials stored in the wp-config.php file to carry out these tasks. when you run the wp db repair command, the cli runs the mysqlcheck utility with –repair=true, using the credentials (host, db name, user and password) specified in the wp-config.php file.

To start repairing your database, ssh to your server.

Run the wp db repair command:

$ wp db repair --path=/var/www/html

At the end of the command, specify the path to the root folder. Otherwise, you can run the commands directly from the root folder.

$ cd /var/www/domain/public_html

$ wp db repair

Optimize wordpress mysql database using WP-CLI

You can opitimize your database tables in a similar manner as you repaired the database. You will need to run the wp db optimize command to start optimizing your database. When you run the wp db optimize command, the cli runs the mysqlcheck utility with ‘–optimize=true’ using the database credentials and hostname specified inside the wp-config.ph file.

To start optimizing your database, ssh to your server and run the command:

$ wp db optimize --path=/var/www/domain/public_html

Otherwise, you can run the command from the root folder. You will need to go to the root folder.

$ cd /var/www/domain/public_html

$ wp db optimize

The output will look like the following and at the end a message that your database has been successfully optimized:

$ wp db optimize

WordPressdb.wp_commentmeta