Beginner’s Guide to WordPress Database Management with phpMyAdmin

Last updated on September 8th, 2014 by Editorial Staff

 

WordPress is written using PHP as its scripting language and MySQL as its database management system. In order to use WordPress, you don’t really need to learn either of them. However, a very basic understanding of the two could help you troubleshoot problems as well as increase your understanding of how WordPress work. In this article, we will explain how WordPress uses the database including a primer on default WordPress database tables. We will also show you how to manage a WordPress database using phpMyAdmin which will include creating database backups, optimizing the WordPress database, and much more.

Video Tutorial

 

If you don’t like the video or need more instructions, then continue reading.

How WordPress uses Database

WordPress uses PHP (programming language) to store and retrieve data from the database. The information stored in a WordPress database include: posts, pages, comments, categories, tags, custom fields, users, and other site options such as site urls etc. We will elaborate on this later in the tutorial when we cover all WordPress database tables.

When you first install WordPress, it asks you to provide your Database name, host, username, and password. This information is stored in the configuration file (wp-config.php).

During the installation, WordPress uses the information you provide about the database to create tables and store default installation data inside those tables. After the installation, WordPress runs queries to this database to dynamically generate HTML pages for your website or blog. This is what makes WordPress extremely powerful because you don’t have to create a new .html file for each page that you want to create. WordPress handles everything dynamically.

Understanding WordPress Database Tables

Each WordPress installation has 11 default tables in the database. Each database table contains data for different sections, features, and functionality of WordPress. Looking at the structure of these tables, you can easily understand where different parts of your website are stored. Currently, a default WordPress installation creates the following tables:

Note: wp_ before each table name is the database prefix you choose during the installation. It can be different.

wp_commentmeta : This table contains meta information about comments posted on a WordPress website. This table has four fields meta_id, comment_id, meta_key, and meta_value. Each meta_id is related to a comment_id. One example of comment meta information stored is the status of comment (approved, pending, trash, etc).

wp_comments : As the name suggests this table contains your WordPress comments. It contains comment author name, url, email, comment, etc.

wp_links : To manage blogrolls create by earlier versions of WordPress or the Link Manager plugin.

wp_options : This table contains most of your WordPress site wide settings such as: site url, admin email, default category, posts per page, time format, and much much more. The options table is also used by numerous WordPress plugins to store plugin settings.

wp_postmeta : This table contains meta information about your WordPress posts, pages, and custom post types. Example of post meta information would be which template to use to display a page, custom fields, etc. Some plugins would also use this table to store plugin data such as WordPress SEO information.

wp_posts : The name says posts but actually this table contains all post types or should we say content types. This table contains all your posts, pages, revisions, and custom post types.

wp_terms : WordPress has a powerful taxonomy system that allows you to organize your content. Individual taxonomy items are called terms and they are stored in this table. Example, your WordPress categories and tags are taxonomies, and each category and tag inside them is a term.

wp_term_relationships : This table manages relationship of WordPress post types with terms in wp_terms table. For example this is the table that helps WordPress determine post X is in Y category.

wp_term_taxonomy : This table defines taxonomies for terms defined in wp_terms table. For example if you have a term “WordPress Tutorials“, then this table contains the data that says it is associated with a taxonomy categories. In short this table has the data that helps WordPress differentiate between which term is a category, which is a tag, etc.

wp_usermeta : Contains meta information about Users on your website.

wp_users : Contains User information like username, password, user email, etc.

Managing WordPress Database using phpMyAdmin

phpMyAdmin is an open source software that provides a web based graphical user interface to manage your mySQL database. Most WordPress hosting providers have phpMyAdmin installed in their control panel. This allows users to easily access the database and perform common database management tasks.

All of our recommended web hosting providers use cPanel. To access phpMyAdmin in cPanel, scroll down to Databases and click on phpMyAdmin. This will open phpMyAdmin in a new browser tab.

Clicking on Databases will show you a list of Databases you have created or have access to. Click on your WordPress Database, and it will show you the list of your WordPress Database tables.

You can perform various tasks from phpMyAdmin such as find/replace a certain word in your post, repair your database, optimize your database, add new admin users, deactivate all plugins, change passwords etc etc etc.

Note: Before you change anything, you must make a database backup. There is no undo button. So just make the backup.

Creating a WordPress Database Backup using phpMyAdmin

To create a backup of your WordPress database from phpMyAdmin, click on your WordPress Database. On the top menu, click on the Export tab.

In newer versions of phpMyAdmin, it will ask you for a export method. The quick method will export your database in a .sql file. In custom method it will provide you with more options and ability to download backup in compressed zip or gzip archive. We recommend using custom method and choosing zip as the compression method. The custom method also allows you to exclude tables from the database. Lets say if you used a plugin that created a table inside your WordPress database, then you can choose to exclude that table from the backup if you want.

Your exported database file can be imported back into a different or the same database using phpMyAdmin’s import tab.

Creating a WordPress Backup using a Plugin

Keeping regular backups of your WordPress site is the best thing you can do for your WordPress security. While the WordPress database contains majority of your site information, it still lacks a fairly important element, images. All your images are stored in the uploads folder in your /wp-content/ directory. Even though the database has the information which image is attached where in the post, it is useless if the image folder doesn’t have those files.

Often beginners think that the database backup is all what they need. It is NOT true. You need to have a full site backup that includes your themes, plugins, and images. NO, most hosting company do not keep daily backups.

Note: If you are on a managed hosting solution like WPEngine, then you don’t have to worry. They take care of daily backups.

For majority of us who are not on WPEngine, you should use one of the many solutions available to keep regular backups of your site.

We are using VaultPress, a paid monthly service, to keep regular backups of our site. For those of you who love plugins and don’t like paying recurring fees, then we recommend that you check out BackupBuddy which is hands down the most complete backup solution for WordPress.

Optimizing your WordPress Database in phpMyAdmin

After using WordPress for a while your database becomes fragmented. There are memory overheads which increases your overall database size and query execution time. For those of us who remember in the old PC days, you would notice that your computer would get faster once you use Disk Defragmenter. The MySQL database works in a similar way. It comes with a simple command that allows you to optimize your database. Go to phpMyAdmin and click on your WordPress Database. This will show you a list of your WordPress tables. Click on Check All link below the tables. Next to it there is a “With Selected” drop down, click on it and choose Optimize table.

This will optimize your WordPress tables by defragmenting tables. It will make your WordPress queries run a little faster and slightly reduce the size of your database.

Other Things You can do Using phpMyAdmin

There are many things you can change in your WordPress website by changing values in Database using phpMyAdmin. However, it is highly recommended that you don’t ever do it unless it is absolutely necessary or if you know what you are doing. Make sure that you first back up your database before making any changes to your database. Below are some of the things you can do with your database:

• Reset WordPress Password from phpMyAdmin
• Add an Admin User to WordPress Database via mySQL
• Change WordPress Username via phpMyAdmin
• How to Deactivate all WordPress Plugin from phpMyAdmin

Securing your WordPress Database

Before we get into this, we want to emphasize that every site can be hacked. However, there are certain measures you can take to make it a little harder. First thing that you can do to is to change WordPress database prefix. This can significantly reduce the chances of SQL injection attacks on your WordPress database because often hackers target sites in masses where they are targeting the default wp_ table prefix.

You should always choose a strong username and password for your MySQL. This will make it difficult for someone to get access to your database.

For WordPress security, we strongly recommend that you use Sucuri. Here are 5 reasons why we use Sucuri.

We hope that this guide helped improve your understanding of the WordPress database and how it works. While you may not need this guide right away, it always help to know how things work for the future.

 

[출처] http://www.wpbeginner.com/beginners-guide/beginners-guide-to-wordpress-database-management-with-phpmyadmin/