First released in 1998, phpMyAdmin is one of the longest running and most popular PHP projects on the planet. One of the first utilities mentioned within PHP and MySQL tutorials, and ubiquitously available on Web hosting providers, over the years phpMyAdmin has grown to be an indispensable tool within every PHP developer's toolbox.
Despite approaching its 12th birthday, phpMyAdmin is still under active development, with at least one significant version released every year since the project's inception. In fact even after almost a decade of use I still marvel over discovering features which I had no idea existed. In this article I thought I'd highlight 10 useful phpMyAdmin features which may have escaped you during your daily interaction with this fantastic utility.
1. Manage User Privileges
MySQL offers a powerful user privilege management system which allows you to manage a user's ability to interact with MySQL at the database, table, and even column level. These privileges are managed using two MySQL commands, namely grant and revoke, and unless you work with these commands on a regular basis, it's almost guaranteed you'll be consulting the MySQL manual in order to recall the syntax.
Save yourself the hassle by clicking on phpMyAdmin's Privileges tab, located on the application's top ribbon. There you'll be able to add and delete users, and use a simple series of checkboxes to set and later modify a user's privileges.
2. Backup Your Databases
PhpMyAdmin is able to export a database or even select tables within a database to 16 different formats, among them CSV, Excel, XML, YAML, and even to a PDF file. Export a database by selecting a database after navigating to the Databases tab, selecting the desired database, and then clicking the Export tab. There you'll be able to select the desired tables to export (by default all are selected), and choose the desired export format. You also have the option of exporting just the table structures, just the data, or both.
3. Change the Theme
PhpMyAdmin's default theme is optimized for productivity, but in my opinion isn't particularly eye-appealing. Did you know it's possible to change the theme to fit your particular artistic tastes? In fact, the phpMyAdmin website points to several well-designed themes. All you need to do is download and unzip the theme into the phpMyAdmin themes directory.
4. Monitor Server Status
MySQL tracks practically every conceivable aspect of its operation, monitoring all manner of server traffic and query statistics. You can peruse this data by clicking on phpMyAdmin's Status tab, located on the application ribbon at the top of the page. Here you'll find a laundry list of server and query information, including visual cues indicating which statistics might require further investigation.
MySQL's query statistics can be quite confusing at first glance. However, getting a grip on their purpose and tuning the associated configuration parameters can be one of the easiest ways to boost performance. For more information about MySQL's statistics, see this article, and additionally watch this presentation.
5. Enable the Relational Designer
Despite having been a feature for several years now, phpMyAdmin's relational designer is one feature which I've only recently managed to discover, likely because it isn't enabled by default. Once enabled, you'll be able to create foreign key relationships which link two tables together using additional features integrated into phpMyAdmin's table structure view.
While a fair bit of configuration is necessary in order to enable it, the process is fairly straightforward. The phpMyAdmin wiki contains detailed instructions here (this is known as enabling phpMyAdmin's configuration storage).
6. Create PDF Relational Diagrams
With phpMyAdmin's configuration storage enabled in order to support the relational designer, you'll have an added bonus of being able to create visual diagram of a database's relational structure and save the diagram to a PDF. You can access this feature by clicking on the Operations tab located on the application's ribbon (only available with configuration storage is enabled).
7. Bookmark Your Queries
You'll often use phpMyAdmin as a convenient solution for reviewing the results of complex queries pasted into the SQL tab interface. However, it can quickly become tiresome to repeatedly copy and paste queries into the interface, particularly if you regularly execute several different queries. With configuration storage enabled, you can bookmark queries using the Bookmark this query button.
8. Prevent the Root User From Logging In
PhpMyAdmin is incredibly easy to use, allowing you to manipulate databases with just a few keystrokes and clicks of the mouse. Accordingly, performing database management tasks while logged in as the root user can be a recipe for disaster, because of the root user's ability to perform every conceivable task, including dropping a database. To prevent the root user from logging into phpMyAdmin, add the following line to your config.inc.php file:
$cfg['Servers'][$i]['AllowRoot'] = FALSE;
9. Use Your Native Language
PhpMyAdmin uses an English-language interface by default, however did you know a total of 62 languages are supported to varying degrees? Switch to your desired language using the Language select box located on the home screen. You can keep tabs on translation progress on this page. If you're multi-lingual, consider participating in the translation process!
10. Synchronize Databases
One of the greatest inconveniences developers face is devising a straightforward solution for synchronizing databases residing on multiple and even the same server. PhpMyAdmin offers an incredibly simple solution to this challenge, allowing you to easily migrate table structures and data between a source and target database. To use this feature, just click on the Synchronize tab located on the top ribbon, and then choose the source and target databases. Prior to synchronization, visual cues will provide you with details regarding whether a source table already resides in the target, and whether the structure and data are already up to date.