Setting up phpMyAdmin should be an easy task. There used to be several pages to read when wanting to setup phpMyAdmin. Fortunately the guys over at phpMyAdmin brought it back to just one page. Though if you are not careful you can lock yourself out and have to redo the configuration or bump into other problems. This post will show you how to setup a full featured phpMyAdmin installation.

configuration storage

The configuration storage allows phpMyAdmin to have additional features. For a features list click here

  1. bookmarks
  2. relations with a graphical designer
  3. storing phpMyAdmin preferences (also for the User Interface) -- not really a features, but needs a database anyway
  4. comments on columns -- redundant since MySQL 4.1.x (has native column comments)
  5. SQL Query history
  6. Recently used table
  7. Change tracking
  8. Export as PDF

Installation#

phpMyAdmin comes packed with documentation, which can be found in the docs folder. Throughout these steps several of the documentation pages will be listed.

1. Copying the files

Grab the latest copy and just extract anywhere in your web-server document root.

2. Make a configuration directory

Reference: /phpMyAdmin/doc/html/setup.html#using-setup-script

cd phpMyAdmin
mkdir config
chmod o+rw config

3. Make a dummy configuration file

Go to: /phpMyAdmin/setup

When MySQL still has a blank root password go to Server configuration and check 'Allow logins without a password' otherwise specify the root password. Leave the rest to default.

New Server -> Save
Configuration file -> Save

You should now have a config.inc.php in the config directory

4. Move the configuration file

Reference: /phpMyAdmin/doc/html/setup.html#using-setup-script

mv config/config.inc.php .
chmod o-rw config.inc.php

5. Create the configuration storage tables

Go to: /phpMyAdmin

You should be able to login now. Hit the SQL tab and insert the SQL which you can find in /phpMyAdmin/examples/create_tables.sql

6. Creating the phpMyAdmin privileges

Reference: /phpMyAdmin/doc/html/setup.html#using-authentication-modes

replace pmapass with a strong password
optional: rename phpmyadmin database

GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
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 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
ON mysql.tables_priv TO 'pma'@'localhost';

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

7. Update the root password

When you have a blank root password phpMyAdmin will warn you about this security vulnerability. You can set a strong password with the following command:

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

8. Make a new configuration file

Now configure your configuration file to your liking. I would recommend using SSL (at Basic Settings) if it's available to you. In the Configuration storage tab you should set the following values:

  • Control user: pma
  • Control user password: the password you've chosen at step 6
  • Use the suggested values for Database name and tables.

9. Repeat step 4

Just put the real configuration in place and you're good to go!

10. Extra's

As security precaution it is advisable to remove the config and the setup directory

rm -rf config
rm -rf setup

Also the 1440 seconds (24 minutes) session timeout is kind of annoying in development and i could not find the option in the graphical setup panel. To set a session timeout of 8 hours edit the config.inc.php file and add:

$cfg['LoginCookieValidity'] = 3600 * 8; // Value in seconds

phpMyAdmin will now throw an warning about the garbage collection max life time being less then the cookie validaty. This will cause you getting logged out despite just setting a longer LoginCookieValidity. So we will have to adjust this in php.ini under the [Session] header.

session.gc_maxlifetime = 28800

This value is also in seconds. 28800 being equal to 3600 * 8.