How to manage MariaDB or MySQL Databases with Adminer

This brief tutorial shows students and new users how to manage MariaDB | MySQL databases with the Adminer database web management tool on Ubuntu

Adminer is a web-based database management tool that supports MySQL, MariaDB, PostgreSQL, MS SQL, Oracle, and more.

Like phpMyAdmin, Adminer supports all those features you find with it, but with a tidier user interface, high performance, better support, and rapid development, enhancing security, user experience, performance, and more.

For more about Adminer, please see its homepage.

Install Apache2 HTTP Server

Adminer needs a web server to function. A popular open-source web server is Apache2. Run the commands below to install it on Ubuntu.

sudo apt update
sudo apt install apache2

After installing Apache2, the commands below can be used to stop, start, and enable the Apache2 service to always start up with the server boots…

sudo systemctl stop apache2.service
sudo systemctl start apache2.service
sudo systemctl enable apache2.service

Now that Apache2 is installed…. to test whether the web server is working, open your browser and browse to the URL below…

http://localhost

If you see the page above, then Apache2 is successfully installed…

Install MariaDB Database Server

Since we will manage MariaDB databases via Adminer, run the commands below to install the MariaDB database server on Ubuntu.

sudo apt-get install mariadb-server mariadb-client

After installing MariaDB, the commands below can be used to stop, start, and enable the MariaDB service always to start up when the server boots…

Run these on Ubuntu 16.04 LTS

sudo systemctl stop mysql.service
sudo systemctl start mysql.service
sudo systemctl enable mysql.service

Run these on Ubuntu 18.10 and 18.04 LTS

sudo systemctl stop mariadb.service
sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service

Next, run the commands below to secure the database server with a root password if you were not prompted to do so during the installation…

sudo mysql_secure_installation

When prompted, answer the questions below by following the guide.

• Enter current password for root (enter for none): Just press the Enter
• Set root password? [Y/n]: Y
• New password: Enter password
• Re-enter new password: Repeat password
• Remove anonymous users? [Y/n]: Y
• Disallow root login remotely? [Y/n]: Y
• Remove test database and access to it? [Y/n]:  Y
• Reload privilege tables now? [Y/n]:  Y

Now that MariaDB is installed, to test whether the database server was successfully installed, run the commands below…

sudo mysql -u root -p

Type the root password when prompted…

If you see a similar screen as shown above, then the server was successfully installed…

Install PHP Script

To get Adminer working, you’ll need to install PHP and related modules.

However, PHP 7.2 may not be available in Ubuntu default repositories… To run PHP 7.2 on Ubuntu 16.04 and previous, you may need to run the commands below:

sudo apt-get install software-properties-common
sudo add-apt-repository ppa:ondrej/php

Then update and upgrade to PHP 7.2

sudo apt update

Then run the commands below to install

sudo apt-get install php7.2 php-tcpdf php7.2-cgi php7.2-mysqli php-pear php7.2-mbstring php7.2-gettext libapache2-mod-php7.2 php7.2-common php-phpseclib php7.2-mysql

After installing the above PHP-required modules, download PHP’s latest version. You can get it from the link below:

https://www.adminer.org/en/#download

At the time of this writing, the latest version was 4.7.4. If you find a newer version from the link above, replace the download link below with that so you can always get the latest.

cd /var/www/html
sudo wget -O /var/www/html/adminer.php https://github.com/vrana/adminer/releases/download/v4.7.4/adminer-4.7.4.php

After downloading the PHP package above, no setup or configurations are needed. Adminer comes with a single file, and that’s all you need.

After that, open your web browser and browse to the server hostname or IP address followed by admin

http://example.com/adminer.php

You should see the Adminer login page.

You won’t be able to log on with the MariaDB root account.

When you attempt to log on using the MariaDB root account, it will fail… That’s because MariaDB and MySQL have switched their authentication method to auth_socket

The auth_socket plugin authenticates users that connect from the localhost through the Unix socket file… which prevents users from connecting with passwords… So, you won’t be able to connect via Adminer…

When you attempt to log in, you see the error “#1698 – Access denied for user ‘root’@’localhost’”

To fix that, run the commands below:

sudo mysql -u root

That should get you into the database server. After that, run the commands below to turn off plugin authentication for the root user

use mysql;
update user set plugin='' where User='root';
flush privileges;
exit

Restart and run the commands below to set a new password.

sudo systemctl restart mariadb.service

Now try again to log on… this time, it should work!

Congratulations! You have successfully installed Adminer with Apache2, MariaDB, and PHP 7.2 support.

You may also like the post below: