A Quick Guide to Understanding Your phpMyAdmin Area
If you use a WordPress website, it probably uses a MySQL database. Another one that it might use is called MariaDB.
Every website needs one of those to store information like passwords, posts, and user data. When WordPress can’t connect to your MySQL database, your website probably won’t be accessible.
There are a handful of reasons why it’s important to understand how it works. Here are a few that I can think of.
- You may need to delete old data or tables
- You may need to update the titles of a ton of different posts at once
- An individual database table might need to be imported or exported
In order to get to the area we’re going to talk about, click “phpMyAdmin” from your website’s control panel. It may say “Access PhpMyAdmin” or some similar variation like mine does below.
Image Source: Bob Buckley; Thank you!
The next screen you should see looks like this:
Image Source: Bob Buckley; Thank you!
On the left-hand side, click the database that corresponds to your website. In my case, it’s “backupweathersite” below “New” in the tree. Then, you should be at the screen we’re talking about:
Image Source: Bob Buckley; Thank you!
The menu highlighted in red has some useful tabs on it for doing the types of things I mentioned above. Starting with Structure, here’s what you can do with each one.
Structure
Structure is probably the most important of all the tabs because it lists the tables of your database. Each table has its own set of options, like emptying it if you want to delete all the data. You can also drop the table if you want to get rid of the entire thing completely.
Be very careful with that. Instead of deleting everything, you can clear out things like old posts by running SQL queries.
SQL
Selecting SQL will give you this screen:
Image Source: Bob Buckley; Thank you!
There, you can run SQL queries. For example, on one of my sites, I need to remove expired jobs pretty often. If I don’t, the database fills up even if the jobs are deleted on the frontend. This causes the site to slow down a lot, and I can’t add new posts or other data.
The solution? Running this query: delete from `wp_posts` where `post_status` = ‘expired’
That takes care of thousands of old jobs in about a second, which is pretty nice!
As a side note, the screenshots in this article are from a local WordPress installation I have for a different project.
Search
Search does what it sounds like it would. When you put something in the main search field, it will automatically go through the entire database, looking for anything that’s a match.
Here’s a tip that might also help: you can choose to include or exclude searching specific tables if you want so it doesn’t search everything.
Image Source: Bob Buckley; Thank you!
Query
The Query tab essentially does the same thing that the SQL tab does, except it offers guidance for creating the query. You can select the table that you want the query executed in from a dropdown and then click “Update query” to see it in the window below.
Image Source: Bob Buckley; Thank you!
Export
Image Source: Bob Buckley; Thank you!
Export allows you to download all of the tables from your database. This is useful if you want to do manual backups periodically (although there are some great plugins that can handle that). You can export the tables in a bunch of different formats, too.
These include SQL, PDF, CSV, XML, and a handful of others that you’ve probably never heard of. Mainly, Texy! Text and YAML.
Import
The import function allows you to bring in outside database tables to yours.
Image Source: Bob Buckley; Thank you!
You can only import a table if it doesn’t exist in the current database. If the table exists already, you will get an error, and the import won’t finish, according to DreamHost.
The file can be compressed or uncompressed in a few different formats. Those include bzip2, gzip, or zip files. Something you may not be used to is the compressed file name.
It needs to be structured like “.[format].[compression]”. An example of a common one is “.sql.zip”.
Image Source: Bob Buckley; Thank you!
You can choose from a handful of different formats. These include CSV, ESRI Shape File, MediaWiki Table, OpenDocument Spreadsheet, SQL, and XML.
There are also a number of different SQL compatibility modes. The compatibility mode setting will dictate how MySQL works. We won’t go into each one and talk about the differences, but your options for those are ANSI, DB2, MAXDB, MYSQL323, MYSQL40, MSSQL, ORACLE, and TRADITIONAL.
Operations in phpMyAdmin
This tab gives you the ability to perform a handful of different operations, like creating a table and renaming the database. In order to create a new table, just put in a name, and the number of columns, and click Go.
Siteground has a great tutorial for creating tables since things can get kind of complicated. Below that, you can rename the database. The check box that says “Adjust privileges” is there because MySQL does not adjust the original privileges related to the database on its own.
When you check that box, phpMyAdmin adjusts the privilege table, so users have the same privileges on the new items. If you do that, the privileges for all the database-related elements are also adjusted to the new name. That includes things like tables, columns, or procedures.
Image Source: Bob Buckley; Thank you!
This section also allows you to do things like copy the database to another one and change table collations. A collation is a set of rules that defines how to compare and sort character strings.
You probably won’t ever need to mess with things like collation, but it’s nice to be semi-familiar with that, just in case.
Image Source: Bob Buckley; Thank you!
One other thing is that you can’t really rename a database. When you do that, phpMyAdmin will create a new one (example below) and drop the old.
Image Source: Bob Buckley; Thank you!
Routines in phpMyAdmin
The Routines section looks like this:
Image Source: Bob Buckley; Thank you!
“Filters” won’t do anything unless you actually select a database to search. Putting a word into the “Containing the word:” section will find all of the tables containing that word in your database.
Routines are procedures and functions that do various things, like the job data cleanup function mentioned earlier. You can add one there, and it will be displayed under that tab.
Events
In the event scheduler tab, you can create and run tasks automatically based on a schedule. The schedule can vary a lot, like running a task every couple of seconds to every few weeks. In order to use it, you will need to manually turn it on by clicking where it says “off” in the picture and then clicking “Add event”.
Image Source: Bob Buckley; Thank you!
Triggers in phpMyAdmin
A trigger is a named database object that is associated with a table and that activates when a particular event occurs for the table. You could use a trigger to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
Image Source: Bob Buckley; Thank you!
Privileges in phpMyAdmin
Image Source: Bob Buckley; Thank you!
The Privileges section allows you to make changes to current user privileges or export them to a new database. There are a handful of options for editing the privileges:
Image Source: Bob Buckley; Thank you!
Designer
The Designer tab can be kind of a mess, as you can see (it’s in the “More” dropdown):
Image Source: Bob Buckley; Thank you!
The Designer feature is a graphical way of creating, editing, and displaying phpMyAdmin relations. These relations are compatible with those created in phpMyAdmin’s relation view.
To use this feature, you need a properly configured phpMyAdmin configuration storage and must have the $ cfg[‘Servers’][$ i][‘table_coords’] configured. It’s unlikely you’ll ever need to do anything in this tab.
Wrapping Things Up
Going into the phpMyAdmin area for a WordPress site can be pretty daunting. If you find yourself in that position, it’s important to be familiar with all the different aspects of the dashboard. Understanding the basics of what each section does will help you navigate and get things done a little quicker with less of a headache.
Featured Image Credit: Provided by the Author; Thank you!
The post A Quick Guide to Understanding Your phpMyAdmin Area appeared first on ReadWrite.
(38)
