Enabling bookmarks and other advanced features in phpMyAdmin

Created: 3 January 2014 (Updated 28 February 2014)

My Up and Running with phpMyAdmin course on lynda.com covers several features, such as internal relations between tables and bookmarking queries, that depend on phpMyAdmin's configuration storage being enabled. Whether configuration storage is enabled depends on how you installed phpMyAdmin. For example, it's enabled by default in XAMPP, but not in MAMP. The following instructions explain how to enable configuration storage in phpMyAdmin, including an extra step required for MAMP.

Basic procedure

Enabling configuration storage in phpMyAdmin involves the following basic steps:

  1. Import the phpmyadmin database.
  2. Set up a pma user account for configuration storage.
  3. Set the permissions for the pma user account.
  4. Update config.inc.php in phpMyAdmin.
  5. Log out of phpMyAdmin and log in again.

Importing the phpmyadmin database

The phpmyadmin database stores details of table relations, bookmarks, history, and so on. Setting up the database simply involves importing an .sql file.

  1. Launch phpMyAdmin, make sure the home screen is selected, and click the Import tab.

  2. Click the Browse button in the File to Import section, and navigate to the folder where the phpmyadmin files are located. In MAMP, this is /Applications/MAMP/bin/phpmyadmin.

  3. Open the examples folder, and select create_tables.sql.

  4. Click Go at the bottom of the Import tab to import the phpmyadmin database structure.

Creating the pma user account

phpMyAdmin requires a dedicated user account for the phpmyadmin database. You can call the account whatever you like, but these instructions use pma.

  1. Click the Users tab at the top of the phpMyAdmin screen.

  2. Click Add user.

  3. Use the following values in Login Information:
    • User name: pma
    • Host: localhost
    • Password: secret (choose your own password)
  4. Click Go at the bottom of the page to create the user account.

Setting the permissions for the pma account

The permissions for the pma account are complicated, so it's easier to execute a series of SQL statements rather than use the phpMyAdmin interface to set them.

  1. Click the SQL tab at the top of the phpMyAdmin screen.

  2. Copy the following series of SQL queries, and paste them into the SQL field in phpMyAdmin. The queries assume you are using pma as the name of the user account. If you have chosen a different name, replace pma in each GRANT query with the name you're using.

    GRANT USAGE ON mysql.* TO 'pma'@'localhost'; 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';

  3. Click Go to execute the queries. They can be executed simultaneously because each query ends with a semicolon. phpMyAdmin should report that MySQL returned an empty result.

Update the phpMyAdmin configuration file

Before the advanced features can be used, you need to add the details of the pma user and the phpmyadmin database to the phpMyAdmin configuration file, config.inc.php.

  1. You can either edit the existing version of config.inc.php, which is in the main phpmyadmin folder (/Applications/MAMP/bin/phpMyAdmin), or copy the following script, and use it to replace config.inc.php.

    <?php /* * This is needed for cookie based authentication to encrypt password in * cookie */ $cfg['blowfish_secret'] = 'a8b7c6d'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */ /* * Servers configuration */ $i = 0; /* * First server */ $i++; /* Authentication type */ $cfg['Servers'][$i]['auth_type'] = 'config'; $cfg['Servers'][$i]['user'] = 'root'; $cfg['Servers'][$i]['password'] = 'root'; /* Server parameters */ $cfg['Servers'][$i]['host'] = 'localhost'; $cfg['Servers'][$i]['connect_type'] = 'tcp'; $cfg['Servers'][$i]['compress'] = false; /* Select mysql if your server does not have mysqli */ $cfg['Servers'][$i]['extension'] = 'mysqli'; $cfg['Servers'][$i]['AllowNoPassword'] = false; /* * phpMyAdmin configuration storage settings. */ /* User account for configuration storage */ $cfg['Servers'][$i]['controluser'] = 'pma'; $cfg['Servers'][$i]['controlpass'] = 'secret'; /* Storage database and tables */ $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin'; $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]['table_uiprefs'] = 'pma__table_uiprefs'; $cfg['Servers'][$i]['tracking'] = 'pma__tracking'; $cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords'; $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig'; $cfg['Servers'][$i]['recent'] = 'pma__recent'; $cfg['Servers'][$i]['users'] = 'pma__users'; $cfg['Servers'][$i]['usergroups'] = 'pma__usergroups'; $cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding'; /* * End of servers configuration */ ?>

  2. The preceding script uses the MAMP default root password for MySQL (root):

    $cfg['Servers'][$i]['password'] = 'root';

    It also uses secret as the password for the pma user account:

    $cfg['Servers'][$i]['controlpass'] = 'secret';

    Change both values as appropriate.

  3. If you decide to edit the existing version of config.inc.php, you need to set the values for phpMyAdmin configuration storage settings as shown in the last two sections of the preceding script.

  4. If you're using MAMP Pro, you also need to make a copy of the updated config.inc.php, and use it to replace the version of config.inc.php located at /Library/Application Support/appsolute/MAMP PRO/phpmyadmin.

Reload the configuration

Before the changes take effect, you need to reload the MySQL privileges and the phpMyAdmin configuration. The simple way to do this is to stop MySQL and restart the server. Then close your browser, and relaunch phpMyAdmin. You should now have access to the advanced features of phpMyAdmin.

Other Tutorials

Programming tutorials

Articles on Adobe.com

Over the years, I have contributed a large number of articles to the Adobe Developer Connection and Community publishing. Most of the articles are now in the Adobe archive because they refer to old versions of Dreamweaver. But the following articles are not Dreamweaver-specific and are still relevant.

Books & Videos by David Powers

PHP SolutionsBeginning CSS3Dreamweaver CS6: Learn by VideoDreamweaver CS5.5: Learn by VideoDreamweaver CS5.5 for MobileDreamweaver CS5 with PHP