Learn About WordPress Database Management with phpMyAdmin

WordPress has PHP as its scripting language and MySQL as its database management system. But to use WordPress, it isn’t needed to learn either of them.

Simply understanding the basics of the two can help you resolve the problems and help you to understand the working of WordPress.

In this post, you will learn about how WordPress uses the database along with an intro to default WordPress database tables.

You will also learn to manage a WordPress database using phpMyAdmin including creating database backups, optimizing the WordPress database, and more.

What is a Database and How WordPress Uses it?

A system that stores and fetches data in an organized way is called a database. It enables software to manage data in the form of programming.

For example: WordPress uses PHP (programming language) for storing and retrieving data from the database.
WordPress database comprises of pages, posts, comments, categories, tags, users, custom fields, and other WordPress settings.

When you install WordPress for the first time, you are asked for a database name, host, username, and password. The configuration file called wp-config.php stores this information.

WordPress uses the information provided about the database during the installation for creating tables and storing default installation data within those tables.

Once the installation is complete, queries to this database are run by WordPress for dynamically generating HTML pages for your website or blog.

Therefore, WordPress is an extremely powerful platform as you don’t need to create a new .html file for each page that you want to create. Everything is handled by WordPress in a dynamic way.

Where is Your WordPress Database Stored?

MySQL is WordPress’s database management system, which is a software installed on your WordPress hosting server. You will also find your WordPress database stored on the same server.

But you aren’t allowed to access this location if you are on a shared server. However, if your website is hosted on a VPS server or a dedicated server, then you can use command line tools for locating the database.

Most often you will find it at the following path:

/var/lib/mysql/your_database_name

This may differ from one hosting provider to another.

Remember that you don’t actually need to access the database file itself. phpMyAdmin can be used to manage your database.

What is phpMyAdmin?

A web-based software that helps you to manage MySQL databases using the web browser is called as phpMyAdmin. You get an intuitive interface that helps to easily run MySQL commands and database operations.

It also enables you to browse and edit database tables, fields and rows. Moreover, you can import export or even delete all the data within a database.

Steps to Access phpMyAdmin

All top WordPress hosting companies offer phpMyAdmin pre-installed. It can be found under the Databases section of your cPanel dashboard. Below is the screenshot of it from our control panel:

Your cPanel interface may vary based on your web hosting provider. But the phpMyAdmin is always found under the databases section.

When you click on the phpMyAdmin icon it will display its interface where WordPress database can be selected from the left-side column. Then phpMyAdmin will display all the tables in your WordPress database.

Let’s understand the WordPress database tables.

Understanding WordPress Database Tables

There are 12 default tables in the database that come with each WordPress installation. In each database table, you will find the data for different features, sections, and functionality of WordPress.

When you check the structure of these tables, you will easily understand the different parts of your website are stored. The default WordPress installation tables are as below:

• wp_commentmeta: In this table you will find meta information about comments posted on a WordPress website. The table comprises of four fields meta_id, comment_id, meta_key, and meta_value. Each meta_id relates to a comment_id. One example of comment meta information stored represents the status of comment (approved, pending, trash, etc).
• wp_comments: The name itself reveals that this table contains your WordPress comments. It contains comment author name, email, url, comment, etc.
• wp_links: It helps in managing blogrolls created by earlier versions of WordPress or the Link Manager plugin.
• wp_options: In this table you will find most of your WordPress site-wide settings such as: admin email, site url, default category, time format, posts per page, and much more. It is also used by different WordPress plugins to store plugin settings.
• wp_postmeta: In this table, you will find the meta information about your WordPress posts, pages, and custom post types. Example of post meta information can be which template to use to display a page, custom fields, etc. Some plugins may also use this table for storing plugin data such as WordPress SEO information.
• wp_posts: Though the name says posts it contains all post types or content types. You will find all your pages, posts, revisions, and custom post types in this post.
• wp_termmeta: Developers can store custom metadata for terms under their custom taxonomies. For example, metadata in WooCommerce is stored using it for product attributes and categories.
• wp_terms: WordPress has a powerful taxonomy system which helps in organizing your content. Individual taxonomy items are called terms, and are stored in this table. For example, taxonomies are the categories and tags in WordPress, and term is each category / tag within them.
• wp_term_relationships: This tables helps in relationship management of WordPress post types with terms in wp_terms table. For example this table helps WordPress determine post X is in Y category.
• wp_term_taxonomy: This table represents taxonomies for terms displayed in wp_terms table. For example, if you have a term “WordPress Tutorials“, then this table will comprise of the data that is associated with a taxonomy called category. In short, you will find the data that helps WordPress differentiate between which term is a category, which is a tag, etc. in this table.
• wp_usermeta: It comprises of meta information about registered users on your website.
• wp_users: Comprises of user information such as username, password, user email, etc.

Steps to Manage WordPress Database using phpMyAdmin

The WordPress database includes important WordPress settings, all the ages, blog posts, comments and more.

When using phpMyAdmin one should be careful or else you might accidentally delete important data.

For a precautionary measure, ensure that you create a complete database backup. This will later help you to restore your database as it was earlier.

Let’s check how to easily create a WordPress database backup.

Steps to Create a WordPress Database Backup using phpMyAdmin

For creating a backup of your WordPress database using phpMyAdmin, click on your WordPress database. From the top menu, click on the Export tab

If you have newer versions of phpMyAdmin, it will prompt you for an export method. With the Quick method, your database will get exported in a .sql file. With the Custom method, you will get more options and ability to download backup in compressed zip or gzip archive.

It is recommended to use the Custom method and select zip as the compression method. You can also exclude tables from the database with this method.

Suppose you have 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.

You can import back your exported database file into a different or the same database using phpMyAdmin’s Import tab.

Steps to Create a WordPress Backup Using a Plugin

It is always best to maintain regular backups of your WordPress site for your WordPress security. Your WordPress database contains the maximum information of your website but still lacks some fairly important elements such as template files, uploads, images, etc.

You will find all the images stored in the uploads folder in your /wp-content/ directory. Even if the database comprises of the information which image is attached to a post, it is isn’t useful, if the image folder doesn’t have those files.

Many times newbies think that they need just the database backup. But it’s more than that. You also need to take an entire site backup which will include your plugins, themes and images. So, ensure you take it.

If you are not using MilesWeb services or aren’t ready to pay for the backup service, you can use a WordPress backup plugin to set up automated WordPress backups on your site.

Steps to Import a WordPress Database Backup via phpMyAdmin

You can easily import your WordPress database using phpMyAdmin. Just launch the phpMyAdmin and select your WordPress database.

Then click on the Import tab from the top menu.

Now click on the Choose file button and select the database backup file that you downloaded earlier.

Your backup file will be uploaded and imported into your WordPress database. After it’s completed, you will get a success message.

Steps to Optimize your WordPress Database in phpMyAdmin

Your database becomes fragmented after using WordPress for a while. This is because the memory overheads increase your overall database size and query execution time.

A simple command in MySQL enables you to optimize your database. Navigate to phpMyAdmin from your cPanel dashboard and click on your WordPress database. A list of your WordPress tables gets displayed.

Next, click on the Check All link below the tables. From the With Selected drop down besides the Check All link, select Optimize table.

With this your WordPress database will get optimized by defragmenting selected tables. Your WordPress queries speed will be faster than before and the size of your database will get reduced with this.

How to Fix WordPress Issues using PhpMyAdmin?

As mentioned earlier, phpMyAdmin is an easy tool to troubleshoot and fix some common WordPress errors and issues.

Below are some common WordPress issues that can be easily fixed using phpMyAdmin:

Resetting WordPress Password Using PhpMyAdmin

In case you forget your WordPress admin password and are not able to recover it via lost password email, then you can quickly reset it as below:

At first, go to phpMyAdmin and select your WordPress database. You WordPress database tables will get displayed.

Go to the wp_users table and click on the browse link.

The rows in your WordPress users table will get displayed. Click on the Edit link besides the username where you want to change the password.

All the user information fields will get displayed.

Delete the value in the user_pass field and type your new password. Select MD5 from the drop-down menu under the Function column and click on the Go button.

With this your password will get encrypted using the MD5 hash and will be stored in the database.

Congratulations! You have successfully changed your WordPress password with the help of phpMyAdmin.

Wondering why we selected the MD5 hash to secure the password?

It is because in the older version, WordPress used MD5 hash for encrypting passwords. WordPress 2.5 started using stronger encryption technologies. But, WordPress still identifies MD5 to provide backward compatibility.

Now when you log in using a password string stored as an MD5 hash, WordPress identifies it and changes it with the newer encryption algorithms.

Steps for Adding a New Admin User to WordPress using PhpMyAdmin

For instance, you have access to the WordPress database but not the WordPress admin area. When you change the admin user password, it will restrict the other admin user from using their account.

You can add a new admin user using phpMyAdmin as it is easier solution

Firstly, go to phpMyAdmin and select your WordPress database. Your WordPress database tables will get listed. Here click on the ‘Browse’ link besides wp_users table.

The rows inside the wp_users table will be seen. From the menu on the top, click on the ‘Insert’ link.

Below is the explanation of each field:

• ID –It can be ignored as it is automatically generated.
• user_login – It is your WordPress username that you will use to login.
• user_pass – It is your WordPress password that you need to enter for login and select MD5 in the function column.
• user_nicename – It is the URL friendly username that can be used to login.
• user_email – A valid email address needed to recieve password reset and WordPress notification emails.
• user_url – You can add your website URL here or leave it blank.
• user_registered – In the Function column, select the CURRENT_TIME to automatically insert current time here.
• user_activation_key – Leave this field blank as it is used to approve user registration.
• user_status – Leave this field blank.
• display_name – Enter the user’s full name for displaying on the articles. It can also be left blank.

One you fill the form, click on the Go button to insert it into your WordPress database. Now the MySQL query will be run to insert the data.

Now the user is added, but that user does not have an administrator user role on your WordPress site. You will find this value saved in another table called wp_usermeta.

Before making this user an administrator, we need to find the user ID. For this click on the ‘Browse’ link next to wp_users table and a row containing your newly added user with its user ID will get displayed.

Note the user ID as you will need it in the next step.

Open the wp_usermeta table by clicking on the Browse link besides it.

Then click on the Insert tab from the above menu to add a new row to the table.

A form will get displayed to enter a new row. Here you will let WordPress know that the user you created had the administrator user role previously.

You will fill the form in the below way:

• umeta_id – Leave it blank as it is automatically filled in.
• user_id – Mention the user ID you copied earlier.
• meta_key – Enter wp_capabilities in this field. You will need to replace wp_ if your WordPress table names use some other prefix.
• meta_value – Enter the below serialized value:
  a:1:{s:13:”administrator”;s:1:”1″;}

After filling the form, click on the Go button to save your changes.

Now we need to add another row to mention the user level. From the top menu, click on the Insert link for adding another row to the usermeta table.

You will fill the form in the below way:

• umeta_id – Leave it blank as it is automatically filled in.
• user_id – Mention the user ID for your newly added user.
• meta_key – Enter the wp_user_level in this field. However, you might have to replace wp_ if your WordPress table names use a different prefix.
• meta_value – Enter 10 here as the user level value.

Click on the Go button so that the data gets inserted.

Now you can visit the WordPress admin area and log in with your newly added admin user.

Changing a WordPress Username using PhpMyAdmin

You might have observed that WordPress allows you to change user’s full name or nickname but, it does not allow you to change the username.

Many users select usernames during the installation but might want to change them later. You can do it using phpMyAdmin as below:

Navigate to phpMyAdmin and select your WordPress database.

A list of your WordPress database tables will get displayed. Here click on the ‘Browse’ link besides wp_users table.

You will see all the registered users on your website. Click on the Edit link besides the username that you want to modify.

Now you will see the user data. Here go to the user_login field and change the value to the username that you want to.

Click on Go button to save your changes. Then visit your website and login with your new username.

How to Secure Your WordPress Database?

Remember that every site can be hacked. But by taking certain measures you can make it a little harder.

Firstly, change WordPress database prefix. Due to this, the chances of SQL injection attacks on your WordPress database get reduced because often hackers target multiple sites where they are targeting the default wp_ table prefix.

Always select a strong username and password for your MySQL user so that no one can get access to your WordPress database.

Sucuri is the best WordPress security plugin that is able to catch any attempts at MySQL injection just before it reaches your website.

So, you have now learned to manage your WordPress database using phpMyAdmin. If you have any queries related to your WordPress database management, you can post them in the below comments section.

Also Read

Switch Your WordPress Database URLs to HTTPS With a Plugin

How to disable all WordPress plugins directly from the database?