Essential Security Plugins: WP-DBManager

The foundation of any secure WordPress installation is the ability to recover from an attack. To do that you need the ability to restore your site from a known good version. I’ll show you how to setup and use of my favorite plugins to recover as quickly and painlessly as possible.

The plugin WP-DBManager from prolific plugin creator Lester “GaMerZ” Chan allows you to perform a suite of important database functions. These functions provide an important foundation of features that I consider essential for a secure and efficient WordPress installation. Although very proficient at managing many important database functions, WP-DBManager is not a complete database management solution and should be paired with more powerful database tool like the online tool phpMyAdmin.

What You’ll Need

You will need to install the plugin either by uploading the zipped plugin from a download via the WordPress Upload Plugin function or by installing it directly from the WordPress Install Plugin feature. Details about the plugin are available here. Once you have downloaded, installed and activated the plugin it is time to configure it.

Setting up WP-DBManager

You will notice that after WP-DBManager has been installed and activated there is a new menu item below the Settings menu panel called Database. The first submenu setting panel under Database is also called Database and doesn’t contain any configurable settings, but does contain quite a bit of information. At the top of the Database settings panel is a listing of the details of your MySQL database including; Database Host, Database Name, Database User, Database Type and Database Version. This information is a reflection of how your database server is set up and can be used to troubleshoot issues with the database. However, you will probably not ever need to use it.

Below the Database Information listing is the Tables Information listing that details all the tables in your specific database, the number of records in each table, how much data they use, how much of the index each table uses and how much overhead each table uses. Again there is nothing you can configure in this listing, but the information listed can be useful. It is good to get familiar with the tables and their footprint (records, usage and overhead) and to monitor both the listing and their footprints over time. If you notice new tables or a specific table growing in a way that doesn’t make sense, this could be an indicator of an attack or trouble.

The Backup DB settings panel offers the first opportunity to interact with the plugin, but there aren’t any options to configure so we will explore this setting panel for the next section. Only the settings panel DB Options has anything to configure so jump to that panel next.

The DB Options settings panel offers just a few options to configure and it is likely that the plugin automatically set them up for you, but it is important to review them and make sure they are correct. The first option is the Path to mysqldump. This is a setting that is most likely automatically configured when you installed the plugin, but if it isn’t or if you are having trouble involving the mysqldump script, then clicking the Auto Detect button will seek out mysqldump on your specific server. If the Auto Detect function doesn’t work, then you will need to contact your host or site admin for specific details on where mysqldump is located.

The Path to mysql option is the next setting that can be configured, but if your WordPress site is up and running, it should already be configured properly. If for some reason you need to point your site at a new MySQL database, this is probably not the best way to do it and that would be out of the scope of this article. Again the Auto Detect button will likely input the proper URL if you accidentally alter this option.

You can specify where you would like all the backups to be saved on your server. The plugin automatically creates the directory /backup-db within your /wp-content directory, but you can change this location by creating a new directory and then specifying the path to that directory in the Path to Backup configuration option. If you change this location, make sure that the directory is writable with CHMOD settings of 777. If you are concerned about your backups falling into malevolent hands it makes sense to point the directory to a location that is more secure and inaccessible by the public.

To prevent filling your site server filling up with too many database backups, you can limit the number backups that are kept with the Maximum Backup Files option. Once the number of backups reaches this number, the oldest backup is deleted automatically.

There is a Note that gives some tips on where to find both the Path to mysqldump and Path to mysql locations for a Windows or Linux server.

The Automatic Scheduling section provides the first significant configuration options for this plugin. The Automatic Backing Up Of DB set of options controls the automatic backing up of your WordPress database. You can set it to backup every N Minutes/Hours/Days/Weeks/Months and when it backs up the MySQL database should it then GZIP the resultant flat file. When the database is backed up, it can then be emailed to the email address of your choosing. If you don’t provide an email address, the plugin will simply save the file to the designated backup folder.

Unless your site is very active with lots of new posts a day or week and lots of comments, it probably makes sense to set the time to backup once a week. Sites that seldom have many changes might be able to get away with once a month. If you set the option to backup on an hourly or minute basis, you will need to adjust the number of versions saved in the Maximum Backup Files option or you really won’t have much protection since you may be faced with a situation where the good backup version is quickly overwritten by a bad backup version. It is also important to note that the mysqldump function takes an amount of time to work. The larger the database, the longer the amount of time it takes to backup. It is conceivably possible to have a dangerous situation where the size of the database is large enough that it is unable to complete a cycle of mysqldump before the next one is supposed to start. This could quickly cripple the site.

A similar function to the automatic backup is the Automatic Optimizing function which is configured with the Automatic Optimizing Of DB option. You can again configure this by setting the optimization routine to run every N Minutes/Hours/Days/Weeks/Months. This process cleans up some of the bloat that creeps into every database and can help the database and therefore the site run more smoothly. Once your database gets big, the optimization process can have a big impact, but it is important to know that the optimization process has overhead of its own and should  be scheduled to run when the site is at its least busiest.

When you make a change to the Automatic Backing Up Of DB or Automatic Optimizing Of DB options, it will also trigger a manual backup of the database. But you have lots of other ways to manually update the database and perform other useful functions and we will look at how to use them in the next section.

Using WP-DBManager

The first and most important section we will use in WP-DBManager is the Backup DB panel. The top section of this panel provides you some basic information about the Backup Status. The plugin performs some checks on the database and the scripts it will use to help manage it and displays their condition. If everything looks ok, you will get a message that states, “Excellent. You Are Good To Go.” Although the status reporting section of this panel works quite well, it is not foolproof and is still undergoing testing.

Beneath the Checking Backup Status section of the Backup DB panel is the Backup Database section that is the most important function of this plugin and where we create a manual backup of the WordPress database. This is the core reason why we installed WP-DBManager! You can choose whether to GZIP the resultant backup file or not and then all you need to do is click the Backup button. If a backup is successful, it will result in a message at the top of the panel stating something to the effect of, “Database Backed Up Successfully On ‘[Month] [Day], [Year] @ [Time]‘.” The file will be saved on your server in the directory that is specified in this panel and listed in the Database Backup To row. And with that you have backed up your database! But it is still on your server and therefore not in an especially safe location if your server gets hacked. So with the next Database settings panel, Manage Backup DB, we can manage our backups including emailing it to an email address.

The Manage Backup DB settings panel is where we manage all of the stored backups. On this panel we see a listing of all the backups listing their date of creation and their size. Please note that the files are organized in reverse chronological order with the newest files on top and the oldest files on the bottom. On the right edge of the file listing are radio buttons for each row in the list. To perform a function on one of the files, you select it by clicking its corresponding radio button. And there are some powerful functions you can do with those files including Email the file to an email address you specify, Download the file to your computer, Restore the database to the selected version and Delete the selected database backup file.

Another important function provided by this plugin is the optimization of the database. By cleaning up empty fields and running other optimization processes, WP-DBManager can help keep the database running fast and healthy. Although this process isn’t an explicit security function, keeping your MySQL database in good shape is important and may help prevent exploits. The Optimize DB settings panel offer the opportunity to toggle on and off each of the fields in the database table determining whether they are optimized or not. The optimization process is set in motion with a click of the Optimize button at the bottom of the page. After a quick process, the plugin will report with a list all the tables that were optimized. The plugin recommneds running the optimization process once a month which can be configured to be performed automatically via the DB Options settings panel.

A very similar settings panel exists in the Repair DB panel where instead of running an optimization process WP-DBManager runs a repair process. Although the plugin doesn’t specify what actions it performs in the repair process, but it appears to be the standards MySQL repair function. I have to question the usefulness of this plugin. If the database really needs repair, I suspect WordPress won’t be running and therefore you won’t be able to access this panel, but it may prove useful if there are minor issues affecting a limited range of tables in the database.

Advanced Uses of WP-DBManager

There are two settings panels in  WP-DBManager that should be considered advanced because they both have the potential to devastate your database if used improperly. Empty/Drop Tables and Run SQL Query both offer very powerful access to your database, but can be destructive if used improperly.

Empty/Drop Tables is a very useful panel for performing some whoesale cleaning of your database. If, for example, you have stopped using a plugin, but its tables remain in your database, you can remove them manually by selecting the corresponding row’s radio button in the Drop column in the Empty/Drop Tables panel and then clicking the Empty/Drop button. Be aware that this will completely remove that table from your database and destroy all the content that table contains. You will be given a chance to reconsider and must click the Confirm button before the process will proceed.

If you want to keep the table, but simply want to clear out all the data that it contains, the Empty functionality is what you are looking for. Sometimes plugins and/or the core WordPress functionality get a little disconnected and the results are data in tables that are remnants from previous problems. Emptying that data from a table will wipe it clean and restore order to your site. It is recommended that you backup your database before performing either of these functions because the can both result in significant data loss.

The most powerful panel in WP-DBManager is the Run SQL Query panel that will execute a properly formatted and constructed query on your database. If you are an advanced user with a strong understanding of SQL, this can provide you powerful access to your database without the need to access a terminal or a specific database management tool. Use of the Run SQL Query panel can provide almost unlimited access to the data within the database as well as the very structure of the database. You can retrieve information from the database, alter that information, create new information, create new tables or delete existing tables. Like any powerful tool, used improperly it can be very dangerous. It is recommended that only experienced SQL users make use of the Run SQL Query settings panel and that they do so with care and appropriate safeguards.

Recovering from Disaster

WP-DBManager cannot itself help a site administrator recover from a total database meltdown. It can be very useful if a user accidentally deletes or mangles content on the site or if an attack vandalizes content on your site. However, this recovery process is only as good as your best backup. If the content you want to recover by reverting to a previous backup does not exist because no backup exists the recovery process won’t be of any use. If you have implemented a well though out backup program and have an appropriate number of backups saved to both your server and some offsite location, WP-DBManager can help you turn back the clock and restore a previous version of the database.

This process is performed within the Manage Backup DB panel. Select the database backup that you would like to restore from and click the Restore button at the bottom of the page. You will be asked to confirm this action and warned that any content saved to the database after the selected database backup will be lost. If you are hoping to lose the content saved after the selected database because it contains bad content or is missing content that once existed, then proceeding is appropriate. The database will be replaced with the selected database and your site will be restored to the condition the backup preserved.

Summary

WP-DBManager is a powerful and very useful plugin that at first blush might not be considered a security tool. The plugin’s ability to preserve, manage, modify and restore the WordPress database makes it one of the essential plugins for running a secure and protected site and should be considered an essential in every WordPress install.

WP-DBManager [xrr rating=5/5 label=""]