phpMyAdmin – Beginners guide to Digital Ocean

Ernest Marcinko Hosting, Tutorials Leave a Comment

This step is also optional, but I prefer using phpMyAdmin for database management. So let’s take a break from the file system and install phpMyAdmin real quick.

Getting the database root password

Before installing you will need the root database password. You should be able to see it if you type in the following command:

nano /etc/motd.tail

Lost password

If for some reason you can’t find your password you need to generate a new one, because you can’t just retrieve it (because it’s hashed).
The following commads should help you out:

service mysql stop
echo "UPDATE mysql.user SET Password = PASSWORD('mypassword') WHERE User = 'root';" > ~/mysql-reset.sql
echo "FLUSH PRIVILEGES;" >> ~/mysql-reset.sql
chown mysql:mysql ~/mysql-reset.sql
mysqld_safe --init-file=~/mysql-reset.sql & rm ~/mysql-reset.sql
# confirm it works
mysql --user=root --password=mypassword
# quit mysql
> quit;
service mysql stop
service mysql start

Don’t forget to change the mypassword phrase to your desired new password.

Installing phpMyAdmin

There is an extensive guide on how to install phpMyAdmin on Digital Ocean’s support site. It helped me a lot, so I take a few core points from that guide. If you prefer, you can read that guide instead (or as well), but the purpose is the same.

Let’s get started:

sudo apt-get update
sudo apt-get install phpmyadmin

Before running an install command it’s always advised to check for updates with the sudo apt-get update command. Otherwise you might install an outdated version of the package.

After hitting return on the second command, you will be prompted to select a few options and answer questions:

  • At server selection choose apache2 by hitting the SPACEBAR then TAB then ENTER
  • When asked to use dbconfig-common type in yes and hit ENTER
  • Type in your database root password when you are asked to
  • Then type in a password for the phpMyadmin application – this password should be complicated, you don’t want anyone to access your database manager

After the successful install you will need to install and enable the php5-mcrypt extension and restart the apache service.

sudo php5enmod mcrypt
sudo service apache2 restart

Nice. Now if you check the http://your_server_ip/phpmyadmin url, you should be able to log in to your phpMyAdmin application.

Increaseing phpMyAdmin security with .htaccess

This part is optional, however I strongly recommend adding another layer of security to your database manager. Since the .htaccess is not enabled, we need to edit the hosts file for the phpMyAdmin application:

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

After opening, you should see this:

# phpMyAdmin default Apache configuration

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
        Options FollowSymLinks
        DirectoryIndex index.php

and you need to add an additional line after DirectoryIndex index.php. So it should look like this:

# phpMyAdmin default Apache configuration

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
        Options FollowSymLinks
        DirectoryIndex index.php
        AllowOverride All

Save the file and restart the apache service:

sudo service apache2 restart
Adding the .htaccess file

Before we can use a password, we need to install another package:

sudo apt-get install apache2-utils

This contains the htpasswd utility.
Let’s now create the .htaccess file:

sudo nano /usr/share/phpmyadmin/.htaccess

The nano application launches, add the following to the empty file:

AuthType Basic
AuthName "Restricted Files"
AuthUserFile /etc/phpmyadmin/.htpasswd
Require valid-user

This will ensure that an authentication is required in order to proceed.
As a final step, let’s create the .htpasswd file:

sudo htpasswd -c /etc/phpmyadmin/.htpasswd username

That’s it. Try to access your phpMyAdmin directory again, you will be prompted to type in the username and password combination before accessing the phpMyAdmin application.

Possible Issues & Solutions

phpmyadmin.pma_table_uiprefs doesn’t exist

Apparently this is a known bug, which can occur on fresh phpMyAdmin installation, when trying to access database tables. The solution is to open shell and to the following:

sudo nano /etc/phpmyadmin/

Ok, now hit CTRL + W for searching and immediately after CTRL + T for jumping to a line number. Type in 88. Nano should jump to line 88, where you should see the following lines:

$cfg['Servers'][$i]['table_uiprefs'] = 'pma_table_uiprefs';
$cfg['Servers'][$i]['designer_coords'] = 'pma_designer_coords';
$cfg['Servers'][$i]['tracking'] = 'pma_tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';
$cfg['Servers'][$i]['recent'] = 'pma_recent';

Change the pma_ prefixes to pma__ (double underline) like this:

$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';

Save the file and restart apache:

sudo service apache2 restart


<< Chapter #4      Chapter #6 >>

Leave a Reply

Your email address will not be published.