Backup, Optimize or Repair Wordpress Database via WP-CLI
How to Optimize, Repair and Backup Wordpress Database using WP-CLI
Wordpress is the most popular content management platform powering the largest part of websites globally. The main reason behind its popularity is that it is beginner friendly and has some more benefits in terms of SEO. When it comes to speed, you may come across some other content management systems that are faster but in terms of use, as I already mentioned Wordpress is more beginner friendly and more suitable for creating a large variety of websites. You can even use it to build an ecommerce website and there are plugins and themes that provide you additional functionality to successfully operate your blog or online business built using Wordpress. Wordpress is made up of php and mysql. When we install Wordpress on a server, we also install and Mysql or Mariadb database which plays a central role in terms of wordpress performance. The database stores all the information you have on your blog including the content, urls, user information and so on. It also plays an important role in terms of speed and blog performance. A well optimized database means a faster loading blog and superior user experience. It is important to mind your database security and performance to ensure that your blog is functioning properly.
It might not be much of a problem for small blogs or new users, but as you continue to add content to your blog, the size of your database grows. Sometimes you add and remove plugins and themes which also leave behind a lot of data in the database that might need to be manually removed. Cleaning the database from time to time and optimizing your database helps speed up your wordpress website. There are multiple ways to do it including logging into your database using phpmyadmin and then doing the work manually or using a dedicated plugin for Wordpress database management. However, if you need to optimize and repair database tables or clean transients, you will not always need to use a plugin or phpmyadmin to complete these tasks. With just a few commands, you can do the same using WP-CLI. The Wp-CLI or Wordpress command line interface is a command line tool that can be used to manage your wordpress installation including your wordpress database. The WP-CLI is useful in a lot of cases and especially when you have to run a sitewide search and replace as well as several more similar cases. However, in this post, I will keep the discussion limited to the Wordpress database and how to optimize and repair it as well as clean transients using the WP-CLI.
How to Install WP CLI:
The first step to managing your Wordpress installation using the WP CLI is installing the CLI. It is not a complex process. First ssh to your server and once you are connected, you can download the PHAR build, make it executable and place it on your PATH. 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, make it executable and place it somewhere on your PATH:
chmod +x wp-cli.phar
sudo mv wp-cli.phar /usr/local/bin/wp
If you run wp –info, you will see an output like the following, which indicates successful installation:
root@wordpress:/var/www/html# wp --info
OS: Linux 5.15.0-86-generic #96-Ubuntu SMP Wed Sep 20 08:23:49 UTC 2023 x86_64
Shell: /bin/bash
PHP binary: /usr/bin/php8.2
PHP version: 8.2.8
php.ini used: /etc/php/8.2/cli/php.ini
MySQL binary: /usr/bin/mysql
MySQL version: mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
SQL modes: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
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/html
WP-CLI packages dir:
WP-CLI cache dir: /root/.wp-cli/cache
WP-CLI global config:
WP-CLI project config:
WP-CLI version: 2.8.1
Take a backup of your Wordpress Database
Now that you are going to work on your wordpress database, it is always safe to keep a backup ready. In case anything happens, you can just import your database and bring your site back online. While downloading a backup of the Wordpress database may sound complex, it is actually quite easy using the WP CLI and can also be done through phpmyadmin or via SFTP or using a plugin. However, the easiest and simplest possible plus the least time taking method for downloading a database is the WP-CLI’s db export command.
Now that you have the WP CLI installed on your server, just login to your server using SSH and then change directory to the root folder of your blog. The root folder is the folder that stores your Wordpress files (usually located at /var/www/html). If you do not change the directory, you must add the path to the root directory at the end of the WP-CLI command like –path=/var/www/html.
You can download a simple backup of your Wordpress database using the wp db export command and by adding a desired name for the backup in the form of mywpdb_backup.mysql
wp db export mywpdb_backup.mysql
This will save a backup of the Wordpress database in the root folder and you can check out using an FTP client like Filezilla or Cyberduck.
In case you want to use the backup if anything goes wrong nad your site goes offline, all you will need to do is to import the same backup which can be done easily with the WP-CLI command wp db import.
wp db import mywpdb_backup.mysql
You can name the backup file (which is a sql file) anything according to your convenience. We have used the name mywpdb_backup.mysql.
Optimize and Repair Wordpress Database Tables Using WP-CLI
Just like you run a backup of your wordpress database, it is very easy to repair and optimize database tables using the CLI tool.
If you need to repair your wordpress database, all you will need to do is to run the wp db repair command. SSH to your server and change directory to the root directory or you will be required to append the path to your root directory at the end of the command. (Please also note that you cannot run WP-CLI commands as the root user or if you have not created another user and want to run commands as root user you will need to add the –allow-root flag).
wp db repair
Optionally, if you are not inside the root folder:
wp db repair --path=/var/www/html
What does the above command do?
According to Wordpress, “Runs mysqlcheck utility with –repair=true using DB_HOST, DB_NAME, DB_USER and DB_PASSWORD database credentials specified in wp-config.php.”
In the same manner as above, you can run wp db optimize to optimize your database tables and within seconds you will have your Wordpress database tables optimized.
Run the following command to optimize your wordpress database tables:
wp db optimize
What does the above command do?
Runs mysqlcheck utility with –optimize=true using DB_HOST, DB_NAME, DB_USER and DB_PASSWORD database credentials specified in wp-config.php.
You may receive an output like the following:
wordpress.wp_yoast_seo_links
note : Table does not support optimize, doing recreate + analyze instead
status : OK
And at the end, you will see the output Success: Database optimized.
Wordpress Database Clean Transients
If you want to clean transients from your dataabse, you can do that easily using the wp cli.
The command for clearing transients is wp transient delete. However, you can optionally clear all transients or only the expired transients in the following manner.
To clear only the expired transients :
wp transient delete --expired
You will receive an output like the following :
Success: 12 expired transients deleted from the database.
To clear all the transients in your wordpress database :
wp transient delete --all
You will again receive an output like the following:
Success: 14 transients deleted from the database.
So, this is how you can easily manage your Wordpress databse using the WP-CLI. There are several more databse commands that you can use to work with the wordpress databse. For example, you can drop or delete a databse or reset it if you want to start from scratch. However, always take a backup first.