Managing Multiple MySQL Servers From One phpMyAdmin Installation (Using SSL Encryption) - Page 2
On this page
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
- phpMyAdmin: http://www.phpmyadmin.net/
- MySQL: http://www.mysql.com/
About The Author
Falko Timme is the owner of 
Timme 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".
