If you’ve ever installed WordPress, you know that every WordPress website consists of two parts: The core files and a MySQL database.
Of the two, the database is the much more important component. Should your core files go kaput or be corrupted, you can easily replace them and wouldn’t lose too much. However, if your database vanishes and you don’t have a backup — best find a good therapist now.
Therefore, it is important to know how to properly take care of this crucial part of your website. One of the most common tools used for doing so is phpMyAdmin and in this article we will take a closer look at it. But first, a little discourse on WordPress and your database.
What’s In Your WordPress MySQL Database?
As mentioned, the database is actually much more important for your website than your installation of WordPress. WordPress itself is merely a gateway, an interface that lets you interact with the database. All the hard content — pages, posts, comments, and so on — is stored there.
At every install, a number of standard tables will be created in your WordPress database — 11 to be exact. Over time, plugins will add to this number with their own tables, however, the core tables for every fresh WordPress install are these:
- wp_commentmeta – for the meta data of comments on your site
- wp_comments – all comments are stored here
- wp_links – deprecated but holding information entered in WordPress’s Link featured
- wp_options – everything input under Administration > Settings is stored in this table
- wp_postmeta – the meta data of your posts
- wp_posts – data for posts, pages and navigation items goes here
- wp_terms – mainly holds information for taxonomies such as categories and tags
- wp_term_relationships – this saves the relationships with posts and taxonomies
- wp_term_taxonomy – describes taxonomies with the wp_terms table
- wp_usermeta – unsurprisingly, the user meta data
- wp_users – all users go here
As you can see, they contain all the raw data of your website, minus styling, media files, and everything else that makes your website presentable to browsers and users. That happens on the WordPress side.
What Is phpMyAdmin?
In your infinite quest for knowledge, you might have wondered what that “MySQL” in “MySQL database” means. In short, it is the name of an open-source database management system that can be operated via typing in queries, “SQL” actually stands for “Structured Query Language.” (Funny enough, the “My” is actually the name of one of its inventors.)
Now that we have that out of the way, what does phpMyAdmin have to do with it?
phpMyAdmin is an administration tool for said databases, and one of the most popular out there. A lot of hosting providers use it to enable their customers to organize their databases. It also ships with popular development platforms such as WampServer, XAMPP, and MAMP.
As you can guess from the name, phpMyAdmin is written in PHP, and therefore usable with a normal web browser. It allows users to perform administrative tasks for their database via mouse clicks instead of writing SQL queries. Thank God, cause who wants to learn yet another coding language, right?
Exactly! So now let’s look at how to use phpMyAdmin to perform common database maintenance tasks.
How To Use phpMyAdmin For Your WordPress Database
1. Creating The WordPress Database
Creating a database for your fresh WordPress install is most commonly the gateway for people to use phpMyAdmin for the first time. I know it was like that for me.
To install WordPress, on the database side you will need the following:
- A database name
- A database username
- A database user password
Let’s see how we can set up all of these in phpMyadmin:
1. A click on ‘Databases’ will bring you to the following menu:
2. Pick a name for your database and enter it in the ‘Create Database’ field. Choose something that describes what the database is used for, it will be very helpful when you have more than one database running in the same place. Be aware that depending on your provider there might be rules for naming databases that will limit you in your choices.
Next, choose a collation from the drop-down menu. Pick the one that fits best for your language and encoding. In most cases you should go with ‘utf8_’ for encoding. Pick ‘utf8_unicode_ci’ if your particular language is not available.
When you are done with this, click the ‘Create’ button. The new database should then appear in the list.
3. Next up you will have to create a user who has all access privileges to your newly created database. There are several ways to do so. The one that I find most convenient is to click on your newly created database. From there pick ‘Privileges.’
Clicking on ‘add user’ will lead you to the screen where you can create a dedicated user for that database. Enter a user name in the respective field (make sure that ‘Use text field:’ is selected from the drop-down menu) and then enter a hard-to-guess password (you should really know by now what that means). Again, make sure it says ‘Use text field’ in front of it and don’t forget to write everything down so you don’t forget!
The ‘Host’ field will most often be filled with ‘localhost’, however, you have to check with your provider for this.
Under ‘Database for User’ make sure that the checkbox that says ‘Grant all privileges on database [your database name]’ is enabled. Leave everything under ‘Global Privileges’ as it is.
Now click ‘Go’! Congratulations, you now have the database name, username, and password you will need to install WordPress. What are you waiting for?
2. Backing Up Your WordPress database
Among all the things that phpMyAdmin can do, performing a database backup is arguably the most important. As mentioned, the database contains the meat and potatoes of your website, you do not want to lose it. Therefore, you should perform regular backups and always back up your database before updating WordPress or making any other big changes to your website.
Here’s how to do it:
1. Either from the home screen on the left or from the database screen, click on the name of your database to access it.
2. Next, click on ‘Export’ on top of the screen and then choose ‘Custom’ for export method to access the additional options and configure them as follows:
- Check that all tables in the database are selected for export (assuming you only have one website per database with only one database prefix)
- Set output name and compression as desired
- Ensure that ‘SQL’ is enabled under ‘Format’ and that you choose ‘Structure and data’ under ‘Format-specific options’
- Now for ‘Object creation options’ check the following:
- ‘Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement’
- ‘IF NOT EXISTS’
- ‘Enclose table and column names with backquotes’
3. Upon clicking ‘Go’ you should now be prompted with a file download in whichever format you chose. When it is completed, you have successfully backed up your database. Good job!
3. Importing A Database From Backup
Importing a database is the opposite of backing it up. It becomes important in case you need to restore your website or when you move it to another host. phpMyAdmin makes this quite easy as well.
1. Go to the ‘Databases’ screen and choose the database you want to import data into. Depending on your setup, this will show you an empty database or the tables already contained within it.
2. Click ‘Import’ on top to get to the import screen and then ‘Browse…’ to locate the SQL file on your computer.
3. After making sure you have ‘SQL’ selected under ‘Format’ a click on the ‘Go’ button will start the import. Depending on the file size and connection speed, this might take a little while. However, when it is done you will be prompted with a success screen. Phew, that was a close one!
4. Database Optimization Via phpMyAdmin
Why does your database need optimizing? Because over time it accumulates so-called overhead, which is a fancy name for temporary disk space taken up by your tables. You can see it in the right column when looking at your database. Optimization means clearing up this data and should be done regularly. However don’t worry, some overhead is normal and the number doesn’t have to be zero all the time.
Optimizing your database with phpMyAdmin is really easy:
1. Access the database you want to optimize. Click on the ‘check all’ box at the bottom of the screen to mark all tables within that database (note: newer version of phpMyAdmin have a ‘Check tables with overhead’ option to only select those tables that need to be optimized).
2. Use the drop-down menu on the right to select ‘Optimize table.’ Done!
(3. Depending on your version of phpMyAdmin you might have to click on ‘Go’ to start the optimization process.)
For in-depth information on how to clean up and optimize your WordPress database, check this article.
5. Checking and repairing tables
Sometimes tables will crash. It’s not pretty and might result in all of your posts or drafts disappearing or cause some other kind of mayhem. However, oftentimes phpMyAdmin can repair the table and everything will go back to normal (and if not, you backed up your database, right?).
If something is amiss on your website, you might first want to check if any of the tables in your database is at fault:
1. Open the database in question.
2. Select all tables by clicking on ‘check all’ at the bottom of the screen.
3. Use the drop-down menu to choose ‘check table.’ You might also have to click the ‘Go’ button at the bottom to start the process.
phpMyAdmin will show a page with the results. In the right column you will be able to see if your tables are deemed OK or not.
Should any of them exhibit a problem, repairing it is just as easy as optimizing:
4. Mark the table(s) that needs to be repaired by checking their boxes.
5. From the drop-down menu, choose ‘Repair table’ to start the process. Alternatively, you might have to click on ‘Go’ to get it going. That’s it.
6. Dropping tables
Deleting or dropping a table can be necessary if you want to import a backup file and need to make space. In other cases deleted plugins sometimes leave tables in your database that are no longer used. However, you need to be sure you know what you are doing before dropping a table as this can potentially break your entire site!
If you are, here is how to do it:
1. In your database, mark the table or tables you want to delete via check box on the left.
2. In the drop-down menu at the bottom, choose ‘drop.‘
3. You will then be taken to a confirmation screen. If you choose ‘yes,‘ the table will be deleted irreversibly.
7. Dropping An Entire Database
You can not only drop tables one by one, but you can also get rid of a database all at once. A case where that might be necessary is when you have moved your site to another server and no longer need the database. Of course, only drop it if you have successfully exported and imported the database to its new location.
1. Click on ‘Databases’ to view all databases in your account.
2. Mark the checkbox of the database you no longer need. Double check that the tick mark is in the right place!
3. At the bottom of the list, click on ‘Drop.’ A confirmation prompt will pop up. If you press ‘Ok,’ the database in question will be history.
8. Other Functions Of phpMyAdmin
Apart from the options above, phpMyAdmin lets you do several additional things. However, the aforementioned are the main functions that WordPress users will need to perform. Yet it might be of use to be aware of other possibilities as well.
Renaming Your Database
Should you ever need to rename your database, that is entirely within the scope of phpMyAdmin. You can find that option under ‘Operations’ while viewing your database. If you do rename it, make sure the data within your website’s wp-config.php gets updated accordingly.
Copying Your Database
Making a copy of your database is a good idea if you are setting up a development environment to test out themes and plugins before introducing them to your live site. It’s done quickly within phpMyAdmin by choosing your database and going to ‘Operations’ > ‘Copy database to:’.
Replacing The Table Prefix
For security’s sake, it is a good idea to use a different table prefix for your WordPress install than the default ‘wp_’. However, if you have forgotten to change this during the install, phpMyAdmin can help you remedy the situation by replacing ‘wp_’ with something else via the bottom menu. Just make sure you also update wp-config.php to reflect the change.
Copying Tables With A Changed Prefix
This option can be used to run a development version of your existing site from the same database. It allows you to copy all existing tables and change their prefix in the process. If you then duplicate your WordPress data and configure wp-config.php to use the new prefix, you will have a copy of your site up and runing without the need for another database. Read the WordPress codex for more.
In Short: phpMyAdmin, The WordPress User’s Best Friend
As you can see from the above, phpMyAdmin is a powerful tool which every WordPress user and website owner should be familiar with. It enables you to perform complex operations without the need of learning to write SQL commands (though it doesn’t hurt either).
Knowing your way around the database administration tool can make running your website a lot easier and save you a lot of nerve and googling in case of a crisis.
What are your experiences with phpMyAdmin? Do you prefer a different platform? Any hacks, tips and tricks to share with the rest of us? We would love to hear about it.