What is a WordPress Database?
Before delving deeper into this matter, let me introduce the concept of a WordPress database. In plain English, a WordPress database is an entity that stores all the data required to make the site functional. It handles the WordPress files to provide the desired output.
MySQL is the open-source software used by WordPress for database management, and it allows users to perform a vast array of operations. Bear in mind that you can’t create a WordPress site without creating a database. You can learn SQL through Intellipaat SQL training to enhance your skill.
A default WordPress database contains 12 tables:
- wp_commentmeta stores data related to the comments and commenters
- wp_comments stores the comments
- wp_links stores data related to links (a deprecated feature useful for creating blogrolls)
- wp_options stores what you have done in the Settings panel (Writing, Reading, Discussion, Privacy, etc.)
- wp_postmeta stores metadata for your posts
- wp_posts stores your posts, pages, and the navigation menu
- wp_terms stores the categories and tags
- wp_termmeta stores metadata for each term
- wp_term_relationships stores the associations from wp_terms (associations of posts with categories and tags)
- wp_term_taxonomy stores the taxonomy (link, category, or tag) from wp_terms
- wp_usermeta stores metadata for each user
- wp_users stores the list of users.
There is no malfunction if your site’s database contains more tables. Your plugins have probably just created new tables.
How to Manipulate a Database
Working with databases isn’t rocket science, but any mistake you make is almost impossible to repair. My recommendation is to make a complete backup of your database before working with it. Don’t skip this step!
But if you’re testing things, whether you’re a beginner who still experiments with settings in WordPress or a developer who tests a new plugin/theme, you will need to reset your WordPress site. In that case, we suggest the free WP Reset plugin that will enable you to clean the database and start over in a matter of a few seconds.
Use the phpMyAdmin tool to access your database; top-quality hosting providers will give you access to this tool, which streamlines the management of MySQL databases. Here is a list of operations you can perform with phpMyAdmin.
1. Back Up a Site’s Database
You must save the WordPress files and the database to have a functional backup copy of your site. It’s worthless to mention how important is to back up your site. You can do it with a plugin or manually. In the latter case, use an FTP client to save all the WordPress files and follow the steps below to save your site’s database.
Open the phpMyAdmin panel, and you will see the Database tab. Select your site’s database and click Check All to select all the files.
Next, click Export in the upper menu, chose SQL as the format, and hit Go. You have just saved your site’s database.
HINT: You may have more databases in the phpMyAdmin tab, and you might not know which is your site’s database. In this case, head to your wp-config.php file and search for this line:
The second parameter is the name of the database you need to back up so you can save the correct database.
2. Fix Errors
“Error establishing a database connection” is a message that terrifies WordPress users. Multiple factors can generate this error, but you can fix it from the phpMyAdmin panel by using a backup copy of your site’s database. The team at 000webhost.com did a splendid job writing a complete guide explaining the ins and outs of the “Error establishing a database connection” message.
3. Optimize and Repair a Database
This is a useful, basic task. Select the database, check all files, and select Optimize/Repair from the drop-down menu. Repair will patch the tables, and Optimize will defragment the tables, increasing the site’s loading speed.
4. Deactivate Plugins
One of the WordPress admin’s worst nightmares is not being able to access the dashboard. Good practice says that deactivating all plugins could resolve your issue. However, if you can’t access the dashboard, you can’t deactivate plugins. In this case, head to your phpMyAdmin tool and check your site’s database.
Locate the wp_options table and click on it.
You will see a lot of entries, including active_plugins. Click Edit, and it will display a new panel, including a text area with the plugins installed.
Delete all of them and add this message:
5. Reset a Password
Resetting your password is mandatory when you are not able to access your dashboard. Also, it’s recommended to change it from time to time. You can do so with the phpMyAdmin tool by selecting your database and editing the wp_users table.
Once you click Browse, it will display the list of users. Click on the one who needs a password reset, go to the user_pass field, and change the Function type to MD5. Replace the old password in the Value area with the new one.
It’s done, you set a new password!
6. Change a Username
You can change a username directly from your site’s database. Check the wp_users table (see above) and click on the username you want to change. All you need to do is to replace the user_login value with the new username.