Beginner’s guide to WordPress database management with phpMyAdmin

PHP is the scripting language of WordPress and MySQL is the database management system of WordPress. However, you will not need to know about PHP and MySQL to use the WordPress website. But sometimes it is better if you have a basic understanding of them as WordPress users. Because if you have a basic knowledge of PHP and MySQL, it will support you to fix troubleshooting problems. As well as you can enhance the understanding of how WordPress works inside.

Hence, this article presents you with how WordPress uses the database including an introduction to default WordPress database tables. As well as you can learn how to manage a WordPress database using phpMyAdmin. It also contains creating database backups, optimizing the WordPress database, and more. Hence, it is recommended and motivates all WordPress users to read this article to enhance their knowledge about WordPress further. Then the things will be easy for you on your WordPress site.

What is a Database and How WordPress uses it?

A database can be simply defined as a system of storing and fetching data in an organized way. It allows the software to manage data in a programmable way. Like the WordPress store and retrieve data from the database by using its programming language of PHP.

The information includes in WordPress are posts, pages, comments, categories, tags custom fields, and other WordPress settings. You will remember that when you installing WordPress firstly, the installation process asks you to give a database name, host, username, and password. That information is stored in the configuration file and it refers to wp-config.php.

WordPress uses the information provided by you to create tables and store default installation data inside those tables. After completing the installing process, WordPress runs queries to this database to create HTML pages for your website or blog dynamically. You do not need to create a new .html file for each web page you created.

Where is my WordPress database stored?

MySQL is the database management system of WordPress. MySQL is software installed on the WordPress hosting server and the WordPress database is also stored on the same server. The problem is that this location is not accessible to many shared hosting environments. You can locate the database if you are on a VPS hosting plan only. For that, you can use command-line tools. In most cases, you have to locate the database through the following path:

/var/lib/MySQL/your_database_name

Make sure that this path will vary from one hosting provider to another. Then you will not require to access the database file and you can use a tool such as phpMyAdmin and manage the database.

What is phpMyAdmin?

The PhpMyAdmin is a web-based software. You can manage MySQL databases through the web browser by using this PhpMyAdmin. It provides you a user-friendly interface that can run MySQL commands and database operations simply. You can use phpMyAdmin to browse and edit database tables, rows, fields, import, export, and delete all data inside the database.

How to access PhpMyAdmin?

Some of the top WordPress hosting companies installed PhpMyAdmin on WordPress and users receive it free of charge. It is in your hosting account’s cPanel dashboard under the Database section. Below shows you the Bluehost control panel for an example.

This interface will differ based on your hosting provider. Then you have to click on the PhpMyAdmin under the database panel and you can select the WordPress database from the left column. Then phpMyAdmin will display the WordPress database.

Understanding WordPress database tables

WordPress has 12 default tables in the database for different sections, features, and functionality of WordPress. You can refer to the structure of these tables and identify where different parts of the website are stored. Below are the default tables in the WordPress installation.

Wp_commentmeta: Wp_commentmeta table includes Meta information about comments posted on the WordPress. It has four fields such as meta_id, comment_id, meta_key, and meta_value. All meta_id is linked to a comment_id. For example, the status of comments such as approved, pending, and trash are the comment Meta information.
Wp_comments: wp_comments table includes comments of the WordPress such as author name, URL, email, comments and etc.
Wp_links: These wp_links can use to manage blogrolls create by previous versions of WordPress or the Link Manager Plugin.
Wp_options: wp_options table includes WordPress site-wide settings such as site URL, admin email, default category, posts per page, time format and etc. This can be used to store plugin settings by many WordPress plugins.
Wp_postmeta: Wp_postmeta table includes Meta information such as WordPress posts, pages, and custom post types. Templates to use display a page and custom fields are examples for post Meta information. Also, some plugins use the wp_postmeta table to store plugin data such as WordPress SEO information.
Wp_posts: Wp_posts table includes all post types or content types such as posts, pages, revisions, and custom post types.
Wp_termmata: The developers can store custom metadata for terms under the custom taxonomies. For instance, WooCommerce uses wp_termmata to store metadata for product attributes, and categories.
Wp_terms: WordPress users can organize the content by a powerful taxonomy system. Individual taxonomy items are referred to as terms that are stored in the wp_ terms table. For instance, WordPress categories and tags are taxonomies and each category or tag inside them is a term.
Wp_term_relationships: Wp_term_relationships table manage relationship of WordPress post types with terms in wp_terms table. For instance, this supports WordPress to determine post X is in the Y category.
Wp_term_taxonomy: Wp_term_taxonomy table define in wp_terms table. For instance, in the term WordPress Tutorials, this table includes the data which says it is associated with a taxonomy called category.
Wp_usermeta: Wp_usermeta table includes Meta information about registered users on the WordPress website.
Wp_users: wp_users include user information such as username, password, and user email.

Managing WordPress database using phpMyAdmin

The WordPress database includes vital WordPress settings, blog posts, pages, comments and etc. As a WordPress user, you have to care about using phpMyAdmin. Otherwise, your important data will be lost. To avoid the risk of data loss, you should create a complete database backup frequently. Then you can restore the database if something happens to your data.

Creating a WordPress database backup using phpMyAdmin

You can simply create a database backup to save your data by following the below steps finely.

Click on the WordPress database.
Click on the Export tab which is on the top menu.

Use the custom method and select zip as the compression method.

Here in the latest version of phpMyAdmin, it asks you for an export method. The custom method will provide you more options and the ability to download backup in compressed zip or gzip archive. This method allows you to exclude tables from the database.

Then you can import back your backup database into a different or the same database using PHP.MyAdmin import tab.

Creating a WordPress backup using a plugin

One of the best WordPress security is to maintain regular database backups. If the WordPress database includes the bulk of site information, WordPress still lacks some fairly important features such as template files, images, and uploads.

All images have stored in the upload folder in the /wp-content/directory. Hence, if the database has the information in which an image is attached to a post, it is essential to have those files in the image folder as well. The important fact is that you have to back up the full site including themes, plugins, and images, and not only the database. So as WordPress users, you should keep backups by yourself, even your web hosting company maintains a daily backup of them. WP Engine web hosting company keeps backup daily and you can also use a WordPress backup plugin to set up automated WordPress backup on the site.

Importing a WordPress database backup via phpMyAdmin

You can import the WordPress database through phpMyAdmin. You have to simply follow the below steps to do it.

Launch phpMyAdmin.
Select the WordPress database.

Click on the Choose file button.
Select the database backup file. This has downloaded earlier.
Then phpMyAdmin will process the backup file upload. And import it into the WordPress database.

Optimizing WordPress database in PhpMyAdmin

You will experience that the database becomes fragmented after using WordPress. Then you can go for memory overheads. It will increase database size and query execution time. MySQL has a command to optimize the database. You have to perform the below steps to do it.

Click on the WordPress database. This will appear in a list of WordPress tables.
Click on the Check All link below the tables.
Then click on the With Selected drop-down list and choose the Optimize table.

This will be defragmenting the selected tables. So, WordPress queries will run faster and reduce the size of the database.

Fixing WordPress issues using PhpMyAdmin

PhpMyAdmin is the best tool for troubleshooting and fixing common WordPress errors and issues. This article brings you several common WordPress issues. Hence, you can use PhpMyAdmin to resolve them.

Reset WordPress password using PhpMyAdmin

You can use PhpMyAdmin to reset the WordPress password quickly in situations such as you forgot the admin password and it cannot recover via lost password email. For that, you have to follow the below steps finely.

Launch the PhpMyAdmin.
Select the WordPress database.
Browse the wp_users table.

Click on the Edit button which is next to the username. It will appear as a form with all user information fields.

Delete the value in the user_pass field and replace it with the new password.
Select the MD5 from the drop-down menu which is under the function column.
Click on the Go button.

The new password will be encrypted using the MD5 hash and stored in the database. Because WordPress recognizes MD5 to provide backward compatibility. When the user logs in using the password string stored as an MD5 hash, WordPress identifies it and changes using the newer encryption algorithms.

2.Adding a new Admin user to WordPress using PhpMyAdmin.

You can change the admin user password in a situation like you have access to the WordPress database. But you have not access to the WordPress admin area. This will avoid the other admin user from using their accounts. Then the simple way to add a new admin user is to use the PhpMyAdmin. For that, you have to perform the below steps.

Launch the PhpMyAdmin.
Select the WordPress database.
Click on the Browse link which is next to the wp_users table.

Click on the Insert link from the menu which is in the wp_users table.

Fill the form that appeared and it will add a new user to the WordPress website.

You have to fill each field in the form below.

ID – This is created automatically and you can leave it.
User_login – This is the WordPress username.
User_pass – This is the WordPress password. You have to enter this password and select MD5 in the function column.
User_nicename – This is the URL-friendly username that you can use as your login.
User_email – You should give a valid e-mail address and it will send password reset and WordPress notification emails.
User_url – It can enter the website URL or you can leave it blank.
User_registered – You can insert time automatically here by selecting the CURRENT_TIME in the function column.
User_activation_key – This is used to approve user registration and you can keep this field blank.
User_status – You can keep this field blank as well.
Display_name – You can enter the user’s full name and that name will display on the articles or you can keep this field blank.
Click on the Go button. The form will insert into the WordPress database. PhpMyAdmin will now run the MySQL query to insert the data.
However, the added user does not have an administrator user role on the WordPress site. The value is saved in the able called wp_usermeta.
Firstly, it has to find the user ID to make the added user as an administrator.
Click on the Broese link next to the wp_users table. It will appear in a row containing the newly added user with the user ID.

Note down the user ID.
Open the wp_usermeta table by clicking on the Browse link next to it.

Click on the Insert link and add a new row to the table.

It will appear a form to enter a new raw. This is the place in which you are telling WordPress that the added user has the administrator user role.

You have to fill this form as per the below instructions.

Umeta_id – This field is filled automatically and you can keep blank it.
User_id – Enter the user ID.
Meta_key – Enter wp_capabilities in this field. Then you have to replace wp_ if WordPress table names use a different prefix.
Meta_value – Enter below serialized value.
- a : 1 : {s : 13 : “administrator” ; s : 1 : “1”;}
Click on the Go button and save changes.
Add another row to define the user level.
Click on the Insert link on the top menu and add another row to the user meta table.

You should fill the form below.

Umeta_id – This field is filled automatically and you can keep blank it.
User_id – Enter the user ID for the newly added user.
Meta_key – You have to enter wp_user_level in the field. However, you need to replacewp_ if the WordPress table names use a different prefix.
Meta_value – You need to enter 10 as the user level value.
Click on the Go button and insert the data.

3. Change a WordPress username via PhpMyAdmin

You may have experienced that WordPress allows you to change the user’s full name or nickname, but it does not allow you to change the username. However, you can do it via PhpMyAdmin as below.

Launch the PhpMyAdmin from the hosting account’s cPanel dashboard.
Select the WordPress database. Then PhpMyAdmin will show WordPress database tables.
Click on the Browse link which is next to the wp_users table. Then the table will list all registered users on the website.

Click on the Edit link which is next to the username that you want to change.

PhpMyAdmin will appear in the user data.
Locate the user_login field.
Change the value to the username which you want to use.

Click on the Go button and save the changes.

Other useful PhpMyAdmin tricks

The engine behind the website is the WordPress database. You can tweak settings to improve performances, fix issues, or simply change things that cannot be changed from inside the WordPress admin area via PhpMyAdmin.

Deactivate all WordPress plugins.
Change the WordPress theme via PhpMyAdmin.
Duplicate WordPress database via PhpMyAdmin.
Find and replace text in the WordPress database.

Securing WordPress database

Every WordPress website can be hacked. But as WordPress users, you can apply some simple tips to make it difficult for hackers. You can do it by performing the below tips.

You can change the WordPress database prefix. It supports to reduces the opportunity of SQL injection attacks on the WordPress database significantly. As most of the hacker’s target sites in masses where they are targeting the default wp_ table prefix.
You can use a strong username and password for MySQL users. Because it will make it difficult for hackers to obtain access to the WordPress database. It can recommend Sucuri as the best WordPress security plugin for WordPress. It catches any attempts at MySQL injection before it reaches the website.

This article may help you to identify how things work behind WordPress. So this will be an important guide for all WordPress users. This is comprehensive guidance on WordPress database management. But this article has made a try to explains all points in a simple way for all users. Further, you can learn how things work behind WordPress by referring to the guide on how WordPress actually works and how WordPress plugins work.