Home Blog WordPress Management WordPress MySQL database: How it works and how to manage it

WordPress Password Policy Enforcing Strong Passwords

WordPress MySQL database: How it works and how to manage it

At its core, WordPress is a CMS (Content Management System). To manage content, it needs to be able to store it. WordPress does this through the database. It also uses files and folders to store other types of data, such as plugins, images, and theme files. We have previously covered the WordPress filesystem in a separate article; we will focus on the database this time around.

In this article, we will be looking at the WordPress database, its structure, and how each field works. We have also included a brief history lesson on MySQL. Hint – The My in MySQL does not mean it’s yours; My is an actual person, but who? – Continue reading to find out.

A (very) brief history of SQL, MySQL, and MariaDB

WordPress uses an RDBMS (Relational Database Management System) called MySQL. Technically, it’s MySQL-based since, increasingly, MariaDB is being used instead of MySQL. SQL stands for Structured Query Language and is the language we use to interact with the database – and not the database itself.

MySQL has played a significant role in the growth of the internet as we know it today. First introduced in 1995 as an alternative to products offered by Microsoft and Oracle, it quickly became the golden RDBMS standard of choice.

MySQL’s history is colorful, having been acquired by Sun Microsystems and then Oracle (Oracle acquired Sun Microsystems and MySQL with it).

In response to the acquisition of MySQL by Oracle, Monty Widenius, the original creator of MySQL, forked MySQL into MariaDB, which he named after his daughter, Maria. (Interestingly, MySQL is named after Monty’s other daughter – My). Over time, differences between MariaDB and MySQL developed; however, the two remain interchangeable in many scenarios, including WordPress databases.

In fact, in many cases, MariaDB is considered to be a drop-in replacement for MySQL. This means that you can replace MySQL with MariaDB and continue working as if nothing changed. Having said that, MariaDB can offer performance improvements in some situations and also offers wider compatibility with, for example, storage engines.

It’s important to note that MySQL remains free and is released under a dual-license system. In many cases, MySQL is used to refer to databases that are either MySQL or MariaDB.

Why does WordPress need a database?

The primary job of a WordPress website is to manage and serve content. This content needs to be stored in a safe and secure manner while being easily and efficiently accessible. Aside from content, such as blog posts, pages, and user comments, WordPress also needs to store other types of data, such as settings and configurations. It also needs to store plugin settings and theme settings.

Plugins also store data in the database. For example, WP Activity Log stores activity log data in the database.

Databases, more specifically in this case, MySQL databases, are the most efficient way to store data. They are easy to write to and read from and relatively secure. They are also able to hold large amounts of data, ensuring the WordPress website can continue to grow without any issues.

WordPress database definitions

In this section, we will look at some common terms used when talking about MySQL, along with a short definition.

Host

The database host is the server on which MySQL is installed. In most environments, this is the same server that WordPress is installed on. However, this is not necessarily the case. MySQL can be installed on virtually any server, and WordPress will connect to it.

It is worth mentioning that configuration changes must be made if the MySQL server is installed on a different server or moved.

Database

The database is a collection of tables and associated data and metadata. WordPress does not have a default database name since this is not set up by WordPress but needs to be provided to the WordPress installer. Most web hosts will automatically create the database for you, especially on managed plans.

The database user is given rights to the database itself, according to the ACL (Access Control List). WordPress uses the database user to write to and read from the database.

Tables

Tables are organizational units made up of rows and columns and sit within a specific database. Unlike the database, you do not need to create tables yourself, as WordPress takes care of this during installation. We discuss each WordPress database table in more detail in the WordPress database structure section.

Query

SQL stands for Structured Query Language. It is the language through which we can query the database. Each database request is done through a MySQL query. Some examples of MySQL queries will be provided later on in this article.

RDBMS

While MySQL is routinely referred to as a database, in reality, it is an RDBMS, which stands for Relational DataBase Management System. This means that MySQL is not the database itself but rather the system that manages the database. MySQL is just one flavor of SQL-based RDBMS. Other notable flavors include MSSQL (favored with .NET implementations). Different SQL-based RDBMSs use queries based on the SQL query set. However, there might be some differences.

How to create a database for your WordPress website

Before installing WordPress, you need to set up the WordPress MySQL database. The WordPress installation process will create the tables and fields, so this is not something you have to worry about. However, the database needs to be set up and available. You may create a brand new WordPress database or import an existing one from a database backup file or database export.

As mentioned previously, most hosting providers will take care of this for you, making the process completely seamless and transparent to you. However, if this is not the case, you’ll need to set up the WordPress database yourself.

You’ll find quite a few options for creating a WordPress database; however, your environment may limit which options you can choose.

How to access the WordPress database

You may choose to connect to your WordPress site’s database for various reasons – from management and backup to troubleshooting issues. There are a few different options available when looking to connect to the WordPress database. The method or methods available to you will largely depend on the type of WordPress hosting that you use. If you’re not sure how your server is configured, speak with your hosting provider or systems administrator.

How the WordPress database works

In this section, we will look at the WordPress database structure by examining the 12 default tables that make up the WordPress database and their structure.

It is worth noting that some plugins create their own tables. These are not being covered here since it is not feasible to account for all of them.

WordPress database structure

The WordPress database is made up of 12 tables. Each table, by default, starts with the wp_ prefix; however, this can be changed during the initial installation and configuration process. Changing the prefix is generally recommended for WordPress security reasons, especially if you intend or already have multiple installations on the same server.

The 12 default WordPress database tables that make up the WordPress database are as follows (listed in alphabetical order):

  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_terms
  • wp_termmeta
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

We will now go through each table individually and look at what data it stores and its internal structure.

Table structure

Before we get into the details of each table, it is worth taking some time to look at how it is structured. If you’re not familiar with database documentation, this section will give you a crash course that you’ll find helpful in the next section. On the other hand, if you’re pretty familiar with SQL tables, feel free to skip forward.

  • Field name – This is the name of the field, which you’ll find in the SQL table
  • Description – We have put this in to help you understand what kind of data the field holds
  • Type – This is the datatype that the field accepts. Numbers in brackets represent the hard limit on the number of acceptable characters we can enter
  • Null – it is not clear why this field is used at the moment
  • Key – This tells us if the entry is a key or not. There are different types of keys, including:
    • Primary
    • Primary (Part)
    • Index
    • Index (Part)
    • Unique
    • Multiple
  • Default – If the entry has a default value, the default value will be listed here
  • Notes – Any additional notes

wp_commentmeta

The wp_commentsmeta table stores metadata related to comments. Comments are stored separately in the wp_comments table. The table has the following fields:

Column nameDescriptionTypeNullKeyDefault
meta_idThis is a unique ID for the entry. It increments automaticallybigint(20)unsignedPrimary
comment_idThis is the ID of the comment the metadata relates to as found in the wp_comments tablebigint(20)unsignedIndex0
meta_keyThis identifies the type of metadata the entry is forvarchar(255)YesIndexNull
meta_valueThis is the actual metadatalongtextYesNull

wp_comments

The wp_comments table stores post comments. Metadata related to comments is stored in the wp_commentmeta table. The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
comment_IDThis is a unique ID for the entry. It increments automaticallybigint(20)PrimaryNA
comment_post_IDThis is the ID of the post the comment was written for, as found in the wp_posts tablebigint(20)Index0
comment_authorThis is the name of the author who wrote the commenttinytext
comment_author_emailThis is the email address of the author who wrote the commentvarchar(100)Index
comment_author_urlThis is the website URL of the author who wrote the commentvarchar(200)
comment_author_IPThis is the IP address of the author who wrote the commentvarchar(100)
comment_dateThis is the date and time on which the comment was posteddatetime0000-00-00 00:00:00
comment_date_gmtThis is the GMT (Greenwich Meridian Time) date and time on which the comment was posteddatetimeIndex and Index Pt20000-00-00 00:00:00
comment_contentThis is the actual comment lefttext
comment_karmaThis is available for use by plugins for comment management purposesint(11)
comment_approvedThis indicates whether the comment has been approved or notvarchar(20)Index Part 10
comment_agentThis is where the comment was posted fromvarchar(255)
comment_typeThis is the type of comment leftvarchar(20)
comment_parentIf the comment is a reply, this field indicates parent commentbigint(20)unsignedIndex0
user_idIf a commenting user is registered, this will be their ID as available in wp_usersbigint(20)unsigned0

This table was originally created to support blogrolls, a feature that was dropped starting with WordPress 3.5. It is kept for backward compatibility but is no longer in use. The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
link_idThis is a unique ID for the entry. Increments automaticallybigint (20) unsignedPrimary
link_urlThis is the URL of the linkvarchar(255)
link_name
This is the name of the linkvarchar(255)
link_imageThis is the URL of the link-related imagevarchar(255)
link_targetThis is the link’s target framevarchar(25)
link_desciptionThis is a description of the linkvarchar(255)
link_visibleThis tells us if the link is publicly shown or notvarchar(20)IndexY
link_ownerThis is the user ID of the user who created the link as available in wp_usersbigint (20) unsigned1
link_ratingThis is the rating of the linkint(11)0
link_updatedThis is the date and time the link was updateddatetime0000-00-00 00:00:00
link_relThis is the relationship of the linkvarchar(255)
link_notesThis is notes about the linkmediumtext
link_rssThis is the rss feed address of the linkvarchar(255)

wp_options

WordPress settings configured through the admin console are stored here. Plugins and themes will typically also store settings information here, as illustrated in the screenshot below. Here we can see our very own Melapress File Monitor’s scan frequency option set to daily.

wp_options

The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
option_idThis is a unique ID for the entry. Increments automatically.bigint(20) unsignedPrimary
option_nameThis is the name of the option/settingvarchar(64)Unique
option_valueThis is the value of the setting being storedlongtext
autoloadThis setting tells wp_load_alloptions() if it should autoload the option or notvarchar(20)Indexyes

wp_postmeta

Posts metadata that accompanies each post is stored here. Metadata can include attached files, thumbnails, desired post slug, and other information. The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
meta_idThis is a unique ID for the entry. Increments automaticallybigint(20) unsignedPrimary
Field name
post_id

This is the ID of the post the metadata is associated with as available in wp_postsbigint(20) unsignedIndex0
meta_keyThis is an index key that identifies the metadata, since each post can have more than one metadatavarchar(255)YesIndexNull
meta_valueThis is the actual metadatalongtextYesNull

wp_posts

The wp_posts table is a major one and contains the core of WordPress data. It holds the actual posts, pages, as well as navigation menu items, as seen in the below example showing the default sample page included in every WordPress fresh installation.

wp_posts table in phpMyAdmin

The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
IDThis is a unique ID for the entry. Increments automaticallybigint(20) unsignedPrimary and Index (Part 4)
post_authorThis is the ID of the author who wrote the post as available in wp_usersbigint(20) unsignedIndex0
post_dateThis is the date and time when the post was createddatetimeIndex (Part 3)0000-00-00 00:00:00
post_date_gmtThis is the GMT (Greenwich Mean Time) date and time when the post was createddatetime0000-00-00 00:00:00
post_contentThis is the actual content of the postlongtext
post_titleThis is the title of the posttext
post_excerptThis is an excerpt of the posttext
post_statusThis is the status of the postvarchar(20)Index (Part 2)publish
comment_statusThis tells us if comments on the post are allowed or notvarchar(20)open
ping_statusThis tells us if ping and trackbacks are allowed or notvarchar(20)open
post_passwordPosts can be password-protected with any applicable password stored herevarchar(20)
post_nameThis is the post title’s URL slug varchar(200)index
to_pingThis is a list of URLs that WordPress should send pingbacks to whenever the post is updatedtext
pingedThis is a list of URLs that WordPress has pingbacked when updatedtext
post_modifiedThis is the date and time of the post’s last modificationdatetime0000-00-00 00:00:00
post_modified_gmtThis is the GMT date and time of the post’s last modificationdatetime0000-00-00 00:00:00
post_content_filteredThis is a filtered version of post_content that’s typically used by plugins for caching purposeslongtext
post_parentWhen post is a revision or attachment, this creates the parent-child relationshipbigint(20) unsignedIndex0
guidThis is the Global Unique Identifier (GUID) of the postvarchar(255)
menu_orderThis is the order number in which pages and non-post items appearint(11)0
post_typeThis identifies the content typevarchar(20)Index (Part 1)post
post_mime_typeThis is the mime type of post attachmentsvarchar(100)
comment_countThis is the total number of comments, trackbacks, and pingbacksbigint(20)0

wp_terms

Terms are classification objects used to classify objects in WordPress. For example, categories and tags used in posts are types of terms. This table contains all the different types of terms used throughout WordPress. The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
term_idThis is a unique ID for the entry. Increments automaticallybigint(20) unsignedPrimary
nameThis is the name of the termvarchar(200)Index
slugThis is the slug of the termvarchar(200)Multiple
term_groupThis is an alias that themes and plugins can use to group terms tigetherbigint(10)0

wp_termmeta

This table stores the metadata associated with terms found in wp_terms. The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
meta_idThis is a unique ID for the entry. Increments automaticallybigint(20) unsignedPrimary
term_idThis is the ID of the term the metadata relates to as available in wp_termsbigint(20) unsignedIndex0
meta_keyThis is an identifier key for the term metadatavarchar(255)YesIndexNULL
meta_valueThis is the actual metadatalongtextYesNULL

wp_term_relationships

This table maintains relationships between posts and taxonomies. The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
object_idThis is the ID of the post as available in wp_postsbigint(20) unsignedPrimary (Part 1)0
term_taxonomy_idThis is the ID of the term taxonomy as available in wp_term_taxonomybigint(20) unsignedPrimary (Part 2) and Index0
term_orderThis is the order of the termint(11)0

wp_term_taxonomy

This table gives terms taxonomies and, as such, a context in which they can be used. For example, we can use the term database as a post category and as a product category (assuming we are selling database services). In this case, post category and product category are term taxonomies. The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
term_taxonomy_idThis is a unique ID for the entry. Increments automaticallybigint(20) unsignedPrimary
term_idThis is the ID of the term as available in wp_termsbigint(20) unsignedUnique (Part 1)0
taxonomyThis is the slug of the taxonomyvarchar(32)Unique (Part 2) and Index
descriptionThis is a description of the taxonomylongtext
parentThis is the ID of the parent taxonomy if taxonomy is a childbigint(20) unsigned0
countThis is the number of objects that are assigned this taxonomybigint(20)0

wp_usermeta

This table stores additional user data that is not found in the wp_users table. WordPress itself, as well as plugins or themes, can make use of this table.

An example of user metadata is the user nickname. Although WordPress includes this field by default, it is still part of the metadata, as shown below. Another example is WooCommerce; an ecommerce plugin that uses this table to store customer information such as shipping address.

wp_usermeta

The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
umeta_idThis is a unique ID for the entry. Increments automaticallybigint(20) unsignedPrimary
user_idThis is the user’s ID the information relates to as found in wp_usersbigint(20) unsignedIndex0
meta_keyThis is a key identifier for the meta entryvarchar(255)yesIndexNull
meta_valueThis is the actual metadatalongtextYesNull

wp_users

WordPress users’ information is stored here. With users being an integral part of the WordPress ecosystem, this table is an essential one.

The table only stores the core information for each user, as shown in the example below. All other information is stored in the wp_usermeta table.

wp_users table in phpMyAdmin

The table has the following columns:

Column nameDescriptionTypeNullKeyDefault
IDThis is a unique ID for the entry. Increments automaticallybigint(20) unsignedPrimary
user_loginThis is the user’s usernamevarchar(60)Index
user_passThis is the user’s passwordvarchar(64)
user_nicenameThis is the user’s display namevarchar(50)Index
user_emailThis is the user’s email addressvarchar(100)
user_urlThis is the user’s URL (ex. website)varchar(100)
user_registeredThis is the date and time on which the user was registereddatetime0000-00-00 00:00:00
user_activation_keyThis is the user’s activation key, used for resetting their passwordvarchar(60)
user_statusThis is no longer used as from WordPress 3.0, but used to indicate if the user was spamint(11)0
display_nameThis is the user’s public display namevarchar(250)

Managing the WordPress MySQL database

In the day-to-day running of your WordPress website, you will not need to manage the WordPress MySQL database. However, in some cases, you may need or choose to undertake interventions to facilitate specific actions or carry out essential tasks, such as restoring a WordPress database backup or fixing a database connection issue.

Basics

WordPress database. In this section, you’ll see how intuitive and easy MySQL is. As always, it is recommended that you run tests in a staging environment before making any changes to the live environment.

Creating and deleting databases

Whether it’s a new WordPress installation or a migration, creating a database for WordPress is probably one of the first MySQL commands you’ll run. To create a new database, execute the following command:

CREATE DATABASE databasename;

Where, wordpressdatabase is the name of the WordPress database.

MySQL servers will have more than one database. To navigate to a specific database, such as the WordPress database, you’ll need to tell MySQL that you want to use that database by executing the following command:

USE databasename

Where, wordpressdatabase is the name of the WordPress database.

Dropping something in MySQL, whether a database or a user, is the equivalent of deleting it. To delete a database, run the following command:

DROP DATABASE databasename;

Where, wordpressdatabase is the name of the WordPress database.

P.S. Once a database is dropped, that data is lost forever. You might want to take a backup before deleting a given database so that if something breaks, you can easily go back.

Adding a new user

WordPress connects to the MySQL database through a database user, with the required permissions on the WordPress database. Creating a dedicated user is a definite best practice, which you can do by executing the following command:

CREATE USER username@hostname IDENTIFIED BY 'password';

Where:

  • username is the username you would like to give the new user
  • hostname is the name of the host where the user is located (this is usually localhost)
  • password is the password you would like to set for the user

The database username and password need to be passed on to WordPress through the wp-config configuration file. This allows WordPress to connect to MySQL and carry out database-related tasks.

Assigning rights to the WordPress database

Once you add a new user, you will need to assign that user rights to the database. This ensures WordPress has the rights it needs to carry out the tasks it needs to. The command to do so is as follows:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
    -> ON wordpressdatabase.*
    -> TO username@hostname;

Where:

  • wordpressdatabase is the name of the WordPress database
  • username is the username of the user you would like to assign database rights to
  • hostname is the name of the host where the user is located (this is usually localhost)

Whenever you add, remove, or edit any rights, we must flush privileges for the changes to take effect. The command to do so is:

FLUSH PRIVILEGES;

Remove an existing user

To delete a user, run the following command:

DROP USER ‘username’@’hostname’;

Importing and exporting

Importing and exporting data effectively allows you to take backups and restore the data should the need arise. When taking backups, it is essential to undertake test restores where the backup is restored to an offline WP site to validate that it works. The command to export the WordPress database is as follows:

mysqldump wordpressdatabase > filename.sql

Where:

  • wordpressdatabase is the name of the WordPress database
  • filename is the name you would like the backup to be saved as

It’s important to remember to store the file in a secure location. Do not leave the WordPress database backup file exposed; ideally, it should be kept on a separate, offline server.

To import a .SQL file, run the following command:

databasename < filename.sql

Where:

  • database is the name of the WordPress database
  • filename is the name of the backup file

Backups

Taking backups of the WordPress database is perhaps the most important management task. It provides for quick resumption of your WordPress site should you (or someone else) delete data stored in the tables.

WordPress database backups can be done manually or by using a plugin. Manual backups can be done by exporting and importing databases, which we covered in the previous section. You can also manually backup the WordPress database using cPanel.

Plugin database backups

The main advantage of plugin database backups is that everything is done through the WordPress dashboard without needing to learn any MySQL commands. The best WordPress database plugins and tools provide more than just backups and can help you optimize the database, migrate data, and much more.

Maintenance and optimization

Orphaned entries

Most plugins and themes clean up after themselves when they’re uninstalled. However, it is not always possible to ensure that all entries are properly cleaned up, which can leave orphaned plugin data entries in your database.

Unfortunately, there is no surefire way to delete every orphaned entry. If you recognize a table or database entry from a plugin you have uninstalled, you can delete it manually; however, not all plugins and themes necessarily use a naming convention that makes it clear entries belong to them.

Generally speaking, this is not something you should worry about – as long as you install plugins from reputable vendors.

Tables optimization

MySQL has a built-in table optimization feature. It can help you reduce storage space and make Input/Output more efficient, thus increasing database performance. If you are using phpMyAdmin, navigate to Operations and then click on the Optimize table option.

Security

MySQL security and WordPress security go hand-in-hand. Avoid using a generic username and password for the MySQL user, and be sure to harden the WordPress MySQL server for better protection against data leakage and attacks.

Get familiar with the WordPress database

Databases can be quite intimidating to the uninitiated – after all, they hold all the data required for WordPress to work. While it is true that a misstep here can bring the site crashing down, do not let this intimidate you. After all, knowing your way around the WordPress database can greatly ease your troubleshooting efforts should this be required.

Setting up a testing or staging environment can provide you with a safe space in which you are free to experiment without risking taking your website offline. You can even set up an XAMPP staging environment on your computer for free – providing you with everything you need to master the WordPress database with 0 risk.

Posted inWordPress Management
Joel Farrugia
Joel Barbara

Joel is our technical writer responsible for writing the different kinds of content we need. With a background in tech and content, he has a passion for making technology accessible and understandable for everyone. You can reach Joel at joel@melapress.com.


Leave a Reply

Your email address will not be published. Required fields are marked *

Stay in the loop

Subscribe to the Melapress newsletter and receive curated WordPress management and security tips and content.

Newsletter icon

It’s free and you can unsubscribe whenever you want. Check our blog for a taste.

Envelope icon
newsletter-pop-up