Many users need the functionalities given by a database such as MySQL, although few are able to manage it from a command console. PhpMyAdmin was developed to interact with MySQL through a web interface. In this guide, we'll take a look at how to install and protect phpMyAdmin so as to use it to manage MySQL databases.
Setting up PhpMyAdmin
What is PhpMyAdmin?
PhpMyAdmin is an open source free tool. It's developed in PHP and it allows us to manage MySQL servers from a web browser. It supports many of MySQL's functionalities while at the same time giving detailed information about the status of the server in real time.
Main characteristics
- Highly intuitive GUI
- Support for many of MySQL's properties
- Examine and modify content in the database, tables, views, fields and indexes
- Create, copy, rename and modify databases, tables, views, fields and indexes
- Take on sever, database and table management tasks and recieve configuration suggestions
- Create, modify and run every SQL query
- Manage MySQL users and their privileges
- Manage stored procedures and triggers
- Import data from CSV y SQL files
- Export data to diverse formats: CSV, SQL, XML,,PDF, Word, LATEX and others
- Manage several servers
- Create graphs from the design of the database in various formats.
- Browse globally from within the database
- And much more...
Even if we don't make use of every one of its functionalities, the ability to make copies of our data and management tasks by accessing the server directly can be our saving grace at times. Its great user interface allows us to do these things without having to be experts in MySQL management.
In a shared hosting service, it's almost guaranteed that we'll have access to a link for this tool in our control panel, but how do we install it in our VPS? Due to its characteristics, our server needs to, at least, have a web service that supports PHP. In this guide we show the details of the steps required to take in order to install these elements in a server running Ubuntu 16.
Installing phpMyAdmin
In our case we can simply download it from the Ubuntu repository:
sudo apt-get update
sudo apt-get install phpmyadmin php-mbstring php-gettext
Click Yes on each confirmation question and we'll reach the server selection screen.
Select apache2 and carry on with the installation. Inside the PhpMyAdmin database configuration screen click Yes.
We'll be asked for the database admin password, after that, we create a password for the phpMyAdmin user.
If everything went well, we just enable the mcrypt and mbstring modules and restart the Apache server to activate the new configuration.
sudo phpenmod mcrypt
sudo phpenmod mbstring
sudo service apache2 restart
Now simply access http://dominio-o-IP/phpmyadmin
We enter our database access credentials and now we're able to manage our server from the web.
Secure the phpMyAdmin instance
We have easily installed our phpMyAdmin instance. The first order in business is to reinforce security in our insallation. We need to take into account that:
- This tool communicates directly with our MySQL instances.
- It manages authentification through database access credentials.
- It runs every type of query in the server.
- It's a highly distributed tool and as such, it is attacked frequently
Let's look at the steps needed to minimize these risks:
First of all, we must use https whenever we want remote access. If we haven't done that, we can find instructions in this guide on how to install a Let's Encrypt certificate for free.
Implementing AuthBasic
Let's add an additional layer of security by protecting access using an extra user and password. To do this we edit the phpMyAdmin config file.
sudo nano /etc/apache2/conf-available/phpmyadmin.conf
and add an AllowOverride All within the <Directory ...> block. This will give us the ability to use the .htaccess authentication and authorization functionalities integrated in Apache.
Save changes and restart the web server.
sudo service apache2 restart
Now we create a .htaccess file in the app folder
sudo nano /usr/share/phpmyadmin/.htaccess
and add the following lines:
AuthType BasicAuthName "Restricted acccess"AuthUserFile /etc/phpmyadmin/.htpasswdRequire valid-user
In summary, we've made it so that, in order to access protected content, identification through user and password is requested. The list of users is stored in a file (.htpassword) that we created outside the protected folder. Here we can find further information on the Apache directives.
All we have to do now is creating the passwords folder. To do so we download an additional Apache package, if we haven't done so already.
sudo apt-get install apache2-utils
this package allows us to use htpassword to generate our own passwords folder
sudo htpasswd -c /etc/phpmyadmin/.htpasswd username
Here we use the same route stated previously. Once the folder is created, we can add new users with the same command, except then we'll omit the -c modifier.
From now on, we'll have to identify ourselves in order to access our own phpMyAdmin. Obviously the username and password we use need to be different from the credentials we typically use.
Modify the access url
Another thing we can do is modifying the acess url. This way we'll make it that much harder for those who intend to access our precious little database without permission.
To do this we need to edit the phpMyAdmin.conf file again, adding a new Alias with the desired url. We can put a comment on the existing url or delete that line outright.
Restart the web server and verify that if we use the old url, the server returns a 404 error. If we do this, we need to check any script we use with phpMyAdmin access and make any proper changes.
Activate access on demand to phpMyAdmin
We've set up phpMyAdmin as an extra folder in our server and thus, it will be permanently exposed. In reality this set up doesn't really make sense, since, as a norm, we rarely access the database directly.
What can we do?
A possible solution is to create an independent VirtualHost service, which we'll activate only when we want to work with the database. The steps required for this are relatively simple.
First, copy the config file to the sites available folder in Apache.
sudo mv /etc/phpmyadmin/apache.conf /etc/apache2/sites-available/phpmyadmin.conf.
Don't forget to delete the existing config files:
sudo rm /etc/apache2/conf-available/phpmyadmin.conf
sudo rm /etc/apache2/conf-enabled/phpmyadmin.conf
Modify the file /etc/apache2/sites-available/phpmyadmin.conf to implement a virtual host service. The end result should look like this:
<VirtualHost *:80>ServerName dominio.para.phpmyadminDocumentRoot /usr/share/phpmyadmin...</VirtualHost>
In reality we simply deleted the Alias lines and enclosed everything inside a VirtualHost directive. Once changes apply, check for any existing errors.
sudo apachectl configtest
If everything is OK, activate the new configuration.
sudo a2ensite phpmyadmin.conf
sudo service apache2 reload
Now we simply register the DNS in the new domain and activate SSL in it. We can now access our MySQL server with upmost security.
AWhen we finish working, we deactivate the domain.
sudo a2dissite phpmyadmin.con
sudo service apache2 reload
This way we block access to our MySQL whenever we're not working directly with our database, keeping our precious data safe from the outside world.
In conclusion
We've learned how to install phpMyAdmin and what can we do to reinforce its secuity. This way, we can access our MySQL server directly when we need to through a very intuitive visual interface which will help out greatly in a pinch.
