What is as important as water, sunshine, the apple of your eye, morning coffee, or an Internet connection?
Answer: your WordPress website database. If there's anything to pamper, cuddle, watch over like milk on the stove, it's it.
In fact, it is simply the most important element of your site. The one that makes it work.
The one which also messes up when it stops working (yes, it happens).
If you've ever struggled with a database connection error, you know what I'm talking about: the white screen, the cold sweat and the like.
Since our DB or database is the fickle type, knowing the best practices to tame it will not be too much to ask.
This is the purpose of this new article, which will present you with lots of concrete tips to create, manage, optimize, secure and repair your WordPress database like a pro.
Presenting the database and how it works on WordPress
To begin with, let's see a global definition of a database. According to the Larousse Encyclopedia, it is a “structured set of files grouping together information with certain characteristics in common”.
More specifically, to fully understand what a database on WordPress is, let's start from the base, no pun intended.
It is important to know that WP is composed of two main elements :
- On the one hand, you have the core files. You know, these are the files you find in the wordpress folder, after you download it. Most subfolders begin with the prefix wp-.
- On the other, you have the database, which you are asked to create when you install WordPress. To work, this database uses a relational database management system called MySQL. The latter is responsible for storing your site's information. To display this information to your visitors, WordPress then executes what are called database queries (or SQL queries), which are interpreted using the PHP language.
Why is this database so important?
Quite simply because it's inside that we find ALL the contents of your site, namely your articles, your pages, the settings of your plugins, comments, revisions, site options etc.
To put it another way, if your database disappears (e.g. in case of hacking or a bug on your hosting server), you lose everything. Absolutely everything.
Unless you've taken the lead in saving it (I'll show you how to do that later).
As you can see, the database is the engine of your site. If it breaks down, you stay on the side of the road.
So to fully understand how all this mechanics work, I suggest you go to the tables.
Does that intrigue you? I'll explain just below.
The WordPress database: a history of tables
Tables, tables and more tables: that's what a WordPress database is all about.
What exactly is a table? For the sake of imagery, let's go back to high school, if you don't mind.
At the time, in my school, there was a space dedicated to storage, with dozens and dozens of lockers for the students.
Well, let's say the overall locker structure represents your database.
And each individual locker, one table.
Each table contains information about the content of your site. For example, the
wp_comments table will store all the comments of your site.
It is important to know that by default, after its setup, your database contains 12 tables.
So grab a chair, sit at the table, and let me make the introductions:
wp_commentmeta: this table stores the metadata of the comments (metadata are data that accompany the comments, author, author's email, time of publication, and article on which it was written).
wp_comments: this table stores the comments.
wp_links: this table stores lists of links to blogs, but it is no longer used (so I have no example to display, as it returns an empty result ;-)).
wp_optionsThis table, one of the most important, contains all the options of the site – those contained in the Settings menu of your WordPress administration. I'll tell you more about it below.
wp_postmeta: this table stores metadata for articles, pages and custom post types.
wp_posts: this table contains all your publications (articles, pages, custom post types).
wp_termmeta: this table allows to record additional data for each taxonomy term.
wp_terms: this table contains all the taxonomies of your site (categories, tags, custom taxonomies etc.).
wp_term_relationships: this table contains the relationships between content types and taxonomies (i.e. which taxonomies apply to which publications).
wp_term_taxonomyThis table stores the taxonomy of each term, which is important, because a term can be present in several taxonomies (e.g. in a category and a custom post type).
wp_usermeta: this table stores all metadata related to users.
wp_users: this table contains all the users of the site, whatever their role (administrator, subscriber, editor etc.).
By default, these tables all start with the prefix
wp_, but you may have a different prefix at home, if you have changed it. This comes as a good practice that I'll show you at the end of this post.
You may have started to see this through the above captures: each table is in the form of an Excel table with a variable number of columns.
Each line corresponds to a record in the database.
For example, in the
wp_options table of my site (capture above), you can find :
- Its URL
option_namecolumn), i.e. the address of the website, on line 1.
- The URL of the
homepage on line 2.
- The blog
nameon line 3.
- The administrator's email
(admin_email) on line 6 and so on.
WordPress uses these options to operate, display content, etc. Sometimes these data can be modified, and sometimes WP manages them alone.
4 basic things to know
Before moving on to the next part, let me make four additional remarks about the WordPress database :
- The 12 tables developed above are part of the “Core” of your database. This means that they will be here by default on every new DB you create.
- To store their data, the enhancements you use can use the default tables in your database (for example, table wp_options). However, some plugins create their own tables. This is Yoast SEO‘s and WooCommerce‘s case, to name a few.
- You can find the login information for your WordPress database (database name, user, password, database address etc.) in your wp-config.php file.
- A multi-site network's database has a few specificities. Each site has separate tables in the database: 9 of the 12 “native” WordPress tables are duplicated for each site.
All right, are you still there? Now, we're going to dive deeper into the subject.
It's time to learn how to create your database.
Come on, get on board!
How to create a database quickly and easily
For a beginner, the easiest way to create a database on a site ready to go online is to use cPanel, provided your web host offers one.
BlueHost, one of WPMarmite's partners, offers for instance example cPanel.
Your best WordPress projects need the best host!
WPMarmite recommends Bluehost: great performance, great support. All you need for a great start.
The cPanel is the administration interface of your hosting that allows you to manage a large number of features in a few clicks, and this in a rather visual way.
Among its many features, there is a tool called Softaculous, which allows to install CMS easily (WordPress, for example).
Good news for you: no more need to bother with WordPress database creation
Softaculous will take care of everything for you (if you are with another host, such as OVH, you can also install your site with a 1-click module).
Pretty cool, right? Now, if you want to struggle with the manual method, don't worry. I have the recipe right underneath.
cPanel also allows you to create and manage databases, via the MySQL Databases link.
The process is quite fast. This will involve giving a name to your database, creating an associated user, or granting privileges, i.e. rights to use the database.
For the steps to follow, I detail everything in our How to install WordPress post, a step-by-step guide.
How do I create a WordPress database locally?
Using WordPress locally means that you will place it on your computer, without having to go through the services of a host.
This can be useful to create or redesign a site in all peace of mind, for example.
To design a site – and therefore a database – locally, there are two major options (be careful, don't create a database alone: it's useless).
The first is called Local by Flywheel. This software will do almost everything for you, including the creation of your DB.
The second is called MAMP. To create a database with MAMP, you will have to ask an old friend: phpMyAdmin. Yeah, back again.
Congratulations, your database is now created.
But your job doesn't stop there. In order for your WordPress database to do its best, you'll have to optimize it.
I'll tell you why and especially how to do so in the next part.
How do I manage and optimize a WordPress database?
This is a natural phenomenon common in human beings: as we get older, we tend to put on a few pounds.
The same goes for your WordPress site. The older it gets, the more its database will gain weight in accumulating information (e.g. posts, revisions, options etc.).
The problem is that an overloaded database can severely slow down a site.
From a user experience perspective, we've seen better: everybody hates a page that takes ages to display.
For Search Engine Optimization, the impact can also be quite negative
According to Google, the performance of a site is one of the 3 most important factors to take into account for your SEO.
As a result, you don't really have a choice: you're going to have to optimize your WordPress database to stay in good shape. This will allow you to free up storage space on your hosting server, so as not to alter its performance too much.
Without further ado, I'll give you a few tips on how to put your database on a diet, and limit its weight gain for new sites.
Review your revisions
WordPress has a revision system that allows you to store a backup of drafts and updates of your posts in your database.
On paper, this is still a useful feature if you want to back pedal. Except that it unnecessarily overloads your database, because WordPress does not impose limits on the number of revisions.
Fortunately, a few lines of code will help to overcome this problem.
To reduce the number of revisions of each article (the number corresponds to the number of revisions kept in the database by content type), use the snippet below in your
define('WP_POST_REVISIONS', 3 );
It is possible to go even further by disabling revisions (in this case, do not use both pieces of code at the same time)
define( 'WP_POST_REVISIONS', false );
Careful with that last piece of code. If you choose to use it, no further revisions will be made. In case of bad manipulation on your part (e.g. closing the editing window), or a bug in your browser, you will lose all the content created beforehand. As a last resort, it will be possible to restore your content through automatic backup. The link to this snippet is in French, but the code it contains may help you do so.
If you are not tempted to manipulate code, know that you can use the WP Rocket premium plugin.
This cache plugin (a system to speed up the loading of your pages) has an option to optimize your database.
Thanks to it, you'll be able to
- Clean up revisions and drafts.
- Clean up the comments.
- Clean up transients, which allow you to temporarily cache information in your database.
- Clean the database tables.
- Schedule an automatic cleaning.
Concerning plugins, here are four that will also allow you to optimize your database (activate only one, though):
Speed up your website with WP Rocket
Remove plugins and themes you don't use
Another lever that you can activate is the deletion of themes and plugins that you don't/no longer use.
No need to keep plugins just to garnish your Dashboard, do we agree?
The trick is that it won't be enough to make all their information disappear from your database, while continuing to overload it.
Indeed, some plugins leave tables lying around, even when deleted
In particular, this allows you, if you reactivate them, not to have to set them up again.
For a plugin that you are sure you don't want to use anymore, this is of no interest.
To delete unused tables, you can use the WP-DB Manager enhancement.
If you plan to paste it manually, you will have to go through phpMyAdmin
This manipulation is not very complex, but I advise you to use it only as a last resort, and by thinking of saving your site beforehand
To get started, go to phpMyAdmin through your host's interface (your cPanel, for example).
Select your WordPress database, top left. All your tables should then appear on the right-hand side of your screen.
Then select the table(s) of your choice and choose Optimize Table (and especially not Empty or Delete, be careful).
Are you still following? Then let's go to the third tip.
Get rid of spam
They can sometimes make us smile, but they are mostly very painful. Did you recognize them? Spam comments, of course.
Real undesirables, those. And if I tell you that on top of that, they're overloading your database, that gives you several good reasons to get rid of them right away.
Among the many anti-spam plugins available on the market, there's one that does the job quite well: Akismet.
With over 5 million active installations, it is the most popular on the official directory.
You will find it by default as soon as you install WordPress on your administration, because it was designed by Automattic, the WordPress parent company.
So, frankly, don't deny yourself that.
How to secure your database
As you have just seen, optimizing your WordPress database remains essential. But that's not all: there's another element you can't ignore if you want to manage a database with WordPress efficiently: its safety.
In this section, I'll show you two ways to increase the security of your DB:
- By saving it
- By changing the prefix of its tables
How to backup your database using a plugin?
I will detail the manual process later but let's start with the “plugin” method, more accessible if you are not an experienced WP user.
Using a plugin to back up your DB will make the operation much less tedious, especially since you'll be able to automate and program the process.
To not hide anything from you, I'll warn you: there are dozens and dozens of plugins to achieve your goals
If you ask Alex, he'll advise you to use UpdraftPlus.
It must be said that this plugin has many advantages:
- You can download your database in one click.
- You can schedule backups of your WordPress DB and export them to an external service (such as Google Drive).
- You can schedule automatic backups.
- You can restore your DB directly from the plugin.
How to backup your database by hand
In some cases, you may need to make a manual backup of your database.
I am thinking for example of the case where your site's administration becomes inaccessible, even momentarily, making it impossible to backup or restore your DB.
To proceed without a plugin, everything happens on phpMyAdmin (again). Go to this tool and select the database of your choice by clicking on it.
It will then appear at the top of your page. Click Export.
phpMyAdmin gives you the choice between two export methods: fast or custom (notably to select the tables of your choice)
By default the SQL format is already selected, leave it as it is, and click Go.
A file in SQL format will then download to your computer. You are now in possession of your database: congratulations ;-).
By saving your database, you have also just exported it.
As a result, it is obviously possible to import it on the site of your choice through the Import tab of phpMyAdmin.
Let's say the process is the same as for export, but in reverse.
In this case, you will upload the SQL file of your choice to import it into your WordPress database.
How do I change the prefix of the database tables
Most of the time, by default when installing WordPress, the
wp_ prefix is assigned to the tables in your database.
In terms of security, this is an issus, because it makes your site more vulnerable to SQL attacks from bad hackers and web pirates (not Jack Sparrow type pirates, real pirates).
To cover yourself, get into the habit of changing this famous prefix as soon as you install your next sites.
Think about choosing something more complicated, even incomprehensible, instead of
dgh47yz_) To generate a strong password, you can use this handy site.
If the damage is already done, don't panic. It's still possible to rectify the situation.
The easiest way is to use a plugin. Brozzme DB Prefix & Tools Addons will do the job very well, for example.
Choose the new prefix of your choice, and that's it. The plugin will take care of updating your DB by itself, as well as the information contained in your
On the other hand, absolutely think of backing up your DB before using the plugin. You never know: in case of a bug or connection problem, your site could crash.
Otherwise, you can also do it by hand. But I warn you, it's much riskier
Here are 5 steps to do so summarized below:
Step 1: Backup your site (files + DB).
Step 2: Modify your
wp-config.php file by adding the new prefix of your choice (the one starting with wp_).
Step 3: Login to phpMyAdmin, select the database of your choice, and enter the following SQL queries (replace
xxx by the new prefix chosen, i.e. by
xjh5g7j8_ in my example) :
Rename table wp_commentmeta to xxx_commentmeta;
Rename table wp_comments to xxx_comments;
Rename table wp_links to xxx_links;
Rename table wp_options to xxx_options;
Rename table wp_postmeta to xxx_postmeta;
Rename table wp_posts to xxx_posts;
Rename table wp_termmeta to xxx_termmeta;
Rename table wp_terms to xxx_terms;
Rename table wp_term_relationships to xxx_term_relationships;
Rename table wp_term_taxonomy to xxx_term_taxonomy;
Rename table wp_usermeta to xxx_usermeta;
Rename table wp_users to xxx_users;
Step 4: Modify the wp_options table, as you will no longer have sufficient rights to access the administration of your site. In the
option_name column, locate the field
wp_user_roles, double-click on it, and replace it with xxx_user_roles.
Step 5: Modify the
wp_usermeta table. In the
meta_key column, replace all old wp_ prefixes with the new ones. For your convenience, you can click on the
meta_key column to filter in alphabetical order.
How to solve the main database errors
Before I let you manage your database as you wish, I want to give you some final advice that you may find useful when you're helpless, that is when your database no longer responds. Unfortunately, this can happen, so you might as well be prepared.
The database connection error
In Shakespeare's language, it's called Error Establishing a Database Connection. Just the sight of it in writing is bad enough.
You will see this nice – what do I say – awful message when your site will no longer be able to connect to its database.
This may be due to a problem with your hosting server. In this case, a simple refresh of your page (a little F5 or refresh) can save you.
But the cause may be deeper: it would be far too simple, otherwise.
In this case, you will first need to check your DB login credentials in your
If they seem to be correct, but it still doesn't work, you can generate a new password via your cPanel.
If your database is corrupted, you will need to repair it with the following code:
define( 'WP_ALLOW_REPAIR', true );
I' give you all the details to do so in WPMarmite's extensive guide to WordPress errors.
How to reset your database
In some specific cases, you may need to reset your database.
This can be handy if you work on a test site, or locally. This saves you from having to reinstall WordPress, for example.
Be careful, do not engage in this practice on a site in production unless you are sure of what to do. Resetting a database means that you will lose all your content. In any case, make a backup before doing anything.
For this, you can use a specific and handy plugin: WP Database Reset.
It will allow you, for instance, to reset the entire database, or only certain tables of your choice.
To ensure its smooth operation, I have presented you with how:
- Create it
- Manage it
- Optimize it
- Secure it
- Repair it
A technical problem or a bad manipulation can happen quickly, so you might as well get into the habit of protecting yourself.
What about you? How do you manage your DB? Or maybe you prefer not bothering and stay focused on your site?
Feel free to share your tips and advice with WPMarmite readers in the comments below.
Receive the next posts for free and access exclusive resources. More than 35000 people have done it, why not you?