[Top bar]
[Bottom bar]
This document is available in: English  Castellano  ChineseBig5  Deutsch  Francais  Nederlands  Portugues  Russian  Korean  

[Photo of the Author]
by Jose M. Fernández
<fgcia(at)correoweb.com>


Content:

 

MySQL, A Database Server

[Illustration]

Abstract:

MySQL is an SQL (Structured Query Language) database server supported by a number of platforms, including Linux. Its Linux version is designed with fast performance in mind.



 

Introduction

MySQL is a SQL (Structured Query Language) database server. It is a client-server application formed by a server and a number of clients.

Discussing an SQL database is very complex since one would have to go back to the origins of relational databases and the aim of this article is not that ambitious, we simply seek to describe and demonstrate a specific implementation of an SQL server.

As a historical reminder let us mention that IBM began to market SQL in 1981 and since then this product has had a fundamental role on the development of relational databases. IBM proposed a version of SQL (which was later accepted) to the American National Standards Institute (ANSI) and since then it has found generalized use in relational databases. DB2, at the moment the most popular database of this type, was created in 1983 and is used mostly in the mainframe environment.

In the GNU world one of the databases most often quoted in the context of relational databases under Linux is MySQL. This application is not included in any Linux distribution because it does not have a GNU type license per se; commercial use of MySQL or any application that uses it requires acquiring a license.

This database server is considered (even mentioned explicitly in its documentation) as the fastest and most robust for large and small volumes of data (of course when comparing it with other servers within its own category). As we will discuss later on, the speed in processing is accomplished at the expense of not implementing a number of features of the SQL standard.

MySQL is available (Sources and Binary) for: Linux 2.0+, SCO, Solaris 2.5, 2.6, SUNOS 4.x, BSDI 2.x, 3.0, SGI IRIX 6.x, AIX 4.x, DEC UNIX 4.x, HPUX 10.20, Windows 95 (pardon me) , amongst the most popular operating systems.

The free version was written by Michael Windenis, and the comercial version is distributed by TCX Datakonsulter AB.

 

Main Characteristics

 

Installation

Naturally, before installing the database it is necessary to download the sources from the WEB:

http://www.tcx.se


or FTP :
ftp://ftp.sunet.se/pub/unix/databases/relational/mysql

First, we must decide to download a source or binary distribution. The last option is the easiest to install but it must be available for our target platform (it is indeed available for most popular platforms).

Binary Installation

After downloading the file:

mysql-Version.tar.gz

proceed to unpack it. It can be done many ways, I personally prefer:

  1. uncompress first :
    gunzip -dfv mysql-Version.tar.gz
  2. then extract the files from the archive:
    tar -xvf mysql-Version.tar

    I run the tar command from the /usr/local directory, therefore the extracted files will be found under
    /usr/local/mysql-3.20.32a-pc-linux-gnu-i586

    This is not a very practical name so it is recommended (also mentioned in the installation guide) that we make a symbolic link to this location

    > ln -s mysql-3.20.32a-pc-linux-gnu-i586/bin mysql

    The mysql directory contains the following:

    drwxr-xr-x 8192 Nov 24 1993 bin
    drwxr-xr-x 8192 Nov 24 1993 etc
    drwxr-xr-x 8192 Aug 17 1997 i18n
    drwxr-xrx 8192 Mar 16 1994 include
    drwxr-xr-x 8192 Mar 19 02:03 jdk1.1.3
    drwxr-xr-x 8192 Aug 17 1997 jre
    drwxr-xr-x 8192 Mar 16 1994 lib
    lrwxrwxrwx 36 Jan 18 19:40 mysql
    drwxr-xr-x 8192 Feb 5 00:07 mysql-3.20.32a-pc-linux-gnu-i586
    drwxr-xr-x 8192 Nov 24 1993 sbin
    drwxr-xr-x 8192 Nov 24 1993 src

    and by executing cd mysql we get to the directory of binaries of MySQL. If everything went well you are ready to lauch the database server.

Source Installation

Uncompress and unpack the sources as suggested in the previous section.


cd mysql-Version
./configure
make install

The source distribution comes with a large amount of documentation on the installation process. There is information on known bugs, platform specific notes as well as suggestions for various operating systems, descriptions of the parameters for several configurations and even a collection of FAQ. If the process of installation goes well the first time the result would be a binary directory like the one described in the binary installation.

An installation from sources is only recommended for users with good experience in installation and compilation of programs and who have enough time and patience to tackle on the number of problems that will undoubly emerge during the procedure.

 

First Steps. Administration (Security)

Upon installation of the server in your system following either of the two methods already described the following directory :

/usr/local/mysql-3.20.32a-pc-linux-gnu-i586

should contain these files and directories:

-rw-r--r-- 1 root root 4133 Oct 1 1997 INSTALL-BINARY
-rw-r--r-- 1 root root 16666 Oct 7 21:10 INSTALL-SOURCE
-rw-r--r-- 1 root root 24088 Oct 27 23:06 NEWS
-rw-r--r-- 1 root root 3562 Apr 11 1997 PORTING
-rw-r--r-- 1 root root 8512 May 21 1997 PUBLIC
-rw-r--r-- 1 root root 1963 Jul 31 1997 README
-rw-r--r-- 1 root root 3416 Jun 4 1997 TODO
drwxr-xr-x 6 root root 8192 Oct 28 00:44 bench
drwxr-xr-x 2 cuenta1 users 8192 Mar 27 00:42 bin
drwxr-xr-x 5 root root 8192 Mar 31 00:26 data
drwxr-xr-x 2 root root 8192 Oct 28 00:44 include
drwxr-xr-x 2 root root 8192 Oct 28 00:44 lib
-rw-r--r-- 1 root root 132883 Jun 8 1997 mysql-faq.html
-rw-r--r-- 1 root root 117622 Jun 10 1997 mysql-faq.txt
-rw-r--r-- 1 root root 9301 Jun 8 1997 mysql-faq_toc.html
drwxr-xr-x 4 root root 8192 Oct 28 00:44 mysqlperl
drwxr-xr-x 2 root root 8192 Oct 28 00:44 scripts
drwxr-xr-x 3 root root 8192 Oct 28 00:44 share
drwxr-xr-x 2 root root 8192 Oct 28 00:44 tests

For more information on the installation of the server take a look at the files README, TODO, INSTALL, mysql-faq, etc., which are very complete and effective (part of this article is based on them).

The directory /data will host any databases that you create on the system, they will be stored in separate subdirectories. The initial installation creates by default the archives supporting security features in the server, that database is “mysql”.

There are several examples of SQL in /bench . Notice that installations from source code include a larger amount of examples than binary installations.

Now the directory /share contains the error messages for the server in each of the languages available.

/include and /lib contain the header files and libraries of the distribution.

As expected /bin contains all the executables, among them the most important are:


`mysql'

An SQL Shell (with GNU readline). It can be used either interactively or not.


`mysqladmin'

Administration Tools. Create/Delete databases. Information about processes and version.


`mysqld'

SQL “deamon” . It must be running all the time.


`mysqlshow'

Views information concerning a database, table or field.


`safe_mysqld'

Launches “mysqld”.


`mysqlaccess'

Script to check the privileges of a combination: Host, User and Databse.


`mysqlbug'

Used to report possible bugs found in the server.


`mysql_install_db'

Creates huge tables with default privileges, it is usually run after installing for the first time a new system.


`isamchk'

Checks, optimizes and fixes tables.

Security

The security system on MySQL warranties that each user can only perform strictly authorized tasks (no more and no less).

The system chooses privileges for a transaction according to “WHICH USER” from “WHICH HOST” is connected to a “GIVEN DATABASE”. The system of permissions is based, on the contents of 3 tables, “USER”, “HOST” and “DB” of the database “mysql”.


The columns of these three tables are:

Database: mysql
Tables
db
host
user


Table: db
Field Type Null Key Default Extra
Host char(60)   PRI    
Db char(32)   PRI    
User char(16)   PRI    
Select_priv char(1)     N  
Insert_priv char(1)     N  
Update_priv char(1)     N  
Delete_priv char(1)     N  
Create_priv char(1)     N  
Drop_priv char(1)     N  

Table: host
Field Type Null Key Default Extra
Host char(60)   PRI    
Db char(32)   PRI    
Select_priv char(1)     N  
Insert_priv char(1)     N  
Update_priv char(1)     N  
Delete_priv char(1)     N  
Create_priv char(1)     N  
Drop_priv char(1)     N  

Table: user
Field Type Null Key Default Extra
Host char(60)   PRI    
User char(16)   PRI    
Password char(16)        
Select_priv char(1)     N  
Insert_priv char(1)     N  
Update_priv char(1)     N  
Delete_priv char(1)     N  
Create_priv char(1)     N  
Drop_priv char(1)     N  
Reload_priv char(1)     N  
Shutdown_priv char(1)     N  
Process_priv char(1)     N  
File_priv char(1)     N  

It can be decided whether to authorize or not SELECT, INSERT, UPDATE, and DELETE files in a table.

It is also possible to allow to CREATE or DROP (delete) tables or databases.

Another interesting permission available is access to the administrative commands like “shutdown”, “reload”, “process”, etc.

The current permissions can be inspected with the script “mysqlaccess”.

A HOST must always be a “local host”, an IP number or an SQL expression. If in the table “db” the host column is empty it means “any host” in the table of “host”. If on the other hand in the table “host” or “user” the column host is empty it means that any HOST can establish a TCP connection with our server.

Db is the name of the database.

An empty “USER” column means any user name.

First Steps

The fastest way to launch the server is running the following command:

mysql.server start

and to stop it :

mysql.server stop

The same operations can be performed with the script safe_mysql, as indicated in the installation guide, but one way or another the file result is to execute the deamon “mysqld”.

As it can be easily understood, it is necessary to launch the server to perform any operation with the database; with the server running we can run operations like “mysqladmin” whose syntax is:

mysqladmin [OPTIONS] command command …

where OPTIONS can be :

-f, --force Does not prompt the user for confirmation when deleting a table.
-?, --help Shows the present help menu .
-h, --host=# Connection to the host.
-p, --password[=...] Access password to the server.
-P --port=... Port number to use for the connection.
-S --socket=... Socket file to be used for the connection
-u, --user=# User for the connection if not the current user.
-V, --version Show information about the current server version .

where command can be one or more of the following: