I prefer to use phpmyadmin for MySQL and MariaDB administration. It helps me to visualize the database and run queries from the simple web UI rather than the MySQL command line. While I am comfortable with writing SQL at the command line, having an easy way to see all the database tables, triggers and stored procedures increases my productivity. I don't like having to constantly reference the table names and column names. In phpmyadmin, that information is at the ready.
Exposing a production MySQL database with phpmyadmin introduces some inherent security risks. In order to mitigate those risks, we have to secure phpmyadmin immediately after installation.
This assumes you already have a clean Ubuntu (or Unix variant) installation and have secure SSH access to your server instance.
OK, let's get started. The first step is to update your local package index using the package manager. Then we'll pull down the files from the web.
sudo apt-get update sudo apt-get install phpmyadmin
After the files are downloaded and installed, a phpmyadmin script will run allowing you to configure phpmyadmin for use.
A special note. When the prompt first appears, it appears that apache2 is already selected. However, it is not really selected, only highlighted. You need to hit the spacebar to select your web server of choice, then tab, then enter.
Chose apache2. Select yes when asked if you will use dbconfig-common to set up the database. Next, you are asked for the root password and then a password to secure phpmyadmin.
Before continuing, we need to enable a mod for phpmyadmin called php5-mcrypt.
sudo php5enmod mcrypt
Restart your Apache instance to enable these changes.
Now, you are ready to login to your phpmyadmin.
Browse to your domain name and append /phpmyadmin to the domain name or IP address.
Enter the database user name and password to login. This is the root user and password you entered during the install.
Once you are logged in to phpmyadmin, you will see this web UI.
Installing phpmyadmin on Ubuntu is a very easy process. The package manager did most of the work for us. One very important step most new admins overlook is securing phpmyadmin properly. This is especially important for a production database. Because phpmyadmin is in extremely wide use, it is a target for some hackers. You should take the following steps to help secure your phpmyadmin instance from unauthorized use.
A recommended first step in securing your phpmyadmin site is to place an authentication gateway in front of the entire application. To enable this, just use Apache's built-in .htaccess authentication function to lock down access to a specific IP address or require additional authentication. I will do both.
First, enable .htaccess overrides in the Apache config file. Edit the linked file in the config directory.
sudo nano /etc/apache2/conf-available/phpmyadmin.conf
Add an AllowOverride All directive to the directory section, like below...
<Directory /usr/share/phpmyadmin> Options FollowSymLinks DirectoryIndex index.php AllowOverride All ...
Save and close the configuration file and restart your Apache instance.
sudo service apache2 restart
Create a .htaccess file
In order to enable the .htaccess file, place the new file in the application directory.
sudo nano /usr/share/phpmyadmin/.htaccess
Next, we are going to enter the following:
AuthType Basic AuthName "Restricted Files" AuthUserFile /etc/phpmyadmin/.htpasswd Require valid-user
I also add the section to restrict access to the application by IP address. In this section, I allow access from a single IP and deny access from all other IP addresses.
# ALLOW USER BY IP <Limit GET POST> Require all denied Require ip 22.214.171.124 </Limit>
When you are finished entering the necessary configuration and IP's, save and close the file.
Now we need to create the .htpasswd file to be used for our basic HTTP authentication.
You will need to install an additional package to enable this function.
sudo apt-get install apache2-utils
Next, create the .htpasswd file.
sudo htpasswd -c /etc/phpmyadmin/.htpasswd <username>
Replace username with your own username. You will be prompted to enter and confirm a password for the username you entered. You can add additional users by running the command again.
Now, browse to the phpmyadmin site again and you will notice a Basic HTTP Auth login prior to loading the phpmyadmin login page. This adds an additional layer of security to your phpmyadmin configuration.
phpmyadmin is a terrific web-based database utility that I use every day. It works great, allows you to quickly make changes to the database model and when properly configured, is a great asset to developers. It needs to be hardened though, by multiple security best practice methods.
I recommend restricting by Basic Auth, restricting by IP address and of course, the default phpmyadmin login and authentication. This helps prevent authorized access to your database and phpmyadmin application.
Want to go a step further? Secure your phpmyadmin with a SSL certificate and encrypt everything.