Contents

1. Brief description about MySQL
2. How to install MySQL on Linux (Ubuntu)
3. Create a demo database and a user
4. Install phpMyAdmin and configuration in apache.conf
5. Restrict phpMyAdmin access to users

Brief description about MySQL

Database plays a major role in any application whether web or mobile. So it is one of the main tasks to secure our database.

There are many databases in the market today like, Oracle, MySQL, MongoDB etc.

In this article we are going to talk about MySQL.

MySQL is the world’s most popular open-source database. It has become the leading database choice for web-based applications, used by high-profile web properties including Facebook, Twitter, YouTube, Yahoo! and many more.

We can access the database via terminal, but the terminal is always not comfortable to work with. Specially if we have a lots of data.

To access the database developers often use 2 of the most popular choice

1. MySQL Workbench, which is a desktop application
2. phpMyAdmin is a web application.

How to install MySQL on Linux (Ubuntu)

Here we will run a few commands as super user to install packages. So make sure you have the rights to run sudo.

Install Apache2

sudo apt install apache2

Install PHP

Here we need to install a few packages. As phpMyAdmin is a PHP application we need to install PHP with two of its extensions and one Apache plugin

sudo apt install php php-mbstring php-mysql libapache2-mod-php

After installing these packages, we need to restart the Apache server

sudo systemctl restart apache2

To verify if PHP has been installed and running on the server. Please create a php file in /var/www/html the directory with <?php phpinfo(); ?> in it. And try to access the file from your browser.

It should show a PHP info page.

Install MySQL

sudo apt install mysql-server

RECOMMENDED: After installing the server, run mysql_secure_installation. It is a MySQL plugin that ensures, to have a strong password for the users. This step is not covered in this article. But you can read about it here.

As you are a super user, you will be able to login to MySQL shell using

sudo mysql

After login to the shell its time to create a user and assign all privileges.

CREATE USER 'demo'@'localhost' IDENTIFIED BY 'nKsKCvyVBhba';

Now lets give all privileges to this user. (In a production application you should give only the required permissions)

GRANT ALL PRIVILEGES ON *.* TO 'demo'@'localhost';

After running this command you should be able to login with these credentials

mysql -u demo -p

and enter the password

Install phpMyAdmin

sudo apt install phpmyadmin

After installation of phpMyAdmin you need to add the Apache configuration file of phpMyAdmin to the main apache.conf. So open the apache.conf using vim /etc/apache2/apache2.conf

And at the end of the file append the path of phpMyAdmin apache.conf file, which is

Include /etc/phpmyadmin/apache.conf

After adding this line restart apache. And access your IP or domain with phpmyadmin url.

You should see the phpMyAdmin login page.

Here you can login with the user credentials we created a few minutes back.

Now, anyone can access your phpMyAdmin URL and hackers can do brute force attacks on this page. They will try to log in with every default credential they have in their database.

Secure phpMyAdmin

The first step you can take to prevent hackers to access the login page is not use the default url which is phpmyadmin. Currently, my phpMyAdmin looks like this

After making the changes my phpMyAdmin looks like this, look at the URL.

You can set the url anything you want. So if you have changed the url with a string only you know. Hackers won’t even find the login page. So you have prevented almost 70% of the attack.

But how did we do this. Let me tell you.

We need to open the apache2.conf file in /etc/phpmyadmin directory with the following command

vim /etc/phpmyadmin/apache2.conf

There you will see on the third line

Alias /phpmyadmin /usr/share/phpmyadmin

Here instead of /phpmyadmin you can write any string you want. After making the changes restart the apache server. Now you can access the page from the browser.

Securing phpMyAdmin one step further

After accessing the site you can easily log in with the MySQL users credentials. But now we will add another layer of security to even showing the login page. Let’s see how it looks

Using this method, the user will be asked to give username and password to view the login page of phpMyAdmin

So how did we achive this

Here we have used Authentication method of Apache. You can read about it here.

Goto /etc/phpmyadmin and run the following command

sudo htpasswd -c filename admin

Here admin is the username and hit enter and enter your password

You can keep this file anywhere on the system.

Next goto /usr/share/phpmyadmin

And create a .htaccess file and put these configs

AuthType Basic
AuthName "Restricted Data"
AuthUserFile /etc/phpmyadmin/filename
Require valid-user

And close the file, then you shold see the basic authentication popup.

That’s it!