Detailed explanation of phpMyAdmin multi-server configuration: Solve the problem of not displaying multiple database connections on the login interface

This tutorial aims to solve the problem that the phpMyAdmin login interface cannot display multiple database server options. By parsing the config.inc.php configuration file in detail, we will demonstrate how to use the officially recommended multi-host configuration method, use a loop structure to define multiple database connections, and ensure that all servers can be displayed normally and available for selection when phpMyAdmin login, thereby improving database management efficiency.

introduction

phpMyAdmin is a widely used MySQL/MariaDB database management tool. In daily development and operation and maintenance, we often need to manage multiple database servers, such as the database of the local development environment, the database in the Docker container, and the database of the remote production environment. However, many users will encounter the problem that the phpMyAdmin login interface only displays one server, or the configuration does not take effect when trying to configure multiple servers. This is usually caused by incorrect server definition in the config.inc.php configuration file.

This article will explore the correct method of multi-server configuration in phpMyAdmin. Based on the officially recommended configuration mode, it will guide you how to effectively manage multiple database connections.

Analysis of common configuration misunderstandings and problems

When configuring multiple servers, users usually try to manually increment the $i variable to define $cfg['Servers'][$i], or place the server configuration in an inappropriate conditional statement. For example, the following are common mistake attempts:

 // Incorrect configuration attempt example if (!empty($dbname)) {
    $cfg['Servers'][$i]['host'] = 'localhost';
    // ... Other configuration $i; // Increment within the conditional block may cause the second server to be not correctly recognized}

// The configuration of the second server // $i ; // If incremented here again, it may cause the index $cfg['Servers'][$i]['host'] = '172.18.0.1:3307';
// ... Other configurations

The problem with this configuration is:

1. Lifecycle and scope of $i variable : If you increment $i in a conditional statement or a specific logical block, it may cause its value to be discontinuous or not as expected in subsequent global configurations.
2. Configuration override or skip : Incorrect $i management may cause phpMyAdmin to recognize only the first or last defined server, or a blank page appears because the index of the configuration array is handled incorrectly.
3. Lack of unified management : When the number of servers increases, this manual maintenance becomes cumbersome and error-prone.

How to correctly configure multiple servers

phpMyAdmin official provides an elegant and robust multi-host configuration solution, which dynamically generates server configurations by looping through a list of hosts. This approach ensures that each server is correctly assigned an independent configuration index and is easy to scale and maintain.

Typically, you can find the main configuration file named config.inc.php in the installation directory of phpMyAdmin (e.g. /etc/phpmyadmin/ or phpmyadmin/libraries/). Please edit this file.

Sample configuration code

The following is an example of config.inc.php multi-server configuration based on the official recommended mode:

 <?php /*
 * Generated by phpMyAdmin
 * all config for servers
 */

declare(strict_types=1);

// Make sure the server index starts at 1, phpMyAdmin does not use $cfg[&#39;Servers&#39;][0]
$i = 0;

// Define a list of all database hosts that need to be managed// Format &#39;hostname:port&#39; or &#39;hostname&#39;
$hosts = [
    &#39;localhost&#39;, // Local MySQL server&#39;172.18.0.1:3307&#39;, // Docker container or other remote MySQL server// More servers can be added here // &#39;remote.database.com:3306&#39;,
];

// traverse the host list and configure phpMyAdmin server connection foreach foreach ($hosts as $host) {
    $i ; // Increment the server index every loop, ensuring that it starts from 1 and is continuous // Basic connection parameter $cfg[&#39;Servers&#39;][$i][&#39;host&#39;] = $host;
    $cfg[&#39;Servers&#39;][$i][&#39;port&#39;] = &#39;&#39;; // If the host name already contains a port, leave blank here $cfg[&#39;Servers&#39;][$i][&#39;socket&#39;] = &#39;&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;connect_type&#39;] = &#39;tcp&#39;; // Connection type, usually tcp
    $cfg[&#39;Servers&#39;][$i][&#39;extension&#39;] = &#39;mysqli&#39;; // It is recommended to use mysqli to extend $cfg[&#39;Servers&#39;][$i][&#39;compress&#39;] = false; // Whether to enable compression// Authentication method and user credentials $cfg[&#39;Servers&#39;][$i][&#39;auth_type&#39;] = &#39;cookie&#39;; // It is recommended to use cookie authentication, which is highly secure// If you use the &#39;config&#39; authentication type, you need to directly specify the user and password here// $cfg[&#39;Servers&#39;][$i][&#39;auth_type&#39;] = &#39;config&#39;;
    // $cfg[&#39;Servers&#39;][$i][&#39;user&#39;] = &#39;your_username&#39;;
    // $cfg[&#39;Servers&#39;][$i][&#39;password&#39;] = &#39;your_password&#39;;

    // Optional: Set an easy-to-recognize alias for each server $cfg[&#39;Servers&#39;][$i][&#39;verbose&#39;] = ($host === &#39;localhost&#39;) ? &#39;Local development database&#39; : &#39;Docker container database&#39;;

    // The control users and databases required for advanced functions of phpMyAdmin // These are internal functions used to store bookmarks, relationships, etc., and require a special user and database.
    // If you do not use these advanced features, you can leave it blank or comment out.
    $cfg[&#39;Servers&#39;][$i][&#39;controluser&#39;] = &#39;pma&#39;; // To control the user, you need to create $cfg[&#39;Servers&#39;][$i][&#39;controlpass&#39;] = &#39;pmapass&#39;; // To control the user password// phpMyAdmin controls the database name $cfg[&#39;Servers&#39;][$i][&#39;pmadb&#39;] = &#39;phpmyadmin&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;bookmarktable&#39;] = &#39;pma__bookmark&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;relation&#39;] = &#39;pma__relation&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;table_info&#39;] = &#39;pma__table_info&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;table_coords&#39;] = &#39;pma__table_coords&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;pdf_pages&#39;] = &#39;pma__pdf_pages&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;column_info&#39;] = &#39;pma__column_info&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;history&#39;] = &#39;pma__history&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;table_uiprefs&#39;] = &#39;pma__table_uiprefs&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;tracking&#39;] = &#39;pma__tracking&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;userconfig&#39;] = &#39;pma__userconfig&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;recent&#39;] = &#39;pma__recent&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;favorite&#39;] = &#39;pma__favorite&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;users&#39;] = &#39;pma__users&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;usergroups&#39;] = &#39;pma__usergroups&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;navigationhiding&#39;] = &#39;pma__navigationhiding&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;savedsearches&#39;] = &#39;pma__savedsearches&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;central_columns&#39;] = &#39;pma__central_columns&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;designer_settings&#39;] = &#39;pma__designer_settings&#39;;
    $cfg[&#39;Servers&#39;][$i][&#39;export_templates&#39;] = &#39;pma__export_templates&#39;;

    // Other optional configurations $cfg[&#39;Servers&#39;][$i][&#39;only_db&#39;] = &#39;&#39;; // Restrict this server to display only specific databases// $cfg[&#39;Servers&#39;][$i][&#39;AllowNoPassword&#39;] = true; // Allow password-free login, it is highly deprecated in production environments}

Configuration details

1. $i = 0; : Initialize the server index counter. phpMyAdmin's server array $cfg['Servers'] starts with index 1.
2. $hosts = [...] : Define an array containing all database host addresses. Each element can be in the form of 'hostname' or 'hostname:port'.
3. foreach ($hosts as $host) : Iterates over the $hosts array. In each loop:
   • $i ; : Increment $i to ensure that each server is assigned a unique continuous index starting from 1.
   • $cfg['Servers'][$i]['host'] = $host; : Sets the host address of the current server.
   • $cfg['Servers'][$i]['port'] = ''; : If the port is already included in the host name (such as 172.18.0.1:3307), then leave it blank; otherwise, if the port is not the default 3306, it needs to be specified explicitly here.
   • $cfg['Servers'][$i]['connect_type'] = 'tcp'; : Specify the connection type, usually TCP/IP.
   • $cfg['Servers'][$i]['extension'] = 'mysqli'; : It is recommended to use mysqli extension for better performance and security.
   • $cfg['Servers'][$i]['auth_type'] = 'cookie'; : It is highly recommended to use cookie authentication. This means that the user needs to enter a username and password every time he logs in.
     • config authentication type : If you choose 'config' authentication (such as the second server in the original question), you must provide $cfg['Servers'][$i]['user'] and $cfg['Servers'][$i]['password'] directly in the configuration. This method hardcodes credentials into configuration files, which are less secure and are especially not suitable for production environments.
   • $cfg['Servers'][$i]['verbose'] : Set an easy-to-recognize alias for each server, which will be displayed in the drop-down menu of the phpMyAdmin login interface.
   • $cfg['Servers'][$i]['controluser'] / $cfg['Servers'][$i]['controlpass'] / $cfg['Servers'][$i]['pmadb'] : These are the configurations required for advanced features of phpMyAdmin (such as bookmarks, relationship diagrams, etc.). You need to create a user in MySQL named pma (or another name you specified) and grant it the necessary permissions to the phpmyadmin (or another name you specified) database. If these advanced features are not required, you can not configure them.

Things to note

• Configuration file location : config.inc.php is usually located in the installation directory of phpMyAdmin or /etc/phpmyadmin/. Please make sure that you modify the correct configuration file.
• Permissions Issue : Ensure that web server users (such as www-data or apache) have read permissions to the config.inc.php file.
• Web server restart : After modifying config.inc.php, you sometimes need to restart the web server (such as Apache or Nginx) and the PHP-FPM service to ensure that the configuration takes effect.
• Security :
  • Always give priority to using cookie authentication types.
  • Avoid using config authentication types in production environments, because it stores database credentials plaintext in configuration files.
  • Set a strong password for the controller and restrict its permissions to phpmyadmin control the database.
• Development environments such as Valet : If you use Valet or other local development tools, the access address of phpMyAdmin may change from localhost/phpmyadmin to custom domain names such as phpmyadmin.test. This will not affect the configuration logic of multiple servers, but make sure you are accessing phpMyAdmin via the correct URL.
• Port and hostname : When the hostname contains a port (such as 172.18.0.1:3307), $cfg['Servers'][$i]['port'] can be left blank. If the hostname does not contain a port and the port is not the default 3306, $cfg['Servers'][$i]['port'] must be explicitly set.

Summarize

By adopting the above-mentioned configuration method based on foreach loops, you can manage multiple database servers clearly and efficiently in phpMyAdmin. This method not only solves the problem that the login interface does not display all servers, but also improves the readability and maintainability of the configuration. Following the guidance of this tutorial, you will be able to easily switch between different database environments, greatly improving your database management experience.

The above is the detailed content of Detailed explanation of phpMyAdmin multi-server configuration: Solve the problem of not displaying multiple database connections on the login interface. For more information, please follow other related articles on the PHP Chinese website!