A WordPress database is a collection of all content within a website. Whether it’s posts, pages, links, or configuration settings of your site.
Having a well optimized database is needed to make your site run smoothly.
A basic understanding of WordPress site databases can help you to modify your website, fix some general issues, and to secure your site.
I’ll show you the structure of a WordPress database, tables, and a way to access and modify your WordPress database.
Structure of a WordPress Database
To create a website, WordPress needs a database host to store all of its data. A database host is a server, usually being handled by your hosting provider — that stores your site’s data. Most providers have localhost as their database hostname, but yours can be different.
All of the website data in a database host will be put in a database management system (DBMS). DBMS refers to software, which helps you to manage, organize, and configure the information in a database.
WordPress chooses MySQL as its database management system. MySQL is open-source just like WordPress itself that works as a server, managing the elements of a database.
When WordPress connects to the MySQL database server, it will automatically create tables. These tables serve as sections in your database where you can store your website’s data. Each database table consists of rows and columns containing data elements.
Because WordPress uses PHP as its backend programming language, you need to use SQL commands within the PHP markup to store and retrieve data from the MySQL database. If you are not familiar with PHP and SQL scripts, you can still access MySQL using a web application.
Most hosting providers use phpMyAdmin as the web application to access the data in MySQL. This free and open-source web software is written in PHP and serves to manage the administration of MySQL.
You can find your MySQL database server and phpMyAdmin under the database section in your hosting control panel.
WordPress Database Tables
When you are first installing a WordPress website, there are twelve default tables created in the database. Each table also has the prefix wp_ before each table name, that you can change during the installation process.
To help you understand the responsibility of each table, let’s take a look at the core tables of a WordPress database.
As one of the most important tables in a WordPress database, this table stores all types of content of your site, such as posts, pages, menu items, revisions, media attachments, and so on.
The post meta table contains extra information about posts in the wp_post table. This table is also the place to store the data of your plugins and themes.
Besides showing all comments on your posts and pages, this table also stores the information of the comment’s author — name, email, URL, and so forth. The information in this table also relates to wp_posts and wp_users tables.
This table has the metadata of all comments on your WordPress website.
The user table contains information of all registered users. This table has all user’s information — username, passwords, email addresses, display names, etc. — that helps you recognize all people who have access to your site.
From this table, you can manage the role of each user. In a standard installation of WordPress, there are six user roles you can add — super admin, administrator, editor, author, contributor, and subscriber. Managing the user’s position can help you to grant specific access to your site users.
As an extension of the user table, wp_usermeta has additional information on users. It’s used to store custom fields, usually created by plugins.
WordPress allows you to group your content into terms — categories, tags, etc. All the terms on your site will be stored in this table.
As the extension of the term table, wp_termmeta shows the extra values of your terms. The information inside this table is similar to wp_postmeta and wp_commentmeta.
A taxonomy is a collection of terms that classify your website content. Without this table, you cannot identify if a term in the wp_terms table is part of categories, tags, or custom taxonomies.
This table allows a term to be part of more than one taxonomy. For instance, you use the term WordPress as a category for some posts and use it again as a custom taxonomy. Thus, the term will appear once in the wp_terms table and twice in the wp_term_taxonomy table.
Each row in this table shows the connection between content in the wp_posts table and taxonomy in the wp_term_taxonomy table.
A few years ago, WordPress had a feature that allowed you to show links to other sites on your website called Blogroll.
Since WordPress removed this feature, you cannot access your site’s Blogroll directly from the Admin Dashboard. However, with the help of some plugins, you can still manage the hyperlinks in this table.
The options table keeps all the settings of your WordPress website. Moreover, wp_options doesn’t share a relationship with another table.
You can find active themes, widgets, and plugins in this table. Some themes and plugins also keep their settings in this table.
SQL: How WordPress Accesses Its Database
Structured Query Language (SQL) is a programming language that serves to manage databases. This language helps you to interact with a database through a DBMS.
To store and retrieve data from a WordPress database, you need to type an SQL query on the MySQL database server. Then, MySQL will respond by making some changes to your database based on the command that you input.
Having basic knowledge of SQL queries can help you to search for data and perform such actions like deleting and editing data, or creating a new table.
One of the basic examples of SQL commands is SELECT. Using this command can help you to search for information from a table:
SELECT * FROM table_name;
For instance, if you’re looking for the option_value column from your wp_options table, then your query will look like this:
SELECT option_value FROM wp_options;
To manipulate the data in your tables, you can use the INSERT, UPDATE, and DELETE statements. It allows you to add new rows, modify existing data, and remove unnecessary rows from your table.
There are many SQL queries you can use to modify your tables.
Before using these commands, ensure to backup your database first. If something goes wrong along the process, you can still restore the data to your database.
How to Access the WordPress Database
You can manage your MySQL database easily using phpMyAdmin. To use this tool, you can navigate to the Databases section in your hosting control panel and select the phpMyAdmin icon.
Click the Enter phpMyAdmin button next to the database you want to access, and you’ll be directed to the phpMyAdmin application. To see all of your database tables, click your database name on the left column.
From the phpMyAdmin application, you can make SQL commands to your database. But before you modify the database, ensure to backup your files first.
To backup your database, select the Export tab at the top of the phpMyAdmin page. On the Export page, you’ll see there are two options to export your data — Quick and Custom. You can choose the Quick option to backup the complete database. But if you want to export only some tables in the database, then choose the Custom option.
After downloading your backup data, you can start modifying your database with SQL queries. You can access the SQL query box by clicking on the SQL tab on the phpMyAdmin page.
Let’s assume that you want to change your website’s theme from Twenty-Seventeen to Twenty-Twenty. You can change your theme directly from a database instead of your WordPress dashboard by using the code snippet below.
UPDATE wp_options SET option_value = replace(option_value, ‘twentyseventeen’,’twentytwenty’);
After you write the code above into the SQL query box and click the Go button, some values in the wp_options table will be changed.
Another way to access your database is by using a wordpress database plugin. There are numerous database management plugins that allow you to perform some actions like creating a backup or running SQL queries.
How to Create a Database for a WordPress Installation
If you want to install a WordPress website manually, you need to create a MySQL database first. Your database information — database name, username, database host, password — will be required to fill in the WordPress installation form.
To create a new database, navigate to the Databases section in your hosting control panel, and click on MySQL Databases icon.
On the MySQL Databases page, under the Create a New MySQL Database And Database User section, there is a form you can fill in to create a new database and set a user who can access it. Once you finish with the form, hit the Create button.
Still on the same page, you can see all databases created under the List of Current MySQL Databases And Users section. By clicking on your database name, you will see the settings to change your user’s permissions, make backups, delete your database, and access your database on phpMyAdmin.
Importing a WordPress Database via phpMyAdmin
Importing a database is necessary if you want to migrate your site to a new hosting or to restore your backup data into an existing WordPress installation.
To import your database file via phpMyAdmin, you can follow these steps below:
1. Navigate to the Databases section and click the MySQL Databases icon.
2. Find the database to store your backup file under the List of Current MySQL Databases And Users section, or create a new one if you migrate to a new hosting.
3. Click on the database name and select phpMyAdmin button to enter to the application.
4. Once you enter the phpMyAdmin application, select the Import tab at the top of the page.
5. Click on the Browse button, then upload the backup file that you’ve exported before.
6. Just leave all settings, and click Go.
A WordPress database is a place to store and manage all content and settings of your website.
You can access a database easily from phpMyAdmin on your hosting control panel. This software allows you to make changes in database tables using SQL queries.
Knowing how to manage your database can help you to modify your site without using the WordPress dashboard
Before trying to change any element in a table, always make sure you have a backup database first. Also, remember to protect your database to prevent any hacking attempts.