WordPress Database Management Basics
Scripting of WordPress is by using PHP and its database management system is MySQL. While you may not need to have a working knowledge of either to run a WordPress site, understanding basic concepts for both systems can help you to troubleshoot simple issues and be able to do more with your site for yourself.
The aim of our article is to provide basic information into the workings of WordPress, with particular attention devoted to the MySQL database management system. Now to begin!
Database management in WordPress – overview
PHP is a programming language used by WordPress for storage and retrieval of data to and from the database. All information about your WordPress website/blog is stored within a database – everything from tags, custom fields and comments to posts, categories, users and even full pages.
The first time you install WordPress, you will get a prompt to provide, in addition to your username and password, a database name and host. Saving of this information will be to a configuration file named (wp-config.php). At the point of installation, WordPress will then apply the data that you provide to build a database, creating tables and saving your default installation settings data into them.
Once the installation is complete, WordPress can run queries into the database in order to generate what you see as HTML pages for your blog, webpages or website. The fact that you do not have to create a fresh .html file when you want a new page makes WordPress so superior. WordPress handles every aspect dynamically.
Demystifying databasedefault tables
At the point of installation, there will be 11 tables within the database, created by default. Every database table represents a different section, feature or functionality within WordPress. By understanding the structure of each table, you will know where each separate section of your database is stored in WordPress.
The prefix wp_ is default and comes before each table name. Selection of this prefix is at the point of installation, and can therefore be different. The following are the default tables created in WordPress:
1. wp_commentmeta – this table stores all information concerning comments posted to a WordPress website. For instance, it may store information on the status of the comment (trash, folder, pending approved etc.). The table has four fields namely, meta_id, meta_key, comment_idandmeta_value.
2. wp_comments – just as the name suggests, this table stores the actual string of comments posted to your WordPress site. It also stores information on the name of the author, the email address, URL etc.
3. wp_links– this table regulates the blogrolls created by WordPress’s earlier versions or by the Link Manger plugin.
4. wp_options – this table stores majority of your WordPress site-wide custom settings e.g. administrator email, site URL, posts per page, default category, time format among many others. Many of the special plugins on WordPress also use this table to store their custom settings.
5. wp_postmeta – this table stores additional/Meta information about WordPress pages, posts and any personalized post types. For instance, it will have info on the template used in displaying a custom field or page etc. A few plugins also store their plugin settings data here as well.
6. wp_posts – while the table is named posts, this table store information on content or post types. It also contains actual pages, posts, custom post types and any revisions carried out.
7. wp_terms – organization of content in WordPress is through a very powerful system of taxonomy. Every separate taxonomy item is referred to as a term, and all are stored here. Examples of terms include your WordPress tags and/or categories. Classification of each tag inside them is as a term.
8. wp_term_relationships – as the name denotes, this tables controls the relationship between post types in WordPress and the wp_terms table. The table will determine for instance that a certain post X belongs in the Y category.
9. wp_term_taxonomy – again, as per its name, this table defines or assigns taxonomies for terms defined within the wp_terms table. For instance, if there is a term called WordPress tutorials on your table, information defining that this term belongs to the taxonomy unit ‘categories’ is stored in this table.
10. wp_usermeta – this provides meta information about all users in your site
11. wp_users–stores the user information for all site visitors/users e.g. login ID/username, email address, passwords.
phpMyAdmin and database management
The open source software phpMyAdmin provides a web-based graphical interface for easy management of your database on MySQL. In fact, majority of WordPress hosting providers include phpMyAdmin within their control panel. Users can therefore access the database easily and carry out common management tasks.
If you are using the recommended webhosting providers, you will most likely have the cPanel control panel. In cPanel, phpMyAdmin is under Databases. It opens onto a new browser tab.
Accessing databases will also give you a list of all Databases you either created or have permission to access. You can also view the entire list of your WordPress MySQL Database tables.
From phpMyAdmin, you can perform a number of tasks – add admin users, repair or optimize your database, deactivate plugins, find and/or replace text within posts, change passwords among many more.
However, you should always make a database backup prior to making any changes, since changes in phpMyAdmin are not reversible. If you are displeased with the results, you can simply roll back to the previous version. For more information and assistance with these tasks, you can reach remote dba support at http://www.remotedba.com.
How to create a backup from phpMyAdmin
From your phpMyAdmin tab, click on the WordPress Database you want to back up. This will open a menu on the top, and you should choose ‘Export’ from the options listed. If you are using a newer version of phpMyAdmin, you will receive a prompt to select an export method. The quick method exports the database through a .sql file.
For more options, select custom exportation, and you can download your backup file in a compressed gzip or zip folder, plus a few other options. The custom zip option is recommendable, because you can also exclude certain tables from the backup file. For instance, you can exclude the table created by a plugin in your WordPress database.
If you need it, you can then import the file back into the same or a different database through the import tab on phpMyAdmin.