Using Adminer to Manage your Databases

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.

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



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 linux command sequence:

# mkdir /usr/share/webapps/adminer
# cd /usr/share/webapps/adminer 
# wget -O index.php http://downloads.sourceforge.net/adminer/adminer-3.3.3.php

The php may be rendered server side instead of giving you the download script. In that case, download the file manually to /usr/share/webapps/adminer

Configuration

Now that adminer is installed, lets configure Apache and make Adminer usable. Copy the following into ‘/etc/httpd/conf/extra/httpd-adminer.conf’ on either Debian (& Ubuntu) or Fedora:

Alias /adminer "/usr/share/webapps/adminer"
<Directory "/usr/share/webapps/adminer">
        AllowOverride All
        Options FollowSymlinks
        Order allow,deny
        Allow from all
php_admin_value open_basedir "/srv/:/tmp/:/usr/share/webapps/:/etc/webapps:/usr/share/pear/"
</Directory>

Finally mention the file created above in http.conf, by adding the following line to /etc/httpd/conf/httpd.conf

Include conf/extra/httpd-adminer.conf

If you’re using ArchLinux use the following set of commands to install Adminer from the appropriate builds directory (e.g. /home/$SOME_USER/builds/apps/adminer)

$ wget https://aur.archlinux.org/packages/ad/adminer/PKGBUILD
$ makepkg -s
$ sudo pacman -U adminer-$VERSION-$ARCH.pkg.tar.xz 

On ArchLinux, You will still need to add the include line to httpd.conf as mentioned for Debian and Fedora.

Finally restart Apache:

# /etc/rc.d/httpd restart #for ArchLinux
# service httpd restart #for Fedora
# /etc/init.d/apache2 restart #for Debian or Ubuntu

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:
Default Adminer Login Screen

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:

MySQL Session in Adminer

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.



Customization

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.

Adminer Login Screen & CSS

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:

<?php
function adminer_object() {
    // required to run any plugin
    include_once "./plugins/plugin.php";
    
    // autoloader
    foreach (glob("plugins/*.php") as $filename) {
        include_once "./$filename";
    }
    
    $plugins = array(
        // specify enabled plugins here
        #new AdminerDumpXml,
        #new AdminerTinymce,
        #new AdminerFileUpload("data/"),
        #new AdminerSlugify,
        #new AdminerTranslation,
        #new AdminerForeignSystem,
	new AdminerEnumOption #Plugins not in use are disabled in this array, 
			#I added my plugin here (defined as a class within its php code)
    );
    
    /* It is possible to combine customization and plugins:
    class AdminerCustomization extends AdminerPlugin {
    }
    return new AdminerCustomization($plugins);
    */
    
    return new AdminerPlugin($plugins);
}

// include original Adminer or Adminer Editor
include "./index.php"; #For our example you should rename adminer.php to index.php
?>

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.

The directory structure should look like this:

adminer.css  adminer-plugins.php  index.php  plugins

./plugins:
enumoption.php  plugin.php

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?.



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.

Adminer list of databases

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 users in Adminer

Creating a table and respective columns:



creating tables

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

Adminer Schema preview

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



Managing users in Adminer

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

PostgreSQL various data types

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

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.



Comments and Discussions
Linux Forum