WordPress is written using PHP as its scripting language and MySQL as its database management system. In order to use WordPress, you don’t really need to learn either of them. However, a very basic understanding of the two could help you troubleshoot problems as well as increase your understanding of how WordPress work. In this article, we will explain how WordPress uses the database including a primer on default WordPress database tables. We will also show you how to manage a WordPress database using phpMyAdmin which will include creating database backups, optimizing the WordPress database, and much more.
Video Tutorial
If you don’t like the video or need more instructions, then continue reading.
How WordPress uses Database
WordPress uses PHP (programming language) to store and retrieve data from the database. The information stored in a WordPress database include: posts, pages, comments, categories, tags, custom fields, users, and other site options such as site urls etc. We will elaborate on this later in the tutorial when we cover all WordPress database tables.
When you first install WordPress, it asks you to provide your Database name, host, username, and password. This information is stored in the configuration file (wp-config.php).
During the installation, WordPress uses the information you provide about the database to create tables and store default installation data inside those tables. After the installation, WordPress runs queries to this database to dynamically generate HTML pages for your website or blog. This is what makes WordPress extremely powerful because you don’t have to create a new .html file for each page that you want to create. WordPress handles everything dynamically.
Understanding WordPress Database Tables
Each WordPress installation has 11 default tables in the database. Each database table contains data for different sections, features, and functionality of WordPress. Looking at the structure of these tables, you can easily understand where different parts of your website are stored. Currently, a default WordPress installation creates the following tables:
Note: wp_ before each table name is the database prefix you choose during the installation. It can be different.
wp_commentmeta : This table contains meta information about comments posted on a WordPress website. This table has four fields meta_id, comment_id, meta_key, and meta_value. Each meta_id is related to a comment_id. One example of comment meta information stored is the status of comment (approved, pending, trash, etc).
wp_comments : As the name suggests this table contains your WordPress comments. It contains comment author name, url, email, comment, etc.
wp_links : To manage blogrolls create by earlier versions of WordPress or the Link Manager plugin.
wp_options : This table contains most of your WordPress site wide settings such as: site url, admin email, default category, posts per page, time format, and much much more. The options table is also used by numerous WordPress plugins to store plugin settings.
wp_postmeta : This table contains meta information about your WordPress posts, pages, and custom post types. Example of post meta information would be which template to use to display a page, custom fields, etc. Some plugins would also use this table to store plugin data such as WordPress SEO information.
wp_posts : The name says posts but actually this table contains all post types or should we say content types. This table contains all your posts, pages, revisions, and custom post types.
wp_terms : WordPress has a powerful taxonomy system that allows you to organize your content. Individual taxonomy items are called terms and they are stored in this table. Example, your WordPress categories and tags are taxonomies, and each category and tag inside them is a term.
wp_term_relationships : This table manages relationship of WordPress post types with terms in wp_terms table. For example this is the table that helps WordPress determine post X is in Y category.
wp_term_taxonomy : This table defines taxonomies for terms defined in wp_terms table. For example if you have a term “WordPress Tutorials“, then this table contains the data that says it is associated with a taxonomy categories. In short this table has the data that helps WordPress differentiate between which term is a category, which is a tag, etc.
wp_usermeta : Contains meta information about Users on your website.
wp_users : Contains User information like username, password, user email, etc.
Managing WordPress Database using phpMyAdmin
phpMyAdmin is an open source software that provides a web based graphical user interface to manage your mySQL database. Most WordPress hosting providers have phpMyAdmin installed in their control panel. This allows users to easily access the database and perform common database management tasks.
All of our recommended web hosting providers use cPanel. To access phpMyAdmin in cPanel, scroll down to Databases and click on phpMyAdmin. This will open phpMyAdmin in a new browser tab.
Clicking on Databases will show you a list of Databases you have created or have access to. Click on your WordPress Database, and it will show you the list of your WordPress Database tables.
You can perform various tasks from phpMyAdmin such as find/replace a certain word in your post, repair your database, optimize your database, add new admin users, deactivate all plugins, change passwords etc etc etc.
Note: Before you change anything, you must make a database backup. There is no undo button. So just make the backup.
Creating a WordPress Database Backup using phpMyAdmin
To create a backup of your WordPress database from phpMyAdmin, click on your WordPress Database. On the top menu, click on the Export tab.
In newer versions of phpMyAdmin, it will ask you for a export method. The quick method will export your database in a .sql file. In custom method it will provide you with more options and ability to download backup in compressed zip or gzip archive. We recommend using custom method and choosing zip as the compression method. The custom method also allows you to exclude tables from the database. Lets say if you used a plugin that created a table inside your WordPress database, then you can choose to exclude that table from the backup if you want.
Your exported database file can be imported back into a different or the same database using phpMyAdmin’s import tab.
Creating a WordPress Backup using a Plugin
Keeping regular backups of your WordPress site is the best thing you can do for your WordPress security. While the WordPress database contains majority of your site information, it still lacks a fairly important element, images. All your images are stored in the uploads folder in your /wp-content/ directory. Even though the database has the information which image is attached where in the post, it is useless if the image folder doesn’t have those files.
Often beginners think that the database backup is all what they need. It is NOT true. You need to have a full site backup that includes your themes, plugins, and images. NO, most hosting company do not keep daily backups.
Note: If you are on a managed hosting solution like WPEngine, then you don’t have to worry. They take care of daily backups.
For majority of us who are not on WPEngine, you should use one of the many solutions available to keep regular backups of your site.
We are using VaultPress, a paid monthly service, to keep regular backups of our site. For those of you who love plugins and don’t like paying recurring fees, then we recommend that you check out BackupBuddy which is hands down the most complete backup solution for WordPress.
Optimizing your WordPress Database in phpMyAdmin
After using WordPress for a while your database becomes fragmented. There are memory overheads which increases your overall database size and query execution time. For those of us who remember in the old PC days, you would notice that your computer would get faster once you use Disk Defragmenter. The MySQL database works in a similar way. It comes with a simple command that allows you to optimize your database. Go to phpMyAdmin and click on your WordPress Database. This will show you a list of your WordPress tables. Click on Check All link below the tables. Next to it there is a “With Selected” drop down, click on it and choose Optimize table.
This will optimize your WordPress tables by defragmenting tables. It will make your WordPress queries run a little faster and slightly reduce the size of your database.
Other Things You can do Using phpMyAdmin
There are many things you can change in your WordPress website by changing values in Database using phpMyAdmin. However, it is highly recommended that you don’t ever do it unless it is absolutely necessary or if you know what you are doing. Make sure that you first back up your database before making any changes to your database. Below are some of the things you can do with your database:
- Reset WordPress Password from phpMyAdmin
- Add an Admin User to WordPress Database via mySQL
- Change WordPress Username via phpMyAdmin
- How to Deactivate all WordPress Plugin from phpMyAdmin
Securing your WordPress Database
Before we get into this, we want to emphasize that every site can be hacked. However, there are certain measures you can take to make it a little harder. First thing that you can do to is to change WordPress database prefix. This can significantly reduce the chances of SQL injection attacks on your WordPress database because often hackers target sites in masses where they are targeting the default wp_ table prefix.
You should always choose a strong username and password for your MySQL. This will make it difficult for someone to get access to your database.
For WordPress security, we strongly recommend that you use Sucuri. Here are 5 reasons why we use Sucuri.
We hope that this guide helped improve your understanding of the WordPress database and how it works. While you may not need this guide right away, it always help to know how things work for the future.
i have a result table in database and i want to fetch the result of the student by registration number.
i m not getting solution to where to code in the wordpress site….and how to see result of the student by registration number.
Thank you so much for the tutorial, its powerful
however I have a question;
I have a database that I have created with workbench and I have imported it to phpmyadmin. however I want to connect with wordpress so that when I submit data through wordpress I can be able to view it in phpmyadmin and I can view it again in wordpress interface that I have created
Your tutorials has been helpful.
based on a previous tutorial on installing wordpress on PC, I followed the tutorial and it was successful. i developed a website for diabetes association of Nigeria which iam a member too. But my challenge now is how to add registration form for new members to register online and how to get donation online in the website. i need your help please.
Hi Ibrahim,
Please see our tutorial on how to create a custom user registration form in WordPress. As for donations, we are not sure which payment providers you will be using to collect donations. Here is our tutorial on how to accept online donations in WordPress
Is it possible to migrate WordPress database to newly developed web applications?
Where can i upload the website I made and that can be accessed offline. Thank you in advance.
hi Ian,
If it is a WordPress website, then see our guide on how to move a local site to a live server. You will need a WordPress hosting account for that.
To make your wordpress website can be accessed offline you can use xampp as a local server and you can install wordpress inside htdocs folder in xampp installation folder.
Thanks a lot.it gave me some clear vision.
What if i want to import wordpress menus? Where in the database are the menus stored?
thanks…it helps a lot to understand it a bit better..
Very helpful tutorial, thank you, it actually worked.
Please where does wordpress store page information. How can I change a page title from the database?
I use contact form 7 for feedback purposes and i store that data in database but now i want that particular data by URL like (abc.com/feedback/5) so it will show the data of id no 5 how i do this in wordpress?
Hello,
I`m planing to make some wp website with registration for specific users ( I have a non profit organisation that has more than 2000 members.) Now, I have the list of them (members) in my Access db with all informations needed. My question is, is it possible to connect this access db with wp db? If yes, how to do that?
Also, every member has a unique ID in my db, which is also a number for the membership card they have. If before mentioned is possible, could I make a field in registration form, beside Username&Pass, that would request that ID number which would be checked from db and if correct, make a registration, if false, deny it?
Hope you understand me cause my englesh is not so grate…
Thanks in advance
I’m having the same problem, please did you figure out a solution?? If you did please, get back to me.
Hello, I am preparing to launch my first wordpress blog site under my first website. Testing and learning the blog setup I am wondering how the email stuff is working. I created a comment in response to a post and ticked the email me a notification when posts are done option. And when I made another post I received an email notification. Looking in the tables using phpmyadmin I can see the email address I entered with the comment stored in wp1_comments but cannot see where/how the fact is stored that an email should be sent to that address when a new post is made.
Thank you
Hi,
Can you please help me that how can I view my blog database because they are not visible anywhere.
snaps attached
Hi,
I run Certification Services like food certificate, ISI Certificate and etc.,.
I have simple wordpress website (self hosted),
Now, I plan to create Page for my clients to get current status about their Application.
Once they enter (1) Application No. (2) Date of Birth / Mobile number, if match details it will display application status else show error message (like not match given details).
Kindly suggest me which plugin / method suite for my requirement.
Thanks
Hello
I need some help, because I don’t understand something:
I register a WP website… and install WooCommerce in it. (I use XAMPP localhost on C partition)
So I have a clear WP+WooCommerce site which I’d like to use to create other WP sites too.
So I copy all the WP files from C… to another partition, let’s say D partition
and I also export its database by phpMyAdmin. Now I have a copy of the WP site and its database on D partition.
But what if I’d like to use the copied WP+WooCommerce site (pattern) I’ve created?
It is still contains the same e-mail address and password I used when I registrated on the official site of WP right? So how can I modify that?
I’d like to create a new website (with different admin e-mail and password) but using the same WP+WooCommerce site (pattern) I’ve created before.
When I did a database backup and followed the custom options. (following these steps: )
When I hit GO, a file goes to my downloads folder and does nothing. the file is empty. What could I be doing wrong?
Thank you for the great explanation of how WordPress interfaces to MySQL. Question: We have some blogs our site that are displaying a blank page. The blogs with blank pages have the same information as those that are working properly. I’ve used phpMyadmin to examine the tables and all looks well. Which table does WordPress use to begin the page generation?
It seems like a plugin or theme issue. To find out what’s causing the issue, deactivate all plugins on your site and then switch to a default theme like Twenty Sixteen.
The video was a little superfluous to the more descriptive written explanation of this article AND, as with many of your videos the narrator speaks too quickly and is thus difficult to understand if you don’t speak American. – even to me as a native English speaker!
how should i connect the in-build search form with my database to see the search results…
In wp_comments table you will find comment_author_email and comment_author_IP fields. Note that the wp_ prefix in the comments table may differ in your database.
You can also find comment author email and IP information inside WordPress admin area.
Which part of the database management system in phpMyAdmin shows a commenter’s IP address and email address?
phpMyAdmin is not a database management system. It is a utility that allows you to manage your database in a web based graphical user interface.
Your actual database resides on your MySQL database server. Any utility that wants to manage, backup, or make changes to your database will need your database username and password to do so.
BackupBuddy works as a WordPress plugin and your database username and password are already stored in your WordPress configuration file. So it can automatically connect to your database without explicitly asking for your username and password.
Is it necessary for any plugin/ tool to access to myphpadmin to backup database.. If not then how backupbuddy creates database backups…
Is backupbuddy require username and password of myphpadmin to access to database…
I am newbie and confused. If you know then please let me know…
I just wanted to say…thanks…good article! (i was wondering how all this worked).
Hi, I would like to know if it’s okay to delete this table I found along with the default ones: wp-sml. I don’t even know what this is for. Thank you so much for your article and support.
Thanks for the info. Is there a way to get the information from plugins (ie-contact forms) and put it into a local database like access..in order to increase data security? Does it have to be in the MySQL on the host? Thank you!
Since last evening my website is not showing any plugins or widgets. It seems to have vanished. ILast I worked on it was to create a couple of subdomains and a post on the main page.
Please contact your web host for support. Before that change your database, ftp, and WordPress passwords.
I made a back up of my SQL database on my computer with Export via PhpMyAdmin. Then, using a copy on that exported database, I made minor adjustments to some serialized links in the option table. Now I want to import the modified database back to the server with Import via PhpMyAdmin.
My question: before importing, do I have to make a drop of all the existing tables already there ?
Yes if you are certain you have a working backup to revert back to incase your changes don’t work out as you intended.
I want to connect database in phpmyadmin to wordpress. But I don’t know. Can you help me?
Hi wondering,
If you want to create a web that contains say accommodations, address and name of university and need to store that data in database. Does WP provide database to store all of those datas, in text format, image and URL
Thanks
Hi thanks for this helpful information
Hi,
Question: when we create installable wordpress plugin and created custom table to store some values,than is there need to import that table also,when we are installing plugin?
It actually depends on the situation. If you used that plugin before and it has stored some important data that you would like to have then you should import it. However if you are just installing the plugin the first time then there is no need to worry about it the plugin will automatically create a new table for itself to use.
Hi guys,
Question: when you create the database that will be used by wordpress, should you create a table as well or leave it? If you should create a table, then how many and why? What are the specifics for the/those table(s)?
Meaning; is it OK to create a database called wordpress and 1 table called mywp and then edit wp-config.php to contain this information plus the connection details for that database or should it be done differently?
Thanks in advance!
you don’t need to create any tables. WordPress will automatically create tables during installation.
hi,
Thanks for an interesting article. I hope you may be able to provide some direction.
I expect that I have some left over tables from plugins, but how can I find out what certain tables are? And how do I know which should be deleted or left alone? Would optimizing these tables fix them if they are my issue?
My reason for asking is that I have a small database for a new site build, but have slow sql queries and database connection errors.
These are my questionable tables
wrd_woocommerce_order_itemmeta (an old plugin that is no longer used)
wrd_woocommerce_order_items
wrd_wpss_Answers (no idea what these are, even after searching online)
wrd_wpss_Fields
wrd_wpss_Questions
wrd_wpss_Quizzes
wrd_wpss_Results
wrd_wpss_Routes
wrd_woocomerce tables are for the WooCommerce plugin. If you are not using it anymore, then you can delete it.
WPSS tables seems to be from a plugin called WordPress Simple Survey. If you don’t have that, then you can delete those as well.
Hi,
First I wanted to say your Staff does a great job publishing a quality Newsletter. I look forward to reading it and find many useful articles for all levels of users.
Slowly I am collection my favorite articles. My questions is: Do you have a place where I can find all the articles so I can save them in pdf format?
Hi Mel,
Yes we are working on a solution for that
Great, great article…
I’d like to know though, which is the correct way to manually export our database using phpmyadmin. You know, a lot of options… and on the WordPress Codex they have not updated the tutorial with the last version of PhpmyAdmin!
Thanks a lot guys
The method that we described in the article works just fine.
Ah ok… but what about all those options like “drop tables”, “if not exist”, etc. You don’t talk about them. Do you leave those untouched? Because in the codex they talk about changing them in some parts. What is your thought about it? Thanks
Good article.
FYI: Possible typo in wp_term_relationships paragraph: For example this is the plugin that helps WordPress determine post X is in Y category.
Should ‘plugin’ be ‘table’?
Good catch. Just fixed that
Hi, I did what you said as I went back and forth reading your article I did it not really sure what I did I do have an idea. just became a member to watch the video’s am returning there shortly. I would like to say Thank You very much and GOD bless been having difficulties and picking up some information how all this operates from you guys and girls is GREAT thanks again have a great day.
Thank you for making even the most “scary” parts of WordPress more friendly for the new user. I have managed to created sql databases as required, but never thought an understanding of database management and php was within my grasp.
Thanks for delivering these posts which are relevant and written in a way that allows everyone regardless of prior knowledge to garner something from each of them!
Thanks for your comment Evelyn. Glad you liked the article. If you have suggestions or want us to cover something specific, then please let us know. Always happy to help.