(dv) 4.0 - Making It Better :: Web Accessible phpMyAdmin

  • This page was last modified on August 16, 2011, at 15:08.
The (mt) Community Wiki is a collaborative project. Any (mt) Media Temple customer or employee may contribute. Not all articles and/or content have been tested for accuracy by (mt) Media Temple.

For officially moderated and tested articles, be sure to visit our KnowledgeBase.

From (mt) Community Wiki

This is part of the (dv) 4.0 - Making It Better series. Please see the parent article for related articles and information on downloading the script that includes options to automate the instructions included in this article. This series of articles is not directly supported by (mt) Media Temple.

One of the nice things about the Plesk interface is that is has phpMyAdmin built into it. One of the sometimes frustrating things about this is that there is no way to access phpMyAdmin without going through Plesk. Sometimes it's simply a hassle to have to click through the interface, and sometimes it would be ideal to provide database access to a developer without providing them access to Plesk. The end-result of this guide is to install a new instance of phpMyAdmin (that even lets you choose which language you would prefer to use) which can be accessed at https://your_domain/.phpMyAdmin or https://your_ip_address/.phpMyAdmin

For more information on the settings that this guide uses, as well as a whole lot more, the official documentation for phpMyAdmin can be found at http://www.phpmyadmin.net/home_page/docs.php

This guide makes a few assumptions. If any of these conflict with settings already on your server, simply alter the respective names and/or directives in the commands included in this article:

  • A database named phpmyadmin_http will be created
  • A database user named pma_http will be created
  • An alias will be created for the apache server at /.phpMyAdmin
  • phpMyAdmin will automatically redirect to https for security
  1. A prerequisite for some commands below is the mkpasswd command, so the package that it comes included in needs installed: 
    yum -y install expect
  2. First, you will want to download phpMyAdmin to your server. For organizational purposes, it is good practice to use /usr/local/src. That way, you always know where to find the temperamental data/source code you use for new installations. So we can step into that directory: 
    cd /usr/local/src

    And then download and unpack phpMyAdmin: 

    wget http://sourceforge.net/projects/phpmyadmin/files%2FphpMyAdmin%2F3.4.1%2FphpMyAdmin-3.4.1-all-languages.tar.gz/download#

    tar zxf phpMyAdmin-3.4.1-all-languages.tar.gz
  3. If all that went smoothly, we'll get rid of the downloaded leftovers and move phpMyAdmin to where we'll eventually be using it: 
    rm -f phpMyAdmin-3.4.1-all-languages.tar.gz

    mv phpMyAdmin-3.4.1-all-languages /var/www/phpMyAdmin

    cd /var/www/phpMyAdmin
  4. We now need to create a database for phpMyAdmin to use, and a user to use that database. This process requires this user to have a password - so we'll create it as a randomly generated 21 character alpha-numeric string as part of this process: 
    pmaPass=`mkpasswd -l 21 -c 7 -C 7 -d 7 -s 0`

    mysql -u'admin' -p`cat /etc/psa/.psa.shadow` -e"create database phpmyadmin_http;"

    mysql -u'admin' -p`cat /etc/psa/.psa.shadow` -e"grant all privileges on phpmyadmin_http.* to pma_http@localhost identified by '$pmaPass';"

    mysql -u'admin' -p`cat /etc/psa/.psa.shadow` -e"grant all privileges on phpmyadmin_http.* to admin@localhost;"
  5. With the database now created, the phpMyAdmin tables now need imported to it. First, we comment out lines we do not need from the import script: 
    perl -0 -p -i -e 's/(CREATE DATABASE IF NOT EXISTS `phpmyadmin`\n)(\s*DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;\n)(USE phpmyadmin;)/-- \1-- \2-- \3/' scripts/create_tables.sql

    And then we import it into the new database: 

    mysql -u'admin' -p`cat /etc/psa/.psa.shadow` phpmyadmin_http -e"source scripts/create_tables.sql;"
  6. From here, a configuration file needs to be created based on all of this information. There are many, many more options than this (see the documentation link above), but the following should work for most users. Make sure to copy and paste this as a single command: 
    cat <<EOF > config.inc.php
<?php
\$cfg['blowfish_secret'] = '`mkpasswd -l 21 -c 7 -C 7 -d 7 -s 0`';
\$cfg['ForceSSL'] = true;
\$i = 0;
\$i++;


\$cfg['Servers'][\$i]['auth_type'] = 'cookie';
\$cfg['Servers'][\$i]['host'] = 'localhost';
\$cfg['Servers'][\$i]['connect_type'] = 'tcp';
\$cfg['Servers'][\$i]['compress'] = false;
\$cfg['Servers'][\$i]['extension'] = 'mysql';
\$cfg['Servers'][\$i]['AllowNoPassword'] = false;

\$cfg['Servers'][\$i]['controluser'] = 'pma_http';
\$cfg['Servers'][\$i]['controlpass'] = '$pmaPass';

\$cfg['Servers'][\$i]['pmadb'] = 'phpmyadmin_http';
\$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]['tracking'] = 'pma_tracking';
\$cfg['Servers'][\$i]['designer_coords'] = 'pma_designer_coords';
\$cfg['Servers'][\$i]['userconfig'] = 'pma_userconfig';
?>
EOF
  7. There is a 'installation wizard' tool that is included in phpMyAdmin, which is no longer needed since these steps take care of the setup process. For security purposes, web access should be removed from this folder: 
    chmod 200 setup
  8. Last, but not least, Apache needs to know how to find phpMyAdmin. To accomplish this, a new Alias will be created and loaded into the server via a restart: 
    echo "Alias /.phpMyAdmin /var/www/phpMyAdmin" > /etc/httpd/conf.d/phpmyadmin_http.conf
/etc/init.d/httpd restart
    9. All done. At this point, you should be able to add on /.phpMyAdmin to any of your server's domains or IP addresses when you type them in your web browser, and find the new phpMyAdmin login page. From here, you can log in with any database user to manage that user's database. You can also log in with your Plesk 'admin' user to manage any databases that Plesk is aware of.
Retrieved from "http://wiki.mediatemple.net/w/(dv)_4.0_-_Making_It_Better_::_Web_Accessible_phpMyAdmin"