Manage Databases with Adminer

Databases are most likely going to form a key part of the services you are running on your server. A database comes bundled with the LAMP web server using the method we installed our web server. Thats not to say of course that you absolutely must install a database on your web server – you can run a web server using Apache and PHP alone, but most of the web applications we will be covering will require a database as well.

There are many different database packages available, some commercial, some free. The one which comes installed as part of the web server we covered is called MySQL. This is a very popular and capable database, and there is loads of support for it online if you have issues or questions later down the road.

When you are installing a new web application, often you will be required to set up a database for it. You can certainly connect to your database and do all of this from the command line, but a much easier method is to use a web based database administration tool. phpMyAdmin is one of the best known web based tools for this, and it provides an abundance of features for working with MySQL databases. Another, much simpler, tool for managing your databases is Adminer.

Adminer is a single PHP file which connects to your database and allows you to set up, view and edit databases and users. With it being a single file, you simply drop it onto your web server, then browse to that file to log into your database and begin work. There is no installation involved.

Download

To begin, go to the Adminer site and download a copy of the file. There are four variants on offer:

  • Adminer for multiple database systems (MySQL, PostgreSQL, MS SQL…), multiple languages
  • Adminer for multiple database systems, English only
  • Adminer for MySQL, multiple languages
  • Adminer for MySQL, English only

    Save the php file and upload it to your web browser. Alternatively right click the file, copy the address, and use wget to download it direct to your server.

    Rather than having to type the full file name into your browser each time you want to use it, I would recommend creating a directory on your web server, saving the Adminer file to that, and then naming it index.php. When Apache comes across a file in a folder called index, it loads that file ahead of any others. This is why all web homepages are named index.html or index.php. This way you don’t have stray files in your web root directory, but rather they are in their own folders, which makes your web root a little neater.

    Note: If you are using PuTTY to connect (SSH) to your server, you can paste values into your command line. The method you are probably most familiar with, pressing Ctrl + V, does not work on PuTTY. To paste, you can either use the right click button on your mouse, or press Shift + Insert on your keyboard.

    Using Adminer

    The log in screen is quite straight forward:
    System: If you chose the file for multiple systems, these will be available in a drop down menu
    Server: Leave this as localhost. If the database you were connecting to was on a different server, you would enter the IP address or hostname of that server here. ‘localhost’ means the database system is on the same server as the adminer file, and thus your web server
    Username: Enter ‘root’ to connect to the overall database system, to administer all databases
    Password: Enter the password for the root user, which would have been set up when installing the database system
    Database: Leave this blank to see all databases. Enter a specific database to be brought straight to that database upon logon.

    After you have set up some users for your various web applications, you can also use their usernames and passwords to log in. You will then see only the databases which this user has been given access to.

    Note: It is strongly advised that, for each web application or database you create or use, that you create a separate user and password for working with them. Very often when you are installing a web application, it will need a database to store and organise its data. For security reasons, you need to use a user account distinct to that web application. This provides you with the security principle of containment. If one of your applications were vulnerable to web-based attacks, SQL injection for example, the attacker has gained access to your database using the user account being used by that web application. With separate database user accounts, the attacker is only able to attack the database for that web app, and not other databases on the system.

    Upon logging in, you will see all the databases your user has visibility of. For the root user, this will be all databases on the system. You can click into them to see their structure (schema), view data, alter tables or even drop (delete) the database. Clicking the privileges link allows you to see what users have access to the database and what they have the rights to do, i.e. view data (Select rights) but not change anything. You can also create new users for that database, set their password and give them specific rights.

    Presuming the user you have logged in with has sufficient rights, you can also run your own SQL commands or export the databases or even import new ones.

    Look and Feel

    Adminer also allows you to change the skin, or appearance, of the application. The Adminer website lists a number of designs which you can preview. To use one, copy the link to the adminer.css file underneath the design and save this CSS file to the same directory as your adminer index.php file. Opening Adminer again, or refreshing your browser, will now make use of this new design.

    Summary

    All in all, this is a very useful web based tool for doing quick jobs with your MySQL databases. While phpMyAdmin is the very popular feature-rich tool for every database job you could think of, Adminer is a lovely lightweight app for getting in there, administering your databases or users, and getting out again. Highly recommended, you’ll find yourself using it all the time!