All WordPress installations require a database. A database is a structured form of data that can be stored and retrieved. There are many kinds of databases -remote, NoSQL, and relational among others. Each kind has its own characteristics. Each kind has its own advantages and disadvantages. WordPress uses a relational database. In this type of database, data is stored into tables. These tables can be queried to retrieve data. The retrieved data, if needed, can be manipulated to display more results. In addition, tables can joined, filtered, and grouped when retrieving data.
The database WordPress uses is MySQL – a free relational database.
WordPress uses MySQL to store data regarding the site into tables within a created database.
Databases can be managed through the command line. More importantly, however, they can also be managed through a database management software.
In this tutorial, we will be learning the latter.
The database management software we will be using is phpMyAdmin. It is a database management software that runs on a web browser. It is bundled along with other modules within the XAMPP Package. (If you are using windows it is also recommended that you use XAMPP, otherwise, follow the instructions here)
For this tutorial we will be assuming that XAMPP has been downloaded. (Note: take note that we will be using a local install of WordPress. If you are using a hosting provider, you can access phpMyAdmin through the hosting provider’s dashboard.) Although phpMyAdmin has many features, we won’t be going through all of them. Instead we will be going through the features most important to database management.
With that, let us proceed.
Before we can use phpMyAdmin, we need to start some modules.
Go to the XAMPP directory (in our case it is “C:\xampp”).
When you are there, click on “xampp-control.exe”
When the XAMPP Control Panel appears, click “Start” on the “Apache” and “MySQL”. Without starting both of these, phpMyAdmin will not run.
Once those modules start, click the “Admin” button on the MySQL module.
This will open the phpMyAdmin index page.
Database Creation and Deletion
We can create and delete tables using phpMyAdmin
From the phpAdmin index page, Click on “Databases”
You will be redirected to the tab below. You can see all the databases on the machine in here.
To create a database, fill in the “Database name” field then click “Create”. It will create a new database with the assigned name.
To delete a database, click on its checkbox, then press “Drop”. It will delete the database
Database Management – Database Table Management
We can manage also manage database tables using phpMyAdmin
From the phpMyAdmin index, click on the ‘Databases’ Tab You will see a list of the databases installed on the machine.
To manage the tables on a database, we need to click on its name.
(In this tutorial, we will be using the “first-wp-db” database because it is a database being used by a an installed WordPress)
Alternatively, we could have clicked on the database name found in the phpMyAdmin sidebar.
After clicking the database name, you will be redirected to a tab showing the database’s structure. Yu can see all the tables present in the database here. These tables were created by WordPress during installation. These tables contain information about the site. For instance, the “wp_comments” table contains data regarding the comments on the WordPress site; the “wp_users” table contains data regarding users of the site; and so on. To learn more about the default tables, you can view the WordPress documentation about them. The “Table Overview” section to be exact.
(Note: If you’re wondering why the tables are prefixed by ‘wp_’, it is because that is table prefix assigned during the installation. See the pic below. A different prefix could have been chosen.)
In the “Structure” tab, we can choose specific actions to be executed on a specific table.
We can also choose an action which works on multiple tables. To do this, click on the checkboxes of the tables you need. Afterwards, choose an action in the “With Selected” dropdown menu.
Repairing and Optimizing Tables
phpMyAdmin allows us to repair and optimize tables, should we need to.
To do this we need to do view the database structure.
Once we can view its structure, click “Check All” to select all tables (we can choose specific tables if needed)
View the options in the “With Selected” drop down.
Choose “Repair Table ” to repair the selected tables. It will automatically execute the action
The “Optimize Table ” option is also present in the “With Selected” dropdown menu. Choose “Optimize Table” then to optimize the selected tables. It will automatically execute the action
Browsing Table Entries
phpMyAdmin allows us to also browse the values of a database tables.
From the phpMyAdmin index, click on the “Databases” tab.
Select the database containing the table whose entries you want to browse. In this tutorial, we will be browsing the “wp_terms” table of “first-wp-db” database.
Alternatively, we can click on the table name on the phpMyAdmin sidebar.
You will be redirected to the “Browse” tab of the table. You can see all the table records of the database table in here.
Checking Table Structures
We can also check the structures of database tables in phpMyAdmin.
From the phpMyAdmin index (or directly from phpMyAdmin sidebar), browse for the table whose structure we will check. Here, we will be using the “wp_terms” table “
Once redirected to the table”s “Browse” tab, click on the “Structure” tab. We will see the tables structure.
Running SQL Queries
We can also run SQL queries on phpMyAdmin.
We need to select the database we will be executing queries on.
From the phpMyAdmin index click on the “Databases” tab. Once there click on a database. Alternatively, click on the database name in the phpMyAdmin sidebar (If we don”t select a database, the queries will not run).
When redirected to the “Structure” tab.
Click on the “SQL” tab.
It will redirect to a tabwhere queries can be run. Type the query and click “Go” to execute it
Alternatively, we could have clicked on the database table name (on the “Structure” tab or from the phpMyAdmin sidebar). This will redirect to the “Browse” tab of the database table. Click on the “SQL” tab. You will be redirected to a tab where queries can be executed for the specific table.
Clicking on the buttons below the query window will create pre-formatted queries
Click “Go” to execute the query.
(Note: The queries generated by the “INSERT” and “UPDATE” buttons have to be edited with the literal values corresponding to the column datatype. See the images below)
There is also another way to insert values into a database table.
From the phpMyAdmin index browse for the table we want to insert values to.
In this example, we will be using the “wp_terms” table
After being redirected, click on the “Insert” tab. We can insert values in the columns of the table by filling in the necessary fields and then clicking “Go”
We can also insert multiple rows by changing the “Continue insertion with rows” field seen at the bottom of the page
phpMyAdmin also allows us to search a database for a specific value.
From the phpMyAdmin index, browse for the database you will be searching the value in.
Click on the “Search” tab.
On this tab, we can find a term inside specific tables and even a specific column in that table.
We can also search a specific value within a specific table.
From the myPhpAdmin index, browse for the database table. Alternatively, click on the table name we will search a value in.
Here, we will be using the “wp_terms” table again.
Once in the “Browse” tab, click on the “Search” tab.
We will only be looking at the “Table search” method.
Fill in fields of the value we are searching for. We can even choose to use different operators.
myPhpAdmin allows us to export and import databases.
We will first look at exporting all databases installed on our server.
From the phpMyAdmin index, click on Export. This will show the tab where we can export all databases.
No need to fill in the “New template” field. Choose “Quick” for the export method. Leave the format to SQL.
Click “Go” to export the databases.
We could have created a template which has custom Export configurations. We give our template a name. Customize the export method. Then we press the “Create” button.
To use the template, we select if from the “Existing templates” dropdown.
Alternatively, navigate to the database you want to export (either from the phpMyAdmin index or from the phpMyAdmin sidebar. From the “Browse” tab, click on the “Export” tab. Leave all fields empty. Then click “Go” to export. (Note: We can also create templates here)
Next, we will be looking at how to import databases.
From phpMyAdmin index, click “Import” tab. You will be redirected to a tab where you can import the database backup file.
Browse for the backup file.
Select the file format of the backup file (although this changes automatically), then click “Go”
We can also import data into a specific database.
Navigate to the database to import in.
Click on the “Import” tab.
Browse for the imported file, then click “Go” at the bottom.
Database Table Import/Export
Aside from exporting and importing databases, we can also export and import tables on phpMyAdmin.
We will look at exporting first.
To export a table, navigate to the specific table. We will be using the “wp_terms” table
From the table’s “Browse” tab, click on the “Export” tab. Click “Go” to export.
(Note: We can also create a custom template similar to that when exporting databases. They are very similar.)
To export a table, navigate to the specific table. We will be using the “wp_terms” table
From the table’s “Browse” tab, click on the “Import” tab.
Browse for the file needed.
Click “Go” at the bottom to import.
Common Database Table Operations
phpMyAdmin also allows us to perform common operations on tables. From the phpMyAdmin index, browse for the table you will use. For this, example we will be using the “wp_terms” table.
From the selected table’s “Browse” tab, click on the “Operations” tab.
We will be able to see and select operations on our table here.
Fill in the fields of the operation we want to execute then click “Go” after.
Managing Database User Accounts
phpMyAdmin allows us to manage the User accounts that can access the databases installed on our local machine.
From the phpMyAdmin index, click on ‘User Accounts’
Here we will see two subtabs – “User accounts overview” and “User groups”.
On the first subtab, we can see an overview of the User Accounts on our local machine and their details.
On the second subtab, we can create and manage User groups. We can also modify the User group’s privileges here.
We will look at the first subtab in more detail so let’s go back to it. Click again on the “User accounts overview”,. We will see an overview of the User accounts again.
Look at the image below. We can see the actions that we can execute on a User account.
You should also know that we can also delete User accounts on this subtab. To do that, click on the checkbox beside the User name of the account we want to delete and then click ‘Go’ after.
Now, we will examine the “Edit privileges” action further. From the “User accounts overview tab”, select a user and click on “Edit privileges”. In this tutorial, the name of the user is “wordpress-user”
We will be redirected to the “Global” subtab. Here we can edit and assign the global privileges of a User account.
We will examine the “Database” subtab next. Click on the “Database” subtab. Here we can assign and edit access privileges of any User account on specific databases. Select a database you want to assign User account privileges to.
(Note: we can also revoke the privileges of a user here).
If you clicked “Go” after selecting the database to assign privileges to (or alternatively, clicked on the “Edit privileges” action for a database that already has privileges for the User account) you will be redirected to a tab where you can assign the Database specific privileges (See the image below). Select all the privileges you need. Click “Go” to apply them.
Next, let’s examine the “Change password” subtab. Click on the “Change password” tab.
Here we can change password a User account uses to access the databases.
Lastly, we will look at “Login information” subtab. Here, we can create a new user account based on the User account whose privileges are being edited. Fill in the fields then choose an option on, “Create a new user account with the same privileges and …” then click “Go”.
And… we’re done!
We have now covered the basics of phpMyAdmin WordPress Database Management.