For beginners: WordPress database management using phpMyAdmin
WordPress is built upon PHP as a scripting language based on MySQL database as the brain for the website for the management. Sometimes, you need to add some more features for WordPress database, so you have to know more about it and how to use it and which tool you want to make its handling easier. This blog will be focused on using phpMyAdmin.
Table of Contents
What’s in your WordPress database?
WordPress database like any other database in the programming world. It uses MySQL programming to handle it. Actually, WordPress database is much more important for your website than your installation of WordPress. Because all the information about your website is stored in the database and all the WordPress is interface to handle this data. All you content is also stored in the database.
After each 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: where all comments are stored.
- 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.
WordPress contains all the raw data about your website, styling, media files, and everything else that makes your website presentable to browsers and users.
How WordPress uses the database?
During installation, WordPress will use the information provided by you and insert data by default into the database tables. After installation, WordPress will start running queries to the database and dynamically generate the HTML pages for the new website. This makes WordPress the most user friendly platform in web development as users don’t need to create new.html files each time to create a page.
What is phpMyAdmin?
phpMyAdmin is one of the most popular administration tool for databases. 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.
It’s written in PHP and can be used in any web browser. It allows users to perform administrative tasks for their database via mouse clicks instead of writing SQL queries to make it easy for any other non deeply technical people.
The abilities of phpMyAdmin
Here we conclude some of the functions of phpMyAdmin. It lets you do several additional things to itself. Thus, the below mentioned are some functions that WordPress users will need to perform while working with the phpMyAdmin:
- Renaming your Database.
- Copying out your Database.
- Replacing the table prefix.
- Selecting and copying tables with a changed prefix.
Connecting to your WordPress database
To perform these steps, you need to have cPanel server as a versatile tool. It will give you the access to phpMyAdmin to connect to the database.
1.Login to your cPanel dashboard.
- Scroll down to phpMyAdmin and click the icon. This will be located under the header, “Databases.”
- Once you log in, you’ll see your username on the left. If the databases you have are not visible, click the “+” next to your username to expand the list.
- Click on the database that corresponds with the WordPress website you wish to access.
- Now, you have connected to the database.
Creating database using phpMyAdmin
1.Click on the tab of database.
- Write a name for your database and choose the name which 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.
- Choose the best format for encoding the content inside the database from the drop down menu next to the text. Then just press n “Create”.
Next up you will have to create a user who has all access privileges to your newly created database.
Before starting to execute the following steps, you have to backup the database to avoid any loss of your data.
How to backup WordPress database using phpMyAdmin?
1.From the home screen, on the left or from the database screen. Choose the name of the database that you want to backup.
- 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.After that click 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.
Creating a WordPress Backup using a Plugin
There are many plugins for backup that we wrote about before. Not only the database that you have to backup on schedule but also, the whole website. So you can use one of backup plugins to make yourself safe and put a schedule for the backup.
How to use database file after backup?
In this point, you need to import the database file using phpMyAdmin.
1.Go to the “Databases” screen and choose the database you want to import. Depending on your setup, this will show you an empty database or the tables already contained within it.
- Press “Import” to get the import screen and then “Browse…” to choose the database file from your computer.
- After that click “Go” to the process of importing. Depending on the file size and connection speed, this might take a little while. Once it is done you will be prompted with a success screen.
Database Optimization using phpMyAdmin
Database optimization is the process which you can use to clear up the temporary data that might be store in the database during previous operations. It should be done regularly to avoid any conflicts inside WordPress database. And don’t worry, the optimization using phpMyAdmin is really easy.
1.Connect to the database that you want.
- Be sure that you checked all the tables inside the database to mark them to execute the process on all the tables.
- Under the tables you can choose and change the process that you want to do “Optimize table” then press “Go” if it’s there according to the version of the phpMyAdmin.
How to repair tables?
Tables may crash and cause some random removal process in your posts or the draft. So you can use phpMyAdmin to check the tables and repair them easily then everything will go back to normal.
- Open the database.
- Select all tables.
- Use the drop-down menu under the tables 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.
If there are any tables that need repairing so you should do the following:
- Mark the table(s) that needs to be repaired by checking their boxes.
- 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.
You may need to delete tables or databases to get more space for a backup file or something like that.
1.In your database, mark the table(s) you want to delete via check box on the left.
- In the drop-down menu at the bottom, choose “drop”.
- You will then be taken to a confirmation screen. If you choose ‘yes,‘ the table will be deleted irreversibly.
Dropping the whole database
1.Choose the tab of “Databases” from the top to view all databases in your account.
- Mark the checkbox of the database you no longer need. Double check that the tick mark is in the right place.
- At the bottom of the list, click on “Drop”. A confirmation prompt will pop up. If you press ‘Ok,’ the database will be deleted permanently.
You can modify users and WordPress options from WordPress database but as it explained before from the WordPress interface will be easier.