[I was pleasantly surprised to find the classic web-based interface to MySQL - phpMyAdmin - at #mysqlconf 2011. Though their booth was rather spartan, staffed by one of two developers in North America (alas, no travel comps from O'Reilly), phpMyAdmin's Designer View proves to be a totally neat tidbit to take home. See below for a quick screenshot!]

phpMyAdmin comes with some more visual gui goodness that is not enabled by default. This includes a MySQL Workbench-like table relations view, and pdf gen. Enabling them is documented in a rather roundabout method on their wiki, so here’s a step-by-step tutorial for taking phpMyAdmin … (finally) to the 21st century! o.O

Note, for this tutorial: I am using MySQL 5.0.92-community and phpMyAdmin via WHM/cPanel running on CentOS

N.B. All commands below should be done with root or superuser, and not one particular user’s phpMyAdmin installation.

  1. Create a control user named pma – replace pmapass below with a good password, then copy and paste the SQL into the SQL form field on phpMyAdmin
    GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
        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';
  2. Create the table structure (and phpmyadmin global db) – You will find a create_tables.sql file via ls in the path to your phpmyadmin scripts. For CentOS, this is @ /usr/local/cpanel/base/3rdparty/phpMyAdmin/scripts … Execute this sql, or copy and paste it and put it in the SQL form field in phpMyAdmin.
  3. Enable usage of db phpmyadmin in config.inc.php
    Replace pmapass below with the password of the pma user you created above. You can use vi (for example) to edit this file. i to insert, delete button to delete, :wq to save and quit

    $cfg['Servers'][$i]['controluser'] = 'pma';
    $cfg['Servers'][$i]['controlpass'] = 'pmapass';
    $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]['designer_coords'] = 'pma_designer_coords';

Designer View shows MySQL Workbench-like interface in phpMyAdmin 3 and higher

Tagged with: