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 usernameThat’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/config.inc.php
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