AdminXP.com: phpMyAdmin: Introduction, Installation, Configuration

phpMyAdmin: Introduction, Installation, Configuration

Web application written in PHP, containing XHTML, CSS and Javascript. Several MySQL servers or particular databases can be very easily administered through a web browser. Since 1998 it has gone through several changes and gained worldwide popularity in the field of MySQL databases administration for individuals and professionals.

Currently phpMyAdmin can:

create and drop databases

create, copy, drop, rename and alter tables

table maintenance

delete, edit and add fields

execute any SQL-statement, even batch-queries

manage keys on fields

load text files into tables

create and read dumps of tables

export data to CSV, XML and Latex formats

administer multiple servers

manage MySQL users and privileges

check referential integrity in MyISAM tables

support InnoDB tables and foreign keys (see FAQ 3.6)

support mysqli, the improved MySQL extension (see FAQ 1.17)

communicate in 50 different languages

using Query-by-example (QBE), create complex queries automatically connecting required tables

create PDF graphics of your Database layout

search globally in a database or a subset of it

transform stored data into any format using a set of predefined functions, like displaying BLOB-data as image or download-link or ...

Requirements

You need PHP 4.1.0 or newer, with session support (*)

MySQL 3.23.32 or newer (the best is 5.x.x)

If you want to display inline thumbnails of JPEGs with the original aspect ratio, you also need GD2 support in PHP

* Starting with phpMyAdmin 2.6.1, MIME-based transformations that use an external program need PHP 4.3.0 or newer

* Not really a requirement but a strong suggestion: if you are using the "cookie" authentication method, having the mcrypt PHP extension on your web server accelerates not only the login phase but every other action that you do in phpMyAdmin.

* a web-browser (doh!).

Installation of phpmyadmin to a local computer

Logo PhpMyAdmin

Go to www.phpmyadmin.net to download the current version and to find further information. In January 2006, the current version was 2.7.0 (latest stable version) and the phpMyAdmin-2.7.0-pl2.zip archive could be downloaded from the download section.
Unzip and copy the content of the archive to the web server. We created a new folder padmin (complete path: c:\inetpub\wwwroot\padmin).
In the text editor open the config.default.php file and save it as config.inc.php (an editor highlighting the PHP sytax is an advantage).

Welcome to phpMyadmin

For testing: in the browser, enter http://localhost/padmin

Altering the configuration file config.inc.php

(Source: www.phpmyadmin.net/documentation and records in the file config.default.php)

You can make the changes directly in the file config.default.php, but it is recommended that you first save the file as config.inc.php and edit the required values in this new main file. config.default.php is a template file, but can also work as a configuration file. In config.inc.php, only changed values can be entered, the others are taken from the config.default.php.

Example of a config.inc.php file

$cfg['PmaAbsoluteUri'] = '';
$cfg['Servers'][1]['host'] = 'localhost'; // MySQL hostname or IP address
$cfg['Servers'][1]['port'] = ''; // MySQL port - leave blank for default port
$cfg['Servers'][1]['socket'] = ''; // Path to the socket - leave blank for default socket
$cfg['Servers'][1]['connect_type'] = 'tcp'; // How to connect to MySQL server ('tcp' or 'socket')
$cfg['Servers'][1]['extension'] = 'mysql'; // The php MySQL extension to use ('mysql' or 'mysqli')
$cfg['Servers'][1]['auth_type'] = 'config'; // Authentication method (config, http or cookie based)?
$cfg['Servers'][1]['user'] = 'root'; // MySQL user
$cfg['Servers'][1]['password'] = 'xxx'; // MySQL password

The most important configuration data:

$cfg['PmaAbsoluteUri'] = '';

// Configuring the absolute path to the folder containing phpmyadmin. Usually the auto-detection is used which detects the path, and then there is no need to configure it.

$cfg['PmaAbsoluteUri'] = 'http://localhost/padmin/'; (change made by us)

// Your phpMyAdmin URL. Complete the variable below with the full url ie http://www.your_web.net/path_to_your_phpMyAdmin_directory/
It must contain characters that are valid for a URL, and the path is * case sensitive on some Web servers, for example Unix-based servers.
In most cases you can leave this variable empty, as the correct value will be detected automatically. However, we recommend that you do test to see that the auto-detection code works in your system. A good test is to browse a table, then edit a row and save it. There will be an error message if phpMyAdmin cannot auto-detect the correct value.

$cfg['PmaAbsoluteUri_DisableWarning'] = FALSE;

// Disabling the warning about the "PmaAbsoluteUri" value not being set up. If it seems that everything works fine, you can change the value to "TRUE" or to add the previous "PmaAbsoluteUri" value.
// Disable the default warning about $cfg['PmaAbsoluteUri'] not being set. You should use this if and ONLY if the PmaAbsoluteUri auto-detection works perfectly.

$cfg['Servers'][1]['host'] = 'localhost';
// Defining the name or the IP address of the computer on which the MySQL server is administered.

$cfg['Servers'][1]['port'] = ''; // MySQL port - leave blank for default port
$cfg['Servers'][1]['socket'] = ''; // Path to the socket - leave blank for default socket
$cfg['Servers'][1]['connect_type'] = 'tcp'; // How to connect to MySQL server ('tcp' or 'socket')

//Values specifying detailed setup of the server. We used the default settings without any changes made.

$cfg['Servers'][1]['extension'] = 'mysql';

// Defining the extension - whether the older mysql or the newer mysqli (in PHP 5+) should be used.

//The php MySQL extension to use ('mysql' or 'mysqli')

www.php.net/mysql / www.php.net/mysqli

$cfg['PmaNoRelation_DisableWarning'] = FALSE;

//Disable the default warning that is displayed on the DB Details Structure page if any of the required Tables for the relationfeatures could not be found.

$cfg['Servers'][$i]['auth_type'] = 'config';

//Authentication method for access to mysql. You can choose config, http or cookie. Config represents the less safe, but the fastest and the easiest method (with password in the value $cfg['Servers'][$i]['password']). It is much safer to use http or cookie (the password is not saved in the configuration file). If you are sure you do not need to secure the access to the database and the phpMyAdmin application, you can use the "config" method with clear conscience

// Authentication method (config, http or cookie based)?

$cfg['Servers'][$i]['user'] = 'root';

// User account used for logging in the database.

// MySQL user

$cfg['Servers'][$i]['password'] = 'xxx';

//Password for the access to MySQL (required upon using the "config" authentication method).

// MySQL password (only needed with 'config' auth_type)

$cfg['LeftDisplayLogo'] = TRUE;

//Hiding the logo at the top of the left frame linking to www.phpmyadmin.net
// display logo at top of left frame

$cfg['blowfish_secret'] = '';

// Defining the secret password for encoding the password to cookie on your client server. It is entered only once and you are not required to enter that anymore.

//The 'cookie' auth_type uses blowfish algorithm to encrypt the password. If at least one server configuration uses 'cookie' auth_type, enter here a passphrase that will be used by blowfish.

$cfg['Servers'][1] vs. $cfg['Servers'][$i] - // [$i] or [1] have the same importance for identifying the values for the server. The $i value can be replaced by numbers 1,2,3.. and you can define to which configuration of the server this value should be affiliated. Detailed explanation of the configuration of the administration of mysql servers would be a topic for a new larger article. You can also read the documentation.

Frequent error messages:

PhpMyAdmin - "Error Cannot load mysql extension. Please check your PHP configuration". Check if your PHP configuration is correct (enabled php_mysql.dll in the php.ini file // removed semicolon on the line extension=php_mysql.dll and a copy of the libmySQL.dll file in the directory %systemroot%\system32)
#1045 - Access denied for user 'root'@'localhost' (using password: NO) - Check the password in the value $cfg['Servers'][$i]['password']
The mbstring PHP extension was not found and you seem to be using a multibyte charset. Without the mbstring extension phpMyAdmin is unable to split strings correctly and it may result in unexpected results. - in the php.ini file enable php_mbstring.dll // remove the semicolon on the line extension=php_mbstring.dll

The version 2.8.0 probably changes some features

"config.default.php" is moved to the "libraries" directory.
"config.inc.php" is created through a web browser in the "scripts" and "setup.php" subdirectories.

Example:

<?php
/*
* Generated configuration file
* Generated by: phpMyAdmin 2.8.0-beta1 setup script by Michal Èihaø
* Version: $Id: setup.php,v 1.23 2006/01/17 17:03:02 cybot_tm Exp $
* Date: Fri, 10 Feb 2006 18:05:31 GMT
*/

/* Servers configuration */
$i = 0;

/* Server localhost (config:root) [1] */
$i++;
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['auth_type'] = 'config';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '2';

/* End of servers configuration */

$cfg['LeftFrameLight'] = true;
$cfg['LeftFrameDBTree'] = true;
$cfg['LeftFrameDBSeparator'] = '_';
$cfg['LeftFrameTableSeparator'] = '__';
$cfg['LeftFrameTableLevel'] = 1;
$cfg['LeftDisplayLogo'] = false;
$cfg['LeftDusplayServers'] = false;
$cfg['LeftPointerEnable'] = true;
$cfg['AllowAnywhereRecoding'] = false;
$cfg['DefaultCharset'] = 'windows-1250';
$cfg['RecodingEngine'] = 'iconv';
$cfg['IconvExtraParams'] = '//TRANSLIT';
?>

Many other improvements. My current problem with the error message "Call to undefined function: pma_generate_common_url()" seems to be the past.
Improved import / export.