Category: Beginner

WordPress is written in PHP and uses MySQL for saving data, and requires a web server like Apache to run. Bluntly put, WordPress uses PHP to send and receive data from the database, where the queries are run by MySQL, whereas the user interface is . Technically, knowledge of neither of these technologies is required for you to run a WordPress blog. That being said, with a little bit of eagerness to understand how they work would definitely help you to optimize your blog.

Let’s begin. You must remember the following form, which you must have filled during the installation of WordPress.

The ‘Database Name’ that you specify here is, of course, the name of the database where all your data would be stored.

The User Name and Password uniquely identify a MySQL user who has all priviledges corresponding to the aforementioned database. By default, their values are ‘root’ and ‘’ (or null) if you installed it in Windows using WAMP, or ‘root’ and whatever password your provided during the installation of MySQL.

The ‘Database Host’ refers to the server where the database is installed. Ideally, if you are running it in your local system, you provide the value ‘localhost’. However, if you are running it in your website, you should provide your domain name here (http://www.example.com).

A ‘Table Prefix’ is added to all tables in WordPress so that they don’t clash with other tables if the same database is used for some other purpose as well.

The open source tool that we will use, which also provides us a GUI (Graphical User Interface) to manage the databases is called PhpMyAdmin. If you are running WordPress on WampServer in Windows, PhpMyAdmin comes pre-installed and running it is as easy as going to http://localhost/phpmyadmin/. By default, you would be logged into PhpMyAdmin as the WampServer involved installation of MySQL with default values.

If you are using a different server, you would have to install PhpMyAdmin first. Go to their downloads page to get a copy of the latest version of PhpMyAdmin. Unzip the downloaded zipped file at a location inside the home folder of Apache (or any other web server that you might be using like Nginx), which is /var/www/ in Linux by default. After unzipping it, go to the relative path of the PhpMyAdmin directory in your web browser. For example, if the files are under /var/www/phpmyadmin/ in your Linux system, go to http://localhost/phpmyadmin/. In this case, however, you would be greeted by a login screen.

Type in your username and password to login. If, in the worst case that you have forgotten your username and passwords, open the wordpress configuration file situated at the WordPress root directory. For example, C:\wamp\www\wordpress\wp-config.php in Windows and /var/www/wordpress/wp-config.php in Linux by default. Search for the following lines:

/** MySQL database username */

define(‘DB_USER’, ‘<username here>’);

/** MySQL database password */

define(‘DB_PASSWORD’, ‘<password here>’);

Once you have successfully logged in, select the wordpress database from the list of databases on the left.

On selection, you will be able to view the list of tables in the database. By default, WordPress creates 11 tables (I am assuming that the table prefix is ‘wp_’). Clicking on any of the tables shows the data in the table in the form of rows.

Here’s a short description of the tables.

• wp_commentmeta: This table contains meta information about comments posted on a WordPress website.
• wp_comments: This table contains the comments on the posts.
• wp_links: This table contains the links that you add in the Administration > Links.
• wp_options: This table contains information on your WordPress site settings.
• wp_postmeta: This table contains meta information about your WordPress posts and pages. This helps in SEO (Search Engine Optimization).
• wp_posts: This table contains all posts, pages and revisions.
• wp_terms: This table contains information like tags and categories in your posts.
• wp_term_relationships: This table manages relationship post types with terms in wp_terms table.
• wp_term_taxonomy: This table defines the terms defined in wp_terms table.
• wp_usermeta: This table contains meta information about Users on your website. Helps in SEO.
• wp_users: This table contains user information like username, password and email.

CAUTION- Take Backups:

Please be careful as PhpMyAdmin is a very powerful tool. Note that once you make a change, it is not possible to ‘Undo’ it. Hence, taking a backup of the database is of utmost importance. Click the export option among the top tabs as shown in the screenshot to start the backup process.

Select the option of ‘Quick’ export method which exports all data off the database. In case you select ‘Custom’, you would be able to select the tables for which you want to backup the data.

A file with a .sql extension gets downloaded into your system. That contains all the data of your site. Consider keeping weekly or even daily backups of your data for safety purposes.

Restoring a Backup:

Click the ‘Import’ button right next to ‘Export’. Select the SQL file to upload and click Go (let the default options remain as they are). Note that you need to clear the old data if you are planning to overwrite for testing purposes.

Optimizing database performance:

Databases are ultimately stored as files in the server. Over time, as the size of the database grows, involving a lot of addition as well as deletion of data, it can lead to fragmentation of the files in the hard drive, which means the big file gets split and stored in different parts of the system. Although it is the job of the operating system to manage these fragments, it makes the process slow. The best way is to regularly defragment your tables through the option provided in MySQL. Select the tables to defragment and select the option as shown.

There are many other tasks that you can do directly via the PhpMyAdmin interface.

Change the password of the admin user:

Note that it is a salted md5 of the actual password. That means if you want to change the password, find the salted md5 of your new password and then insert that value into the table. An easier way to do that is to create a new WordPress user with the password that you desire and copy the password of that entry into your admin entry!

Change username of a particular user:

Open the corresponding entry in the table and change the username to a desired value. Note that it must be unique for the database to accept a change in that entry.

On the security of the database:

The internet is a very scary place. It is possible that one night you go to sleep peacefully and find that all the data has been removed from your site the next morning. No matter how secure you make your website, there always remains a possibility of an attack. However, what you can do is make life a bit tough for the attacker so that hacking your site is a headache.

Most attacks assume the table prefix to be “wp_”. It is advisable to use a different prefix. But you can go one step ahead and keep dummy tables with the “wp_” prefix.

Keep a strong password for the root user as usually, all privileges are granted to the root user. In fact, keep strong passwords for all MySQL users that can access your WordPress database.

Conclusion:

I sincerely hope that you are well equipped to know about the working of your WordPress site with this insight on the database system on which your site runs. Although it might not seem very important, it is certainly an added bonus if you have the knowledge and willing to make sure you never lose any data.