Two resources will be of use as reference here. The documentation for MySQL Community Server and the WordPress Codex. This post is merely a quick introduction to the basic MySQL command line interface and to the table structure of a WordPress install.

There are several ways to connect to a MySQL database. End users and most web developers will not need to worry about individual SQL commands because this is handled by the web application. When you need to gather information from a database that is not provided for in a standard feature (of say WordPress), you'll need to write out and test a custom SQL query. The best way to test this is with the command line interface of MySQL.

Most WordPress servers will have an MySQL install that will restrict the authorized access point for the backend database to itself. In other words, for security sake, you shouldn't be able to use your local desktop machine to connect to our WordPress database running under MySQL on the server machine. You'll need shell access to the server machine.

Once you have shell access on the server machine, you'll want to enter a command at the prompt like this:

-sh-3.2$ mysql -u username -p

This says connect me to the default mysql server with such and such username and ask me for my password. The username in this case is the MySQL username and the pass is the password associated with that MySQL username. (Yet another set of usernames and passes to keep track of) The information in question is actually stored in a file in plain text on the server. This is standard in WordPress.

Open the file wp-config.php in the web directory.

The values you are looking for are at the top of the file under 'DB_USER' and 'DB_PASSWORD'. Is this a security hole? Nope. The file wp-config.php should be set so only the webserver account can read or write to it. Files of this type in php are never just sent off to the world for plain text viewing, they are interpreted. So if a web visitor wanted to look at it by typing in the url they would get a blank page. AND then they would have to be on the local machine to connect to the database, so that is two sets of passwords they would have to obtain.

Great. Now you should have everything you need to log into the MySQL system. You'll be greeted with something that looks like:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 554068 Server version: 5.x.xx Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

MySQL command line is like the shell prompt. Except that MySQL is mostly case-INsensitive. Most documentation does put the command words in UPPERCASE for clarity, but it does not matter. Also, you must end each line with a semi-colon, otherwise MySQL assumes that you want to enter a multi-line command. Here is a quick example to get the time/date:

mysql> SELECT now(); +---------------------+ | now() | +---------------------+ | 2011-07-27 13:07:59 | +---------------------+ 1 row in set (0.00 sec)

Typing in "select now();" asks the MySQL engine for the time and date. Good.

MySQL may be running more databases than the WordPress install. We need to tell MySQL that we want to work with the WordPress database. Go back to the wp-config.php file and look up the 'DB_NAME' variable. This may in fact be the same value as the MySQL username, but is not necessarily so. (You might also try the SHOW DATABASES command)

The command we're after is:

mysql> USE DB_NAME;

We get:

Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>

Now that we are using the WordPress database, MySQL will understand that all of the commands will be directed to it, rather than one of the other databases that MySQL operates. A few more commands to see the structure:

mysql> SHOW TABLES; +-----------------------+ | Tables_in_btop_wp | +-----------------------+ | wp_commentmeta | | wp_comments | | wp_links | | wp_options | | wp_postmeta | | wp_posts | | wp_term_relationships | | wp_term_taxonomy | | wp_terms | | wp_usermeta | | wp_users | +-----------------------+ 11 rows in set (0.00 sec) mysql> DESCRIBE wp_posts; +-----------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------------------+----------------+ | ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | post_author | bigint(20) unsigned | NO | MUL | 0 | | | post_date | datetime | NO | | 0000-00-00 00:00:00 | | | post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | | | post_content | longtext | NO | | NULL | | | post_title | text | NO | | NULL | | | post_excerpt | text | NO | | NULL | | | post_status | varchar(20) | NO | | publish | | | comment_status | varchar(20) | NO | | open | | | ping_status | varchar(20) | NO | | open | | | post_password | varchar(20) | NO | | | | | post_name | varchar(200) | NO | MUL | | | | to_ping | text | NO | | NULL | | | pinged | text | NO | | NULL | | | post_modified | datetime | NO | | 0000-00-00 00:00:00 | | | post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | | | post_content_filtered | text | NO | | NULL | | | post_parent | bigint(20) unsigned | NO | MUL | 0 | | | guid | varchar(255) | NO | | | | | menu_order | int(11) | NO | | 0 | | | post_type | varchar(20) | NO | MUL | post | | | post_mime_type | varchar(100) | NO | | | | | comment_count | bigint(20) | NO | | 0 | | +-----------------------+---------------------+------+-----+---------------------+----------------+ 23 rows in set (0.00 sec)

And now what do you do with that information? Well, suppose we wanted a list of all the pages on a WordPress site with their ID, title and date they were created with the most recent additions at the top?

mysql> SELECT ID, post_title, post_type, post_date FROM wp_posts WHERE post_type = "page" AND post_title <> "Auto Draft" ORDER BY post_date DESC;

A few quick things about WordPress data structure:

  • pages and posts on the website are both "wp_post" records in the database
  • there is a post_type called "revision", this lets people go back to previously saved versions of their content.
  • In a MULTISITE WordPress install, there will be the above basic data structure, with parts repeated, each being tied to their site by the ID number in the table name.
  • Some plugins and themes will add additional tables within the mysql database
  • key table purposes
    • wp_posts: pages and posts
    • wp_users: author info, access level, email, webiste
    • wp_terms: Names of Categories
    • wp_postmeta: Custom field storage
    • wp_term_relationships: helps you get lists of categories and posts associated with them
    • wp_options: web site preferences
  • More detail about this available from the Codex at https://codex.wordpress.org/Database_Description