Book Image

Mastering phpMyAdmin 3.3.x for Effective MySQL Management

Book Image

Mastering phpMyAdmin 3.3.x for Effective MySQL Management

Overview of this book

phpMyAdmin is an open source tool written in PHP to handle MySQL administration over the World Wide Web. It can execute SQL statements and manage users and their permissions. However, when it comes to exploiting phpMyAdmin to its full potential, even experienced developers and system administrators are left baffled.Mastering phpMyAdmin 3.3.x for Effective MySQL Management is an easy-to-follow, step-by-step guide that walks you through every facet of this efficient tool. Author Marc Delisle draws on his experience as one of the leading developers and project administrator of phpMyAdmin and uses his unique tutorial approach to take full advantage of its potential. This book is filled with illustrative examples that will help you understand every phpMyAdmin feature in detail.The book helps you get started with installing and configuring phpMyAdmin and looks at its features. You then work on a sample project with two basic tables and perform basic actions such as creating, editing, and deleting data, tables, and databases. You will learn how to create up-to-date backups and import the data that you have exported. You will then explore different search mechanisms and options for querying across multiple tables.The book gradually proceeds to advanced features such as defining inter-table relations and installing the linked-tables infrastructure. Some queries are out of the scope of the interface and this book will show you how to accomplish these tasks with SQL commands.New features of version 3.3.x, such as synchronizing databases on different servers and managing MySQL replication to improve performance and data security, are covered in this book. Towards the end of the book you will learn to document your database, track changes made to the database, and manage user accounts using phpMyAdmin server management features.This book is an upgrade from the previous version that covered phpMyAdmin Version 3.1. Version 3.3.x introduced features such as new import and export modules, tracking changes, synchronizing structure and data between servers, and providing support for replication.
Table of Contents (26 chapters)
Mastering phpMyAdmin 3.3.x for Effective MySQL Management
Credits
About the Author
About the Reviewers
Preface

Installing phpMyAdmin


It's time to install the product and 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 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 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 for either FTP or SFTP transfer, SSH login, or web control panel login.

  • The MySQL server's name or IP address: If this information is not available, a good alternative choice is localhost, which means that the MySQL server is located on the same machine as the web server. We will assume this to be localhost.

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

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

Installing on different platforms

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.

Installing on a remote server using a Windows machine

Using the File explorer, we double-click the phpMyAdmin-3.3.2-all-languages.zip file that we just downloaded on the Windows machine. A file extractor will start, showing us all of the scripts and directories inside a main phpMyAdmin-3.3.2-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.3.2-all-languages directory has been created for extraction.

Now, it's time to transfer the entire directory structure C:\phpMyAdmin-3.3.2-all-languages to the web server in our webspace. We use our favorite SFTP or 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 to which 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 from our Windows machine as they are no longer needed.

Installing on a local Linux server

Let's say we chose phpMyAdmin-3.3.2-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 by issuing the following shell command or by using any graphical file extractor that our window manager offers:

tar -xzvf phpMyAdmin-3.3.2-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.

Installing on local Windows servers (Apache, IIS)

The procedure here is similar to that described in the Installation on a remote server using a Windows machine 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 whom 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.