Debian: phpMyAdmin

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement.

Step 1: Install Packages

This installation expects apache to be already running

# apt install install php php-cgi php-mysqli php-pear php-mbstring php-gettext libapache2-mod-php php-common php-phpseclib php-mysql

Step 2: Install phpMyAdmin

Create the default location to install PhpMyAdmin Software and configuration directory

# mkdir /var/www/html/phpMyAdmin
# mkdir /etc/phpmyadmin

create a temporary directory for phpmyadmin

# mkdir -p /var/lib/phpmyadmin/tmp
# chown www-data:www-data /var/lib/phpmyadmin/tmp
# chmod 750 /var/lib/phpmyadmin/tmp

Download the latest .tar.gz from the main website https://www.phpmyadmin.net/ and uncompress to this directory

# tar xvf phpMyAdmin-latest-all-languages.tar.gz --strip-components=1 -C /var/www/html/phpmyadmin
# chown -R www-data:www-data /var/www/html/phpmyadmin

Create a default configuration fileDownload the latest .tar.gz from the main website https://www.phpmyadmin.net/ and uncompress to this directory

# cp /var/www/html/phpmyadmin/config.sample.inc.php /etc/phpmyadmin/config.inc.php

Step 3: Configure phpMyAdmin

Make sure MariaDB is properly configured fand load the SQL schema

# mysql -p < /usr/share/phpmyadmin/sql/create_tables.sql

Create and configure an appropriate user and permissions to this database:

# mysql -p 
GRANT ALL PRIVILEGES ON pma.* TO 'pma'@'%' IDENTIFIED BY 'password';

edit /etc/phpmyadmin/config.inc.php to define the various properties needed

Generate blowfish secret key (using for example https://www.motorsportdiesel.com/tools/blowfish-salt/pma/) and define it in the configuration file:

/**
* This is needed for cookie based authentication to encrypt password in
* cookie. Needs to be 32 chars long.
*/
$cfg['blowfish_secret'] = 'NEW BLOWFISH KEY';

Configure the rest of the parameters:

$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/**
 * phpMyAdmin configuration storage settings.
 */

/* User used to manipulate with storage */
$cfg['Servers'][$i]['controlhost'] = 'localhost';
//$cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'password';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

If you use SSL, define the ssl certificates and options, remember to use as $cfg[‘Servers’][$i][‘host’] = ‘db.ops.cyberfront.org’; the SSL host name, else verification will fail.

$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['ssl_key'] = '/etc/mysql/ssl/mysql-client.key';
$cfg['Servers'][$i]['ssl_cert'] = '/etc/mysql/ssl/mysql-client.crt';
$cfg['Servers'][$i]['ssl_ca'] = '/etc/ssl/certs/Cyberfront-CA.pem';

Step 4: Apache Configuration

Create a new file /etc/phpmyadmin/config.inc.php with the phpMyadmin settings:

Alias /phpmyadmin /usr/share/phpmyadmin

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

    <IfModule mod_php5.c>
        <IfModule mod_mime.c>
            AddType application/x-httpd-php .php
        </IfModule>
        <FilesMatch ".+\.php$">
            SetHandler application/x-httpd-php
        </FilesMatch>

        php_value include_path .
        php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
        php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/php/php-php-gettext/:/usr/share/javascript/:/usr/share/php/tcpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/
        php_admin_value mbstring.func_overload 0
    </IfModule>
    <IfModule mod_php.c>
        <IfModule mod_mime.c>
            AddType application/x-httpd-php .php
        </IfModule>
        <FilesMatch ".+\.php$">
            SetHandler application/x-httpd-php
        </FilesMatch>

        php_value include_path .
        php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
        php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/php/php-php-gettext/:/usr/share/javascript/:/usr/share/php/tcpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/
        php_admin_value mbstring.func_overload 0
    </IfModule>

</Directory>

# Authorize for setup
<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
<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>

Restart the apache web service

# systemctl restart apache2

test phpmyadmin by visiting the url you defined

http://<yourserver>.domain.com/phpmyadmin