phpMyAdmin

This guide will describe how to setup phpMyAdmin to use a cookie/session based login system. The advantage of the cookie based logins is that you can get an exact MySQL error as to why you can't login (if you can't login).

Edit:

/var/www/html/phpMyAdmin/config.inc.php

Change:

$cfg['blowfish_secret'] = '';

to

$cfg['blowfish_secret'] = 'anyrandomtextyouwant';

and change:

$cfg['Servers'][$i]['auth_type']     = 'http';    // Authentication method (config, http or cookie based)?

to:

$cfg['Servers'][$i]['auth_type']     = 'cookie';    // Authentication method (config, http or cookie based)?

Close all browsers and try to connect again.

NOTE You may want to instead simply enable the one-click Single-SignOn for phpMyAdmin, which enables cookies by default.

One-Click (Single SignOn) Login to PHPMyAdmin

To enable one-click login for phpMyAdmin:

cd /usr/local/directadmin/
./directadmin set one_click_pma_login 1
service directadmin restart
cd custombuild
./build phpmyadmin

To the feature, go to:

User Level -> "MySQL Management" or "Databases" -> click "Manage" option/button next to any database name -> click "phpMyAdmin" on the far right

I want all access to /phpMyAdmin only be accessible through DirectAdmin

You can make /phpMyAdmin SSO-only accessible (no user/pass allowed) using:

cd /usr/local/directadmin/custombuild
./build set phpmyadmin_public no
./build phpmyadmin

such that any access to /phpMyAdmin manually would throw:

Access to phpMyAdmin is only allowed from control panel.

Related versions entry: One-Click login to any phpMyAdmin database from DirectAdmin (SKINS)open in new window

I cannot login to phpMyAdmin

If phpMyAdmin (PMA) is giving you a login error, there are few things to check to debug the issue.

1) Ensure you've created a database through DA first. If you do not create a database through DA, then the MySQL Users will not be added to MySQL, hence you cannot login. A database must first exist before you can login to phpMyAdmin:

User Level -> MySQL Management -> Create new Database

2) Test the user/pass combination through ssh. Login to ssh and type:

mysql -u<b>USERNAME</b> -p<b>PASSWORD</b>

where the user/pass values are directly after the -u and -p options without any spaces. This will need to work in order for phpMyAdmin to work. If this does work, but PMA does not, then reinstall PMA (see 4) below).

3) Test PMA with the da_admin account. You can get the login/pass from:

/usr/local/directadmin/conf/mysql.conf

This is the master DA MySQL account used for creating databases/users.

4) Re-install PMA via CustomBuild:

cd /usr/local/directadmin/custombuild
./build phpmyadmin

5) If step #3 works fine, but all other steps still result in a failure to login via PMA, then try setting up phpMyAdmin to use cookies instead of the httpd-auth login method. It may give a more detailed message as to why the login fails through apache, but works through ssh.

6) It's required that "localhost" resolves to 127.0.0.1. Check your /etc/hosts to make sure you see the line:

127.0.0.1    localhost

The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated.

If you want to get rid of this error in phpMyAdmin:

The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.

OR

 Your preferences will be saved for current session only. Storing them permanently requires phpMyAdmin configuration storage.

Save the following script, make it executable, and run it:

#!/bin/sh

DA_MYSQL=/usr/local/directadmin/conf/mysql.conf
if [ ! -s ${DA_MYSQL} ]; then
        echo "Cannot find ${DA_MYSQL} so cannot create PMA tables";
        exit 1;
fi

MYSQLUSER=`grep -m1 "^user=" ${DA_MYSQL} | cut -d= -f2`
MYSQLPASSWORD=`grep -m1 "^passwd=" ${DA_MYSQL} | cut -d= -f2`

PHPMYADMIN_USER=da_phpmyadmin
PHPMYADMIN_PASS=`perl -le'print map+(A..Z,a..z,0..9)[rand 62],0..15'`;

if [ `grep -m1 -c -e "^host=" ${DA_MYSQL}` -gt "0" ]; then
        MYSQLHOST=`grep -m1 "^host=" ${DA_MYSQL} | cut -d= -f2`
else
        MYSQLHOST=localhost
fi

MYSQL_ACCESS_HOST=localhost
if [ "$MYSQLHOST" != "localhost" ]; then
        #its a remote databsae, so connections would come from the server IP.
        if [ -s ${WORKDIR}/scripts/setup.txt ]; then
                MYSQL_ACCESS_HOST=`cat ${WORKDIR}/scripts/setup.txt | grep -m1 -e '^ip=' | cut -d= -f2`
        fi

        #if we're on a LAN, then things change.
        if [ -s ${DA_MYSQL} ]; then
                if [ `grep -m1 -c -e "^access_host=" ${DA_MYSQL}` = "0" ]; then
                        MYSQL_ACCESS_HOST=`grep -m1 "^access_host=" ${DA_MYSQL} | cut -d= -f2`
                fi
        fi
fi

#end initMySQL()


SQL=/var/www/html/phpMyAdmin/examples/create_tables.sql
if [ ! -s ${SQL} ]; then
        SQL=/var/www/html/phpMyAdmin/sql/create_tables.sql
        if [ ! -s ${SQL} ]; then
                echo "Cannot find $SQL so cannot create PMA tables";
                exit 2;
        fi
fi

mysql -u${MYSQLUSER} -p${MYSQLPASSWORD} < ${SQL}
mysql -e "GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,LOCK TABLES,INDEX ON phpmyadmin.* TO '${PHPMYADMIN_USER}'@'${MYSQL_ACCESS_HOST}' IDENTIFIED BY '${PHPMYADMIN_PASS}';" --host=${MYSQLHOST} --user=${MYSQLUSER} --password=${MYSQLPASSWORD}
mysql -e "SET PASSWORD FOR '${PHPMYADMIN_USER}'@'${MYSQL_ACCESS_HOST}' = PASSWORD('${PHPMYADMIN_PASS}');" --host=${MYSQLHOST} --user=${MYSQLUSER} --password=${MYSQLPASSWORD}
mysql -e "FLUSH PRIVILEGES;" --host=${MYSQLHOST} --user=${MYSQLUSER} --password=${MYSQLPASSWORD}

CONFIG=/usr/local/directadmin/custombuild/custom/phpmyadmin/config.inc.php

#if dir doesn't exist, create.. then if file doesn't exist, cp from regular pma location..

[[ -d /usr/local/directadmin/custombuild/custom/phpmyadmin/ ]] || mkdir -p /usr/local/directadmin/custombuild/custom/phpmyadmin/
[[ -f /usr/local/directadmin/custombuild/custom/phpmyadmin/config.inc.php  ]] ||  cp /var/www/html/phpMyAdmin/config.inc.php /usr/local/directadmin/custombuild/custom/phpmyadmin/config.inc.php

#heredoc to enable features by appending to the new custom config file

cat <<-'EOF' >> $CONFIG
$cfg['Servers'][$i]['controlhost'] = '';
$cfg['Servers'][$i]['controluser'] = '';
$cfg['Servers'][$i]['controlpass'] = '';
$cfg['Servers'][$i]['pmadb'] = '';
$cfg['Servers'][$i]['bookmarktable'] = '';
$cfg['Servers'][$i]['relation'] = '';
$cfg['Servers'][$i]['relation'] = '';
$cfg['Servers'][$i]['table_info'] = '';
$cfg['Servers'][$i]['table_coords'] = '';
$cfg['Servers'][$i]['pdf_pages'] = '';
$cfg['Servers'][$i]['column_info'] = '';
$cfg['Servers'][$i]['history'] = '';
$cfg['Servers'][$i]['recent'] = '';
$cfg['Servers'][$i]['favorite'] = '';
$cfg['Servers'][$i]['table_uiprefs'] = '';
$cfg['Servers'][$i]['tracking'] = '';
$cfg['Servers'][$i]['userconfig'] = '';
$cfg['Servers'][$i]['users'] = '';
$cfg['Servers'][$i]['usergroups'] = '';
$cfg['Servers'][$i]['navigationhiding'] = '';
$cfg['Servers'][$i]['savedsearches'] = '';
$cfg['Servers'][$i]['central_columns'] = '';
$cfg['Servers'][$i]['designer_settings'] = '';
$cfg['Servers'][$i]['export_templates'] = '';
$cfg['Servers'][$i]['tracking_version_auto_create'] = false;
$cfg['Servers'][$i]['tracking_default_statements']
    = 'CREATE TABLE,ALTER TABLE,DROP TABLE,RENAME TABLE,CREATE INDEX,' .
      'DROP INDEX,INSERT,UPDATE,DELETE,TRUNCATE,REPLACE,CREATE VIEW,' .
      'ALTER VIEW,DROP VIEW,CREATE DATABASE,ALTER DATABASE,DROP DATABASE';
$cfg['Servers'][$i]['tracking_add_drop_view'] = true;
$cfg['Servers'][$i]['tracking_add_drop_table'] = true;
$cfg['Servers'][$i]['tracking_add_drop_database'] = true;
$cfg['Export']['odt_relation'] = true;
$cfg['Export']['latex_relation'] = true;
$cfg['Export']['sql_relation'] = false;
EOF

#set the permissions for security:
chown webapps:apache ${CONFIG}
chmod 440 ${CONFIG}


#Build with CB
cd /usr/local/directadmin/custombuild/
./build phpmyadmin

exit 0;

This script will set up the da_phpmyadmin user/pass, and configure the config.inc.php file.

You must log out of phpMyAdmin, and log back in again to get it to re-read the configs.

Using custom configs for PhpMyAdmin

If you've got custom options enabled for any of PhpMyAdmin, then you'll need a way to maintain those customizations between updates by CustomBuild.

To do that, you'd simply copy your custom config to the relevant path. The paths are as follows:

#phpMyAdmin
/usr/local/directadmin/custombuild/custom/phpmyadmin/config.inc.php
/usr/local/directadmin/custombuild/custom/phpmyadmin/.htaccess
/usr/local/directadmin/custombuild/custom/phpmyadmin/themes

If the relevant config exists, then the CustomBuild script will copy it to the relevant path of the newly installed script, somewhere in /var/www/html/*.

How to customize the php.ini settings for webapps

DirectAdmin supports multiple versions of PHP as set in the CustomBuild options.conf file:

grep php._ /usr/local/directadmin/custombuild/options.conf

The php1_release version is what is used for webapps, such as PHPMyAdmin:

grep php1_ /usr/local/directadmin/custombuild/options.conf

So, technically, you could edit the php1_release version's php.ini file located here (replace XX with the actual php version, eg 80 for php80):

/usr/local/phpXX/lib/php.ini

However, there is a better way if you'd prefer to only raise the value for webapps and not for sites that aren't already using their own customized php.ini values and are using the default php1_release php version. Instead, you could edit this file (replace XX accordingly with the appropriate version):

/usr/local/phpXX/lib/php.conf.d/50-webapps.ini

This file has the following PATH specification:

[PATH=/var/www/html]

Which will apply those changes to only apps within that path, such as webapps like PHPMyAdmin or Roundcube.

So, edit that file with the new settings.

For example, if you wanted these increased to 128M:

printf "memory_limit = 128M\npost_max_size = 128M\nupload_max_filesize = 128M\n" >> /usr/local/phpXX/lib/php.conf.d/50-webapps.ini

Note that this is just an example and generally you want the memory_limit to be larger than post_max_size, and post_max_size larger than upload_max_filesize.

To check that the new value is set properly, log into PHPMyAdmin and check what value is shown for "Max upload size".

Alternatively, create a phpinfo.php page in /var/www/html/ and check it via lynx (replace IP with your server's actual IP):

lynx --dump http://IP/phpinfo.php | grep "memory_limit\|post_max\|upload_max"

Note, that if you are performing large dumps or imports, you may experience the following error and may also need to increase the max_execution_time in the same php.ini file:

This request takes too long to process, it is timed out by the server. If it should not be timed out, please contact administrator of this web site to increase 'Connection Timeout'.

Last Updated: