by Jose M. Fernández <fgcia(at)correoweb.com> Content: Introduction Main Characteristics Installation First Steps. Administration (Security) The SQL Language under MySQL Conclusions

MySQL, A Database Server

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

• The main design goal of Mysql is speed and robustness
• Written in C and C++, tested with GCC 2.7.2.1. Uses GNU autoconf for portability.
• Clients in C, C++, JAVA, Perl, TCL.
• Multiprocessor support, the server can use several CPUs if available.
• Runs on various platforms and O.S.
• System of passwords and privileges very flexible and secure
• All passwords travel through the net encrypted
• Variable and fixed size records
• 16 indices per table, each index can be made of 1 to 15 columns or a part of them with a maximum length of 127 bytes
• All columns may have default values
• A utility (Isamchk) to check, optimize and fix tables
• All data is stored in ISO8859_1- format
• Clients use TCP or UNIX Sockets to communicate with the server
• The server supports error messages in several languages
• All commands have the options -help or -? to provide help
• Several types of columns, like integers of 1, 2, 3, 4 and 8 bytes, floats, double precision, character, dates, enum, etc.
• ODBC for Windows 95 (with sources), ACCESS can be used to connect with the server

Installation

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

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:

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 :

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:

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

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

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

Views information concerning a database, table or field.

Launches “mysqld”.

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

Used to report possible bugs found in the server.

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

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:

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:

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:

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:

• create database_name
  Creates a new database
• drop database_name
  Deletes the database named and all its tables
• kill process_id
  kill a process associated with mysql
• processlist
  List the processes running on the server
• shutdown
  Shutdown the server
• status
  Show current status of the server
• version
  Show the version number of the server

  For instance:

  mysqladmin create newdatabase
  

  creates a new database with the name "newdatabase"

  we can see the processes running on the server by running

  mysqladmin processlist
  

  Another important command is mysqlshow which let us see the databases available, for example executing that command without options gives:

  > mysqlshow
  
  

  	+-----------+
  	| Databases |
  	+-----------+
  	| mysql     |
  	| people    |
  	| test      |
  	+-----------+
  

  
   

  The SQL Language under MySQL

  In the introduction we already indicated that this server is considered one of the fastest within its class for large and small sets of data, and we also mentioned that this performance came at the expense of not implementing a number of features of SQL that in my opinion are important. Two important features left out are the Triggers and the Transactional Logic.

  Triggers are nothing but a small portion of code that gets "fired" --executed-- when a given operation is executed on the database (an update, delete, etc..). Obviously the test for the trigger condition as well as its management is something that consumes resources of the system and this is the only reason why they are not implemented.

  The consistency among the tables in a relational database is very important. SQL provides a more or less simple tool to provide for this consistency: "Transactional Logic". It is the server that should provide the mechanisms for blocking files as well as consolidation and regresion of operations in the database. Well, MySQL does not support the transactions in order to improve the speed of the server (at least that is what the documentation says), the only aid we have is to use the commands LOCK tables / UNLOCK tables that permit to block tables from other users use but not allowing us to remove the operations already performed with the data.

  Taking into account the limitations of the server we will next review a number of SQL commands, not with the goal of analysing SQL commands per se but to see how this server implements them.

  After launching the server we are ready to send instructions. For example, let us create a database named "people" that is made of three tables "clients", "states", "counties". It is a very simple and not very useful example but it gives us an idea how to manipulate the data in a real case. First we must say that these operations can be performed in several ways: through an API in C, C++, JAVA or though a ODBC if we were working under Windows95 (Pardon me again), we can also use the shell provided by the distribution. I will opt for the last method because for the purpose of this article it is enough and we avoid describing the specifics of other programing languages.

  The Mysql shell can be launched by running:

  mysql databasename
  
  

  after receiving the shell prompt we can start sending commands to the server.

  It is also possible to use the shell in batch mode by running:

  mysql -e (“SQL command …… “)databasename
  

  this sends an SQL command to the server.

  To create the database "people" of our example we execute the command:

  mysqladmin create people
  
  

  Then run the shell as

  mysql people
  
  

  now from the shell we can start to send commands to the server, for instance to view the tables available within the database:

  > show tables /g
  
  

  the system responds with:

  
  Database: people
  

  	+-------------+
  	|   Tables    |
  	+-------------+
  	| clients     |
  	| counties    |
  	|  states     |
  	+-------------+
  

  All commands sent to the server from the shell finish with /g, which indicates the end of command and submits it to the server for processing.

  Naturally, the only way we could have gotten the response above is if we had previously created the corresponding tables with the command CREATE. A typical CREATE command has the following look:

  	CREATE TABLE clients
  
  	      (NIF         CHAR(9)   NOT NULL PRIMARY KEY,
  	       Name        CHAR(15)  NOT NULL,
  	       Family_name CHAR(35)  NOT NULL,
  	       Address     CHAR(50)  NOT NULL,
  	       City        INT(5)    NOT NULL,
  	       State       INT(2)    NOT NULL,
  	       Phone       INT(9),
  	       Date        DATE      NOT NULL)   /g
  

  
  

      CREATE TABLE states    
  
  	      (Cod_state           INT(2)   NOT NULL PRIMARY KEY,
  	       Description_s       CHAR(30) NOT NULL)   /g
  

  
  

      CREATE TABLE counties
  	      (Cod_state           INT(2)    NOT NULL,
  	       Cod_county          INT(3)    NOT NULL,
  	       Description_c       CHAR(30)  NOT NULL,
  	          PRIMARY KEY(Cod_state,Cod_county)) /g
  

  
  

  If we run next

  > show colums from clients from people /g
  > show columns from states from people /g
  > show columns from counties from people /g
  
  

  we would obtain:

  Database: people   	Table: clients   Rows: 4
  +--------------+----------+------+-----+---------+-------+
  | Field        | Type     | Null | Key | Default | Extra |
  +--------------+----------+------+-----+---------+-------+
  | NIF          | char(9)  |      |PRI  |         |       |
  | Name         | char(15) | YES  |     |         |       |
  | Family_name  | char(35) |      |     |         |       |
  | Address      | char(50) |      |     |         |       |
  | City         | int(5)   |      |     |   0     |       |
  | State        | int(2)   |      |     |   0     |       |
  | Phone        | int(9)   | YES  |     |         |       |
  | Date         | date     | YES  |     |         |       |
  +--------------+----------+------+-----+---------+-------+
  

  Database: people   	Table: states      Rows: 3
  +-----------------+----------+------+-----+---------+-------+
  | Field           | Type     | Null | Key| Default  | Extra |
  +-----------------+----------+------+-----+---------+-------+
  | Cod_state       | int(2)   |      | PRI |    0    |       |
  | Descripcion_s   | char(30) |      |     |         |       |
  +-----------------+----------+------+-----+---------+-------+
  

  Database: people   	Table: counties    Rows: 9
  +------------------+----------+------+-----+---------+-------+
  | Field            | Type     | Null | Key| Default  | Extra |
  +------------------+----------+------+-----+---------+-------+
  | Cod_state        | int(2)   |      | PRI |   0     |       |
  | Cod_county       |  int(3)  |      | PRI |   0     |       |
  | Descripcion_c    | char(30) |      |     |         |       |
  +------------------+----------+------+-----+---------+-------+
  

  Afterwards we would proceed to insert data in each of the tables. Let us use the SQL command INSERT without any participation of other programming languages nor API routine:

  To insert a record in the clients, counties and states tables do this:

  INSERT INTO clients VALUES

  ("5316828K","PEDRO","ROMERO DIAZ","C/ HOLA 9 ",29003,29,911111111,19980203)

  /g

  
  

  INSERT INTO counties VALUES

  (28,001,"Fuenlabrada") /g

  
  

  INSERT INTO states VALUES

  (08,"Barcelona") /g

  
  

  To conclude our excursion with the SQL commands we will select the rows inserted in the tables of the current database. Let us select records from the clients table varying the condition for selection and selecting first from the table of counties:

  > SELECT ALL Cod_state, Cod_county, Description_c from counties where Cod_state = 28 /g
  

  Cod_state	Cod_county	Description_c
  28              1               Fuenlabrada
  28              2               Pozuelo
  28              3               Madrid
  

  > SELECT ALL NIF,Name,Family_name,Address from clientes where City = 28001
  

  NIF Name Family_name direccion

  2416728U JOSE FERNANDEZ ROMERO C/ FELIZ 1 3G

  
  

  
  

  > SELECT ALL NIF,Name,Family_name,Address from clients

  where State = 29

  
  

  NIF        Name           Family_name           Address
  23198623N  JUAN ANDRES    RUIZ MORALES          C/ CATEDRAL 12 2B
  5316828K   PEDRO          ROMERO DIAZ           C/ HOLA 9
  52312844J  LUIS ALBERTO   LISTO JIMENEZ         C/ ROSA DE LOS VIENTOS 129  3I
  

   

  Conclusions

  We opened this article saying that our purpose with this article was to show the fundamental characteristics of a specific SQL server, we did not want to reduce the article to a list of recipes and commands to use MySQL but instead we wished to study the possibilities and limitations of this software; only knowing in depth an application like this one we can truly get the greatest advantages it has to offer. MySQL omits the implementation of triggers and transactional logic and therefore makes the management of data (inserting, modifying, deleting records) very complex for multiuser applications and using numerous interelated tables. Nevertheless, I recommend this server for applications requiring very fast access of large databases.

  Finally I would like to mention that most of the information in this article was obtained from the MySQL documentation included in the distribution, from several articles in technical magazine as well as from an already yellowish IBM manual about SQL.

  ---

  Webpages maintained by the LinuxFocus Editor team © Jose M. Fernández, FDL LinuxFocus.org

  Translation information: es --> -- : Jose M. Fernández <fgcia(at)correoweb.com>

  2002-10-22, generated by lfparser version 2.32