How to Use phpMyAdmin to Manage WordPress Database?
You have to use many tools in order to run a WordPress website. phpMyAdmin is one of the free and open source tools to manage MySQL and MariaDB databases. It is the popular tool among WordPress users due to two reasons. One is that most WordPress sites use MySQL database and second is almost all popular hosting companies offer phpMyAdmin as part of the hosting account. If you are running a WordPress site, then here is an introduction and how to use phpMyAdmin to manage your WordPress database.
What Can You Do with phpMyAdmin Tool?
WordPress stores all data in the backend database and phpMyAdmin is an essential tool to perform any action directly on your database.
- Download complete database in zip and gzip format. phpMyAdmin is the only option to download your database in hosting companies like SiteGround.
- Create a new database to connect with your site. Generally, this is done automatically with one-click installation tools like Softaculous. However, you can also manually create or delete database from phpMyAdmin.
- Create, edit, copy and delete database, tables or table entries. As part of regular maintenance, you may need to cleanup post metadata and options table through this tool.
- Run SQL queries to modify multiple tables at once. This is useful in situations like moving your local website to live environment and vice versa.
These are only few important things, refer the complete features of the tool in the official website.
Warning: Remember any modification you do in database through phpMyAdmin tool is irreversible. For example, your website may become unusable if you wrongly delete a table or an entry in a table. Therefore, make sure to have a proper backup before you try to play around with phpMyAdmin tool.
How to Access phpMyAdmin?
phpMyAdmin is an independent tool that you can download and install from their community website. Fortunately, you do not need to do this as all hosting companies offer this tool as part of their hosting account. As far as we have checked, Plesk, cPanel and custom hosting tools offer phpMyAdmin. So, you can simply login to your hosting account and access the tool.
For example on Bluehost, you can access the tool by going to “Advanced” link that will take you to cPanel. And on SiteGround Site Tools, you can access the tool under “MySQL” section.
The official phpMyAdmin website also offers a demo where you can create dummy database and play around to make yourselves familiar.
The tool will look like below having two panes. The left pane will show all available databases on your hosting account. You can select specific database for your site and perform actions using the options available in the right pane.
You can navigate through different tabs in the right pane to perform required action.
Manage WordPress Databases
You have to use phpMyAdmin to perform some repetitive and critical tasks. Here we will explain how to manage those tasks in your WordPress database.
1. Create and Delete Database
In order to create a new database, click on the “New” link that is shown on top of the tree view of the available databases. Enter name, select character set and click on “Create” button to create a new database.
Generally, your database will be created automatically during the WordPress installation. You can view the created databases here in phpMyAdmin tool to change the name or copy. Select the DB name and go to “Operations” tab. You can do the following actions:
- Create table – add a new table to your database.
- Rename database – if you change the name, make sure to update it in wp-config.php file.
- Delete database – do not perform this unless you know what you are doing.
- Copy database – copy selected DB to create a duplicate. This is useful to perform queries on the duplicated copy without affecting your site.
- Change collation – do not change this, otherwise your entire database may become corrupted. For example, emoji on your site needs correct character set for the proper display.
2. Backup Website Database
This is the most important and useful action every WordPress users do regularly.
- Select the database you want to export as a downloadable file and go to “Export” tab.
- Choose “Custom – display all possible options” as export method.
- Make sure the format is in SQL.
- Choose the list of tables you want to import, select all for exporting the entire database.
Make sure to select the compression option as zipped or gzipped to download the compressed SQL file. Click “Go” button to start the download.
You can keep this downloaded DB as your backup. In case of problems with your live site, you can go to the “Import” tab and upload the old database to restore your site.
3. Run SQL Query
Go to the “SQL” tab and paste the query you want to run on your database. This is useful in situations like replacing a link or word in entire database. For example, when migrating your site to different domain name, you can use simple SQL query to replace the old name to new name in all occurrences.
4. Modify Table Entries
Sometimes, you need to modify table entries directly in the database. After selecting the database, choose the table and the tool will show all entries under “Browse” tab. You can navigate through the entries and edit or delete as per your need.
5. Search and Replace
Many situations, you need to change multiple occurrences of a text or link on your database. Using SQL to do this requires technical knowledge to write queries. Fortunately, you can use the “Search” function available in phpMyAdmin to perform complex search and replace functions without writing queries. For example, you can easily update all affiliate links from one URL to another.
phpMyAdmin is very useful to perform quick actions on your database. However, it does not have any undo button to revert your last action. Therefore, be careful when updating or deleting the database entries to avoid accidentally taking your site or permanently losing your hard worked content.