Data Centers

How to make MySQL administration simple with Adminer

If you're looking for a powerful, secure web-based database interface, Jack Wallen shows you how to install and use Adminer on Ubuntu Server.

datahero.jpg
Image: Jack Wallen

So you're looking for a way to make MySQL a bit easier to administer. You've given phpMyAdmin a go and are looking for a solution that's a bit more secure and offers more in the way of features and database support. That, my friend, is where Adminer comes in.

As with many forms of technology, because of widespread usage, phpMyAdmin suffers from a few deal-breaking vulnerabilities. In fact, Adminer was created with these five priorities in mind:

  • Security
  • User experience
  • Performance
  • Feature set
  • Size

The feature set includes the following:

  • Can connect to a database server with username and password
  • Create, edit, and delete databases
  • List fields, indexes, foreign keys and triggers of table
  • Change the name, engine, collation, auto_increment, and comment of tables
  • Edit name, type, collation, comment, and default values of columns
  • Add and drop tables, and columns
  • Create, alter, drop and link lists, search by indexes (including fulltext), select from views, call stored procedures and functions, and drop triggers
  • List data in tables with search, aggregate, sort, and limit results
  • Insert new records or update and delete the existing ones
  • Supports all data types, blobs (through file transfer)
  • Execute any SQL command (from a text field or a file)
  • Supports MySQL, MongoDB, MariaDB, PostgreSQL, SQLite, MS SQL, Oracle, SimpleDB, Elasticsearch

I want to walk you through the installation and usage of Adminer. You'll be surprised at how easy it is to setup and create a database with this tool.

Installation

I'll be demonstrating on Ubuntu Server 16.04. Although Adminer is found in the standard repositories and can be installed with the command sudo apt install adminer, the version found there is out of date. Instead, we'll install the latest release. I will assume you already have your LAMP server up and running.

Log onto your database server. Instead of installing Adminer into /var/www/html, let's install it in a non-standard directory. First create the directory with the command:

sudo mkdir /usr/share/adminer

Download the necessary file into that directory, with the command:

sudo wget "http://www.adminer.org/latest.php" -O /usr/share/adminer/latest.php

Create a link to the file with the command. This makes it easy to later download a newer version without having to go through too many steps:

sudo ln -s /usr/share/adminer/latest.php /usr/share/adminer/adminer.php

Create a new Apache configuration file for Adminer with the command:

sudo echo "Alias /adminer.php /usr/share/adminer/adminer.php" | sudo tee /etc/apache2/conf-available/adminer.conf

Enable the configuration with the command:

sudo a2enconf adminer.conf

Restart apache by issuing sudo systemctl restart apache2.

Logging in

Adminer is now installed and ready to serve. Point your browser to http://SERVER_IP/adminer.php (Where SERVER_IP is the IP address of the server) and log in with your database administrator credentials. You will then find yourself on the Adminer main page (Figure A).

Figure A

Figure A

The Adminer main page.

Creating a new database

Click on the Create database link. In the resulting window (Figure B), type a name for the database, select the character set, and click Save.

Figure B

Figure B

Creating a new database.

In the resulting window (Figure C), you can alter the database or create tables, views, procedures, functions, or events.

Figure C

Figure C

Our newly created database, ready for tables.

Let's create a new table on our database. Click Create table, and in the resulting window (Figure D), give the table a name, select the engine and character set, and then add columns.

Figure D

Figure D

Creating a table for our database.

If you attempt to create the table without columns, the creation will error out. Before you click Save, add as many columns as necessary. Once you've created all of your columns (Figure E), click Save and your table will be added.

Figure E

Figure E

Make sure to fill out all of your names, types, and lengths, as they are all required.

Congratulations, you've just created a database and added a table. You can then start adding items to your tables by navigating to the database, clicking on the table to be used, and clicking New item (Figure F).

Figure F

Figure F

Adding items to your tables.

In the resulting window (Figure G), add your items. When you're done, click either Save to finish, or click Save and insert next to save the current items and add more.

Figure G

Figure G

And easy means of adding items to tables.

Easy database administration

It doesn't get much easier than Adminer. If you're looking for an alternative to phpMyAdmin, one that offers more security and features, you need to give Adminer a go. This handy database web interface will not disappoint

Also See

About Jack Wallen

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.

Editor's Picks

Free Newsletters, In your Inbox