How To Optimize, Repair or Backup Your WordPress Database

In today’s short tutorial we’ll have a look at how to take care and backup our site’s database. This way we can make sure that our site runs optimally and prevent unexpected data loss.

What is the database?

According to Wikipedia a database is an organised collection of data, generally stored and accessed electronically from a computer system. This means that the WordPress database is the place where all our site’s generated data lives, posts, pages, users, comments and more, everything is stored in it. For more information about the WordPress database you can have a look at this WPBeginner glossary entry, and for an in depth look at the default tables and their relations you can check out the database description in the codex.

From all the above we can easily figure out that the database is pretty important for the well-being of our site, any corruption can result to irreversible loss of data and accumulation of clutter might make queries, and by extension our site, slower. Below we’ll find out how we can backup, optimize and repair our database, to prevent these issues.

To help us with these tasks we will be using the WP-DBManager plugin which is available for free in the WordPress plugin repository.

After we install and activate the plugin we will get a new settings page titled Database in the administration sidebar. The first sub-menu page gives us some information about the database and an overview of the existing tables. In the table overview you will most likely see far more tables that the default ones you read above, these are created by plugins and themes in order to store their data. Along with the table name you can see how many records it contains, how much disk space it takes up and any existing overhead.

Now that we got an idea of how our database looks like, let’s back it up before optimizing it.

Backing up the database

The Backup sub-menu page is pretty straight forward, the plugin checks if everything is OK in order to proceed with the backup process and notifies you if any errors occur. If everything is good to go just scroll down to the Backup Database section.

Here you can select to compress your database backup using GZIP, you can do that if your database is rather large in order to save space on your server, then click the backup button to proceed with the backup. That’s it, your database is backed up. You can navigate to the Manage Backup DB sub-menu page to see and manage your backups.

Here you can see the name, date/time and size of your backups. You can email, download, restore or delete a backup.

Optimize the database

Next navigate to the Optimize DB sub-menu page. Here you will get a list of all your database’s tables with an option next to each one, using that option you can choose to exclude a table from the optimization process. To proceed scroll down and click the Optimize button, this process reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. That’s it, next you will get an admin notice that the selected tables were optimized successfully.

Repairing the database

The process is identical to the optimization one. Should you need to repair any particular tables on your database you can navigate to the Repair DB sub-menu page, select the tables you need to repair and click the Repair button at the bottom of the page. It is recommended to repair the database only if you are having issues with particular tables and not periodically like you can do with the database optimization.

Automating the processes

You can set the plugin to automatically create backups and optimize your database so you have less to worry about. To do that navigate to Database > Settings and scroll to the Automatic Scheduling section.

Here you have to set the frequency for each process. Make sure that database back ups are created often enough to minimize data loss in case of database corruption. If you have a site that is updated daily or more often you can set it to be backed up one or more times daily, if you only publish a post per week and don’t have many comments submitted, you might be okay with a weekly backup schedule. Next you can set the frequency of the automatic database optimization, similarly to the backup, an active site with multiple weekly posts and active comments sections might need a monthly database optimization, a rarely updated site would be just fine with a bi-monthly or even rarer optimization schedule. Once done save your configuration by clicking the Save Changes button at the bottom of the page.

That’s all for our small tutorial on database optimization! If you have any questions or suggestions on other tutorials you’d like to see on our blog, let us know in the comments below!