Contents Index References Cover Copyright previous next
Appendix. Installing the supporting software
CONTENTS:
1. Installing Apache
1.1 Installation details
1.1.1 Which installation package to choose
1.1.2 Running the installation process
1.1.3 Starting/stopping Apache
1.2 Apache directories
1.3 Installing the published pages
1.3.1 The htdocs directory
1.3.2 Requesting a file
1.4 Alias
 
2. Installing PHP
2.1 Downloading the binary package
2.2 Unzipping the downloaded packages
2.2.1 Unzipping the PHP package
2.2.2 Unzipping the PECL package
2.3 Leave the php5ts.dll module alone
2.4 Set up the configuration file
2.4.1 Using a distributed file
2.4.2 The configuration directives
2.5 Install the configuration file
2.6 Give read permission to the Web server
2.7 Register with Apache
2.8 Test the basic system
2.9 Install the extensions
 
3. Installing MySQL
3.1 Installing the MySQL server
3.2 Running the server
3.2.1 Starting the MySQL server
3.2.2 Stopping the MySQL server
3.3 Setting up the MySQL service
3.4 Connecting to and exiting from the server
3.5 Defining user passwords
3.5.1 Windows system
3.5.2 Unix System
3.6 MySQL Options
3.7 Creating databases
3.8 Useful MySQL commands
3.8.1 Administrating the server before connection
Show the databases
Start the server
Stop the server
Show the tables in a database
Connect to the server
Connect directly to a database
Connect to the server from a host
3.8.2 Database operations after connection to the server
Show the databases
Connect to a database
3.8.3 Security related commands
Creating and/or granting privileges to an account
Revoking privileges from an account
Removing a user
Setting a password
3.8.4 Other operations after connection to a database
Connect to another database
Display the list of tables
Display the definition of a table
To run the PHP applications as exemplified in the present document, you have to install these software systems:
-A web server which we chose to be Apache
-The PHP processor
-The MySQL database server
The following is a quick guide to installing these systems with default options which are sufficient for common uses. A description of the options are to be found in the document specially devoted to the concerned system.

1. Installing Apache

Installing Apache involves these simple steps:
1.Download a free copy of the Apache installation package from the Internet (start at http://httpd.apache.org, you will be guided through) -- for more, see below.
2.Run the installation package, which will start the installation process.
3.Upon your clicking on the FINISH button, Apache is up and running. An icone will appear on the task bar at the bottom of the screen. You click on it to stop and start the Apache system.

Pages that can be requested by a user are to be set in a dedicated directory named htdocs, to be found directly under the Apache home directory.

To run PHP scripts, you need also to install a PHP server, as described in 2. Installing PHP below.

1.1 Installation details

1.1.1 Which installation package to choose

As for the installation package, you can choose a binary or a source package. The simplest to use is the binary package.

With Windows you can choose

-either an .exe binary package
-or an .msi binary package
The .exe package includes an MSI (Microsoft System Installer) program, so it is somewhat bigger. Normally your Windows system already has an MSI program. If you know how to run it on a package, you can select the .msi. Otherwise, you can select the .exe package, and run it by opening it, or by double clicking on its name, in the Windows explorer window.

1.1.2 Running the installation process

In a Windows system, if you know how to run the MSI program, run it with the .msi Apache package, otherwise run the .exe package. This will start the installation process.

During the installation process, you will be requested to enter some informations:

-The network domain name -- this is the name you acquire from some domain name provider. Example: hatayservices.com. To install a test server for local use, just enter any name
-The server name -- this is a subdomain name in your domain, where the Apache directories will be set. Example: apache.hatayservices.com. To set up a test server to use locally, any name would do.
-The administrator e-mail address. Enter an e-mail address of your choice
You can also change some of the options that the installation software proposes:
-change the path of the directory where to put Apache, to a path of your own, using names without spaces. Example: C:\software\ApacheGroup\
-select the custom setup mode (it is not more difficult than the typical mode)

1.1.3 Starting/stopping Apache

In Windows systems, Apache normally starts every time Windows starts.

The Apache icon that appears on the task bar at the bottom of the screen can be used to stop it, and start again.

1.2 The Apache directories

The Apache directories are shown in the following figure:
 
server root
  
  binApache core executables
  
  cgi-binCGI scripts
  
  confconfiguration and data types files
  
  errorstandard error message text elements
  
  htdocsdocument root
  
  iconsicons for all use
  
  includeC and other language include modules
  
  libApache library
  
  logsApache log files
  
  manualApache description manual
  
  modulesdynamic modules
  
  proxyproxy information
  
  
The yellow boxes represent the directories. The labels in the boxes are the names of the directories. To the right is a short description of what each directory contains.

1.3 Installing the published pages

1.3.1 The htdocs directory

Published files, i.e. files accessible to the remote users are to be set under the htdocs directory, directly in it, or in some directory descending from it.

1.3.2 Requesting a file

The request for a file is in the form:
http://server_name/file_path
where:
server_nameis the name of the server as defined in the installation process. However, it you are accessing from the local host, this name is localhost
file_pathis the path to the file from the htdocs directory (or otherwise stated: it is the relative URI for the file, with the htdocs directory as the URI base).
In the above request, the port number is omitted, it defaults to 80. That is, the request is actually

http://server_name:80/file_path

80 is also the default port number assumed by Apache when it is generated as described above (more generally, 80 is the default port for HTTP communications.)

The complete code for the request is:

http://server_name:port_number/file_path

Example: http://php.hatayservices.com:4080/HPP/PHPInfo.php

1.4 Alias

A directory, descendant of the htdocs directory or not, can be given an alias which makes it directly accessible to a remote user. An alias is declared in the configuration file (%ApacheHome%/conf/httpd.conf -- %ApacheHome% is the directory where the Apache system is installed), with the syntax:
Alias /alias_name "dir_path"
where:
alias_nameis the alias assigned to the directory
dir_pathis the absolute path of the directory in the server file system
Example, under Windows:
Alias /hpp "C:/books/PHP/samples"
(you can put the line anywhere in the httpd.conf file, but it is advisable to group it with the other 'Alias' directives).
The directory will be accessed using the request:
http://host_id:port/hpp/

2. Installing PHP

This section describes how to install PHP 5 in a Windows system.

The installation steps are:

1.Download the PHP-5 and PECL-5 binary packages
2.Unzip the PHP-5 package into a predefined PHP home directory
3.Unzip the PECL package into the ext directory under the PHP home directory
4.Leave the php5ts.dll module in the PHP home directory
5.Set up the php.ini configuration file
6.Put the php.ini file in the Windows home directory or the Apache home directory
7.If your system uses NTFS, give read permission on php.ini to the Web Server
8.Set the Apache configuration file to enable PHP

This terminates the installation of the basic PHP system.

After you have tested that the basic system works, you can proceed on to install the desired extensions. For example, the MySQL extension for database processing.

2.1 Downloading the binary packages

A PHP processor can be installed:
-either by compiling a source package
-or by running a compiled binary package

Installing from a binary package is the simpler course. This is described in the following.

We are going to install PHP 5 which by the time this document reaches you, will be largely stabilized. Moreover, from this version on, there are specific problems that did not exist in PHP 4, and we may wish to know how to tackle them in the future.

Two packages are to be use:

-the PHP 5 package proper
-the PECL 5 (PHP Extension Community Library) that contains extension modules which were built into the PHP core, but are separated as of PHP 4.0.1

First, access the PHP home page at www.php.net. Clicking on the downloads keyword on the menu bar on top of the screen will get you to the download page.

Or you may go directly to this page at the address www.php.net/downloads.php (these sorts of addresses may change in the course of time).

Then look for the Windows Binaries. You want:
- the PHP 5 zip
- the PECL 5 zip
(not the installation zip)
As of the writing of this text, these are the packages:
- php-5.0.0-win32.zip
- pecl-5.0.2-win32.zip

In each case, click on the name, then choose the mirror to download from.

You can download the package anywhere, but it is advisable to do it in an orderly way. For example, into a directory dedicated to zip files, or to PHP connected downloads, etc...

2.2 Unzipping the downloaded packages

If you use Winzip to unzip the files, a file path will be proposed for the directory (or folder) where the unzipped files will be placed. This will be something like:
C:\My documents\Unzipped\php-5.0.0-Win32

You have to change to a path where the names don't contain space characters ('My documents' will not do). For example:

C:\SoftwareSystems\PHP\

You set on changing by clicking on the button labelled "Select different folder", or something alike. Then you can type the new path name into the entry field that contains the proposed path, on top of the screen. But it is easier to create the desired directory beforehand, and at this point, to navigate to it through the directory tree.

2.2.1 Unzipping the PHP package

Unzip the PHP package to an empty directory that will be the home directory for PHP. For example:

C:\SoftwaresSystems\PHP

The structure of the PHP file system looks like this:

 
PHP Home
  
  devDevice configuration files
  
  extExtension modules
  
  extrasExtra tools
  
  includesInclude modules
  
  logsPHP logs
  
  PEARPHP Extension and Application Repository
-- a structured library of open-source code
  
  
 ; php*.dll's

other dll's

other files

Files directly in Home directory
  
  
  
  
PHP Home is the directory where you unzipped the PHP package, C:\SoftwareSystems\PHP as suggested in the above.

At this point, you need to understand the ext directory and some of the files directly contained in the PHP Home directory.

The ext directory is to contain the extension modules which are .dll files that provide functions not included in the core of the PHP system.

From among the functions directly contained in the Home directory, we are going to deal with these:

php5ts.dllthe PHP core
php5apache2.dllwhich runs PHP 5 as a service under Apache 2
php.exewhich runs PHP 5 as a CGI processor
php.ini-distwhich contains the skeleton of a configuration file that can be used for a test system
php.ini-recommendedwhich contains the skeleton of a configuration file that can be used for a production system

2.2.2 Unzipping the PECL package

The PECL (PHP Extension Community Library) contains the extension modules that go to the ext directory under the PHP Home.

So, unzip it into that directory. If you use Winzip, when the program proposes a path name, click on the Select different folder button to navigate to the ext directory.

2.3 Leave php5ts.dll alone

The php5ts.dll must be put in a directory where it can be found by Apache. Since the Apache configuration file must define the path to the apache2php5.dll module, as will be seen in a moment, one place where to put php5ts.dll is in the same the directory as apache2php5.dll. This is because Apache will first look in that directory for all the files it may need, beside apache2php5.dll.

As the modules php5ts.dll and apache2php5.dll are already in the same directory, namely the PHP Home directory, there is no need to do anything about this matter.

2.4 Set up the configuration file

2.4.1 Using a distributed file

The PHP configuration file is called php.ini. Two models of it are available in the distributed binary package:
- the php.ini-dist file and
- the php.ini-recommend file

You can use the contents of the php.ini-dist file in the configuration file of a test PHP system. The contents of the php.ini-recommended file is to be used in the configuration file of a production PHP system.

Whichever file is chosen, just change its name to php.ini and use it.

When the distributed file is used as is, it defines a minimum configuration that does not have any of the extension function enabled.

2.4.2 Configuration directives

The contents of the configuration file is composed of a number of directives. The line that start with a semi-colon (;) are comments. Some of the lines are directives changed into comments by the adding a semi-colon in front of it; you activate the directive by deleting the semi-colon.

You can change some of the directives or add new ones to taylor the system to your needs.

The directives can be seen in the php.ini file. Please click here to see a sample that comes with the binary package distribution. Here are the comments on some of them:

Language Options
short_open_tagpossible values:
on: use of the <? opening tag allowed
off: <? not recognized as PHP opening tag
asp_tagspossible values:
on: use of ASP style <% ... %> tags allowed
off: <% ... %>tags not recognized
precisionvalue: integer -- number of digits displayed in floating point numbers
output_bufferingvalue: on, off or integer. An integer denotes the limit size of the buffer used for output messages (no limit with 'on').
When on or an integer, it causes outgoing messages to wait in the buffer and therefore allows message headers to be sent after the message body
implicit_flushwhen on: message is sent after each call to the print or echo function
Security
safe_modevalues: on or off
When on, safe mode is instated:
-data from an incoming message are not directly set in the corresponding global variables, but only in the superglobal arrays $_GET, $_POST, etc...
-executable files can be limited to those contained in a directory designated by the safe_mode_exec_dir directive
-environment variables modifiabled by the user can be restricted to those with prefixes named in the safe_mode_allowed_env_vars
-some of the variables can be protected from modification by the user (safe_mode_protected_env_vars directive)
Resource limit
max_execution_timemaximum time a script can run
max_input_timemaximum time a script can spend parsing request data
memory_limitmaximum memory size a script can use
Error reporting and logging
error_reportinglevel of gravity of reported errors
display_errorswhen on: errors are reported to the user; this is NOT recommended as error messages can contain information on the system -- recommended value for a production system: off
log_errorswhen on errors are logged on a file -- this is the alternative to displying errors to the user.
error_logpath name of the error log file
Data handling
arg_separator.outputredefines the argument separator in an output URL (the default is &)
arg_separator.inputredefines the argument separator accepted in an input URL (the default is &)
register_globalswhen On data coming in from the messages are directly registered in the global variables; for security reasons, it is recommended to set this directive Off
register_long_arrayswhen On in coming data are registered in the deprecated long arrays like $HTTP_GET_VARS; in a PHP system with only new scripts, it is recommended to turn the directive Off
post_max_sizespecifies the maximum size of the data transmitted by the POST method
magic_quotes_gpcwhen On, single quotes are automatically added around data destined to database tables
Paths and directories
include_pathspecifies the path to the directory that contains include modules
extension_dirspecifies the directory in which the extension modules reside. Example:
extension_dir = "C:/Softwares/PHP5/ext"
File upload
file_uploadswhen On file upload is authorized
upload_tmp_dir specifies the temporary directory used for file upload - if unspecified, the default directory is used
upload_max_filesizespecifies the maximum size of an uploaded file
Fopen
allow_url_fopenwhen On, allows files defined by a URL (such as http://... or ftp://... to be opened by the fopen function, like a regular local file.
Dynamic extensions
extensionenables a dynamic extension - the directory that contains the extension modules is defined by the extension_dir directive above
Module settings - these directives define the options pertaining to specific extension modules
mysql.allow_persistentwhen On, allows persistent links to the MySQL server
mysql.max_persistentdefines the number of persitent links, for MySQL
mysql.default_portspecifies the default port for the mysql_connect function -- when omitted, it is the port as defined by the MySQL server
mysql.connect_timeoutconnect timeout for MySQL

2.5 Install the configuration file

In Windows XP, 9x, NT or 2000 systems, you can set the php.ini file in the Windows Home directory, that you can display by entering the command:
echo %WINDIR%

This is usually the directory C:\Windows, in XP or 9x systems, or C:\WINNT in NT or 2000 systems.

2.6 Give read permission to the Web server

This is a procedure of the Windows 2000, NT or XP system. In these systems, a file or directory can be protected by limiting its access to designated users with definite permission levels.

You can give the read permission on php.ini to Everyone.

2.7 Register with Apache

The PHP system must be made known to Apache. In the Apache configuration file, add these 2 directives (perhaps just after the other LoadModule directives):
LoadModule php5_module "module_path"
AddType application/x-httpd-php .php
where:
-The LoadModule directive causes the PHP module 'php5_module' to be loaded when Apache starts. In this directive:
module_path is the file path of the physical module. Example:
LoadModule php5_module "c:/SoftwareSystems/PHP/php5apache2.dll"
-The Addtype directive (coded as shown) indicates that all files with the name extension of .php are PHP pages (.php is the usual suffix for PHP pages, but you can choose another suffix).

2.8 Testing the basic system

Now you have to get a PHP page to test.
<HTML><HEAD><TITLE>TEST APACHE-PHP</TITLE>
<BODY>
<H1 style="color:red; text-align:center;">
PHP INFORMATION</H1>
<?php phpinfo(); ?>
</BODY>
</HTML>
 
First, create a directory you would call PHP, in the htdocs directory of Apache. Then copy into PHP, the file PHPInfo.php that you can find in the PHP/samples directory.

The contents of this file is shown here on the left.
phpinfo is a PHP function which displays the informations on the current PHP system.

To call this page, enter the following address in the address field of your browser:
http://localhost/PHP/PHPInfo.php

If your system (Apache, PHP, the PHP directory, and the sample program PHPInfo.php) are correctly installed, you should see the table that displays the informations on your PHP installation.

2.9 Install the extensions

You have to:
- install the .dll module that perform the desired extension function
- enable the extension in the configuration file

The most usual extensions are named in the extension directives which appear as comments in the distributed php.ini files (php.ini-dist or phi.ini-recommended). To enable an extension, uncomment its directive by deleting the semi-colon sign at the beginning of the line. Some of the directives you can uncomment are (full explanation in the reference manual):

extension=php_bz2.dllcompression utility
extension=php_cpdf.dllhelps create PDF document in PHP
extension=php_curl.dllprovides for communication using different protocols: http, https, ftp, gopher, telnet, etc...
extension=php_dbx.dlloffers the functions to access all supported database using the same functions (so that your script is still valid when you change the database)
extension=php_fdf.dll(Form Data Format) handles forms within PDF documents
extension=php_imap.dllhandles server side image map applications, and more
extension=php_java.dllintegrates Java support to PHP
extension=php_ldap.dllprovides for LDAP support
extension=php_mbstring.dllhandles multibyte character codes
extension=php_mysql.dllprovides support for MySQL database access
extension=php_openssl.dllhandles secured Internet communication -- uses the functions of 'OpenSSL' for generation and verification of signatures and encrypting and decrypting data
extension=php_oracle.dllhelps in Oracle database access
extension=php_pdf.dllprovides for PDF file creation
extension=php_sockets.dllprovides for low-level communication using sockets
extension=php_xmlrpc.dllimplements XML-RPC functions
extension=php_xsl.dllimplements the XSL standard
extension=php_zip.dllprovides for the reading of the files contained in a zip archive.

Note that these directives are used to enable extension functions. PHP has a set of core functions which need not be declared in an extension directive. Some examples are:
- the Calendar functions
- the Date and Time functions
- the Function handler functions
- the HTTP functions
- the String functions
- etc...

3. Installing MySQL

To have MySQL up and running in your computer, you have to:
-install the software
-define the users authorized to access the server
-start the server if this is not done automatically

In a Windows system, this involves the following steps:
1. Download the MySQL package from the mysql.com site
2. Unpack the package
3. Run the setup program
4. Start the MySQL server
5. In an NT type Windows system, install the MySQL server as a service
6. Assign passwords to the root and anonymous users

All these steps are very simple. Steps 1. to 3. install the server with the default options. With step 5. the MySQL server will start each time Windows starts.

3.1 Installing the MySQL server

These are the steps to install the MySQL server in a Windows system:
1.Download a MySQL zip file -- connect to the www.mysql.com. You will be directed to the download page. You will then be requested to select a version, then your system (Linux, Windows, etc...) and a mirror site near your home.
Currently, the recommended version is mysql-4.0, but it will be moving upward. The file to be loaded is something like:
mysql-4.0.21-win.zip.
You can receive the downloaded file anywhere. It is advisable to store your downloads in an orderly manner though (if you don't have many, putting all the downloads in one dedicated directory will do).
Things will be changing from the mysql-4.1 version, as to the extension needed to access the database from PHP.
2.Unpack the downloaded file into a dedicated directory. You have to create the directory beforehand (no containing directory is created so that the unpacked files may mix with existing files if you don't have a dedicated directory). The unpacked files will constitute the installation kit
3.Run the setup.exe file. The installation routine will propose a directory for the mysql system (C:\mysql in a Windows system). The simplest is to accept it. You then have the choice between a Typical, Compact and Custom installation. Typical is the recommended choice if you don't have any strong reason to do otherwise.

That's all. MySQL is installed and ready to run.

3.2 Running the server

3.2.1 Starting the MySQL server

The MySQL server can be started from the command line using the mysqld command.

The first time you use the --console argument to have the log messages displayed on your console. Example:

C:\> C:\mysql\bin\mysqld --console
In this:
-The command is entered from the C: directory.
-The prefix C:\mysql\bin\ is the path to the mysqld.exe program file (supposing you accepted the default location C:\mysql for the MySQL system). It is not needed if the command is entered from the C:\mysql\bin\ directory, or if the environment variable PATH points to this directory.
-the --console argument causes all execution messages to be directed to the consoloe foryou to see, instead of to a log file

The first time the MySQL server is started, it creates:

-One data file to constitute the tablespace (this is where tables pertaining to the server are stored -- more files can be added to the tablespace later on). This data file is auto-extending, i.e. when full, its size is automatically extended. Its name is ibdata1, and it is set in the mysql/data/ directory.
-Two log files named ib_logfile0 and ib_logfile1, also set in the mysql/data/ directory.
The messages displayed on the console report on the creating of these files.

After this, the server is started, which is reported by messages like these:

041020 17:54:40 InnoDB: Started
C:\mysql\bin\mysqld: Ready for connections.
Version: '4.0.21-debug' socket: '' port: 3306

Note the port number 3306 assigned to MySQL.

The console from which the mysqld --console command was entered is now used for message displaying. You must open another (DOS) command entry window to enter new commands to the MySQL server. You will need to enter commands, e.g. for stopping the MySQL server.

3.2.2 Stopping the MySQL server

You use the mysqladmin -u root shutdown command to stop the server in its primeval state:
C:\> C:\mysql\bin\mysqladmin -u root shutdown
In this:
-The prefix C:\mysql\bin\ is the path to the mysqladmin.exe program file. It is not needed if the command is entered from the C:\mysql\bin\ directory, or if the environment variable PATH points to this directory.
-The -u root identifies the user who enters the command as "root". This user is readily defined by the the server installation process. This is the user who has all the rights over the server. He is empowered to stop the server.
-shutdown is the argument which tells the server to stop.

In the primeval state, when the server is just installed, there is no password protection, so you do not have to, and must not introduce a password. A password is introduced by the argument -p. After a password has been defined for the user "root", the command to shut down the server is:

C:\>C:\mysql\bin\mysqladmin -u root -p shutdown
You will then be prompted to enter the password.

At this stage, do not try to start the MySQL server using the mysqld command without the --console argument. The server may not start.

3.3 Setting up the MySQL service

In NT type Windows systems (Windows NT, 2000, XP), it is recommended to install MySQL as a Windows service. MySQL will then automatically start when Windows starts. To this end, enter the command:
C:\> C:\mysql\bin\mysqld --install
The program will propose the name MySQL for the service. Just accept this name, and the service is installed.

3.4 Connecting to and exiting from the server

To access the databases you have first to connect to the database server. For this you have to declare a user under whose name you connect. The standard installation process readily creates 2 users, one named 'root' and one with an empty name, called the anonymous user.

You can connect as the root user. This can be done using the mysql command from the command line. Like this:

mysql -u root
In this:
-The -u root argument indicates that you are connecting as the root user
-The path to the bin directory that contains the mysql server may be prefixed to the command name, if needed

Just after the server is installed, as passwords have not been defined, you need not and must not enter a password. After a password is defined, the command to connect to the server as the root user is:

mysql -u root -p
You will then be prompted to enter the password.

Here are the commands to exit from the MySQL server:

exit
or
quit

3.5 Defining user account passwords

A user connects to the MySQL server by declaring a user account name and the host where she or he connects from (the local host is assumed if no host is declared). A user account can be protected by one or more passwords, each associated with a connecting host. A user account is often simply called an account or a user, for short.

Creating a password for an account also creates the account if it did not exist.

When the MySQL server is installed as said in the above, 2 user accounts are defined, root and anonymous.

Each of these accounts is defined for access from 2 hosts.

In a Windows system:

-one of the account is for connection from the local host,
-the other is for connection from any other host

In a Unix system, both accounts are for connection from the local host

-one using the name 'localhost',
-the other the actual name or Internet address of the local host.

In both systems, these accounts are initially created with no password. To protect your databases, you have to assign passwords to the accounts. To do this, you first connect to the server as the root account, then you define the passwords using the SET PASSWORD command as described in the following.

3.5.1 Windows systems

The root account, connecting either from the local host or from a remote host, is granted the authority to perform all operations on all databases. Particularly, at this time only the root account can shutdown the server.

The anonymous account has an empty name; it is intended for common use. Its privileges are as follows:

-when connecting from the local host, it has all privileges on all data bases
-when connecting from any other host, it has all privileges on the 'test' database and any database with name starting with test.

To assign a password to these accounts, you can use these SQL commands, from the command line:

To assign passwords to the root account:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
SET PASSWORD FOR 'root'@'%' = PASSWORD('password');
To assign passwords to the anonymous account:
SET PASSWORD FOR ''@'localhost' = PASSWORD('password');
SET PASSWORD FOR ''@'%' = PASSWORD('password');
where
- password is a different password for each account
- the % sign stands for 'any host'

These commands set the password values in the user table contained in the mysql database that was automatically created by the standard installation process.

There should be lines with the root and empty names, and the % sign as the host name, but there may be not. You can display this table using the command:

SELECT Host, User FROM mysql.user;
This should display the table:
+-----------+------+
| Host      + User |
+-----------+------+
| %         |      |
| %         | root |
| localhost |      |
| localhost | root |
+-----------+------+
If instead of the % sign, there is another name, you should reenter the above SET PASSWORD commands replacing the % sign with that name. The accounts with this name must not be left unchanged, since anyone would then be able to connect to the MySQL server from the host by this name, without using a password.

If the % did not exist in the table, you can create an account to authorize the root account to access the database from any host, using the SQL command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

3.5.2 Unix systems

The root account, connecting from the local host using the localhost or the actual name, or the Internet Adddress, is granted the authority to perform all operations on all databases. Particularly, at this time only the root account can shutdown the server.

The anonymous account, connecting from the local host using the localhost or the actual name, or the Internet Adddress, is granted all privileges on the test database, and all databases with name starting with test.

To see the actual name of the local host, you enter the SQL command:

SELECT Host, account FROM mysql.account;

The host name other than localhost is the actual name of the local host.

To assign a password to the root and anonymous accounts, you can enter these commands from the command line:

To assign passwords to the root account:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password');
SET PASSWORD FOR 'root'@'hostname' = PASSWORD('password');
To assign passwords to the anonymous account:
SET PASSWORD FOR ''@'localhost' = PASSWORD('password');
SET PASSWORD FOR ''@'hostname' = PASSWORD('password');
where
-hostname is the actual name of the local host
-password is a different password for each account.

3.6 The MySQL options

Options of the MySQL server can be defined in files:
- my.ini or my.cnf on Windows
- my.cnf on Unix.

On Windows the my.ini file is contained in the Windows home directory which can be shown by the echo %windir% DOS command. The my.cnf is in the C: root.

On Unix, different casts of the my.cnf file are in:

-the /etc directory (global options)
-the MySQL data directory which usually has the path /usr/local/mysql/data (server specific option)
-directories identified by the --defaults-extra-file options
-~/.my.cnf (user specific options)

Options are not dealt with in this guide which is concerned with installing the MySQL server with the default options.

3.7 Creating databases

The standard installation process creates 2 databases:
-test which is empty
-mysql which contains a number of tables to receive the informations on the server, notably the user table which holds the informations on the uiser accounts

The mysqlshow utility command (entered without conntecting to the server) can be used to display the databases under the server. Now that you have assigned a password to the accounts, you have to enter the password with the command. This is done by adding the -p option to the command line. Example:

mysqlshow -u root -p
In this:
--u root indicate that the command is entered by the root user. If this is omitted, the user is anonymous, therefore only the databases to which it has access, i.e. the databases with name starting with 'test' will be shown
--p indicate a request with password
-the path to the bin directory can be prefixed to the command name, if needed
You will be requested to enter the password.

A new database can be created using the SQL command CREATE DATABASE, after connecting to the server.

CREATE DATABASE db_name;

3.8 Useful MySQL commands

Here are some of the commands needed to run the MySQL server.

3.8.1 Administrating the server before connection

Show the databases

mysqlshow -u root -p

Start the server

mysqld --console
The mysqld server is complete with debugging facilities. A streamlined version of the server is mysqld-nt which can be invoked in NT type systems (Windows NT, 2000, XP), for better performance. You use the mysqld-nt command instead of mysqld:
mysqld-nt --console
In both case you have to use another command window to enter MySQL line commands. The window from where you enter the mysqld command will display the messages from MySQL.

When MySQL is set up as a service in a Windows system (this is usually the case with Windows XP, 2000, NT), the server can be started as a service using:

NET START MySQL
where MySQL is the name assigned to MySQL as a service (note that when MySQL is a service, the server starts automatically when Windows starts).

Stop the server

To stop the server:
mysqladmin shutdown -u root -p
You are prompted for the root user password

To stop the server as a service:

NET STOP MySQL

Show the tables in a database

mysqlshow -u username -p dbname
where:
usernameis the name of a user authorized to access the database
dbnameis the name of the database

Connect to the server

mysql -u username -p 
where:
usernameis the name of a user authorized to access the database - after connecting to the server you have to connect to a database
You will be prompted for the password. Omit the -p if you are allowed to connect without a password.

Connect directly to a database

mysql -u username -p dbname
where:
usernameis the name of a user authorized to access the database
dbnameis the name of the database
This connects you directly to the specified database. You will be prompted for the password. Omit -p if you are allowed to connect without a password.

Connect to the server from a host

mysql -u username -h hostname -p 
where:
usernameis the name of a user authorized to access the database - after connecting to the server you have to connect to a database
hostnameis the name of the host you are connecting from
You will be prompted for the password. Sometimes it is necessary to specify the host you are connecting from, because the passwords and the privileges (i.e. the operations you are alloawed) are different depending on where you are connecting from.

3.8.2 Database operations after connection to the server

These are some of the commands you can use after being connected to the server.

Show the databases

show databases;

Connect to a database

connect dbname;
where:
dbnameis the name of the database

3.8.3 Security related commands

Security related informations are contained in the mysql database which is accessible only to a user having the WITH GRANT option. The following commands can be used after being connected as one of these users (the root user is one of them).

Creating and/or granting privileges to an account

Privileges are granted to a user by the GRANT command. The account can be created if it did not exist before. A simple form of the GRANT command is:
GRANT privileges ON dbname.tablename 
      TO 'username'[@'hostname'] [IDENTIFIED BY 'password']
      [WITH GRANT OPTION];
where:
-the square brackets [ ... ] denote optional parameters, not to appear in an actual command
-privilegesstands for a comma separated list of the granted privileges. Possible values for the privileges are:

ALL PRIVILEGESgrants the authority to do everything, except granting privileges
ALTERtable privilege -- grants authority to modify tables using the ALTER TABLE command
CREATEtable privilege -- grants authority to create tables a using the CREATE TABLE command
CREATE TEMPORARY TABLESgrants authority to create temporary tables using the CREATE TEMPORARY TABLE command
DELETEtable privilege -- grants authority to delete table rows using the DELETE Command
DROPtable privilege -- grants authority to drop a table using the DROP TABLE command
EXECUTEglobal privilege -- grants authority to run stored procedures (MySQL 5.0)
FILEglobal privilege -- grants authority to load a file using the SELECT ... INTO OUTFILE or LOAD DATA INFILE command
INDEXtable privilege -- grants authority to handle indexes using the CREATE INDEX and DROP INDEX commands
INSERTtable privilege -- grants authority to insert into a table using the INSERT command
LOCK TABLESgrants authority to lock tables for which you have the SELECT privilege, using the LOCK TABLES command
PROCESSglobal privilege -- grants authority to display the process list using the SHOW PROCESS LIST command
REFERENCESnot implemented as yet
RELOADglobal privilege -- grants authority to reload tables using the FLUSH command
REPLICATION CLIENTglobal privilege -- grants authority to ask where the slave and master servers are
REPLICATION SLAVEglobal privilege -- grants authority to replicate slaves
SELECTtable privilege -- grants authority to query tables using the SELECT command
SHOW DATABASESglobal privilege -- grants authority to display databases using the SHOW DATABASES command
SHUTDOWNglobal privilege -- grants authority to shut down the server using the mysqladmin shutdown utility command
SUPERglobal privilege -- grants authority to use the CHANGE MASTER, KILL thread, PURGE MASTER LOGS, SET GLOBAL commands and the mysqladmin debug command
UPDATEtable privilege -- grants authority to update tables using the UPDATE command
USAGEgrants NO privileges - this value is used to create spare users to be granted privileges when they are activated later on
When more than one privilege are granted, they are separated by commas. Intervening spaces are free.
-dbnameis the name of the database for which the privileges are granted.
The value * stands for "all databases".
-tablenameis the name of the table for which the privileges are granted.
The value * stands for "all tables".
-usernameis the name of the user to be granted the privileges
-hostnameis the host name from which the user has to connect to be granted the privileges. By default (@hostname omitted), this value is %, i.e. the user can connect from any host, except the local host
-passwordis the password assigned to the account. If the IDENTIFIED BY password parameter is omitted, the user has no password (anyone who knows its name can use it).
-GRANT OPTIONThe WITH GRANT OPTION grants the user the authority to use the GRANT command, i.e. to create new users and/or to modify the privileges of existing users
The global privileges are to be granted on all databases and tables (GRANT ... ON *.* ...). Only table privileges can be granted on tables.

Privileges can also be granted on specific columns. The syntax is as follows:

GRANT privilege(column_list), ... ON dbname.tablename ...

Revoking privileges from an account

Privileges are revoked by the REVOKE command. In its simple form, the REVOKE command has the syntax:
REVOKE privileges ON dbname.tablename FROM username [, username...]
The parameters have the same meaning as in the GRANT command.

The following command removes all privileges from the named users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM username [, username...]

Removing a user

To remove a user, you can use the SQL DELETE command:
DELETE FROM mysql.user WHERE user='username';
where username is the name of the user to be removed.

To remove the anonymous user

DELETE FROM mysql.user WHERE user='';

Setting a password

An account is defined for a user connecting from a host.

The password for an account of the root user can be set using the utility command mysqladmin:

mysqladmin -u root password [-h hostname] -p"newpsw";
-the square brackets [ ... ] denote optional parameters
-hostnameis the name of the host where the database is; if omitted, the localhost is presumed
-newpswis the new password assigned to the accounnt
-the -pis necessary if the account already has a password; it causes the password to be prompted for

After connecting to the server, you can change the password of an account using the SQL UPDATE command (this works with both Windows and Unix):

UPDATE mysql.user SET password = PASSWORD("newpsw")
       WHERE user='username' host='hostname';
where:
-myqsl.user(to be coded as shown) identifies the 'user' table in the 'mysql' database -- this is where user passwords are
-passwordshown in lower case, is the column containing the passwords, in the mysql.user
-PASSWORD(shown here in upper case, but can be in lower case, as all the other words) is the function which will encrypt the password so that the encrypted form is recorded
-newpswis the new password
-user(in the second line) is the column containing the user names, in the mysql.user table
-usernameis the name of the user
-hostis the column containing the host names, in the mysql.user table
-hostnameis the name of the host, in the account; if the host=hostname parameter is omitted, the new password is set in all of the accounts of the named user

On Windows, after connecting to the server, you can set the password for a user account, using the SET PASSWORD command:

SET PASSWORD FOR 'username'[@'hostname'] = PASSWORD('password');
-the square brackets [ ... ] denote optional parameters (not to appear in the actual command)
-usernameis the name of the user
-hostnameis the name of the host where the user connects from
-passwordis the password assign to the account
Examples:
SET PASSWORD FOR 'myname'@'localhost' = PASSWORD('mypass');
SET PASSWORD FOR 'myname' = PASSWORD('mypass');

A user connected under an account can change the password for that account using the command:

SET PASSWORD = PASSWORD ('password');

3.8.4 Other operations after connection to a database

Connect to another database

connect dbname;
where:
dbnameis the name of the database to connect to

Display the list of tables

show tables;

Display the definition of a table

describe tablename;
where:
tablenameis the name of the table to describe

top Contents Index References Cover Copyright previous next