Installing phpMyAdmin

phpMyAdmin is a great tool for administrating your database. At least it is a lot easier than using a simple mysql prompt.

I'll assume that PHP/Apache and MySQL is installed, configured and running on your server.

Installation

cd /usr/ports/databases/phpmyadmin
make install clean distclean

To make phpMyAdmin available through your web site, you can add the following to /usr/local/etc/apache2/httpd.conf:

Alias /phpmyadmin/ "/usr/local/www/phpMyAdmin/"

You could also make a soft link, pointing at the phpMyAdmin directory from your website folder:

The following assumes that your homepage is in a folder under the www/ directory.

cd /usr/local/www/domain.tld/
ln -s /usr/local/www/phpMyAdmin/ phpmyadmin

But before you can use phpMyAdmin you need to do a little configuring:

Configuration

Since phpMyAdmin is a tool for administrating your database, you must create a user for phpMyAdmin in MySQL.:

Replace password with one of your own choice:

mysql -u root -p

Enter password: root password here



mysql> GRANT USAGE ON mysql.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT (Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, 
       Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, 
       Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv,
       Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, 
       Repl_client_priv) ON mysql.user TO 'phpmyadmin'@'localhost';
mysql> GRANT SELECT ON mysql.db TO 'phpmyadmin'@'localhost';
mysql> GRANT SELECT ON mysql.host TO 'phpmyadmin'@'localhost';
mysql> GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) 
       ON mysql.tables_priv TO 'phpmyadmin'@'localhost';
mysql> FLUSH PRIVILEGES;

Next you cd into the folder where phpMyAdmin is installed and open the configuration file. The following describes the changes I've made to the default configuration. :

cd /usr/local/www/phpMyAdmin/
vim config.inc.php

$i = 0;
$i++;

$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['controluser'] = 'phpmyadmin';
$cfg['Servers'][$i]['controlpass'] = 'password';
$cfg['Servers'][$i]['auth_type'] = 'http';
$cfg['Servers'][$i]['user'] = '';

Replace password with the password you've chosen for your phpmyadmin user. In earlier versions the control user was necessary to enable HTTP an cookie authentication. Today it is only necessary to have a controluser if you want to be able to use the new 'linked-tables infrastructure' features.

Security

It is not recommended to use the 'phpmyadmin','root' and other system accounts when working on your databases via phpMyAdmin. Only use 'root' when you want to do things that only this user have access to.

You can disable root login by setting $cfg['Servers'][$i]['AllowRoot'] = FALSE;

But root is probably not the only mysql user you want to prevent from logging in to phpmyadmin. It is a good idea to prevent login with all usernames created for use only by the system (such as postfix, squirrelmail etc...). Fortunately phpmyadmin has the AllowDeny order, and AllowDeny rules settings, which you can use to exclude any user:

// Host authentication order, leave blank to not use
$cfg['Servers'][$i]['AllowDeny']['order'] = 'deny,allow';

// Host authentication rules, leave blank for defaults
$cfg['Servers'][$i]['AllowDeny']['rules'] = array('deny postfix from all',
                                                  'deny spamassassin from all',
                                                  'deny squirrelmail from all');

Note: This will not prevent root or squirrelmail to use mysql... it only prevents someone from using these usernames with phpmyadmin.

Linked-tables infrastructure

This is an exiting new feature of phpmyadmin which I personally look forward to try out. The initial setup is easy. Basically you just go into the scripts library and run the *.sql file which will create the tables for you.

Note: You MUST have a control user in config.inc.php (descriped at the top of this page).

The script for creating the tables is found in /usr/local/www/phpMyAdmin/scripts. There are two different scripts, one for mysql 4.1.2 and above, and one for older mysql versions. Since I have installed mysql 5.0 I will use the 4.1.2+ script which is create_tables_mysql_4_1_2+.sql:

CORRECT THIS LINE IN THE SCRIPT IF YOUR CONTROL USER IS NOT NAMED 'pma':

--
-- Privileges
--
GRANT SELECT, INSERT, DELETE, UPDATE ON `phpmyadmin`.* TO
'pma'@localhost;

Since my controls user is named phpmyadmin, I will put this in instead of pma.

The script will create a database called phpmyadmin containing the necessary tables. This is how you execute it:

cd /usr/local/www/phpMyAdmin
mysql < create_tables_mysql_4_1_2+.sql -u root -p