WordPress Database Tutorial: Security, Backup and Efficiency Tips

WordPress is currently the most important open source website publishing software. It has diverse applications, ranging from self-publishing content (blogging) to acting as a full-fledged content management system. Yet, only a few tutorials give serious emphasis to the WordPress database, which is the core of WordPress operations. This tutorial seeks to remedy this situation.

As we all know, WordPress is a PHP-powered dynamic website platform that depends on the MySQL database; content, and everything else, including website settings, are stored in it.

This tutorial contains some semi-advanced tips and techniques for working with the WordPress MySQL database. In this tutorial, you will learn three important topics which every WordPress website developer should know.

First, you will learn some WordPress database security tips. One of the down sides of the application is that WordPress is frequently attacked and exploited by hackers because of its highly familiar database structure and overall popularity among web publishing software. Luckily, the creators of WordPress provide frequent updates to protect against any potential exploits (this is why you need to regularly update your WordPress version). This tutorial will also suggest some ways to harden the database to make hacking methods such as SQL injection look impossible.

Second, you will learn how to perform a WordPress database backup. As we all know, the web is unpredictable. At any time, accidents can happen; that includes getting your hosting agency files exploited and deleted by a hacker. Without a backup, it can be impossible to start all over again. This is why doing frequent WordPress database backups is very helpful.

Third, you will learn how to make your WordPress database more efficient. Some WordPress websites are hosted in a very limited environment that won’t allow the creation of more than one database. However, creating multiple databases can be useful for diverse WordPress applications (such as having a separate MySQL database for English language content and another for customized French-based content). This can be a difficult thing to do, and in this tutorial, suggestions are made for how to efficiently handle a single MySQL database for different WordPress applications.

{mospagebreak title=WordPress Database Security Tips}

As I said earlier, lots of WordPress websites get hacked because of their very common WordPress database setup, and the failure to update to a more secured version. After installation of WordPress on your hosting server, the software creates a database table such as the one shown in the screen shot below:

This is a list of the WordPress default database tables (there are 10 of them). Every WordPress website on the Internet starts out with the exact same database setup unless and until they do something different. 

This highly similar nature of WordPress database table names can be easily exploited, especially if you have an outdated WordPress version. This is because hackers know that WordPress database tables starts with "wp_".

Luckily, WordPress creators provide users with a way to change this. And this is easily done in the WordPress installation stage. To do this:

1. Open the wp-config.php file.

2. Find this section:

* WordPress Database Table prefix.


* You can have multiple installations in one database if you give each a unique

* prefix. Only numbers, letters, and underscores please!


$table_prefix = ‘wp_’;

Replace the value of the $table_prefix variable with any name you like (that follows the WordPress table prefix naming rules). For example, I will change it to "codexmwordpressdatabase." Below is the screen shot that shows how to make the changes and the results in the database (using phpmyadmin):

Bear in mind that changing the table prefix is not the only solution that helps protect your WordPress MySQL database. There are lots of features that can enhance security.

For example, you can assign a specific IP address that can only have admin privileges in the MySQL databases. This will prevent anyone from getting admin privileges if he is using an IP address that is different from the one that is allowed to have admin privileges. It’s a little bit tricky to do, but it’s possible. You can check the specific thread in the MySQL forums for more information. 

This can be accomplished using the MySQL grant statement.

You can also assign the strongest possible MySQL database username/password and secure your log-in with SSL or SSH. This may vary from one hosting agency to another. For example, one hosting agency may allow you to use more than eight characters as a password and provide you with an SSL login (https//), while another hosting company does not allow passwords to exceed eight characters and does not provide you with a secure log-in.

You might want to change your WordPress admin password directly in the MySQL database under database users. However, you need to have it in MD5 format.

If you really want to improve your website’s security, do not place your passwords and usernames online or in your FTP server in clear text form, even if you think they are blocked, encrypted or secured.

{mospagebreak title=WordPress Database Backup Procedure}

While there are many plug-ins and hosting features that allow you to backup MySQL databases, the simplest method does not involve any plug-in or hosting features at all. This will make you less dependent on plug-ins and your hosting company, making you more proficient and technically knowledgeable in working with databases.

To back up the WordPress database, follow this simple procedure:

1. Log in to your WordPress phpmyadmin.

2. Once logged in, click "Export" on the phpmyadmin dashboard (see screen shot below).

3. The Export options will be shown. Under "Export," select the name of your WordPress MySQL database (not shown in screen shot).

4. Check "SQL."

5. Check "Save as file."

6. Finally, click "Go."

7. Download the database to your local computer/desktop. You have successfully backed up your database. The filename extension should be .sql; in order to use it again, you will use the "import" feature.

{mospagebreak title=Improving WordPress Database Efficiency}

In most WordPress database default installations, one database is good for one WordPress website. And as said earlier, launching another WordPress website requires the developer to create another database if this is allowed, but it can be restricted by some web hosts (such restrictions are mostly associated with free hosting accounts).

In this case, a single allowable MySQL database is not utilized to the fullest (to be used by more than one WordPress-powered website). However, the creator of WordPress allows website developers to use only one MySQL database for more than one WordPress website. This is not commonly done, but is a very efficient way to use your MySQL database.

In the procedure described below, it is assumed that the first WordPress website has been completely launched and set up using the original, single MySQL database.

You are going to use the same database to create another WordPress.

Step 1: Open the wp-config.php of the new WordPress website (to be set up) using your favorite PHP editor.

Step 2: Find the following details below and enter the MySQL database access information:

// ** MySQL settings – You can get this info from your web host ** //

/** Enter the same WordPress database name you use with the other WordPress websites */

define(‘DB_NAME’, ‘Yourwordpressdatabasename’);

/** MySQL database username of the database you use above */

define(‘DB_USER’, ‘root’);

/** MySQL database password */

define(‘DB_PASSWORD’, ‘XXXXX’);

/** MySQL hostname */

define(‘DB_HOST’, ‘localhost’);

Okay, once the above parameters are changed, look for this line in your wp-config.php file and change the table prefix name to a new one:


* WordPress Database Table prefix.


* You can have multiple installations in one database if you give each a unique

* prefix. Only numbers, letters, and underscores please! The prefix below should not be the same as the one you’ve used before


$table_prefix = ‘codexmwordpresspart2_’;

Once the wp-config.php changes have been completed, proceed with the WordPress installation (for the second website). And then, when you look at the MySQL database, you will see that a new set of 10 MySQL database tables has been created, in addition to the previous tables. See the screen shot below:

Inside the red box are the tables from the original/first WordPress website database tables. The yellow box contains the tables created for the second, new website.

[gp-comments width="770" linklove="off" ]