Managing Your MySQL Database

Many new WordPress users are intimidated by the MySQL database, perhaps because it seems to be way above their technical skills or abilities. Truth be told, regular users of WordPress — those who just use it to publish content — don't really ever have to dig into the database unless they want to. You only need to explore the database if you're dealing with theme or plugin development, or with contributing code to the WordPress project. In this section, we give you a basic overview of the WordPress database stored in MySQL so that you have an understanding of the structure and know where items are stored.

After WordPress is installed on your server (which I discuss in Chapter 4 of this minibook), the database gets populated with 11 tables that exist to store different types of data from your WordPress blog. Figure 3-2 displays the structure of the tables, as follows:

• wp_commentmeta: This table stores every comment published to your site contains information, or metadata, that includes
  • • A unique comment ID number
  • • A comment meta key, meta value, and meta ID (unique numerical identifiers assigned to each comment left by you, or visitors, on your site)
• wp_comments: This table stores the body of the comments published to your site, including
  • • A post ID that specifies which post the comment belongs to
  • • The comment content
  • • The comment author's name, URL, IP address, and e-mail address
  • • The comment date (day, month, year, and time)
  • • The comment status (approved, unapproved, or spam)
• wp_links: This stores the name, URL, and description of all Links you create by using the WordPress Link Manager. It also stores all the advanced options for the links you created, if any.
• wp_options: This stores all the option settings that you set for WordPress after you install it, including all theme and plugin option settings.
• wp_postmeta: This includes all posts or pages published to your site and contains metadata that includes
  • • The unique post ID number. (Each blog post has a unique ID number to set it apart from the others.)
  • • The post meta key, meta value (unique numerical identifiers for each post created on your site), and any custom fields you've created for the post.
• wp_posts: This table features the body of any post or page you've published to your blog, including autosaved revisions and post option settings, such as
  • • The post author, date, and time
  • • The post title, content, and excerpt
  • • The post status (published, draft, or private)
  • • The post comment status (open or closed)
  • • The post type (page, post, or custom post type)
  • • The post comment count
• wp_terms: This stores the categories you've created for posts and links as well as tags that have been created for your posts.

  Figure 3-2: The WordPress database structure.

  

• wp_term_relationships: This stores the relationships among the posts as well as the categories and tags that have been assigned to them.
• wp_term_taxonomies: WordPress has three types of taxonomies by default: category, link, and tag. This table stores the taxonomy associated for the terms stored in the wp_terms table.
• wp_usermeta: This table features metadata from every user with an account on your WordPress Web site. This metadata includes
  • • A unique user ID
  • • A user meta key, meta value, and meta ID, which are unique identifiers for users on your site
• wp_users: The list of users with an account on your WordPress Web site is maintained within this table and includes
  • • The username, first name, last name, and nickname
  • • The user login
  • • The user password
  • • The user e-mail
  • • The registration date
  • • The user status and role (subscriber, contributor, author, editor, or administrator)

Most Web hosting providers give you a utility, or an interface, to view your MySQL database, and the most common one is phpMyAdmin (as shown in Figure 3-2). If you're unsure how you can view your database on your hosting account, get in touch with your hosting provider to find out.

When the Multisite feature in WordPress is activated (check out Book VIII for information about the Multisite feature), WordPress adds six additional tables in the database:

• wp_blogs: This table stores information about each blog created in your network, including
  • • A unique blog numerical ID
  • • A unique site ID number (determines the ID of the site the blog belongs to)
  • • The blog domain
  • • The blog server path
  • • The date the blog was registered
  • • The date the blog was updated
  • • The blog status (public, private, archived, spam; see Book VIII for more information on blog status)
• wp_blog_versions: This table stores general information about each network blog ID, database version, and date of last update.
• wp_registration_log: This table stores information about registered users, including
  • • Unique user numerical ID
  • • User e-mail address
  • • Users IP address
  • • Users Blog ID
  • • The date the user registered
• wp_signups: This table stores information about user sign-ups that includes all the information from the wp_registration_log table, the date the user account was activated, and the unique activation key the user accessed during the sign-up process.
• wp_site: This table stores information about your main installation site including the site ID, domain, and server path.
• wp_sitemeta: This table stores all the information about the Multisite configurations set after you install the Multisite feature. See Book VIII.