One of our main recommendations to WordPress users is to learn how to maintain their WordPress database.
WordPress uses PHP as the scripting language and MySQL as the database management system. You don't need to learn any language to use WordPress.
However, having a basic understanding of both can help you troubleshoot problems and increase your knowledge of how WordPress works (behind the scenes) .
In this article, we will explain how WordPress uses the database, including an introduction to WordPress's default database tables. We will also show you how to manage your WordPress database using phpMyAdmin, including creating database backups, optimizing your WordPress database, repairing your WordPress database, and more.
This is a comprehensive guide on WordPress database management, so we’ve added a table of contents for easier navigation:
- What is a Database? How Does WordPress Use It?
- What is phpMyAdmin?
- How to Access phpMyAdmin
- Understanding WordPress Database Tables
- Managing WordPress Database with phpMyAdmin
- Creating a WordPress Database Backup Using phpMyAdmin
- Creating WordPress Backups Using Plugins
- Importing WordPress database backup via phpMyAdmin
- Optimizing your WordPress database in phpMyAdmin
- Fix WordPress Issues Using phpMyAdmin
- Reset WordPress password using phpMyAdmin
- Adding a New Administrator User to WordPress Using phpMyAdmin
- Changing WordPress Username via phpMyAdmin
- Other Useful phpMyAdmin Tips
- Securing Your WordPress Database
What is a Database? How Does WordPress Use It?
A database is a system for organizing and retrieving data. It allows software to manage data in a programmable way.
For example, WordPress uses PHP, a programming language, to store and retrieve database data.
The information stored in the WordPress database includes posts, pages, comments, categories, tags, custom fields, users, and other WordPress settings. We will explain this in detail later in this tutorial when we discuss WordPress database tables.
When you first install WordPress , it will ask you to provide a database name, host, username, and password. This information is stored in a configuration file called wp-config.php.
During the installation process, WordPress uses the information you provide about your database to create tables and store default installation data in those tables.
Once installed, WordPress runs queries against this database to dynamically generate the HTML pages for your website or blog.
This makes WordPress very powerful because you don't have to create a new .html file for every page you want to create. WordPress handles everything dynamically.
Where is my WordPress database stored?
WordPress uses MySQL as its database management system, which is installed on your WordPress hosting server. Your WordPress database is also stored on the same server.
In most shared hosting environments, this location is not accessible. However, if you are using a VPS hosting plan or a dedicated server, you can use command line tools to locate your database.
Typically, it is located at the following path:
/var/lib/mysql/your database name
However, this may vary from one hosting provider to another.
It's important to note that you don't actually need access to the database file itself. You can use other tools such as phpMyAdmin to manage your database.
What is phpMyAdmin?
phpMyAdmin is a web-based software that allows you to manage MySQL databases using a web browser. It provides 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 the data in the database.
How to Access phpMyAdmin
All top WordPress hosting providers come with PhpMyAdmin pre-installed. You can find it in the “Databases” section of your hosting account’s cPanel control panel. Here’s a sample screenshot of the Bluehost control panel:
Depending on your hosting provider, your cPanel interface may look different than the screenshot above. However, you should still find the phpMyAdmin icon under the “Databases” section.
Clicking on it will open the phpMyAdmin interface where you can select your WordPress database in the left column. After that, phpMyAdmin will display all the tables in your WordPress database.
Let's take a look at the WordPress database tables.
Understanding WordPress Database Tables
Every WordPress installation has 12 default tables in its database. Each database table contains data for different sections, features, and functions of WordPress.
By looking at the structure of these tables, you can easily understand where the various parts of your website are stored. Currently, a default WordPress installation creates the following tables:
Note: By default, the prefix wp_is prepended to each table name in the database, but you can change it during installation.
- wp_commentmeta: This table contains meta information about comments posted on a WordPress site. The table contains four fields: meta_id, comment_id, meta_key, and meta_value. Each meta_id is associated with a comment_id. An example of stored comment meta information is the status of the comment (approved, pending, discarded).
- wp_comments: As the name suggests, this table contains your WordPress comments. It contains information such as the comment author’s name, URL, email, comment, etc.
- wp_links: Manages blog links created by earlier versions of WordPress or the Link Manager plugin.
- wp_options: This table contains most of the settings for a WordPress site, such as the site URL, administrator email, default category, number of posts per page, time format, etc. Many WordPress plugins also use the options table to store plugin settings.
- wp_postmeta: This table contains meta information about WordPress posts, pages, and custom post types. Examples of post meta information include the template used to display the page, custom fields, etc. Some plugins also use this table to store plugin data, such as WordPress SEO information.
- wp_posts: As the name suggests, it contains all post types, or should I 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. Each taxonomy term is called a term and is stored in this table. For example, your WordPress categories and tags are a taxonomy, and each category/tag in them is a term.
- wp_term_relationships: This table manages the relationships between WordPress post types and terms in the wp_terms table. For example, this table helps WordPress determine if post X belongs to category Y.
- wp_term_taxonomy: This table defines the taxonomy for the terms defined in the wp_terms table. For example, if you have the term " WordPress tutorials ", this table contains data indicating that it is associated with a taxonomy called "category". In short, this table contains data that helps WordPress distinguish which terms are categories, which terms are tags, and so on.
- wp_usermeta: Contains meta information about registered users on your website.
- wp_users: Contains user information such as username, password, user email, etc.
Managing WordPress Database with phpMyAdmin
Your WordPress database contains important WordPress settings, as well as all your blog posts, pages, comments, etc. Be extremely careful when using phpMyAdmin, otherwise you might accidentally delete important data.
As a precaution, you should always create a full database backup. This will allow you to restore your database to a previous state.
Let’s see 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 your database. Then, in the top menu, click on the Export tab.
In newer versions of phpMyAdmin, it will ask you for the export method. The Quick method will export the database as a .sql file. The Custom method will give you more options and support downloading the backup in a compressed zip or gzip archive format.
We recommend using the custom method and selecting zip as the compression method. The custom method also allows you to exclude tables from the database.
Suppose you used a plugin that created a database table, but you no longer use that plugin. Then, you can choose to exclude that table from the backup.
You can import an exported database file back into a different or the same database using the Import tab of phpMyAdmin.
Creating WordPress Backups Using Plugins
Regularly backing up your WordPress site is the best way to keep your WordPress site safe. Although the WordPress database contains most of the information on your site, it still lacks some fairly important elements, such as template files, images, uploaded content, and more.
All images are stored in the uploads folder under /wp-content/. Even if you have information in the database about the images attached to each article, if you don't have those files in the images folder, then uploading is meaningless.
Newbies often think that database backup is enough. This is not the case. You need a complete website backup, including themes, plugins, and images.
While most hosting companies claim that they have daily backups, we recommend that you keep your own backups just in case .
NOTE: If you use a managed WordPress hosting solution like WP Engine , then they create daily backups.
For most people who don’t use WP Engine, you should use a WordPress backup plugin to set up automatic WordPress backups on your site.
Pro Tip: Do you need to regularly backup your website and database? You can with WPBeginner WordPress maintenance and support services. Our team of experts will set up routine cloud backups for you, manage and optimize your website’s database, and optimize your website’s performance.
Importing WordPress database backup via phpMyAdmin
phpMyAdmin also allows you to easily import your WordPress database. Simply launch phpMyAdmin and select your WordPress database.
Next, you need to click on the ‘Import’ link from the top menu.
On the next screen, click on the Choose File button and select the database backup file you downloaded earlier.
phpMyAdmin will now process your backup file upload and import it into your WordPress database. Once completed, you will see a success message.
Optimizing your WordPress database in phpMyAdmin
After using WordPress for a while, your database will become fragmented. Memory overhead increases the overall size of your database and query execution time.
MySQL comes with a simple command that will help you optimize your database. Simply open phpMyAdmin and click on your WordPress database. This will display a list of your WordPress tables.
Click on the 'Check All' link below the table. There is an 'Include Selected' drop-down menu next to it. You need to click on it and select 'Optimize Table'.
This will optimize your WordPress database by defragmenting the selected tables. This will make your WordPress queries run faster and slightly reduce the size of your database.
Fix 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 take a look at some common WordPress issues that can be easily fixed using phpMyAdmin.
Reset WordPress password using phpMyAdmin
If you have forgotten your WordPress admin password and are unable to recover it via the lost password email, then this method will allow you to quickly reset it.
First, launch phpMyAdmin and select your WordPress database. This will display your WordPress database tables, and you need to click “Browse” next to the wp_users table.
Note: The table prefix for the tables in your WordPress database may be different than the one we show in the screenshots.
Now you will see the row in the WordPress users table. Go ahead and click on the Edit button next to the username for which you want to change the password.
phpMyAdmin will show you a form with all the user information fields.
You 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 the "Go" button.
Your password will be encrypted using MD5 hashing and then stored in the database.
Congratulations! You have successfully changed your WordPress password using phpMyAdmin.
Now, some of you might be wondering why we chose MD5 hashing to encrypt passwords.
In older versions, WordPress used MD5 hashes to encrypt passwords. Starting with WordPress 2.5, it started using stronger encryption. However, WordPress still recognizes MD5 for backward compatibility.
Once you log in using a password string stored in MD5 hashed form, WordPress will recognize it and change it using the newer encryption algorithm.
Adding a New Administrator User to WordPress Using phpMyAdmin
Let's assume you have access to the WordPress database, but not the WordPress admin area. While you can change the admin user password, this will prevent other admin users from using their accounts.
A simpler solution is to add a new admin user through phpMyAdmin.
First, you need to launch phpMyAdmin and then select your WordPress database. This will display your WordPress database tables and you need to click on the “Browse” link next to the wp_users table.
phpMyAdmin will now show you the rows in the wp_users table.
Go ahead and click on the “Insert” link in the top menu.
This will display a form that you need to fill out in order to add a new user to your WordPress site.
Here's how you need to fill out each field in this form.
- ID – You can ignore this as it is automatically generated.
- user_login – This will be the WordPress username you use to log in.
- user_pass – This is your WordPress password. You need to enter your password and select MD5 in the function field.
- user_nicename – This is the URL-friendly username that you can use to log in.
- 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 CURRENT_TIME in the Function column to automatically insert the current time here.
- user_activation_key – You can also leave this field blank, it is used to approve user registration.
- user_status – You can also leave this field empty.
- display_name – You can enter the user’s full name to be displayed in the post. You can also leave it blank.
Once you’ve filled out 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 the user does not have the admin 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 need to find the user ID.
Simply click on the Browse link next to the wp_users table and you will see a row with the newly added user and his user ID.
Make a note of the User ID as 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.
Next, you need to click on the ‘Insert’ link at the top.
This will allow you to add new rows to the table.
Now you will see a form for entering a new row.
Here you will tell WordPress that the user you created earlier has the administrator user role.
Here's how to fill out this form.
- umeta_id – You need to leave this blank as it will be automatically populated.
- user_id – Enter the User ID you copied earlier.
- meta_key – You need to enter “wp_capabilities” in this field. However, if your WordPress table names use a different prefix, you may need to replace wp_.
- meta_value – You need to enter the following serialized value:
a:1:{s:13:”administrator”;s:1:”1″;}
Finally, click on the “Execute” button to save your changes.
Next, we need to add another row to define the user level. Click the "Insert" link in the top menu to add another row to the usermeta table.
Here's how to fill out this form.
- umeta_id – You need to leave this blank as it will be automatically populated.
- user_id – Enter the user ID of the newly added user.
- meta_key – You need to enter “wp_user_level” in this field. However, if your WordPress table names use a different prefix, you may need to replace wp_.
- meta_value – Here, you need to enter “10” as the user level value.
Don't forget to click the "Go" button to insert the data.
That’s it, you can now access the WordPress admin area and log in with the newly added admin user.
Changing 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 their username.
Many users choose a username during installation, but may want to change it later. Here's how to change it through 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 control panel. After that, you need to select your WordPress database.
phpMyAdmin will display your WordPress database tables. You need to click on the 'Browse' link next to the wp_users table.
The table will now list all registered users on your site.
Next, click on the “Edit” link next to the username you want to change.
phpMyAdmin will now show you the user data.
You need to find the user_login field and change its value to the username you want to use.
Don't forget to click on the "Go" button to save your changes. Now you can visit your website and log in with your new username.
Other Useful phpMyAdmin Tips
The WordPress database is the engine behind your website. Using phpMyAdmin, you can tweak settings to improve performance, fix problems, or simply change things that can't be changed in the WordPress admin interface.
Here are some tips on how you can use phpMyAdmin:
- Deactivate all WordPress plugins.
- Change your WordPress theme through phpMyAdmin .
- Clone the WordPress database via phpMyAdmin .
- Find and Replace Text in the WordPress Database
Securing Your WordPress Database
Before we dive in, we want to emphasize that every website can be hacked. However, there are things you can do to make it more difficult.
The first thing you can do is to change your WordPress database prefix. This can significantly reduce the likelihood of your WordPress database being attacked by SQL injections. This is because hackers often use the default wp_ table prefix to attack websites.
For step by step instructions, see our tutorial on how to change the WordPress database prefix .
Additionally, you should always choose a strong username and password for your MySQL user. This will make it difficult for others to access your WordPress database.
To ensure WordPress security, we strongly recommend you to use Sucuri . It is the best WordPress security plugin that can block any MySQL injection attacks before they can reach your website.
We hope this guide helped you learn WordPress database management and how to use phpMyAdmin. You may also want to read our guide on how to repair WordPress database and how to delete custom field values from WordPress database .
If you liked this article, you can also share it on Twitter and Facebook.
Disclosure: Some of the links in this article contain affiliate links, which means we may earn a commission if you click through to visit us, at no extra cost to you. See how SidelinePlay is funded, why it’s important, and how you can support us.
Was this helpful?