Adminer - lightweight phpMyAdmin alternative

Introduction​

Adminer is a small compact alternative to phpMyAdmin. With the english+MySQL only version only taking up 189 Kb.
I've used it for about 3 months. I was looking for an alternative to phpMyAdmin for my smaller servers. And oh boy is it good, so good in fact that, i don't use phpMyAdmin anymore.

Note: While Adminer is great for seasoned developers. I'd recommend newcomers to stick with phpMyAdmin. Adminer does not have all the bells & whistles, as phpMyAdmin. It does not hold your hand, just so you know what to expect.

Database​

First of all, you of course need a webserver to put Adminer on. You can put it on an entirely different server, than your DB server(s).

Next you need a MySQL/MariaDB user that is allowed to login from your IP-Adress. Only allow the user to login from IP's that you trust (e.g. Home, Own VPN). Else you'll have a massive security hole, in your database setup.

I usually create a Super User, if anyone has other suggestions, please do post them. And i'll put them in the guide. This user will be able to everything on your server, but locking it down to a single IP, which you are the sole user off, i don't really see the problem.

Login to the root MySQL/MariaDB user on your server

Code:

mysql -u root -p

Type your password, and then create the user, that suits your setup best:

Code:

GRANT ALL PRIVILEGES ON *.* TO 'myUsername'@'%' IDENTIFIED by 'YourPass' WITH GRANT OPTION;

Super User, access to everything, from all ip's. The 'WITH GRANT OPTION' part means that, the user will be able to create, update, delete other users.

Code:

GRANT ALL PRIVILEGES ON myDatabaseName.* TO 'myUsername'@'%' IDENTIFIED by 'YourPass';

User with all privileges to the database 'myDatabaseName' - from all ip's.

Code:

GRANT ALL PRIVILEGES ON myDatabaseName.* TO 'myUsername'@'192.168.1.56' IDENTIFIED by 'YourPass';

User with all privileges to the database 'myDatabaseName' - from the ip-adress: 192.18.1.56

Installing Adminer
​

You can download Adminer from: Adminer - Database management in a single PHP file - and install plugins, as per the instructions.
I use a custom version, with pre-installed plugins which you can get here: GitHub - pematon/adminer-custom: Our custom all-in-one configuration of Adminer database tool.

After you have downloaded adminer, extract it and then upload it to your web root. You have now installed Adminer, and you're ready to login.

Nginx Configuration - Basic Security​

I would recommend to secure Adminer, with Nginx. Even though your MySQL user has a password, it's always a good idea to, secure any administrative panels. Heres how:
.
Creating an encrypted password:

SSH to your box, login and type this in your terminal:

Code:

openssl passwd

Next you'll be asked to enter a password, and then type it again to confirm it.
If the password matches, you'll get it returned encrypted. It should look similar to this:

Code:

IrFCqiVNk2Rno

Copy the encrypted password, and save it somewhere on your machine.

Now we need to create a file, where we store the username and password combination. You can save it anywhere you want. Just keep it out of your public webroot. I would save it in a folder in /home/nginx/

To create the file in /home/nginx:

Code:

nano /home/nginx/adminer_login

Now you'll see an empty document. The format is

Code:

username:password

. Here i'll name the user honeydew. But you can call it whatever you want.

Code:

honeydew:IrFCqiVNk2Rno

Save and close the file, by pressing CTRL+X - Then y - Then enter

Now we're going to configure Nginx to use this file, for a specific path. Open your nginx config, for the vhost, that you created earlier

Code:

/usr/local/nginx/conf/conf.d/yourdomain.com.conf

Inside the server block, we're going to create a location block, with the path to Adminer. You can add the location block anywhere in the file, as long as it's in the server block.

In this example, we're going to tell Nginx that Adminer is to be found at: example.com/manage/adminer
The title of the login box to be Adminer - and of course the path to the credentials file.

Code:

server {
   . . .

  #Adminer Security
  location /manage/adminer {
   auth_basic "Adminer";
   auth_basic_user_file /home/nginx/adminer_login;
   #OPTIONAL - Uncomment to only allow the server, and your ip to access the page.
   #allow 127.0.0.1;
   #allow [COLOR=#ff4d4d]YourIP[/COLOR];
   #deny all;
  }

  . . .

}

Save the config by pressing CTRL+X - Then Y and then ENTER

Quick way to test if there's any syntax errors in the config. In the terminal, type:

Code:

nginx -t

The output should look like this:

Code:

nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok
nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful

If everything is fine, restart Nginx:

Code:

service nginx restart

Now we're ready to test it out. Go to yourdomain.com/path/you've/chosen
A login form should pop op:

Type in your chosen username and password. And bam, you're ready to go!

Inspiration from: How To Install and Secure phpMyAdmin with Nginx on an Ubuntu 14.04 Server | DigitalOcean