Configure phpmyadmin advanced settings

THIS PAGE NEEDS UPDATING

When seeing the error while running phpMyAdmin "The phpMyAdmin configuration storage is not completely configured. Some extended features have been deactiviated. Find out why check here."

  1. Decide key values for the following setup
    • The name of the database to hold your phpMyAdmin settings myAdvancedPMAdb - typ. phpmyadmin
    • The name of the behind the scenes user of these settings myAdvPMAusername - typ. pma - or pmadbuser
  2. Create the myAdvancedPMAdb database
    1. Login to your phpMyAdmin as root via a browser
    2. Create a new database (or copy an existing) with the name you chose: myAdvancedPMAdb using "Collation"
  3. Create the myAdvPMAusername user
    1. Login to your phpMyAdmin as root via a browser
    2. Navigate to the db: mysql, and its table: user
    3. Copy and existing admin capable user and then change the username and the password
    4. For the password, there are two options:
      1. Use another tool or website to generate the encrypted version - then insert the value into the password field of the pma user
      2. Save the new pma user with an empty password field, and then, from the host system, enter mysql and grant privileges.
        • mysql> select user,password,host from mysql.user;
        • mysql> grant usage on *.* to 'myAdvPMAusername'@'yourHostNameAsSeenInPriorCmd' identified by 'clearTextPassword';
  4. Configure the import script ready to automatically create the tables of this database
    1. In Finder, navigate to where your phpMyAdmin is being served on your mac

      Typ. ~/yourloginname/Sites/phpmyadminTopDir/

    2. Navigate further down to the script we'll be using

      examples/

    3. If you are not using the default value of phpmyadmin for the database name, then edit the file (after making a backup copy) create_tables.sql

      Change the line:

      CREATE DATABASE IF NOT EXISTS `phpmyadmin`
      DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
      USE phpmyadmin;

      to read:

      CREATE DATABASE IF NOT EXISTS `myAdvancedPMAdb`
      DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
      USE myAdvancedPMAdb;
  5. Import the script to automatically create the tables of this database
    1. In phpMyAdmin, logged in as root, select your database myAdvancedPMAdb
    2. Select the tab: "Import"
    3. Click "Choose File" and navigate to the script you just edited create_tables.sql and click "Choose"
    4. Leave the other defaults like "Format: SQL", "SQL compatibility mode: None", "Check: Do not use auto_increment for zero values"
    5. Click "Go"
  6. While still on the database's home page, set the privileges for the tables
    1. Click "Add user"
    2. Enter the username: myAdvPMAusername
    3. Enter the password in the field and its related "Re-type" field
    4. Since the database is already created, click "Grant all privileges on database \"myAdvancedPMAdb\""
    5. Click "Go" (hidden in the lower right corner)

    The resultant query should look like this on success:

    CREATE USER 'myAdvPMAusername'@'%' IDENTIFIED BY  '***';
    
    GRANT USAGE ON * . * TO  'myAdvPMAusername'@'%' IDENTIFIED BY  '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    
    GRANT ALL PRIVILEGES ON  `myAdvancedPMAdb` . * TO  'myAdvPMAusername'@'%';
  7. Configure your config.ini.php to match the above settings:
    1. In Finder, navigate to where your phpMyAdmin is being served on your mac

      Typ. ~/yourloginname/Sites/phpmyadminTopDir/

    2. Open the file config.ini.php (after saving a backup?)
    3. Add the following items to file
      	/* User used to manipulate with storage */
      	$cfg['Servers'][$i]['controluser']     = 'myAdvPMAusername';
      	$cfg['Servers'][$i]['controlpass']     = 'myAdvPMAuserPW';
      
      	/* Storage database and tables */
      	$cfg['Servers'][$i]['pmadb']           = 'myAdvancedPMAdb';
      	$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]['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]['table_uiprefs']   = 'pma__table_uiprefs';
      
  8. Check that all is well

    Close any window from which you are logged into phpmyadmin, and then open a new window and login to phpmyadmin (as root)

    If you get the message "Connection for controluser as defined in your configuration failed.", use phpmyadmin to enter the "mysql" database's "user" table, and make sure your control user has all the privileges it needs.

    UPDATE  `mysql`.`user` SET  `Select_priv` =  'Y',`Insert_priv` =  'Y',`Update_priv` =  'Y',`Delete_priv` =  'Y',`Create_priv` =  'Y',`Drop_priv` =  'Y',`Reload_priv` =  'Y',`Shutdown_priv` =  'Y',`Process_priv` =  'Y',`File_priv` =  'Y',`Grant_priv` =  'Y',`References_priv` =  'Y',`Index_priv` =  'Y',`Alter_priv` =  'Y',`Show_db_priv` =  'Y',`Super_priv` =  'Y',`Create_tmp_table_priv` =  'Y',`Lock_tables_priv` =  'Y',`Execute_priv` =  'Y',`Repl_slave_priv` =  'Y',`Repl_client_priv` =  'Y' WHERE  `user`.`Host` =  'localhost' AND  `user`.`User` =  'pmadbuser';

    Also, make multiple entries for the myAdvPMAusername in the mysql.user table for each variation of the host id that may be used in the background - Ex. {"localhost","127.0.0.1","myMacNetworkName.local"}

    1. Login to MySQL from the command line (using the Terminal application)
      cd /usr/local/mysql/bin
      $ mysql --user=root -p mysql
      password:
    2. Verify what already exists as far as users etc.
      mysql> select host, user, password from mysql.user;
    3. Add a user entry for each possible hostname that might be used:
      mysql> grant create,insert,delete,update,select on myAdvancedPMAdb.* to 'myAdvPMAusername'@'localhost';
      mysql> set password for 'myAdvPMAusername'@'localhost' = password('myAdvPMAuserPW');
      mysql> grant create,insert,delete,update,select on myAdvancedPMAdb.* to 'myAdvPMAusername'@'127.0.0.1';
      mysql> set password for 'myAdvPMAusername'@'127.0.0.1' = password('myAdvPMAuserPW');
      mysql> grant create,insert,delete,update,select on myAdvancedPMAdb.* to 'myAdvPMAusername'@'myMacNetworkName.local';
      mysql> set password for 'myAdvPMAusername'@'myMacNetworkName.local' = password('myAdvPMAuserPW');
      mysql> flush privileges;
      mysql> exit;

    Much thanks to Php freaks forum for the help