Sunday, November 10, 2019

Debian Buster: install and configure phpmyadmin

Required Apache2, Mysql/MariaDB,  and PHP 7.x

There is no phpmyadmin package in Debian 10 Buster Repository. You need to install it manually.

  1. Create folder /usr/share/phpmyadmin/
  2. Download phpmyadmin from https://www.phpmyadmin.net/downloads/ and extract into  /usr/share/phpmyadmin/. Note: phpMyAdmin version 4.9.1.
  3. Create folder /etc/phpmyadmin/
  4. Create a new config file from configuration using file config.sample.inc.php
    # cp /usr/share/phpmyadmin/config.sample.inc.php /etc/phpmyadmin/config.inc.php
  5. Create folder /var/lib/phpmyadmin/tmp
    # mkdir /var/lib/phpmyadmin/tmp
  6. Edit /etc/phpmyadmin/config.inc.php
    $cfg['blowfish_secret'] = 'ABCDEabcde1234567890ABCDEfghij12'; // random 32 characters
    ...
    /**
     * Directories for saving/loading files from server
     */
    $cfg['UploadDir'] = '/var/lib/phpmyadmin/tmp';
    $cfg['SaveDir'] = '/var/lib/phpmyadmin/tmp';
  7. Create apache configuration file for phpmyadmin
    # touch /etc/apache2/conf-available/phpmyadmin.conf
  8. Edit /etc/apache2/conf-available/phpmyadmin.conf, copy paste these lines.
    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>
  9. Enabling your phpmyadmin config in apache2
    # a2enconf phpmyadmin
    Note to disable # a2disconf phpmyadmin
  10. Reload/restart your apache2
    # systemctl reload apache2

Remove folder /usr/share/phpmyadmin/test/ and /usr/share/phpmyadmin/setup/. You can access your phpmyadmin using http://localhost/phpmyadmin.

If your mysql root (with password) can not login with error:
mysqli_real_connect(): (HY000/1698): Access denied for user 'root'@'localhost', do these:

  1. using cli connect to mysql
    # mysql -u root -p
    Enter password:
  2. Select mysql database
     > use mysql;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Database changed
  3. Change root plugin from unix_socket to mysql_native_password
    > update user set plugin='mysql_native_password' where user='root';
    Query OK, 1 row affected (1.372 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
  4. restart your mysql
    # systemctl restart mysql

If something goes wrong after you change unix_socket to mysql_native_password, revert it back.

Stop mysql server

# systemctl stop mysql

Start your mysql using mysqld_safe

# mysqld_safe --skip-grant-tables

In other terminal connect to mysql using root user

# mysql -u root
MariaDB [(none)]> user mysql;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'user mysql' at line 1
MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set plugin='unix_socket' where user='root';
\q

Now, You may kill your terminal where mysqld_safe running and start your mysql service normally.

References:

  1. https://docs.phpmyadmin.net/en/latest/setup.html
  2. https://salsa.debian.org/phpmyadmin-team/phpmyadmin/blob/master/debian/README.Debian
  3. https://computingforgeeks.com/install-phpmyadmin-with-apache-on-debian-10-buster/
  4. https://docs.phpmyadmin.net/en/latest/config.html#config
  5. https://askubuntu.com/questions/998920/1698-access-denied-for-user-rootlocalhost-mysql-5-7-and-ubuntu-16-04

No comments:

Post a Comment