Digital Memo All begin with 0 & 1

12Oct/100

Connecting to multiple database on phpMyAdmin

phpMyAdmin, which often comes together with LAMP, is a great MySQL database management console in most of the time.

I personally refer phpMyAdmin over MySQL GUI Tools and its successor, MySQL Workbench, as phpMyAdmin consume CPU Usage, and has much more features then the other.

In this article, I am going to show how to fully utilize phpMyAdmin by adding multiple MySQL server to it.

By default, phpmyadmin is connecting to localhost. I am going to add another MySQL server which is located remotely.

Before proceeds, you need to have phpmyadmin already running in your machine.

  1. Navigate to phpmyadmin directory, and open config.inc.php with your favourite editor. You should see the code similiar to the one shown below.
  2. 
    /*
     * First server
     */
    $i++;
    
    /* Authentication type and info */
    $cfg['Servers'][$i]['auth_type']            = 'config';
    $cfg['Servers'][$i]['user']                 = 'root';
    $cfg['Servers'][$i]['password']             = 'pass1';
    $cfg['Servers'][$i]['AllowNoPassword']      = true;
    
    /* Server parameters */
    $cfg['Servers'][$i]['host']             = 'localhost';
    $cfg['Servers'][$i]['connect_type']     = 'tcp';
    $cfg['Servers'][$i]['compress']         = false;
    
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    
    /* User for advanced features */
    $cfg['Servers'][$i]['controluser'] = 'pma';
    $cfg['Servers'][$i]['controlpass'] = '';
    
    /* Advanced phpMyAdmin features */
    $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';
    
  3. Copy the code above, and paste it right under the highlighted code.
  4. Next, modify the code: change the comment wordings "First Server" to "Second Server", and the parameters user, password, host. The final codes, starting from "Servers configuration" to "End of Servers configuration" should look as follow:
    /*
     * Servers configuration
     */
    $i = 0;
    
    /*
     * First server
     */
    $i++;
    
    /* Authentication type and info */
    $cfg['Servers'][$i]['auth_type']            = 'config';
    $cfg['Servers'][$i]['user']                 = 'root';
    $cfg['Servers'][$i]['password']             = 'pass1';
    $cfg['Servers'][$i]['AllowNoPassword']      = true;
    
    /* Server parameters */
    $cfg['Servers'][$i]['host']             = 'localhost';
    $cfg['Servers'][$i]['connect_type']     = 'tcp';
    $cfg['Servers'][$i]['compress']         = false;
    
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    
    /* User for advanced features */
    $cfg['Servers'][$i]['controluser'] = 'pma';
    $cfg['Servers'][$i]['controlpass'] = '';
    
    /* Advanced phpMyAdmin features */
    $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';
    
    /*
     * Second server
     */
    $i++;
    
    /* Authentication type and info */
    $cfg['Servers'][$i]['auth_type']            = 'config';
    $cfg['Servers'][$i]['user']                 = 'root';
    $cfg['Servers'][$i]['password']             = 'pass2';
    $cfg['Servers'][$i]['AllowNoPassword']      = true;
    
    /* Server parameters */
    $cfg['Servers'][$i]['host']             = 'remotesql';
    $cfg['Servers'][$i]['connect_type']     = 'tcp';
    $cfg['Servers'][$i]['compress']         = false;
    
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    
    /* User for advanced features */
    $cfg['Servers'][$i]['controluser'] = 'pma';
    $cfg['Servers'][$i]['controlpass'] = '';
    
    /* Advanced phpMyAdmin features */
    $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';
    
    /*
     * End of servers configuration
     */
    

  5. You are done! Save your config.inc.php file. Now open your phpmyadmin application in browser.
  6. At the very first page of phpmyadmin, under "Actions" column, you should see the database selection option as below. Just choose your working mysql server and start to work on it with phpmyadmin!

    phpmyadmin multiple db selection

* Take note on different phpmyadmin version, which might results in different $cfg keys.
pixelstats trackingpixel
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment


eight + 7 =

No trackbacks yet.