Using Adminer to Manage your Databases

1. Introduction

If you find yourself interacting with a database system such as MySQL, PostgreSQL, MS SQL, Oracle, or even SQLite, sometimes you find that some of the tasks you perform are more conveniently executed using a GUI rather then using the default management utility (usually run from a CLI) provided by the database system itself. Some of you may already use other tools such as phpMyAdmin, or phpPgAdmin. This article will talk about another web based database management tool known as Adminer. Adminer allows for the management of all the database systems mentioned above.This article covers Debian (& Ubuntu), Fedora, and ArchLinux.

From its website: Adminer (formerly phpMinAdmin) is a full-featured database management tool written in PHP. Conversely to phpMyAdmin, it consist of a single file ready to deploy to the target server. Adminer is available for MySQL, PostgreSQL, SQLite, MS SQL and Oracle.

Adminer has an entire page dedicated to a comparison between itself and phpMyAdmin. Some notable features in Adminer that are either absent or incomplete in phpMyAdmin include: full support for views, full support for triggers, events, functions, routines, and ability to group data and apply functions to data in select data (to name a few). This article will cover its installation, configuration, customization, and some usage example for MySQL and PostgreSQL.

2. Pre-requisites

Have some knowledge in web administration and development (HTML, CSS, PHP, and Apache)

This article assumes you have Apache, PHP, your database system of choice configured.

I'll be running Adminer on a local development LAMP stack I run on my netbook

3. Installation

If you go to Adminer's home page and attempt to download, the site will give you the .php file. Adminer is a self contained .php file. If your using ArchLinux there is a package available in AUR. Packages do not exist for Debian or Fedora. No worries, we'll follow the guidelines established in that package. First make a directory (/usr/share/webapps/adminer) for Adminer and then download adminer.php to that directory (as index.php) by issuing the following command sequence:

Now try navigating to Adminer's login page by going to the following address on your web browser:

127.0.0.1/adminer

You should see the following:

Try logging in to your database, enter your database system, user name, and password. You shouldn't have to name the server if your doing this on a local server. You should see something similar to the image below after successfully logging in:

If you have any issues accessing Adminer try restarting the Apache, else try and see if you can access the file by placing adminer.php in /srv/http. For the purposes of this tutorial, it's okay to download adminer.php to /usr/share/webapps/adminer. If you install Adminer using this method then you do not have to configure httpd.conf as discussed earlier.

In the next section, I'll show how to customize Adminer's interface and then discuss how to install plugins.

5. Customization

5.1. Customizing the interface

Lets start by customizing Adminer's interface. Begin by navigating back to Adminer's website and go to the skins part of the page, titled Alternative designs, or click here. Find a theme that you like (I choose the one on the first column, two down) and download it the directory you installed adminer.php to (/usr/share/webapps/adminer).

Now refresh Adminer and you should see something similar to the image below. Since Adminer is a web application you can create your own custom themes by editing the CSS file your downloaded.

5.2. Plugin Customization

Adminer has been designed to allow for its extension through third party plugins. I'll show an example by installing the enum-option plugin. Before applying this plugin enumerated data is show using using HTML radio buttons (See Below):

After using this plugin, enumerated data is now selected by using HTML drop down menus. Lets start by creating a file adminer-plugins.php which should be located within the Adminer directory. Place the following code within that file:

Create a sub-directory "plugins". Now install the plugin.php file located here to that sub-directory. Next install the plugin you would like to use alongside plugin.php. In my case, I downloaded the enum-option plugin.

To test it out, browse to 127.0.0.1/adminer/adminer-plugins.php. Try editing data defined as the enumerated data type. You should see something similar to the following:

Pretty cool eh?.

6. Usage

So most of the usage examples will be self explanatory for you seasoned DBAs. I'll keep the examples rather simple but try and cover a couple of things that I find useful. After successfully logging into Adminer, you should be presented with the following screen.

From here you can either navigate existing databases, manage global permissions, view the database system process list, system variables, and the system status, execute custom commands, and more. Now I'll create a database and some tables. Then, I'll visualize its schema using Adminer. The images below will be screen shots of different usage examples.

Adding a user: (You can specify permissions at a column level. Additionally, you can also specify permissions for routines)

Creating a table and respective columns:

So after making some more tables, lets browse the database schema (If you establish foreign keys between tables, Adminer will show the respective links!):

You can managing a user's privileges at a server, database, table, column, and routine level:

Adminer also supports ALL data types of each database system (Did you know that PostgreSQL supports IP address data types?!):

There's a lot more to cover in Adminer but I'll leave that open to you to further research.

7. Conclusion

In conclusion, Adminer is an extremely powerful interface to many well known database systems. It is full featured php web application and is under 500KB's. Adminer allows your to manage your database from the web locally, or over a network. Some of its many features (not covered in this article) include its the ability to print the server's process list and terminate rouge processes, manage event partitions (in MySQL), export table structure to various formats, manages views, triggers, insert and edit existing data records. Its a pretty extensive application, check it out today.

Author: Javier L. Velasquez

I've been using Linux since 2007. I consider myself a part time student and a part time web developer by day and FOSS advocate by night. I enjoy having philosophical discussions about the ethical implications of close source and open source development.

Stay Tuned

Make sure you tune in to our Newsletter and Linux IT jobs portal to stay informed about the latest opportunities in the field. To help you to keep your skills sharp we also offer various Linux Training courses. Lastly, visit our Linux Forum if you want to share your Linux experiences with us or require additional help.

Readers

Stay updated with Linux Career news, advice and tips. Stay informed what IT skills are most likely to help you advance your IT career.

Partners

Who are we?

LinuxCareer.com is not affiliated with any local or international company, nor is it a recruitment or employment agency. We specialise in Linux based careers and closely related Information Technology fields by providing careers advice and latest employment opportunities.

JOIN LINUXCAREER

You can also get involved in the LinuxCareer project by participating on our FORUM or SUBMITTING A LINUX ARTICLE. We offer a range of privileges to our authors and good company.