Mastering phpMyAdmin 3.3.x for Effective MySQL Management

The Web has evolved! In the last few years the Web has changed dramatically. In its infancy, the Web was a medium used mainly to convey static information ("Look, my home-page is on the Web!"). Now, large parts of the Web carry information that is dynamically generated by application programs on which enterprises, and even individuals, rely for their intranets and public websites.

Because of the clear benefits of databases better accessibility and structuring of information web applications are mostly database driven. While the frontend is usually a well-known (and quickly deployed) web browser, there is a database system at the backend. Application programs provide the interface between the browser and the database.

Those who are not operating a database-driven website are not using the medium to its fullest capability. Also, they could be lagging behind competitors who have made the switch. So, it is not a question of whether we should implement a database-driven site, but rather about when and how to implement it.

Why web applications? Because they improve the user experience and involve users in the process by opening up possibilities such as:

Nowadays, WWW might stand for World Wide Wave a big wave that profoundly modifies the way developers think about user interface, data presentation, and most of all, the way data reaches users and comes back to the data center.

When we look at the web applications platforms currently offered by host providers, we will see that most prevalent is the PHP/MySQL combination.

Well-supported by their respective homesites http://www.php.net and http://www.mysql.com this duo has enabled developers to build a lot of ready-made open source web applications and, most importantly, enabled in-house developers to quickly put solid web solutions in place.

MySQL, which is mostly compliant with the SQL:2003 standard, is a database system well known for its speed, robustness, and a small connection overhead. This is important in a web context where pages must be served as quickly as possible.

PHP, usually installed as a module on the web server, is a popular scripting language in which applications are written to communicate with MySQL (or other database systems) on the backend and browsers on the frontend. Ironically, the acronym's significance has evolved along with the evolution of the Web, from Personal HomePage to Professional HomePage to its current recursive definition: PHP: Hypertext Processor. An explanation of the successive name changes can be seen in PHP's source code at http://svn.php.net/viewvc/archived/php3/trunk/CHANGES?r1=5246&r2=5459. Available on millions of web domains, PHP drives its own wave of quickly-developing applications.

phpMyAdmin (see the official home page at http://www.phpmyadmin.net) is a web application written in PHP; it contains (like most web applications) XHTML, CSS, and JavaScript client code. This application provides a complete web interface for administering MySQL databases, and is widely recognized as the leading application in this field. Being open source since its birth, it has enjoyed support from numerous developers and translators worldwide (being translated into 58 languages at the time of writing this book). The project is currently hosted at Sourceforge.net and developed using their facilities by the phpMyAdmin team.

Host providers everywhere are showing their trust in phpMyAdmin by installing it on their servers. The popular cPanel (a website control application) interfaces with phpMyAdmin. In addition, we can install our own copy of phpMyAdmin inside our webspace, as long as our provider respects the minimum requirements (see the System requirements section later in this chapter).

The goal of phpMyAdmin is to offer the complete web-based management of MySQL servers and data, and to keep up with the evolution of MySQL and web standards. While the product is always evolving, it supports all standard operations, along with extra features.

The development team constantly fine-tunes the product based on the reported bugs and requested features, releasing new versions regularly.

phpMyAdmin offers features that cover basic MySQL database and table operations. It also has an internal relational system that maintains metadata to support advanced features. Finally, system administrators can manage users and privileges from phpMyAdmin. It is important to note that phpMyAdmin's choice of available operations depends on the rights the user has on a specific MySQL server.

Some host providers offer an integrated web panel where we can manage accounts, including MySQL accounts, and also a file manager that can be used to upload web content. Depending on this, the mechanism that we use to transfer phpMyAdmin source files to our webspace may vary. We will need some specific information (listed below) before starting the installation:

The up-to-date requirements for a specific phpMyAdmin version are always stated in the accompanying Documentation.html file. For phpMyAdmin 3.3, the minimum PHP version required is PHP 5.2 with session support and the Standard PHP Library (SPL). Moreover, the web server must have access to a MySQL server (version 5.0 or later) either locally or on a remote machine. It is strongly recommended that the PHP mcrypt extension be present for improved performance in cookie authentication mode (more on this in Chapter 2, Configuring Authentication and Security). In fact, on a 64-bit server, this extension is required.

On the browser side, cookie support must be activated, whatever authentication mode we use.

There are various files available in the Download section of http://www.phpmyadmin.net. There might be more than one version offered here, and it is always a good idea to download the latest stable version. We only need to download one file, which works regardless of the platform (browser, web server, MySQL, or PHP version). For version 3.3, there are two groups of files english and all-languages. If we need only the English interface, we can download a file whose name contains "english" for example, phpMyAdmin-3.3.2-english.zip. On the other hand, if we have the need for at least one other language, choosing all-languages would be appropriate.

If we are using a server supporting only PHP4 for which the PHP team has discontinued support since December 31, 2007 the latest stable version of phpMyAdmin is not a good choice for download. I recommend using version 2.11.x, which is the latest branch that supports PHP4.

The files offered have various extensions: .zip, .tar.bz2, .tar.gz, .7z. Download a file having an extension for which you have the corresponding extractor. In the Windows world, .zip is the most universal file format, although the files are bigger than .gz or .bz2 files (which are common in the Linux/Unix world). The .7z extension denotes a 7-Zip file, which is a format that achieves a higher compression ratio than the other formats offered an extractor is available at http://www.7-zip.org. In the following examples, we will assume that the chosen file was phpMyAdmin-3.3.2-all-languages.zip.

After clicking on the appropriate file, the nearest mirror site will be chosen by Sourceforge.net. The file will start to download, and we can save it on our computer.

The next step depends on the platform you are using. The following sections detail the procedures for some common platforms. You may proceed directly to the relevant section.

tar -xzvf phpMyAdmin-3.3.2-all-languages.tar.gz

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

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

In versions before 2.8.0, a generic config.inc.php file was included in the downloaded kit. Since 2.8.0, this file is no longer present in the directory structure. Since version 2.9.0, a config.sample.inc.php file is included, and this 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 that contains some configuration information. Because phpMyAdmin offers theme management, this file contains the theme-specific colors and settings. There is one layout.inc.php file per theme, located in themes/themename, for example, themes/original. We will cover modifying some of those parameters in Chapter 4, Taking First Steps, under the Customizing the browse mode section.

$cfg['CheckConfigurationPermissions'] = false;

phpMyAdmin's behavior, given that no configuration file is present, has changed in version 3.1.0. In versions 3.0 and earlier, the application used its default settings as defined in libraries/config.default.php and tried to connect to a MySQL server on localhost the same machine where the web server is running with user as root and no password. This is the default set-up produced by most MySQL installation procedures, even though it is not really secure. Therefore, if our freshly-installed MySQL server were still to have the default root account, we would have logged on automatically and would have seen a warning given by phpMyAdmin about such lack of security.

Since version 3.1.0, the development team has wanted to promote a more flexible login panel. This is why, with the lack of a configuration file, phpMyAdmin displays the cookie-based login panel by default (more details on this in Chapter 2, Configuring Authentication and Security, which explains that with the default configuration, it's not possible to log in with an empty password):

We can verify this fact by visiting http://www.mydomain.com/phpMyAdmin and substituting the appropriate 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've just made a connection to it. However, not having created a configuration file means that we would not be able to manage other hosts via 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.

The cookie-based authentication method uses Blowfish encryption for storing credentials in browser cookies. If 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 some choices:

These 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, because the setup script does not cover all of the possible configuration options.

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, because this file must respect PHP's syntax, it's common for new users to experience problems in this phase of the installation.

To access the setup script, we must visit http://www.mydomain.com/phpmyadmin/setup. Here is what appears upon initial execution:

In most cases, the icons beside each parameter point to the respective phpMyAdmin official wiki and to their 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 displayed 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. Let's cover the second 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 username and password, in the setup phase, it's recommended that you communicate over HTTPS, at least for this phase. HTTPS uses Secure Socket Layer (SSL) to encrypt the communication and make eavesdropping on the line impossible. If our web server supports HTTPS, we can simply follow the proposed link. This will restart the setup process, this time over HTTPS. We have made this assumption in our example.

The third warning encourages you to use the ForceSSL option, which will automatically switch to HTTPS when using phpMyAdmin (not related to the setup phase).

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 gray. In this config directory, we can:

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 via the same mechanism (say FTP) that we used to upload phpMyAdmin. In any case, we'll 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 we see:

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

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 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, and also in Chapter 10, Benefiting from the Relational System. For now, we notice that the setup process has detected that PHP also 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.

It's recommended you abide by the philosophy proposed by the interface, and keep cookie as the Authentication type. We assume that our MySQL server is located on localhost. Hence, we keep this value, and all of the proposed values intact, except for the following:

You can see that any parameter that is 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 panel should look like this:

We then click on 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. 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:

We can have a look at the generated configuration lines by using the Display button; and 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 via 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 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 by the web server, ensuring that further modifications are possible. This copy can be done via FTP or through commands such as:

cd config
cp config.inc.php ..

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

After the configuration steps are done, it's recommended that you completely remove the config directory, as this directory is only used by the web-based setup script. Since version 3.2.0, phpMyAdmin displays the following warning on the home page (see Chapter 3, Over Viewing the Interface) 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.

We can create this text file from scratch by 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 later in this chapter.

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 to our webspace again. This transfer is done via an FTP or an SFTP client. You have the option to use a standalone FTP/SFTP client such as FileZilla, or save it directly via FTP/SFTP, if your PHP editor supports this feature.

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.

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

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

$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'] = '';

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

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

$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';

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