How to Secure and Optimize WordPress Database?
WordPress is a tremendous open source script. It requires MySQL and PHP to function. PHP works by retrieving information from the database to display on the site. As for MySQL, it is a database management system which is used to store information and settings of a WordPress site.
Although you don’t need to learn either PHP or MySQL to build a WordPress site, knowing them is a bonus. It helps understanding how WordPress works and how it handles everything dynamically. In this post, you will learn what is a WordPress database, how it works and finally how to secure it.
What is the WordPress Database?
When you install WordPress, you provide it with the database name, username, and password. This information is stored in WordPress configuration file i.e. the wp-config.php. During installation, 11 database tables are created, and default content is stored inside them.
Each of these tables contains different types of data i.e. posts, pages, comments, tags, categories, theme settings, etc. Whenever you create a new post, page or add a comment, WordPress runs SQL queries to build dynamic pages, and the content is stored in their respective database tables.
With a setup like this, you no longer need to create a .html file for each page. Everything happens dynamically. That is the beauty of WordPress!
Various themes and plugins use the WP database to store their settings and configuration data. Sometimes these 3rd party solutions create their own tables for that. These tables are not integral to the installation and hence are non-core database tables. Currently, these are the 11 core database tables, let’s take a look at them:
- wp_commentmeta: This table contains the metadata about comments.
- wp_comments: All the comments are stored in this table.
- wp_links: This table was used to include blogroll links in earlier WordPress versions.
- wp_options: This table contains site settings, activated plugins, time format, admin email, etc.
- wp_postmeta: This table contains the metadata about posts, pages and custom post types.
- wp_posts: This table stores all posts, pages and custom post types entries.
- wp_terms: Custom taxonomies, categories, and tags are all stored in this table. There can also be a wp_termmeta table, but that’s out of the scope of this article.
- wp_term_relationships: This table defines the relationships between taxonomies and post types.
- wp_term_taxonomy: This table differentiates between taxonomies (category, tag and link) for entries in wp_terms.
- wp_usermeta: All the metadata about registered users goes here.
- wp_users: All registered users are stored in this table.
Why Is Database So Important?
Let’s say you have been writing content, adding plugins and configuring them to tiniest details, for years. You have hundreds of posts, pages and tens of thousands of comments. Where does this stuff go? The answer is Database! Every post, page, category, tag, comment and setting which you make on a WordPress site, it gets stored in the database. It does not contain the theme, plugin and media files. However, all the text content goes in the database.
This is why you must take a great deal of precautions to secure your database. It is the backbone of your site.
It contains 90% of your content, excluding media files. If you don’t want your content to be stolen, infected or deleted, database security is a must.
How to Secure WordPress Database?
Being a popular CMS, WordPress is targeted 24/7 by hackers for vulnerabilities and exploits. There are many techniques to secure a database. Changing database prefix and restricting user privileges are two significant steps towards tighter security.
Change Table Prefix
Being an open source software, the names of the WordPress’ database tables are known to everyone. When it is installed, the table prefix
wp_ is assigned to all these tables.
wp_postmeta, etc. Users normally stick with this default prefix. Quick installers like Softaculous make installation of scripts fast and easy. However, even they keep
wp_ as the default table prefix.
Since hackers know this, they inject SQL attacks accordingly. This makes for easy prey. Changing the table prefix to something random and unpredictable is a good security measure. To change database table prefix, follow the instructions:
- Get started by keeping a backup of everything (there will be a backup plugins related post in this series pretty soon)
- Install and activate Change DB Prefix plugin.
- Go to Dashboard > Settings > Change DB Prefix
- Enter an unpredictable, random table prefix like dabra_ (keep it short, max 4 letters)
- Click “Save Changes.”
An obscure and hard to guess table prefix greatly reduces automated attacks and malicious scripts from being run on your site.
Backup Your Database
The wisest thing you can do is make regular backups. Always have a recent backup of your database. So, if anything goes wrong, you can just flip another server and get your site online ASAP. Here is how you can make a manual backup from phpMyAdmin:
- Go to cPanel > phpMyAdmin.
- Click your site’s database from the left side.
- Click the “Export” tab, select “Custom” as Export Method.
- Then select Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement under Object creation options.
- Finally, click “Go.”
You can attach this database with any WordPress installation and get your exact content back. But, if that’s hard, you can use one of the following plugins.
Creating & Scheduling a Backup Using Plugin
If you are unfamiliar with phpMyAdmin, or maybe your server doesn’t have one, then there are many backup plugins which you can use to backup your db. BackWPup is a popular backup plugin. It is a robust plugin and has a good set of useful features like storing backups on Dropbox, server, and S3. Let’s use it to create a backup:
- Install and activate BackWPup plugin.
- Once activated, go to BackWPup > Add new job.
- Select what you want to backup (XML file, theme & plugin files, database backup, etc.).
- The format of backup should be in the .ZIP format.
- Select where you want the zipped file to go e.g. on a server, to email, Dropbox or S3 service.
- Save the changes. Upon job creation, you can run it whenever you want, and a new backup will be created with same settings and sent where you specified.
- To schedule regular backups, go to Schedule tab on the same page.
- Select “with WordPress cron” under Start Job.
- Define backup schedule e.g. daily, weekly or monthly at the bottom of the page.
- In the end, click Save changes button.
Once a schedule is set up, you will receive backups at the destination you’ve specified while creating the job.
Database User Privileges
It is crucial that the MySQL user specified in the wp-config.php file has strict privileges. Giving it more permissions than it needs can be a bad decision. Strict MySQL user privileges take the security up a notch. During the installation, assigning ALL privileges to the database user is acceptable because of necessary tables, structure, and objects which are set by the MySQL user. However, ALL privileges should be a temporary measure. So once the installation is over, in most cases, the privileges that a MySQL user needs are DATA READ and DATA WRITE.
For uploading files, creating new posts, adding users and installing WordPress plugins DATA READ and DATA WRITE privileges are enough. The user does not need to alter database structure, so it doesn’t need administrative permissions such as GRANT. Let’s limit our MySQL user privileges by:
- Open your cPanel, scroll down to Databases section.
- Click MySQL Databases. On the next page, click the MySQL user added to your WordPress database.
- Change the privileges from ALL to following: SELECT, UPDATE, INSERT, and DELETE.
Certain themes and plugins require other privileges like ALTER to create their own tables. If a plugin requires more database privileges, contact the plugin developer to ask them about it. As soon as the plugin is activated and no longer needs the extended privileges, you can get back to basic settings.
Upgrading to a new version of WordPress also requires extended privileges. So, assign ALL privileges during this process and then retract them when done. These kinda security practices are better left for professionals to take care of and are not need in 90% of the cases.
Use a Strong Password
On a scale of 1 to 10, how strong would you rate your database password? Passwords like password12345 do more harm than good. A weak password is like putting a key under your doormat.
Since we are talking about database password, it is extremely important to make sure it is safe. Your DB pass should be longer than eight characters, should contain alphanumeric characters and character symbols. This password should be random, meaningless and not guessable at all. If it takes you more than 2 minutes to memorize yourself, then it’s a good password. Here are a few good password generators:
Once you have a strong password, follow these instructions:
- Go to cPanel > MySQL Databases, click “Change Password” for the user attached to your site.
- Change the password.
- After changing password, go to wp-config.php. Update the password there as well.
It is great to have a hard to guess table prefix and strict MySQL user privileges. However, you can never be too careful. So, take regular backups. Most people do not realize how useful a database backup can be unless they get hacked. Therefore, before it gets too late, I’d recommend employing all the database security techniques mentioned above. Trust me these are very effective.
Have you ever thought about securing the WordPress database? What techniques do you use? Share in the comments below.
Finally, you can catch all of my articles on my profile page, and you can follow me or reach out at Twitter @mrahmadawais; to discuss this article. As usual, don’t hesitate to leave any questions or comments below, and I’ll aim to respond to each of them.