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 design 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 acomplished 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: 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:
- uncrompress first :
gunzip -dfv mysql-Version.tar.gz
- retrieve files from the archive second:
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 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 (and that may be the first time anything that happens) 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'
Use 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 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, why not, on the contents of 3 tables, USER, HOST and DB of the database mysql.
The columns of these three tables are:
Database: mysql
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 |
|
If can be decided whether to authorize or not SELECT, INSERT, UPDATE, and DELETE files in a table.
It is also possible to allow or not 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 host local, an IP number or an SQL expression. If in the table db the host column is empty it means any host#148; 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:
- 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 running: 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 |
+-----------+
SQL Language under MySQL
In the introduction we already indicated how 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.
Aftern 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 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 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 proper 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 next run
> 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 from 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.
-
|