Setting up MySQL and PostgreSQL web interfaces

10-30-2017 Update: How to upgrade to (or install) phpMyAdmin 4.7.4: (video shows 4.5.4)

I upgraded my system recently and the new best way is to install is with Composer. See https://docs.phpmyadmin.net/en/latest/setup.html#installing-using-composer for all the details, its my original source. Also note that I have handy instructions about how to better install Composer on your machine back a few pages in this guide on the Web Server Preparations page.

If you installed the phpMyAdmin the Ubuntu way as I showed in the video below, uninstall it before installing with composer. If your doing a fresh install, you need to make a directory like /var/www/html/phpmyadmin with your login ID as the owner using chown; if you're upgrading, just make yourself the owner of the current phpmyadmin directory because it's already there. If you're doing a new install, or even if you plan on hosting separate sites with other friends/users, it's better to have your phpmyadmin directory as a sub-directory of your website's main folder. If you're upgrading and don't want to lose the existing protected web directories level of security that I showed at the 13:45 time mark (and detailed below under "Protected Web Directories layer of security"), you have to move the ".htaccess" and ".htpasswd" files to somewhere temporarily then move them back after you're all done.

The new best way to install phpMyAdmin (if you installed Composer like I wrote about on the Web Server Preparations page), is going to be similar to this:

• php ~/bin/composer create-project phpmyadmin/phpmyadmin /var/www/html/phpmyadmin

The screen messages at the end of a "php ~/bin/composer..." command will show a set of suggested install's. If the suggestion is of the form creator/package-name, like:

• symfony/cache suggests installing symfony/polyfill-apcu (For using ApcuAdapter on HHVM)

where symfony/polyfill-apcu is the polyfill-apcu package created by symfony then from inside the directory of that main software "/usr/share/phpmyadmin" or "/var/www/html/phpmyadmin" you can install them with the commands:

• php ~/bin/composer require symfony/polyfill-apcu
• php ~/bin/composer require symfony/event-dispatcher
• ...and so on ending with ...
• php ~/bin/composer update

You're nearly done. Before you can run phpMyAdmin as a webpage, you may need to do an Apache linking. If it's a sub-directory of your website, no worry's about altering Apache files, just after your ".com" put "/phpmyadmin/setup" and go. Because I was just upgrading when I first did this, the phpMyAdmin 4.7.4 that I installed didn't have a Apache.conf file but my web-server needed to know where it was, so I added info to one of my main servers configuration files; a-site-I-made.com.conf in /etc/apache2/sites-available like so:

• ## START phpMyAdmin default Apache configuration
• Alias /phpmyadmin /usr/share/phpmyadmin
• 
  
• <Directory /usr/share/phpmyadmin>
• Options SymLinksIfOwnerMatch
• DirectoryIndex index.php
• </Directory>
• 
  
• ## Authorize for setup and remove after phpMyAdmin is working
• <Directory /usr/share/phpmyadmin/setup>
• <IfModule mod_authz_core.c>
• <IfModule mod_authn_file.c>
• AuthType Basic
• AuthName "phpMyAdmin Setup"
• AuthUserFile /etc/phpmyadmin/htpasswd.setup
• </IfModule>
• Require valid-user
• </IfModule>
• </Directory>
• 
  
• ## Disallow web access to directories that don't need it
• ## uncomment the following 9 lines after setup of yoursite.com/phpmyadmin/setup
• #<Directory /usr/share/phpmyadmin/templates>
• # Require all denied
• #</Directory>
• #<Directory /usr/share/phpmyadmin/libraries>
• # Require all denied
• #</Directory>
• #<Directory /usr/share/phpmyadmin/setup/lib>
• # Require all denied
• #</Directory>
• ## END phpMyAdmin default Apache configuration

For me, that went right above the </VirtualHost> tag in the file. I'm not sure if the Authorize for setup section needs to be there anymore, but if you finish up below with the security stuff, you'll definitely delete it later. If you are doing a first time install in some website root directory, you don't need to do that whole Apache editing thing, especially if you are using the /phpmyadmin/setup to finish installing.

Restart that Apache server and you're now ready to open your-site.com/phpmyadmin/setup page. When the Setup first launched I saw a complaint like "Bzip2 compression and decompression requires functions (bzopen, bzcompress) which are unavailable on this system." This was remedied by installing the bzip2 program with sudo apt install ... or whatever similar method you like. (Special note 5/23/2018: Ubuntu changed something so installing php7.0-bz2 is what's needed now) The setup program wouldn't recognize that it was installed till I rebooted my server then went back to the your-site.com/phpmyadmin/setup page. I spent a lot of time determining what settings to change for my specific purpose, and you should too.

After that you downloading the config.inc.php file made by the setup page, then you move it into the /var/www/html/phpmyadmin folder (or wherever you put your phpmyadmin files), you'll need to make an appropriate MySQL user account to log into the phpmyadmin page because the latest MySQL in Ubuntu is blocking root from remote login's. Also, if you don't like the way phpMyAdmin runs, delete the config.inc.php file and make a better one with setup.

Another great quirk since phpMyAdmin 4.5.4 is the new version want's a higher security Blowfish passphrase. It may have been automatically made while you were running the setup, but if you see an error message about that, ... make up 32 random characters and put them in the file /usr/share/phpmyadmin/config.inc.php in the $cfg['blowfish_secret'] = ''; statement right between the '' marks. If you don't trust yourself to make a random string the right length, try the linux command "pwgen 32 1"; 32 is the number of characters, and 1 is the quantity of 32 character outputs.

Finish up with these suggestions; https://docs.phpmyadmin.net/en/latest/setup.html#securing-your-phpmyadmin-installation

• If while reading the punch-list there you wonder where the "display_errors" variable is, look in your three different php.ini files that I discussed on the page Install Your Distro to Build Your Base Server in subheading "PHP Configuration Screw-up for PHP 7.0+:".
• If you delete the test and setup directories like instructed, be sure to remove the erroneous directory reference in that Apache .conf file mentioned above; look for <Directory /usr/share/phpmyadmin/setup/lib> Require all denied </Directory> and delete it cause /setup/lib will be gone too. Also delete that section called # Authorize for setup, then reload Apache.

For more information about MySQL go to the source: https://www.mysql.com/why-mysql/white-papers/whats-new-mysql-5-7/.

For more information about PostgreSQL go to the source: https://www.postgresql.org/about/