Trusted WordPress tutorials, when you need them most.
Beginner’s Guide to WordPress
WPB Cup
25 Million+
Websites using our plugins
16+
Years of WordPress experience
3000+
WordPress tutorials
by experts

Beginner’s Guide to WordPress Database Management With phpMyAdmin

Editorial Note: We earn a commission from partner links on WPBeginner. Commissions do not affect our editors' opinions or evaluations. Learn more about Editorial Process.

Are you looking for ways to manage the WordPress database but don’t know how to get started?

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 knowledge of how WordPress works (behind the scenes).

In this article, we will explain how WordPress uses the database, including an intro to 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 more.

How to manage WordPress database using phpMyAdmin

This is a comprehensive guide on WordPress database management, so we have added a table of contents for easier navigation:

What Is a Database, and How Does WordPress Use it?

A database is a system of storing and fetching data in an organized way. A database allows software to manage data in a programmable way.

For example, WordPress uses PHP ( a programming language) to store and retrieve data from the database.

The information stored in a WordPress database includes posts, pages, comments, categories, tags, custom fields, users, and other WordPress settings. We will elaborate on this later in the tutorial when we talk about WordPress database tables.

When you first install WordPress, it asks you to provide a database name, host, username, and password. This information is stored in the configuration file, which is called wp-config.php.

WordPress database information

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 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.

Where Is My WordPress Database Stored?

WordPress uses MySQL as its database management system, which is software installed on your WordPress hosting server. Your WordPress database is also stored on the same server.

This location is not accessible on most shared hosting environments. However, if you are on a VPS hosting plan or a dedicated server, then you can use command line tools to locate the database.

Most often, it is located at the following path:

/var/lib/mysql/your_database_name

However, this could differ from one hosting provider to another.

It is important to keep in mind that you don’t really need to access the database file itself. You can use other tools like phpMyAdmin to manage your database.

What Is phpMyAdmin?

phpMyAdmin is a web-based software that allows you to manage MySQL databases using your web browser. It offers an easy-to-use interface that allows you to run MySQL commands and database operations.

You can also use it to browse and edit database tables, rows, and fields. It also allows you to import, export, or delete all data inside a database.

How to Access phpMyAdmin

PhpMyAdmin comes pre-installed with all top WordPress hosting companies. You can find it under the Databases section of your hosting account’s cPanel dashboard. Below is an example screenshot from the Bluehost control panel:

phpmyadmin panel in Bluehost

Depending on your hosting provider, your cPanel interface may look different than the above screenshot. You would still be able to find the phpMyAdmin icon under the Databases section.

Clicking on it will open the phpMyAdmin interface, where you can select your WordPress database from the left column. After that, phpMyAdmin will display all tables in your WordPress database.

PhpMyAdmin dashboard

Let’s take a look at the WordPress database tables.

Understanding WordPress Database Tables

Each WordPress installation has 12 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 could be different if you changed it at the time of installation.

wp_commentmeta : This table contains meta information about comments posted on a WordPress website. The 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 a comment (approved, pending, trash).

wp_comments : As the name suggests, this table contains your WordPress comments. It contains the comment author’s name, URL, email, comment, and more.

wp_links : To manage blogrolls created by earlier versions of WordPress or the Link Manager plugin.

wp_options : This table contains most of your WordPress site-wide settings, such as the site URL, admin email, default category, posts per page, time format, and 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. Examples of post meta information include the template used to display a page, custom fields, etc. Some plugins 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_termmeta : This table allows developers to store custom metadata for terms under their custom taxonomies. For example, WooCommerce uses it to store metadata for product attributes and categories.

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. For example, your WordPress categories and tags are taxonomies, and each category/tag inside them is a term.

wp_term_relationships : This table manages the relationship of WordPress post types with terms in the wp_terms table. For example, this is the table that helps WordPress determine whether post X is in the Y category.

wp_term_taxonomy : This table defines taxonomies for terms defined in the wp_terms table. For example, if you have the term “WordPress Tutorials“, then this table contains the data that says it is associated with a taxonomy called category. In short, this table has the data that helps WordPress differentiate between which term is a category, which is a tag, and more.

wp_usermeta : Contains meta information about registered users on your website.

wp_users : Contains user information like username, password, user email, and more.

Managing the WordPress Database Using phpMyAdmin

Your WordPress database contains important WordPress settings, all your blog posts, pages, comments, and more. You need to be extremely careful when using phpMyAdmin, or you may end up accidentally deleting important data.

As a precaution, you should always create a complete database backup. This will allow you to restore your database to the way it was before.

Let’s take a look at how to easily create a WordPress database backup.

Creating a WordPress Database Backup Using phpMyAdmin

To create a backup of your WordPress database from phpMyAdmin, click on the database. Then, in the top menu, simply click on the ‘Export’ tab.

Export WordPress database

In newer versions of phpMyAdmin, it will ask you for an export method. The quick method will export your database in a .sql file. In the custom method, it will provide you with more options and the ability to download backups in compressed zip or gzip archive formats.

We recommend using the custom method and choosing zip as the compression method. The custom method also allows you to exclude tables from the database.

Let’s say that you used a plugin that created a database table, and you are no longer using that plugin. Then, you can choose to exclude that table from the backup if you want.

Compression method

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 WordPress security. While the WordPress database contains the majority of your site information, it still lacks several fairly important elements, such as template files, images, uploads, etc.

All your images are stored in the uploads folder in your /wp-content/ directory. Even though the database has the information about which image is attached to a post, it is useless if the image folder doesn’t have those files.

Often, beginners think that a database backup is all that they need. This is NOT true. You need to have a full site backup that includes your themes, plugins, and images.

While most hosting companies claim they have daily backups, we recommend that you keep backups yourself, just in case.

Note: If you are using a managed WordPress hosting solution like WP Engine, then they do create daily backups.

For the majority of people who are not on WP Engine, you should use a WordPress backup plugin to set up automated WordPress backups on your site.

Importing a WordPress Database Backup via phpMyAdmin

phpMyAdmin also allows you to easily import your WordPress database. Simply launch phpMyAdmin and then select your WordPress database.

Next, you need to click on the ‘Import’ link in the top menu.

Import database via phpMyAdmin

On the next screen, click on the ‘Choose file’ button and then select the database backup file you downloaded earlier.

phpMyAdmin will now process your backup file upload and import it into your WordPress database. Once it has finished, you will see a success message.

Database imported successfully

Optimizing Your WordPress Database in phpMyAdmin

After using WordPress for a while, your database becomes fragmented. There are memory overheads that increase your overall database size and query execution time.

MySQL comes with a simple command that allows you to optimize your database. Simply go to phpMyAdmin and click on your WordPress database. This will show you a list of your WordPress tables.

Click on the ‘Check all’ link below the tables. Next to it, there is a “With Selected” dropdown. You need to click on it and choose ‘Optimize table’.

Optimize WordPress database tables

This will optimize your WordPress database by defragmenting selected tables. It will make your WordPress queries run a little faster and slightly reduce the size of your database.

Fixing WordPress Issues Using phpMyAdmin

As we mentioned earlier, phpMyAdmin is a handy tool for troubleshooting and fixing some common WordPress errors and issues.

Let’s check out some common WordPress issues that can be easily fixed using phpMyAdmin.

Reset WordPress Password Using phpMyAdmin

If you forgot your WordPress admin password and cannot recover it via a lost password email, then this method allows you to quickly reset your WordPress password.

First, launch phpMyAdmin and select your WordPress database. This will display your WordPress database tables where you need to click ‘Browse’ next to the wp_users table.

Browse user table

Note: Tables in your WordPress database may have a different table prefix than the one we are showing in our screenshot.

You will now see the rows in your WordPress users table. Go ahead and click on the ‘Edit’ button next to the username for which you want to change the password.

Edit user

phpMyAdmin will show you a form with all the user information fields.

You will need to delete the value in the ‘user_pass’ field and replace it with your new password. Under the ‘Function’ column, select ‘MD5’ from the drop-down menu and click on the ‘Go’ button.

Change user password

Your password will be encrypted using the MD5 hash, and then it will be stored in the database.

Congratulations! You have successfully changed your WordPress password using phpMyAdmin.

Now some of you may be wondering why we selected the MD5 hash to encrypt the password.

In the older version, WordPress used MD5 hash to encrypt passwords. Since WordPress 2.5, it has started using stronger encryption technologies. However, WordPress still recognizes MD5 to provide backward compatibility.

As soon as you log in using a password string stored as an MD5 hash, WordPress recognizes it and changes it using the newer encryption algorithms.

Adding a New Admin User to WordPress Using phpMyAdmin

Let’s suppose you have access to the WordPress database but not the WordPress admin area. While you can change the admin user password, it will prevent other admin users from using their accounts.

An easier solution would be to add a new admin user via phpMyAdmin.

First, you need to launch phpMyAdmin and then select your WordPress database. This will show your WordPress database tables, where you need to click on the ‘Browse’ link next to the wp_users table.

Browse user table

phpMyAdmin will now show you the rows inside the wp_users table.

Go ahead and click on the ‘Insert’ link from the menu at the top.

Insert new row in WordPress users table

This will bring up a form that you need to fill up to add a new user to your WordPress site.

Here is how you need to fill each field in this form.

  • ID – You can ignore this one, as this is automatically generated.
  • user_login – This will be your WordPress username that you will use to log in.
  • user_pass – This is your WordPress password. You need to enter the password and select MD5 in the function column.
  • user_nicename – This is the URL-friendly username you can use as your login.
  • user_email – Enter a valid email address, as you may need it to receive password reset and WordPress notification emails.
  • user_url – Add your website URL, or you can leave it blank.
  • user_registered – You need to select the CURRENT_TIME in the function column to automatically insert the current time here.
  • user_activation_key – You can leave this field blank as well it is used to approve user registration.
  • user_status – You can leave this field blank as well.
  • display_name – You can enter the user’s full name as you want it to be displayed on the articles. You can also leave it blank.
Fill user form

After filling in the form, click on the ‘Go’ button to insert it into your WordPress database. phpMyAdmin will now run the MySQL query to insert the data.

We have added the user, but that user does not have an administrator user role on your WordPress site. This value is saved in another table called wp_usermeta.

Before we can make this user an administrator, we will need to find the user ID.

Simply click on the ‘Browse’ link next to the wp_users table, and you will see a row containing your newly added user with their user ID.

Find user ID

Write down the user ID because you will need it in the next step.

Now, let’s open the wp_usermeta table by clicking on the ‘Browse’ link next to it.

Browsing wp_usermeta table

Next, you need to click on the ‘Insert’ link at the top.

This will allow you to add a new row to the table.

Insert new row to usermeta table

You will now see a form to enter a new row.

This is where you will tell WordPress that the user you created earlier has the administrator user role.

Adding administrator user role via usermeta

Here is how you will fill in this form.

  • umeta_id – You need to leave it blank, as it is automatically filled in.
  • user_id – Enter the user ID you copied earlier.
  • meta_key – You need to enter ‘wp_capabilities’ in this field. However, you may need to replace wp_ if your WordPress table names use a different prefix.
  • meta_value – You need to enter the following serialized value:
    a:1:{s:13:”administrator”;s:1:”1″;}

Finally, click on the ‘Go’ button to save your changes.

Next, we need to add another row to define the user level. Click on the ‘Insert’ link in the top menu to add another row to the usermeta table.

Adding the user level meta field

Here is how you will fill in this form.

  • umeta_id – You need to leave it blank, as it is automatically filled in.
  • user_id – Enter the user ID for your newly added user.
  • meta_key – You need to enter ‘wp_user_level’ in this field. However, you may need to replace wp_ if your WordPress table names use a different prefix.
  • meta_value – Here, you need to enter ’10’ as the user level value.

Don’t forget to click on the ‘Go’ button to insert the data.

That’s all, you can now visit the WordPress admin area and log in with your newly added admin user.

Change a WordPress Username via PhpMyAdmin

You may have noticed that while WordPress allows you to change a user’s full name or nickname, it does not allow you to change the username.

A lot of users end up choosing usernames during the installation that they may later want to change. Here is how you can do this via phpMyAdmin.

Note: There is an easier way to change your WordPress username using a plugin.

First, you need to launch phpMyAdmin from your hosting account’s cPanel dashboard. After that, you need to select your WordPress database.

phpMyAdmin will show your WordPress database tables. You need to click on the ‘Browse’ link next to the wp_users table.

Browse user table

This table will now list all the registered users on your website.

Next, click on the ‘Edit’ link next to the username that you want to change.

Edit user data via phpMyadmin

phpMyAdmin will now show you the user data.

You need to locate the user_login field and change the value to the username you want to use.

Change WordPress username in phpMyAdmin

Don’t forget to click on the ‘Go’ button to save your changes. You can now visit your website and log in with your new username.

Other Useful phpMyAdmin Tricks

Your WordPress database is the engine behind your website. With phpMyAdmin, you can tweak settings to improve performance, fix issues, or simply change things that cannot be changed from inside the WordPress admin area.

Here are just a few more tricks you can use with 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.

The first thing that you can do is to change the WordPress database prefix. This can significantly reduce the chances of SQL injection attacks on your WordPress database. This is because hackers often target sites using the default wp_ table prefix.

You should always choose a strong username and password for your MySQL user. This will make it difficult for someone to get access to your WordPress database.

For WordPress security, we strongly recommend that you use Sucuri. It is the best WordPress security plugin that will catch any attempts at MySQL injection even before they reach your website.

We hope that this guide helped you learn WordPress database management and how to use phpMyAdmin. You may also want to read our guide to how to speed up WordPress performance and check out our top picks for the best email marketing services to grow your business.

If you liked this article, then please subscribe to our YouTube Channel for WordPress video tutorials. You can also find us on Twitter and Facebook.

Disclosure: Our content is reader-supported. This means if you click on some of our links, then we may earn a commission. See how WPBeginner is funded, why it matters, and how you can support us. Here's our editorial process.

Editorial Staff

Editorial Staff at WPBeginner is a team of WordPress experts led by Syed Balkhi with over 16 years of experience in WordPress, Web Hosting, eCommerce, SEO, and Marketing. Started in 2009, WPBeginner is now the largest free WordPress resource site in the industry and is often referred to as the Wikipedia for WordPress.

The Ultimate WordPress Toolkit

Get FREE access to our toolkit - a collection of WordPress related products and resources that every professional should have!

Reader Interactions

109 CommentsLeave a Reply

  1. Syed Balkhi says

    Hey WPBeginner readers,
    Did you know you can win exciting prizes by commenting on WPBeginner?
    Every month, our top blog commenters will win HUGE rewards, including premium WordPress plugin licenses and cash prizes.
    You can get more details about the contest from here.
    Start sharing your thoughts below to stand a chance to win!

  2. Jiří Vaněk says

    Wow, this is a great article, I have saved it to my favorites. I actually knew some of the tricks in phpmyadmin, but definitely not all of them, and here it’s all beautifully described, including pictures. This article is a goldmine for working with databases and WordPress.

    • WPBeginner Support says

      For any issues with your database, you would want to reach out to your hosting provider and they should be able to assist.

      Admin

  3. Kevin says

    A good introduction thank you.

    I have several WP sites and several databases.
    How can I tell which database belongs to which site?

  4. Vimal Rathod says

    You write well I learned a lot from this blog. My question was answered but I also got the information I needed

  5. ajay says

    I have two totally separate WP websites setup. Different domains, different databases. I manage both of them and they are both hosted on a dedicated server. I am trying to include some basic content that requires just a tad more than an RSS feed. I need to pull data from SITE-1 and display it on SITE-2

    • WPBeginner Support says

      If it is a post or page you can go under Tools>Export on the site with the content to select the specific content you want to be transferred and then use Tools>Import on the site to bring in the content

      Admin

  6. Wadhi says

    do i need to learn back end development if i use WordPress. I mean aren’t there some plugins which can help me create a website with front-end and back end system. I want a food delivery website

    • WPBeginner Support says

      You do not need to learn development to run a WordPress site. Some plugins will give you that kind of editing power but not all of those plugins are needed.

      Admin

  7. George says

    when i clink on new post, this is what i see; Sorry, that didn’t work.
    Please try again or come back later.
    504 Error. Gateway Timeout.
    please help

  8. Steve Hanzek says

    I’m looking to create a website that would allow bands and venues to maintain their schedules, and a visitor to select a date and see all the bands playing on that date, and all the venues with bands playing on that date.
    I’m proficient with the SQL (mySQL) needed to do the querying, just want to find out if WordPress will let me create custom pages based on the results of a query?
    Thanks

Leave a Reply to Pooja Raval Cancel reply

Thanks for choosing to leave a comment. Please keep in mind that all comments are moderated according to our comment policy, and your email address will NOT be published. Please Do NOT use keywords in the name field. Let's have a personal and meaningful conversation.

WPBeginner Assistant
How can I help you?

By chatting, you consent to this chat being stored according to our privacy policy and your email will be added to receive weekly WordPress tutorials from WPBeginner.