How to enable phpMyAdmin’s Designer in 3 steps

I recently discovered a neat feature in phpMyAdmin called designer. Designer is a great tool for viewing databases and managing the relationships between them.

I recently needed designer for another handy feature but the first thing I had to tackle, how do I get this to appear? If you have designer enabled, then when you open your database in phpMyAdmin, you should see something like this

phpMyAdmin Designer Menu

If this isn’t visible, then you need to do some legwork. Read on to quickly enable this feature.

Step 1 – Update the phpMyAdmin configuration

First thing’s first, you need to access the phpMyAdmin configuration file called config.inc.php. On a server with cPanel / WHM you’ll find it in /usr/local/cpanel/base/3rdparty/phpMyAdmin
Next, edit the file and either uncomment or add the following lines (should be around line 30):

/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'pmapass';

/* 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'

I’d suggest changing pmapass to something else.

Step 2 – Create the phpMyAdmin tables

Now that we’ve updated the configuration, we need to create the tables that phpMyAdmin phpMyAdmin will use to manage the design view. In the phpMyAdmin/exammples directory there is a file named create_tables.sql. You need to import the SQL in this file into mySql. The easiest way to do this is just copy and paste the contents of the file into phpMyAdmin. If opening this file is troublesome, you can copy and paste from the latest online version.

Once this is done, we have one more job to do.

Step 3 – Create a new phpMyAdmin user

For phpMyAdmin to manage all of the goings on, it needs a control user to make the necessary changes

Now that we have everything in place, we just need to give access to these tables. In the SQL box in phpMyAdmin, run the following SQL. Again, note that the user has the correct password:

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';

All done

That’s all you need to do. Now that the configuration is changed you need to clear the browser cookies. Just close the browser and access phpMyAdmin again. When you access the table, you should see the designer option appear in the top menu.

Of course, I couldn’t have done this without the help from DaveRandom and Carrie Kendall for having the answers online for me to interpret.

No related posts.