Configuring phpMyAdmin

Here, we learn how to prepare and use the configuration file containing the parameters to connect to MySQL, and which can be customized as per our requirements.

Before configuring, we can rename the directory phpMyAdmin-3.4.5-all-languages to something like phpMyAdmin or something easier to remember. This way, we and our users can visit an easily remembered URL to start phpMyAdmin. On most servers, the directory part of URLs is case-sensitive so we should communicate the exact URL to our users. We can also use a symbolic link if our server supports this feature.

In the following examples, we will assume that the directory has been renamed to phpMyAdmin.

The config.inc.php file

This file contains valid PHP code, defining the majority of the parameters (expressed by PHP variables) that we can change to tune phpMyAdmin to our own needs. There are also normal PHP comments in it, and we can comment our changes.

Tip

Be careful not to add any blank line at the beginning or end of the file; this would hamper the execution of phpMyAdmin.

Note that phpMyAdmin looks for this file in the first level directory—the same one where index.php is located.

A config.sample.inc.php file is included, which can be copied and renamed to config.inc.php to act as a starting point. However, it is recommended that you use the web-based setup script (explained in this chapter) instead, for a more comfortable configuration interface.

There is another file—layout.inc.php—containing some configuration information. As phpMyAdmin offers theme management, this file contains the theme-specific colors and settings. There is one layout.inc.php per theme, located in themes/<themename>, for example, themes/pmahomme. We will cover modifying some of those parameters in Chapter 4.

Avoiding false error messages about permissions on config.inc.php

In its normal behavior, phpMyAdmin verifies that the permissions on this file do not allow everyone to modify it. This means that the file should not be writable by the world. Also, it displays a warning if the permissions are not correct. However, in some situations (for example a NTFS file system mounted on a non-Windows server), the permission detection fails. In these cases, you should set the following configuration parameter to false:

$cfg['CheckConfigurationPermissions'] = false;

The following sections explain various methods to add or change a parameter in config.inc.php.

Configuration principles

phpMyAdmin maintains no user accounts of its own; rather, it uses MySQL's privilege system.

Tip

It might now be the time to browse http://dev.mysql.com/doc/refman/5.1/en/privilege-system.html, to learn the basics about MySQL's privilege system.

With the lack of a configuration file, phpMyAdmin displays the cookie-based login panel by default (more details on this in Chapter 2, which explains that with the default configuration, it's not possible to log in with an empty password):

We can verify this fact by opening our browser and visiting http://www.mydomain.com/phpMyAdmin, and substituting the proper values for the domain part and the directory part.

If we are able to log in, it means that there is a MySQL server running on the same host as the web server (localhost), and we have just made a connection to it. However, not having created a configuration file means that we would not be able to manage other hosts through our installation of phpMyAdmin. Moreover, many advanced phpMyAdmin features (for example, query bookmarks, full-relational support, column transformation, and so on) would not be activated.

Note

The cookie-based authentication method uses Blowfish encryption for storing credentials in browser cookies. When no configuration file exists, a Blowfish secret key is generated and stored in session data, which can open the door to security issues. This is why the following warning message is displayed:

The configuration file now needs a secret passphrase (blowfish_secret)

At this point, we have the following choices:

• Use phpMyAdmin without a configuration file

• Use the web-based setup script to generate a config.inc.php file

• Create a config.inc.php file manually

These two latter options are presented in the following sections. We should note that, even if we use the web-based setup script, we should familiarize ourselves with the config.inc.php file format as the setup script does not cover all the possible configuration options.

Web-based setup script

The web-based setup mechanism is strongly recommended in order to avoid syntax errors that could result from the manual creation of the configuration file. Also, as this file must respect PHP's syntax, it's common for new users to experience problems in this phase of the installation.

Note

A warning is in order here: The current version has only a limited number of translation languages for the setup interface.

To access the setup script, we must visit http://www.mydomain.com/phpMyAdmin/setup. On the initial execution, the following screenshot appears:

In most cases, the icons beside each parameter point to the respective phpMyAdmin official wiki and to the documentation, providing you with more information about this parameter and its possible values.

If Show hidden messages appears and we click on this link, messages that might have been shown earlier are revealed.

There are three warnings here. As taking care of the first message will require more manipulations, we will handle it in a moment. The second warning encourages you to use the ForceSSL option, which would automatically switch to HTTPS when using phpMyAdmin (not related to the setup phase).

Let us cover the third message— Insecure connection. This message appears if we are accessing the web server over HTTP—an insecure protocol. As we are possibly going to input confidential information, such as the user name and password in the setup phase, it's recommended that you communicate over HTTPS at least for this phase. HTTPS uses SSL (Secure Socket Layer) to encrypt the communication and make eavesdropping on the line impossible. If our web server supports HTTPS, we can simply follow the proposed link. It will restart the setup process, this time over HTTPS.

The first warning tells us that phpMyAdmin did not find a writable directory with the name config. This is normal as it was not present in the downloaded kit. Also, as the directory is not yet there, we observe that the Save, Load, and Delete buttons in the interface are grey. In this config directory, we can:

• Save the working version of the configuration file during the setup process

• Load a previously prepared config.inc.php file

It's not absolutely necessary that we create this configuration directory, as we can download the config.inc.php file produced by the setup procedure to our client machine. We can then upload it to phpMyAdmin in the first-level directory through the same mechanism (say, FTP) that we used to upload phpMyAdmin. For this exercise, we will create this directory.

The principle here is that the web server must be able to write to this directory. There is more than one way to achieve this. Here is one that would work on a Linux server—adding read, write, and execute permissions for everyone on this directory.

cd phpMyAdmin
mkdir config
chmod 777 config

Having done that, we refresh the page in our browser and get a screen resembling the following screenshot:

In the configuration dialog, a drop-down menu permits the user to choose the proper end-of-line format. We should pick up the format that corresponds to the platform (UNIX / Linux or Windows) on which we will open config.inc.php file with a text editor later.

A single copy of phpMyAdmin can be used to manage many MySQL servers but for now we will define parameters describing our first MySQL server. We click on New server, and the server configuration panel is shown.

A complete explanation of these parameters can be found in the following sections of this chapter. For now, we notice that the setup process has detected that PHP supports the mysqli extension. Therefore, this is the one that is chosen by default. This extension is the programming library used by PHP to communicate with MySQL.

We assume that our MySQL server is located on localhost. Hence, we keep this value and all the proposed values intact, except for the following:

• Basic settings | Verbose name of this server—we enter my server

• Authentication | User for config auth —we remove root and leave it empty, as the default authentication type is cookie, which ignores a username entered here

You can see that any parameter changed from its default value appears in a different color. Moreover, a small arrow becomes available, the purpose of which is to restore a field to its default value. Hence, you can feel free to experiment with changing parameters, knowing that you can easily revert to the proposed value. At this point, the Basic settings panel should resemble the following screenshot:

We then click Save and are brought back to the Overview panel. This save operation did not yet save anything to disk; changes were saved in memory. We are warned that a Blowfish secret key was generated. However, we don't have to remember it, as it's not keyed in during login process but is used internally. For the curious, you can switch to the Features panel and click on the Security tab to see which secret key was generated. Let us get back to the Overview panel. Now, our setup process knows about one MySQL server, and there are links that enable us to Edit or Delete these server settings as shown in the following screenshot:

We can have a look at the generated configuration lines by using the Display button; then we can analyze these parameters using the explanations given in the Description of some configuration parameters section later in this chapter.

At this point, this configuration is still just in memory, so we need to save it. This is done through the Save button on the Overview panel. It saves config.inc.php in the special config directory that we created previously. This is a directory strictly used for configuration purposes. If, for any reason, it was not possible to create this config directory, you just have to download the file by clicking on the Download button and upload it to the web server directory where phpMyAdmin is installed.

The last step is to copy config.inc.php from the config directory to the top-level directory—the one that contains index.php. By copying this file, it becomes owned by the user instead of the web server, ensuring that further modifications are possible. This copy can be done through FTP or through commands such as:

cd config
cp config.inc.php ..

As a security measure and until the configuration steps are not completed, it's recommended that you change the permission on the config directory, for example with the following command:

chmod ugo-rwx config

This is to block any unauthorized reading and writing in this directory.

Other configuration parameters can be set with these web-based setup pages. To do so, we would have to:

1. Enable read and write access to the config directory.

2. Copy the config.inc.php there.

3. Ensure that read and write access are provided to this file for the web server.

4. Start the web-based setup tool.

After the configuration steps are done, it's recommended to completely remove the config directory, as this directory is only used by the web-based setup script. phpMyAdmin displays the following warning on the home page (refer to Chapter 3) if it detects that this directory still exists:

Directory config, which is used by the setup script, still exists in your phpMyAdmin directory. You should remove it once phpMyAdmin has been configured.

You are invited to peruse the remaining menus to get a sense of the available configuration possibilities, either now or later when we cover a related subject.

In order to keep this book's text lighter, we will only refer to the parameters' textual values in the following chapters.

Manually creating config.inc.php

We can create this text file from scratch using our favorite text editor, or by using config.sample.inc.php as a starting point. The exact procedure depends upon which client operating system we are using. We can refer to the next section for further information.

The default values for all possible configuration parameters that can be located inside config.inc.php are defined in libraries/config.default.php. We can take a look at this file to see the syntax used as well as further comments about configuration. See the important note about this file in the Upgrading phpMyAdmin section of this chapter.

Tips for editing config.inc.php on a Windows client

This file contains special characters (Unix-style end of lines). Hence, we must open it with a text editor that understands this format. If we use the wrong text editor, this file will be displayed with very long lines. The best choice is a standard PHP editor such as NetBeans or Zend Studio for Eclipse. Another choice would be WordPad, Metapad, or UltraEdit.

Every time the config.inc.php file is modified, it will have to be transferred again to our web space. This transfer is done through an FTP or an SFTP client. You have the option to use a standalone FTP/SFTP client such as FileZilla, or save directly through FTP/SFTP if your PHP editor supports this feature.

Description of some configuration parameters

In this chapter and the next one, we will concentrate on the parameters that deal with connection and authentication. Other parameters will be discussed in the chapters where the corresponding features are explained.

PmaAbsoluteUri

The first parameter we will look at is $cfg['PmaAbsoluteUri'] = '';

Sometimes, phpMyAdmin needs to send an HTTP Location header and must know the absolute URI of its installation point. Using an absolute URI in this case is required by RFC 2616, section 14.30.

In most cases, we can leave this one empty as phpMyAdmin tries to auto-detect the correct value. If we browse a table later, then edit a row, and click on Save, we will receive an error message from our browser saying, for example, This document does not exist. This means that the absolute URI that phpMyAdmin built in order to reach the intended page was wrong, indicating that we must manually put the correct value in this parameter.

For example, we would change it to:

$cfg['PmaAbsoluteUri'] = 'http://www.mydomain.com/phpMyAdmin/';

Server-specific sections

The next section of the file contains server-specific configurations, each starting with the following code snippet:

$i++;
$cfg['Servers'][$i]['host'] = '';

If we examine only the normal server parameters (other parameters are covered in the Installing phpMyAdmin configuration storage section of this chapter), we see a section that resembles the following code block for each server:

$i++;
$cfg['Servers'][$i]['host']          = '';
$cfg['Servers'][$i]['port']          = '';
$cfg['Servers'][$i]['socket']        = '';
$cfg['Servers'][$i]['connect_type']  = 'tcp';
$cfg['Servers'][$i]['extension']     = 'mysqli';
$cfg['Servers'][$i]['compress']      = FALSE;
$cfg['Servers'][$i]['controluser']   = '';
$cfg['Servers'][$i]['controlpass']   = '';
$cfg['Servers'][$i]['auth_type']     = 'cookie';
$cfg['Servers'][$i]['user']          = '';
$cfg['Servers'][$i]['password']      = '';
$cfg['Servers'][$i]['only_db']       = '';
$cfg['Servers'][$i]['hide_db']       = '';
$cfg['Servers'][$i]['verbose']       = '';

In this section, we have to enter in $cfg['Servers'][$i]['host'], the hostname or IP address of the MySQL server, for example, mysql.mydomain.com or localhost. If this server is running on a non-standard port or socket, we fill in the correct values in $cfg['Servers'][$i]['port'] or $cfg['Servers'][$i]['socket']. See the connect_type, sockets, and port section for more details about sockets.

The displayed server name inside phpMyAdmin's interface will be the one entered in'host' unless we enter a non-blank value in the following parameter, for example:

$cfg['Servers'][$i]['verbose'] = 'Test server';

This feature can thus be used to display a different server hostname as seen by the users on the login panel and on the main page, although the real server name can be seen as part of the user definition (for example, root@localhost) on the main page.

extension

The traditional-mechanism PHP uses to communicate with a MySQL server, as available in PHP before version 5, is the mysql extension. This extension is still available in PHP 5. However, a new extension called mysqli has been developed and should be preferred for PHP 5, due to its improved performance and its support of the full functionality of MySQL family 4.1.x. This extension is designed to work with MySQL version 4.1.3 and higher. As phpMyAdmin supports both extensions, we can choose either one for a particular server. We indicate the extension we want to use in $cfg['Servers'][$i]['extension']. The default value used is mysqli.

connect_type, socket, and port

Both the mysql and mysqli extensions automatically use a socket to connect to MySQL if the server is on localhost. Consider the following configuration:

$cfg['Servers'][$i]['host']         = 'localhost';
$cfg['Servers'][$i]['port']         = '';
$cfg['Servers'][$i]['socket']       = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension']    = 'mysql';

The default value for connect_type is tcp. However, the extension will use a socket because it concludes that this is more efficient as the host is localhost. So in this case, we can use tcp or socket as the connect_type. To force a real TCP connection, we can specify 127.0.0.1 instead of localhost in the host parameter. Because the socket parameter is empty, the extension will try the default socket. If this default socket, as defined in php.ini, does not correspond to the real socket assigned to the MySQL server, we have to put the socket name (for example, /tmp/mysql.sock) in $cfg['Servers'][$i]['socket'].

If the hostname is not localhost, a TCP connection will occur; in this case, on the special port 3307. However, leaving the port value empty would use the default 3306 port:

$cfg['Servers'][$i]['host']         = 'mysql.mydomain.com';
$cfg['Servers'][$i]['port']         = '3307';
$cfg['Servers'][$i]['socket']       = '';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['extension']    = 'mysql';

compress

The protocol used to communicate between PHP and MySQL allows a compressed mode. Using this mode provides better efficiency. To take advantage of this mode, simply specify:

$cfg['Servers'][$i]['compress'] = TRUE;

PersistentConnections

Another important parameter (which is not server-specific but applies to all server definitions) is $cfg['PersistentConnections']. For every server we connect to using the mysql extension, this parameter, when set to TRUE, instructs PHP to keep the connection to the MySQL server open. This speeds up the interaction between PHP and MySQL. However, it is set to FALSE by default in config.inc.php as persistent connections are often a cause of resource depletion on servers (you would find MySQL refusing new connections). For this reason, the option is not even available for the mysqli extension. Hence, setting it to TRUE here would have no effect if you are connecting with this extension.

controluser

Defining the control user has the following two purposes:

• On a MySQL server running with --skip-show-database, the control user permits the use of multi-user authentication even though servers running with this option are not commonly seen. This aspect is described in Chapter 2.

• On all versions of MySQL server, this user is necessary to be able to use the advanced features of phpMyAdmin.

For authentication purposes, controluser is a special user (the usual name we choose for it is pma) who has the rights to read some fields in the special mysql database (which contains all the user definitions). phpMyAdmin sends queries with this special controluser only for the specific needs of authentication, and not for normal operation. The commands to create the control user are available in phpMyAdmin's Documentation.html and may vary from one version to the other. This documentation contains the most current commands.

When our controluser is created in the MySQL server, we fill in the parameters as in the following example, replacing xxx with a suitably complex password:

$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'xxx';

Standard password guidelines apply here. Please refer to http://en.wikipedia.org/wiki/Password_strength for suggestions.