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.
The configuration storage allows phpMyAdmin to have additional features. For a features list click here
- relations with a graphical designer
- storing phpMyAdmin preferences (also for the User Interface) -- not really a features, but needs a database anyway
- comments on columns -- redundant since MySQL 4.1.x (has native column comments)
- SQL Query history
- Recently used table
- Change tracking
- Export as PDF
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
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
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
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!
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.