How to Get the Most from Database Management

Did you know that you can add or remove users from a place other than your WordPress dashboard? Did you know that you can disable themes and plugins or fix errors outside of your admin dashboard?

Managing your site from the dashboard is simple and intuitive; it’s one of the reasons behind WordPress’s huge expansion. However, you can accomplish a lot of tasks by accessing your site’s database, usually via phpMyAdmin.

This article is for you if you want to learn more about WordPress databases and how to get the most from them. Read it carefully to learn some tricks that could help you in stressful situations.

What is a WordPress Database?

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.

A default WordPress database contains 12 tables:

1. wp_commentmeta stores data related to the comments and commenters
2. wp_comments stores the comments
3. wp_links stores data related to links (a deprecated feature useful for creating blogrolls)
4. wp_options stores what you have done in the Settings panel (Writing, Reading, Discussion, Privacy, etc.)
5. wp_postmeta stores metadata for your posts
6. wp_posts stores your posts, pages, and navigation menu
7. wp_terms stores the categories and tags
8. wp_termmeta stores metadata for each term
9. wp_term_relationships stores the associations from wp_terms (associations of posts with categories and tags)
10. wp_term_taxonomy stores the taxonomy (link, category, or tag) from wp_terms
11. wp_usermeta stores metadata for each user
12. 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. We are recommendation is to make a complete backup of your database before working with it.

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:

define(‘DB_NAME’, ‘database_name’);

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.

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 a 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:a:0:{}

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.

In Conclusion

Mastering WordPress databases aren’t for beginners, but it’s certainly not an exclusive right of developers. Even a non-coder can learn how to manipulate WordPress databases. These were only a few tricks to entice you give it a try. Altogether, you can do way more things with your site’s database. Just remember to back up your site.