August 2, 2019
WordPress database is where all of the necessary website data is stored. Not just the basic information like usernames and passwords but posts, pages and comments, even the website theme and WordPress configuration settings. Today we will take a look at why and how you should manage the WordPress database. Buckle up and enjoy the ride.
Let’s look at what is a database first. As briefly stated earlier, a database can be a life-saving option for us to store and retrieve valuable data.
A database is a structured set of organized data. In the IT field, database management systems (DBMS) refer to software that interacts with a user, data, and retrieve and store data. It is pretty much the same when you save pictures or videos in your computer drive, or the business card binder if you prefer old-school card management. Having a personal database to access that data is very helpful.
There are several ways to use and access the database. WordPress uses MySQL for its database management system. MySQL is open-source software, and it is responsible for managing the components of a WordPress database such as user data, user meta, posts, comments, and so on. We will discuss it a little bit later.
WordPress uses PHP, using SQL query within the PHP markup, as the language of instruction to CRUD (Create, Read, Update, and Delete) data from the MySQL database. The database is an essential part of WordPress. It is the backbone that stores all core information. The database also ensures that your page runs smoothly, fully optimized, and helps you back-up data regularly.
The next question, where is the database stored?
Your WordPress database is arranged by a database host, which then puts it on the MySQL server. Think about the database host as an actual house that you can go to with a specific address (hostname). You can retrieve something from the room section (database name). Of course, you will need permission to that, that’s why you need to enter the username and password before entering the house.
For the most part, you can use localhost as hostname to manage and connect your WordPress with your database. In Hostinger, for example, you can find database host detail under the Database section from the control panel:
In the database, your data is stored in the form of tables. Each table consists of unique data and is displayed in rows. And the rows also contain other pieces of information or parameters. By default, WordPress will automatically create those tables when you first set up the website. The WordPress 4.4 version will make the following core database tables:
By default, you can’t remove the WordPress core tables. But it will add to your content. To explore more about the tables, we suggest you read it here.
At Hostinger we always think about our users in the first place. We thought you might wanna know about the best web hosting offers in the market.
If you’re about to start something new, grab our special discount coupon and go online right now!
SQL is short for Structured Query Language; it is a programming language used to manage databases. An SQL request issued to CRUD data in the database server is called a query. WordPress uses MySQL queries to store and retrieve the data and generate it into web pages.
A query can become handy especially if you want to optimize your web page. For example, to remove spam comments that bloat your database and slow down your web page. A query can remove it pretty quickly, run the following command to do that:
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';
You can also perform a query if you want to delete an unwanted post, specifically the older ones. To do that, run the following query:
DELETE * FROM `wp_posts`
WHERE `post_type` = 'post'
AND DATEDIFF(NOW(), `post_date`) > 30
You can change the number “30” to any post dates you wish to remove. In this example, it will remove post older than 30 days.
You can always manually remove spam and older post from the WordPress interface, but the query makes the power literally at your fingertips.
Pro Tip: Remember to do a backup before executing any changes in your database!
We covered a lot of information regarding database management. Now, let’s put it in good use.
Manage your WordPress database, or rather MySQL server, with phpMyAdmin. It is an open-source web-based application with an easy-to-use user interface. Hostinger has phpMyAdmin in your control panel. You can access it under the Database section:
Or better yet, we’ve already created a unique direct link for you to access phpMyAdmin. Just remember to fill the username and password of your MySQL server. You can find it here:
After you click either the direct link or phpMyAdmin button, it will direct you to phpMyAdmin interface:
As you can see, those are the core database created by WordPress. I haven’t made significant changes in my web page, so there are no additional tables created. You have to remember though, every time you make changes in WordPress, those tables can add and pile up. That’s why you should manage it well.
Let’s perform basic management by creating a backup. Click the Export tab and choose the appropriate option.
That instruction will download the SQL files to your computer.
You can also use plugins to manage your WordPress database. See a list of the best WordPress database management plugins here.
Databases and WordPress are like two sides of the same coin; they work side by side to ensure that your web page works as good as it can, and all of its data well-managed.
Let’s wrap up. We have covered the benefits of having a database to store and retrieve data. We also learn how to use the query to ease our work in managing database, and lastly, we know how to perform basic WordPress database management with phpMyAdmin.