Mastering phpMyAdmin 3.1 for Effective MySQL Management

By Marc Delisle
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Introduction and Installation

About this book

MySQL has gained wide popularity because of its open-source nature, performance, reliability, robustness, and support for various platforms.

phpMyAdmin is a web-based front-end to manage MySQL databases and has been adopted by a number of open-source distributors. It is one of the most widely used open-source applications written in PHP. phpMyAdmin supports a wide range of operations with MySQL. Currently, it can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges, export data into various formats and is available in 52 languages. The powerful graphical interface of version 3.1 has made phpMyAdmin an indispensable tool for MySQL and web developers.

This is the first book that covers version 3.1, the latest version of phpMyAdmin. It has been fully updated from the previous version that covered phpMyAdmin Version 2.11. It is a comprehensive guide to unlocking the full potential of phpMyAdmin. It builds a solid understanding of the core capabilities of phpMyAdmin before walking you through every facet right from showing you how to set up and manage the database with clear, step-by-step instructions and screenshots to exploring the less well known and generally under-used features and capabilities of phpMyAdmin in depth. Along the way you'll build a more detailed understanding of SQL and how it works in MySQL.

Whether you are an experienced developer, system administrator, web designer or new to MySQL and phpMyAdmin, this book will show you how to increase your productivity and control when working with your MySQL.

Publication date:
March 2009
Publisher
Packt
Pages
352
ISBN
9781847197863

 

Chapter 1. Introduction and Installation

I wish you a warm welcome to this book! The goal of this first chapter is to:

  • Know the position of this software product in the Web spectrum

  • Be aware of all its features

  • Become proficient at installing and configuring it

Introducing phpMyAdmin

This section describes the place of phpMyAdmin in the context of PHP/MySQL web applications and summarizes its features.

Web applications

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 homepage 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 front end used is usually a well- known (and quickly deployed) web browser, there is a database system at the back end. 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? They improve user experience and involve them in the process by opening up possibilities such as:

  • Gathering feedback about the site

  • Letting users communicate with us and with each other through forums

  • Ordering goods from our e-commerce site

  • Enabling easily editable web-based information (content management)

  • Designing and maintaining databases from the Web

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.

PHP and MySQL:The leading open source duo

When we look at the web applications platforms currently offered by host providers, we will see that the 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 offer a lot of readymade open source web applications, and most importantly, enabled in-house developers to quickly put in place solid web solutions.

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 inside the web server, is a popular scripting language in which applications are written to communicate with MySQL (or other database systems) on the back end and browsers on the front end. Ironically, the acronym's significance has evolved along with the Web evolution, from Personal Homepage to Professional HomePage to its current recursive definition: PHP: Hypertext Processor. A reference about the successive name changes can be seen in PHP's source code at http://cvs.php.net/viewvc.cgi/php3/CHANGES?r1=1.23&r2=1.24. Available on millions of Web domains, PHP drives its own wave of quickly developing applications.

What is phpMyAdmin?

phpMyAdmin (official homepage at http://www.phpmyadmin.net) is a web application written in PHP and contains (like most web applications) XHTML, CSS, and JavaScript client code. It 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 existence, it has enjoyed support from numerous developers and translators worldwide (being translated into 55 languages at the time of writing this book). The project is currently hosted on SourceForge.

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).

phpMyAdmin features

The goal of phpMyAdmin is to offer complete web-based management of MySQL servers and data, and to keep up with MySQL and web standards evolution. While the product is not perfect, it currently includes the most commonly requested features and other 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.

The basic features consist of:

  • Database creation, deletion, renaming, and attribute changes

  • Table creation, renaming, copying, and deletion

  • Table structure maintenance, including indexes

  • Special table operations (repair, optimization, and changing type)

  • Data insertion, modification, deletion

  • Data display in horizontal/vertical mode, and Print view

  • Data navigation and sorting

  • Binary data uploading

  • Data search (table or database)

  • Querying by example (multi-table)

  • Batch loading of data (import)

  • Exporting structure and data in various formats, with compression

  • Multi-user and multi-server installation with web-based setup

The advanced features include:

  • Field-level comments

  • Foreign keys (with or without InnoDB)

  • Browse foreign table

  • Bookmarks of queries

  • Data dictionary

  • PDF relational schema and dictionary

  • SQL queries history

  • Connection to MySQL, using either the traditional mysql extension or the new mysqli extension (in PHP 5)

  • Character-set support for databases, tables, and fields (with MySQL 4.1)

  • Column contents transformation based on MIME type

  • Visual Designer for relations

  • Support for MySQL 5.0 features—views, procedures, triggers, profiling

  • Support for MySQL 5.1 features—events and partitions

  • Support for special storage engines—Maria, PBXT, and PBMS (BLOB streaming)

  • Support for Swekey hardware authentication USB key

  • Theme management to customize the look of the interface

The server administration features consist of:

  • User and privileges management

  • Database privileges check

  • Verify server's runtime information and obtain configuration hints

  • Full server export

 

Introducing phpMyAdmin


This section describes the place of phpMyAdmin in the context of PHP/MySQL web applications and summarizes its features.

Web applications

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 homepage 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 front end used is usually a well- known (and quickly deployed) web browser, there is a database system at the back end. 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? They improve user experience and involve them in the process by opening up possibilities such as:

  • Gathering feedback about the site

  • Letting users communicate with us and with each other through forums

  • Ordering goods from our e-commerce site

  • Enabling easily editable web-based information (content management)

  • Designing and maintaining databases from the Web

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.

PHP and MySQL:The leading open source duo

When we look at the web applications platforms currently offered by host providers, we will see that the 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 offer a lot of readymade open source web applications, and most importantly, enabled in-house developers to quickly put in place solid web solutions.

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 inside the web server, is a popular scripting language in which applications are written to communicate with MySQL (or other database systems) on the back end and browsers on the front end. Ironically, the acronym's significance has evolved along with the Web evolution, from Personal Homepage to Professional HomePage to its current recursive definition: PHP: Hypertext Processor. A reference about the successive name changes can be seen in PHP's source code at http://cvs.php.net/viewvc.cgi/php3/CHANGES?r1=1.23&r2=1.24. Available on millions of Web domains, PHP drives its own wave of quickly developing applications.

What is phpMyAdmin?

phpMyAdmin (official homepage at http://www.phpmyadmin.net) is a web application written in PHP and contains (like most web applications) XHTML, CSS, and JavaScript client code. It 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 existence, it has enjoyed support from numerous developers and translators worldwide (being translated into 55 languages at the time of writing this book). The project is currently hosted on SourceForge.

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).

phpMyAdmin features

The goal of phpMyAdmin is to offer complete web-based management of MySQL servers and data, and to keep up with MySQL and web standards evolution. While the product is not perfect, it currently includes the most commonly requested features and other 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.

The basic features consist of:

  • Database creation, deletion, renaming, and attribute changes

  • Table creation, renaming, copying, and deletion

  • Table structure maintenance, including indexes

  • Special table operations (repair, optimization, and changing type)

  • Data insertion, modification, deletion

  • Data display in horizontal/vertical mode, and Print view

  • Data navigation and sorting

  • Binary data uploading

  • Data search (table or database)

  • Querying by example (multi-table)

  • Batch loading of data (import)

  • Exporting structure and data in various formats, with compression

  • Multi-user and multi-server installation with web-based setup

The advanced features include:

  • Field-level comments

  • Foreign keys (with or without InnoDB)

  • Browse foreign table

  • Bookmarks of queries

  • Data dictionary

  • PDF relational schema and dictionary

  • SQL queries history

  • Connection to MySQL, using either the traditional mysql extension or the new mysqli extension (in PHP 5)

  • Character-set support for databases, tables, and fields (with MySQL 4.1)

  • Column contents transformation based on MIME type

  • Visual Designer for relations

  • Support for MySQL 5.0 features—views, procedures, triggers, profiling

  • Support for MySQL 5.1 features—events and partitions

  • Support for special storage engines—Maria, PBXT, and PBMS (BLOB streaming)

  • Support for Swekey hardware authentication USB key

  • Theme management to customize the look of the interface

The server administration features consist of:

  • User and privileges management

  • Database privileges check

  • Verify server's runtime information and obtain configuration hints

  • Full server export

 

Installing phpMyAdmin


It's time to install the product and to configure it minimally for first-time use.

Our reason for installing phpMyAdmin could be one of the following:

  • Our host provider did not install a central copy

  • Our provider installed it, but the version installed is not current

  • We are working directly on our enterprise's web server

Required information

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 we use to transfer phpMyAdmin to our webspace will vary. We will need some specific information (listed below) before starting the installation:

  • The web server's name or address. Here, we will assume it to be www.mydomain.com.

  • Our web server's account information (username, password). This information will be used either for FTP or SFTP transfer, SSH login, or web control panel login.

  • The MySQL server's name or address. Often, this is localhost, which means that it is located on the same machine as the web server. We will assume this to be mysql.mydomain.com.

  • Our MySQL server's account information (username, password).

System requirements

The up-to-date requirements for a specific phpMyAdmin version are always stated in the accompanying Documentation.html. For phpMyAdmin 3.1, 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). 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.

Downloading the files

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.1, there are two groups of files—english and all-languages. If we need only the English interface, we can download a file containing "english"—for example, phpMyAdmin-3.1.0-english.zip. On the other hand, if we have the need for at least one other language, a choice containing "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 it is bigger than .gz or .bz2 (common in the Linux/Unix world). In the following examples, we will assume that the chosen file was phpMyAdmin-3.1.1-all-languages.zip.

After clicking on the appropriate file, we will have to choose the nearest mirror site. The file will start to download, and we can save it on our computer.

Installation procedure

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.

Installation on a remote server using a Windows client

Using the File explorer, we double-click the phpMyAdmin-3.1.1-all-languages.zip file we just downloaded on the Windows machine. A file extractor should start, showing us all the scripts and directories inside a main phpMyAdmin-3.1.1-all-languages directory.

Use whatever mechanism your file extractor offers to save all the files, including subdirectories, to some location on your workstation. Here, we have chosen c:\. Therefore, a c:\phpMyAdmin-3.1.1-all-languages directory has been created for extraction.

Now, it's time to transfer the entire directory structure c:\phpMyAdmin-3.1.1-all-languages to the web server in our webspace. We use our favorite FTP software or the web control panel for the transfer.

The exact directory under which we transfer phpMyAdmin may vary. It could be our public_html directory or another directory where we usually transfer web documents. For further instructions about the exact directory to be used or the best way to transfer the directory structure, we can consult our host provider's help desk.

After the transfer is complete, these files can be removed as they are no longer needed on our Windows client.

Installation on a local Linux server

Let's say we chose phpMyAdmin-3.1.1-all-languages.tar.gz and downloaded it directly to some directory on the Linux server. We move it to our web server's document root directory (for example, /var/www/html) or to one of its subdirectories (for example, /var/www/html/utilities). We then extract it with the following shell command or by using any graphical file extractor that our window manager offers:

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

We must ensure that the permissions and ownership of the directory and files are appropriate for our web server. The web server user or group must be able to read them.

Installation on local Windows servers (Apache, IIS)

The procedure here is similar to that described in the Installation on a remote server using a Windows client section, except that the target directory will be under our DocumentRoot (for Apache) or our wwwroot (for IIS). Of course, we do not need to transfer anything after modifications are made to config.inc.php (described in the next section), as the directory is already on the webspace.

Apache is usually run as a service. Hence, we have to ensure that the user under which the service is running has normal read privileges to access our newly created directory. The same principle applies to IIS, which uses the IUSR_machinename user. This user must have read access to the directory. You can adjust permissions in the Security/permissions tab of the directory's properties.

 

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.

Note

Before configuring, we can rename the directory phpMyAdmin-3.1.1-all-languages to something like phpMyAdmin, phpmyadmin, admin, or something easier to remember. This way, we and our users can visit an easily remembered URL to start phpMyAdmin. We can also use a symbolic link if our server supports this feature.

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.

Note

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.

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, which can be copied over 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/original. We will cover modifying some of those parameters in Chapter 4, under the First steps section.

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 this parameter to false:

$cfg['CheckConfigurationPermissions'] = false;

Configuration principles

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 root and no password. This is the default setup produced by most MySQL installation procedures, even though it is not really secure. However, 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.

Note

If the notion of MySQL root user eludes you, 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.

Since version 3.1.0, the development team has wanted to promote a more flexible login panel. This is why, in the situation of lacking a configuration file, phpMyAdmin displays the cookie based login panel by default (more details on this in Chapter 2):

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've just made a connection to it. However, not having created a configuration file means that we would be limited to managing this host only. 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 warning message is displayed: The configuration file now needs a secret passphrase (blowfish_secret).

At this point, we have some 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 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 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/scripts/setup. Here is what appears on the initial execution:

In most cases, the icons beside each parameter point to the respective phpMyAdmin official wikis 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, the following messages are revealed:

There are three warnings here. We will first deal with the second one—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. We have made this assumption in our example.

The third warning encourages you to use the ForceSSL option, which would 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 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 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. This is according to the platform on which we plan to open later, with a text editor, the config.inc.php file.

A single copy of phpMyAdmin can be used to manage many MySQL servers. We will now 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 in Chapter 11. 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.

I encourage you to stay in 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 the proposed values intact, except for the following:

  • Verbose name of this server—we enter my server

  • User for config auth—we remove root and leave it empty

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 panel should look like this:

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 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—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 sever 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 via FTP or through commands such as:

cd config
cp config.inc.php ..

Note

As a security measure, it's recommended that you change the permission on the config directory—for example, with the chmod ugo-rwx config command. 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. 1. Enable read and write access to the config directory

  2. 2. Copy the config.inc.php there

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

  4. 4. Start the web-based setup tool

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.

Manual creation of 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. Another choice would be WordPad, Metapad, or UltraEdit. We should be careful not to add any characters (even blank lines) at the beginning or end of the file. This would disturb the execution of phpMyAdmin and generate the Cannot send header output... error message. If this happens, refer to Appendix B.

Every time the config.inc.php file is modified, it will have to be transferred again to our webspace. 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 directly via 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'] = '';

PMA is a familiar abbreviation for phpMyAdmin. For configuration parameters, the chosen convention is to capitalize the first letter, producing Pma in this case. At some places in its code, phpMyAdmin sends 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, and then edit a row and click 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:

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

If we examine only the normal server parameters (other parameters will be covered starting with Chapter 11), we see a section that looks like the following 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 section on connect_type 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 hide the real server hostname as seen by the users.

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, because of 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.

In phpMyAdmin version 2.6.0, a new library has been implemented. This has made possible the use of both extensions, choosing either one for a particular server. We indicate the extension we want to use in $cfg['Servers'][$i]['extension'].

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 because persistent connections are often a cause of resource depletion on servers. So you will find MySQL refusing new connections. For this reason, the option is not even available for the mysqli extension. Hence they setting it to TRUE here would have no effect if you are connecting with this extension.

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 this 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—here, 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 configuration

Starting with PHP 4.3.0 and MySQL 3.23.49, 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;

 

Upgrading phpMyAdmin


Normally, upgrading is just a matter of installing the newer version into a separate directory and copying the previous version's config.inc.php to the new directory. If the previous version is phpMyAdmin 2.6.0 or older, we cannot copy its config.inc.php to the new version because the file format has changed too much.

Note

An upgrade path or the first-installation path, which should not be taken is to copy libraries/config.default.php to config.inc.php. This is because the default configuration file is version-specific, and is not guaranteed to work for the future versions.

New parameters appear from version to version. They are documented in Documentation.html and defined in libraries/config.default.php. If a configuration parameter is not present in config.inc.php, its value from libraries/config.default.php will be used. Therefore, we do not have to include it in config.inc.php if the default value suits us.

Special care must be taken to propagate the changes we might have made to the layout.inc.php files depending on the themes used. We may even have to copy our custom themes subdirectories if we added our own themes to the structure.

 

Summary


This chapter covers how the web has evolved as a means of delivering applications, and why we should use PHP/MySQL to develop these applications. The chapter also gives an overview of why phpMyAdmin is recognized as a leading application to interface MySQL from the Web and a brief list of its features. It then discusses common reasons for installing phpMyAdmin, steps for downloading it from the main site, basic configuration, uploading phpMyAdmin to our web server, and also about upgrading.

Now that the basic installation has been done, the next chapter will deal with the configuration subject in depth, by exploring the authentication and security aspects.

About the Author

  • Marc Delisle

    Marc Delisle was awarded "MySQL Community Member of the year 2009" because of his involvement with phpMyAdmin. He started to contribute to the project in December 1998, when he made the multi-language version. He is still involved with phpMyAdmin as a developer and project administrator. Marc is a system administrator at Cegep de Sherbrooke, Québec, Canada. He has been teaching networking, security, and web application development. In one of his classes, he was pleased to meet a phpMyAdmin user from Argentina. Marc lives in Sherbrooke with his wife and they enjoy spending time with their four children.

    Browse publications by this author
Book Title
Access this book, plus 7,500 other titles for FREE
Access now