Managing Multiple MySQL Servers From One phpMyAdmin Installation (Using SSL Encryption) - Page 2

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Tue, 2012-09-18 18:25. ::

3 Configuring phpMyAdmin

local.example.com:

Before we proceed with configuring phpMyAdmin, let's first check if we can really connect from local.example.com to the MySQL server on remote.example.com (through sn SSL connection):

mysql --ssl-ca=/etc/mysql/newcerts/ca-cert.pem --ssl-cert=/etc/mysql/newcerts/client-cert.pem --ssl-key=/etc/mysql/newcerts/client-key.pem -h remote.example.com -u root -p

You will be asked for the password, and if all goes well, you will be granted access:

root@local:/etc/mysql/newcerts# mysql --ssl-ca=/etc/mysql/newcerts/ca-cert.pem --ssl-cert=/etc/mysql/newcerts/client-cert.pem --ssl-key=/etc/mysql/newcerts/client-key.pem -h remote.example.com -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 101
Server version: 5.5.24-8-log (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Type

quit;

to leave the MySQL shell.

Now we come to the part where we configure phpMyAdmin - I assume that you can already use phpMyAdmin to connect to the local MySQL server, and now we want to configure it so that we can connect to remote.example.com as well using SSL (on the phpMyAdmin login screen, there will be a dropdown menu from which you can select the server that you want to connect to).

Before phpMyAdmin can use SSL to talk to the remote MySQL server, we need to modify its sources a bit - open mysqli.dbi.lib.php (on Debian and Ubuntu, it's /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php if you have installed phpMyAdmin through apt):

vi /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php

Find the following section...

[...]
    /* Optionally enable SSL */
    if ($GLOBALS['cfg']['Server']['ssl'] && defined('MYSQLI_CLIENT_SSL')) {
        $client_flags |= MYSQLI_CLIENT_SSL;
    }
[...]

... and modify it as follows:

[...]
    /* Optionally enable SSL */
    if ($GLOBALS['cfg']['Server']['ssl'] && defined('MYSQLI_CLIENT_SSL')) {
        mysqli_ssl_set($link, $GLOBALS['cfg']['Server']['key'], $GLOBALS['cfg']['Server']['cert'], $GLOBALS['cfg']['Server']['ca'], $GLOBALS['cfg']['Server']['capath'], $GLOBALS['cfg']['Server']['cipher']);
        $client_flags |= MYSQLI_CLIENT_SSL;
    }
[...]

Next open config.inc.php - if you are on Debian/Ubuntu and have installed phpMyAdmin through apt, it's /etc/phpmyadmin/config.inc.php:

vi /etc/phpmyadmin/config.inc.php

Find the following section:

[...]
/* Configure according to dbconfig-common if enabled */
if (!empty($dbname)) {
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    if (empty($dbserver)) $dbserver = 'localhost';
    $cfg['Servers'][$i]['host'] = $dbserver;

    if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }
    //$cfg['Servers'][$i]['compress'] = false;
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    /* Optional: User for advanced features */
    $cfg['Servers'][$i]['controluser'] = $dbuser;
    $cfg['Servers'][$i]['controlpass'] = $dbpass;
    /* Optional: Advanced phpMyAdmin features */
    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $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';
    $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
    $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}
[...]

Add the following section below it for localhost (= local.example.com):

[...]
/* localhost.example.com */
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* 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';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;
[...]

(In the $cfg['Servers'][$i]['hide_db'] line you can specify all databases that you do not want to show up in phpMyAdmin - like information_schema, performance_schema, and test.)

And for remote.example.com, add the following section:

[...]
/* remote.example.com */
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'remote.example.com';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['key'] = '/etc/mysql/newcerts/client-key.pem';
$cfg['Servers'][$i]['cert'] = '/etc/mysql/newcerts/client-cert.pem';
$cfg['Servers'][$i]['ca'] = '/etc/mysql/newcerts/ca-cert.pem';
$cfg['Servers'][$i]['capath'] = NULL;
$cfg['Servers'][$i]['cipher'] = NULL;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;
[...]

Make sure you use mysqli in $cfg['Servers'][$i]['extension'], set $cfg['Servers'][$i]['ssl'] to true and specify the SSL files as shown. My complete servers section looks as follows:

[...]
/* Configure according to dbconfig-common if enabled */
if (!empty($dbname)) {
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    if (empty($dbserver)) $dbserver = 'localhost';
    $cfg['Servers'][$i]['host'] = $dbserver;

    if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }
    //$cfg['Servers'][$i]['compress'] = false;
    /* Select mysqli if your server has it */
    $cfg['Servers'][$i]['extension'] = 'mysqli';
    /* Optional: User for advanced features */
    $cfg['Servers'][$i]['controluser'] = $dbuser;
    $cfg['Servers'][$i]['controlpass'] = $dbpass;
    /* Optional: Advanced phpMyAdmin features */
    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $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';
    $cfg['Servers'][$i]['tracking'] = 'pma_tracking';
    $cfg['Servers'][$i]['userconfig'] = 'pma_userconfig';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}

/* localhost.example.com */
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* 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';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;

/* remote.example.com */
/* Authentication type */
$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
$cfg['Servers'][$i]['host'] = 'remote.example.com';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['key'] = '/etc/mysql/newcerts/client-key.pem';
$cfg['Servers'][$i]['cert'] = '/etc/mysql/newcerts/client-cert.pem';
$cfg['Servers'][$i]['ca'] = '/etc/mysql/newcerts/ca-cert.pem';
$cfg['Servers'][$i]['capath'] = NULL;
$cfg['Servers'][$i]['cipher'] = NULL;
/* Select mysqli if your server has it */
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['hide_db'] = '(information_schema|performance_schema|test)';
$i++;

/* End of servers configuration
 */
[...]

That's it - on the phpMyAdmin login screen, there should now be a dropdown menu from which you can select the MySQL server you want to connect to (localhost or remote.example.com), and if you select a remote MySQL server (like remote.example.com), phpMyAdmin will connect to it through an SSL connection.

 

4 Links

 

About The Author

Falko Timme is the owner of nginx WebhostingTimme Hosting (ultra-fast nginx web hosting). He is the lead maintainer of HowtoForge (since 2005) and one of the core developers of ISPConfig (since 2000). He has also contributed to the O'Reilly book "Linux System Administration".


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by N (not registered) on Thu, 2014-01-23 15:04.

The `ls` output as shown, reveal world-readable keys - and certificates.  What are your thoughts on ownership and groups of keys and certificates, when combining them with phpmyadmin and mysql ? 

My first - untested - guess would be that on the mysql server machine the key and certificates will be owned and readable for the mysql user and group, but no one else. 

On the client machine it is a bit more tricky since the webserving user (i.e. www-data on debian alike systems) should be able to read them, but perhaps other users may use them, too.