![[Photo of the Author]](../../common/images/Who.gif)
original in es Jose M. Fernández
![[Illustration]](../../common/images/illustration21.jpg)
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.
Naturally, before installing the database it is necessary to download the sources from the WEB:
http://www.tcx.seFirst, 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).
After downloading the file:
mysql-Version.tar.gzproceed to unpack it. It can be done many ways, I personally prefer:
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 mysqlThe mysql directory contains the following:
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.
Uncompress and unpack the sources as suggested in the previous section.
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.
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-i586should contain these files and directories:
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.
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:
| Tables |
|---|
| db |
| host |
| user |
| 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 |
| 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 |
| 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.
The fastest way to launch the server is running the following command:
mysql.server startThe 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 :
where command can be one or more of the following:
For instance:
mysqladmin create newdatabasecreates a new database with the name "newdatabase"
we can see the processes running on the server by running
mysqladmin processlistAnother important command is mysqlshow which let us see the databases available, for example executing that command without options gives:
> mysqlshow+-----------+ | Databases | +-----------+ | mysql | | people | | test | +-----------+
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 databasenameafter 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 …… “)databasenamethis sends an SQL command to the server.
To create the database "people" of our example we execute the command:
mysqladmin create peopleThen run the shell as
mysql peoplenow from the shell we can start to send commands to the server, for instance to view the tables available within the database:
> show tables /gthe system responds with:
+-------------+ | 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
CREATE TABLE states
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 /gwe 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:
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 /gCod_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 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
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.