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 folders and files, and a database. 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.
Table of contents
A (very) brief history of SQL, MySQL, and MariaDB
WordPress uses an RDBMS 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 to MySQL. This means that you can uninstall MySQL, install MariaDB instead, 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.
How to access the WordPress database
There are a few different options available when it comes to connecting 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. Either way, options can include;
phpMyAdmin
phpMyAdmin is a favorite tool as it allows us to connect to the database via a web-based GUI. phpMyAdmin needs to be installed on the same server that hosts the database, with many hosting providers offering phpMyAdmin straight out of the box.
Plesk/cPanel
Plesk and cPanel are two control panel platforms that serve a similar function – a user interface that facilitates server management. Of course, there are certain key differences, including the technologies and Operating Systems they support. Either way, they also allow us to access databases, albeit in slightly different ways.
SSH/MySQL/mariaDB client
SSH is a less user-friendly way to connect to a database, offering a CLI (Command Line Interface) instead of a GUI. Because of this, a deeper understanding of SQL commands is recommended. SSH needs to be explicitly set up on the same server that hosts the database before connecting to it.
Plugins
You can also use WordPress plugins to access your WordPress database. Using a plugin, you can access the database straight from your WordPress admin console. Here, you’ll need to make sure that you choose a plugin from a reputable supplier and follow all applicable best practices to keep your data safe. If you are not sure which plugin to go for, read our guide on how to choose the best plugins for your WordPress website.
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 tables that make up the WordPress database are as follows (listed in alphabetical order):
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 name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| meta_id | This is a unique ID for the entry. It increments automatically | bigint(20)unsigned | Primary | ||
| comment_id | This is the ID of the comment the metadata relates to as found in the wp_comments table | bigint(20)unsigned | Index | 0 | |
| meta_key | This identifies the type of metadata the entry is for | varchar(255) | Yes | Index | Null |
| meta_value | This is the actual metadata | longtext | Yes | Null |
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 name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| comment_ID | This is a unique ID for the entry. It increments automatically | bigint(20) | Primary | NA | |
| comment_post_ID | This is the ID of the post the comment was written for, as found in the wp_posts table | bigint(20) | Index | 0 | |
| comment_author | This is the name of the author who wrote the comment | tinytext | |||
| comment_author_email | This is the email address of the author who wrote the comment | varchar(100) | Index | ||
| comment_author_url | This is the website URL of the author who wrote the comment | varchar(200) | |||
| comment_author_IP | This is the IP address of the author who wrote the comment | varchar(100) | |||
| comment_date | This is the date and time on which the comment was posted | datetime | 0000-00-00 00:00:00 | ||
| comment_date_gmt | This is the GMT (Greenwich Meridian Time) date and time on which the comment was posted | datetime | Index and Index Pt2 | 0000-00-00 00:00:00 | |
| comment_content | This is the actual comment left | text | |||
| comment_karma | This is available for use by plugins for comment management purposes | int(11) | |||
| comment_approved | This indicates whether the comment has been approved or not | varchar(20) | Index Part 1 | 0 | |
| comment_agent | This is where the comment was posted from | varchar(255) | |||
| comment_type | This is the type of comment left | varchar(20) | |||
| comment_parent | If the comment is a reply, this field indicates parent comment | bigint(20)unsigned | Index | 0 | |
| user_id | If a commenting user is registered, this will be their ID as available in wp_users | bigint(20)unsigned | 0 |
wp_links
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 name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| link_id | This is a unique ID for the entry. Increments automatically | bigint (20) unsigned | Primary | ||
| link_url | This is the URL of the link | varchar(255) | |||
| link_name | |||||
| This is the name of the link | varchar(255) | ||||
| link_image | This is the URL of the link-related image | varchar(255) | |||
| link_target | This is the link’s target frame | varchar(25) | |||
| link_desciption | This is a description of the link | varchar(255) | |||
| link_visible | This tells us if the link is publicly shown or not | varchar(20) | Index | Y | |
| link_owner | This is the user ID of the user who created the link as available in wp_users | bigint (20) unsigned | 1 | ||
| link_rating | This is the rating of the link | int(11) | 0 | ||
| link_updated | This is the date and time the link was updated | datetime | 0000-00-00 00:00:00 | ||
| link_rel | This is the relationship of the link | varchar(255) | |||
| link_notes | This is notes about the link | mediumtext | |||
| link_rss | This is the rss feed address of the link | varchar(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 Website File Changes Monitor’s scan frequency option set to daily.
The table has the following columns:
| Column name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| option_id | This is a unique ID for the entry. Increments automatically. | bigint(20) unsigned | Primary | ||
| option_name | This is the name of the option/setting | varchar(64) | Unique | ||
| option_value | This is the value of the setting being stored | longtext | |||
| autoload | This setting tells wp_load_alloptions() if it should autoload the option or not | varchar(20) | Index | yes |
wp_postmeta
Posts metadata that accompanies each post is stored here. Metadata can include attached files, thumbnails, desired post slug, and other such information. The table has the following columns:
| Column name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| meta_id | This is a unique ID for the entry. Increments automatically | bigint(20) unsigned | Primary | ||
| Field name post_id |
This is the ID of the post the metadata is associated with as available in wp_posts | bigint(20) unsigned | Index | 0 | |
| meta_key | This is an index key that identifies the metadata, since each post can have more than one metadata | varchar(255) | Yes | Index | Null |
| meta_value | This is the actual metadata | longtext | Yes | Null |
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.
The table has the following columns:
| Column name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| ID | This is a unique ID for the entry. Increments automatically | bigint(20) unsigned | Primary and Index (Part 4) | ||
| post_author | This is the ID of the author who wrote the post as available in wp_users | bigint(20) unsigned | Index | 0 | |
| post_date | This is the date and time when the post was created | datetime | Index (Part 3) | 0000-00-00 00:00:00 | |
| post_date_gmt | This is the GMT (Greenwich Mean Time) date and time when the post was created | datetime | 0000-00-00 00:00:00 | ||
| post_content | This is the actual content of the post | longtext | |||
| post_title | This is the title of the post | text | |||
| post_excerpt | This is an excerpt of the post | text | |||
| post_status | This is the status of the post | varchar(20) | Index (Part 2) | publish | |
| comment_status | This tells us if comments on the post are allowed or not | varchar(20) | open | ||
| ping_status | This tells us if ping and trackbacks are allowed or not | varchar(20) | open | ||
| post_password | Posts can be password-protected with any applicable password stored here | varchar(20) | |||
| post_name | This is the post title’s URL slug | varchar(200) | index | ||
| to_ping | This is a list of URLs that WordPress should send pingbacks to whenever the post is updated | text | |||
| pinged | This is a list of URLs that WordPress has pingbacked when updated | text | |||
| post_modified | This is the date and time of the post’s last modification | datetime | 0000-00-00 00:00:00 | ||
| post_modified_gmt | This is the GMT date and time of the post’s last modification | datetime | 0000-00-00 00:00:00 | ||
| post_content_filtered | This is a filtered version of post_content that’s typically used by plugins for caching purposes | longtext | |||
| post_parent | When post is a revision or attachment, this creates the parent-child relationship | bigint(20) unsigned | Index | 0 | |
| guid | This is the Global Unique Identifier (GUID) of the post | varchar(255) | |||
| menu_order | This is the order number in which pages and non-post items appear | int(11) | 0 | ||
| post_type | This identifies the content type | varchar(20) | Index (Part 1) | post | |
| post_mime_type | This is the mime type of post attachments | varchar(100) | |||
| comment_count | This is the total number of comments, trackbacks, and pingbacks | bigint(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 name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| term_id | This is a unique ID for the entry. Increments automatically | bigint(20) unsigned | Primary | ||
| name | This is the name of the term | varchar(200) | Index | ||
| slug | This is the slug of the term | varchar(200) | Multiple | ||
| term_group | This is an alias that themes and plugins can use to group terms tigether | bigint(10) | 0 |
wp_termmeta
This table stores the metadata associated with terms found in wp_terms. The table has the following columns:
| Column name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| meta_id | This is a unique ID for the entry. Increments automatically | bigint(20) unsigned | Primary | ||
| term_id | This is the ID of the term the metadata relates to as available in wp_terms | bigint(20) unsigned | Index | 0 | |
| meta_key | This is an identifier key for the term metadata | varchar(255) | Yes | Index | NULL |
| meta_value | This is the actual metadata | longtext | Yes | NULL |
wp_term_relationships
This table maintains relationships between posts and taxonomies. The table has the following columns:
| Column name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| object_id | This is the ID of the post as available in wp_posts | bigint(20) unsigned | Primary (Part 1) | 0 | |
| term_taxonomy_id | This is the ID of the term taxonomy as available in wp_term_taxonomy | bigint(20) unsigned | Primary (Part 2) and Index | 0 | |
| term_order | This is the order of the term | int(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 name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| term_taxonomy_id | This is a unique ID for the entry. Increments automatically | bigint(20) unsigned | Primary | ||
| term_id | This is the ID of the term as available in wp_terms | bigint(20) unsigned | Unique (Part 1) | 0 | |
| taxonomy | This is the slug of the taxonomy | varchar(32) | Unique (Part 2) and Index | ||
| description | This is a description of the taxonomy | longtext | |||
| parent | This is the ID of the parent taxonomy if taxonomy is a child | bigint(20) unsigned | 0 | ||
| count | This is the number of objects that are assigned this taxonomy | bigint(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.
The table has the following columns:
| Column name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| umeta_id | This is a unique ID for the entry. Increments automatically | bigint(20) unsigned | Primary | ||
| user_id | This is the user’s ID the information relates to as found in wp_users | bigint(20) unsigned | Index | 0 | |
| meta_key | This is a key identifier for the meta entry | varchar(255) | yes | Index | Null |
| meta_value | This is the actual metadata | longtext | Yes | Null |
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.
The table has the following columns:
| Column name | Description | Type | Null | Key | Default |
|---|---|---|---|---|---|
| ID | This is a unique ID for the entry. Increments automatically | bigint(20) unsigned | Primary | ||
| user_login | This is the user’s username | varchar(60) | Index | ||
| user_pass | This is the user’s password | varchar(64) | |||
| user_nicename | This is the user’s display name | varchar(50) | Index | ||
| user_email | This is the user’s email address | varchar(100) | |||
| user_url | This is the user’s URL (ex. website) | varchar(100) | |||
| user_registered | This is the date and time on which the user was registered | datetime | 0000-00-00 00:00:00 | ||
| user_activation_key | This is the user’s activation key, used for resetting their password | varchar(60) | |||
| user_status | This is no longer used as from WordPress 3.0, but used to indicate if the user was spam | int(11) | 0 | ||
| display_name | This is the user’s public display name | varchar(250) |
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 a XAMPP staging environment on your computer for free – providing you with everything you need to master WordPress’ database.
The post The ultimate guide to the WordPress database appeared first on WP White Security.
This content was originally published here.