MySQL
Serveur de base de données

Résumé: MySQL est un serveur de base de données SQL (Structured Query Language) disponible sur de nombreuses plate-formes, dont Linux. La conception de la version Linux a privilégié les performances.

Introduction

MySQL est un serveur de base de données SQL (Structured Query Language) . Il s'agit d'une application client-serveur composée d'un serveur et d'un certain nombre de clients.

L'ambition de cet article n'est pas de définir ce qu'est une base de données SQL, car cela signifierait revenir aux origines des bases relationnelles, nous essaierons simplement de donner une présentation, une description de l'implémentation d'un serveur SQL spécifique.

Rappelons pour l'histoire qu'IBM a mis SQL sur le marché en 1981 et que depuis ce produit a joué un rôle fondamental dans le développement des bases de données relationnelles. IBM a proposé une version de SQL à l'Institut National Américain des Standards (ANSI) (qui fut accepté par la suite) et depuis l'usage de ce dernier s'est généralisé dans les bases de données relationnelles. DB2, la base de donnée la plus répandue à l'époque, a été créée en 1983 et est utilisée pricipalement en environnement mainframe.

Dans le monde GNU, l'une des bases de données les plus citées dans le domaine des bases relationnelles sous Linux est MySQL. Cette application n'est incluse dans aucune des distributions Linux car elle n'est pas disponible sous licence de type GNU et l'utilisation commerciale de MySQL ou des applications qui l'exploitent nécessite l'acquisition d'une license.

Ce serveur de base de données est considéré (c'est même mentionné explicitement dans la documentation) comme le plus rapide et le plus robuste pour des volumes de données importants ou modestes. (en le comparant aux autres serveurs de sa catégorie bien sûr). Comme nous l'expliquerons plus loin, la rapidité de traitement est obtenue au détriment de l'implémentation d'un certain nombre de fonctionnalités du standard SQL.

MySQL est disponible (Sources et Binaires) pour : 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 (désolé), et sur les systèmes les plus répandus.

La version libre a été développée par Michael Windenis, et la version commerciale est distribuée par TCX Datakonsulter AB.

Caracteristiques principales

• Les premiers objectifs du design de MySQL sont vitesse et robustesse.
• Ecrit en C et C++, testé avec GCC 2.7.2.1. Utilise GNU autoconf pour la portabilité.
• Clients en C, C++, JAVA, Perl, TCL.
• Support Multiprocesseur, le serveur peut exploiter plusieurs CPU s'ils sont présents.
• Fonctionne sur de multiples plate-formes et O.S.
• Système de mots de passe et d'autorisations très flexible et sûr.
• Tous les mots de passe sont cryptés avant transmission.
• Enregistrements de taille fixe et variable.
• 16 indices par table, chaque index est constitué de 1 à 15 colonnes ou une partie d'entre elles jusqu'à une longueur maximum de 127 octets.
• Toutes les colonnes peuvent avoir des valeurs par défaut.
• Un utilitaire fourni (Isamchk) contrôle, optimise et répare les tables.
• Toutes les données stockées sont au format ISO8859_1.
• Les clients utilisent des Sockets TCP ou UNIX pour communiquer avec le serveur.
• Le serveur permet de choisir la langue des messages d'erreur.
• Toutes les commandes proposent de l'aide à travers les options -help ou -?.
• Les types des colonnes peuvent être choisis parmi un large choix : entiers de 1,2,3,4 et 8 octets, flottants, double précision, caractères, dates, énumérations, etc.
• ODBC pour Windows 95 (avec les sources), ACCESS peut être utilisé pour se connecter au serveur

Installation

Naturellement, il est nécessaire de télécharger les sources sur le WEB avant d'installer la base de données :

D'abord, il faut décider de télécharger une distribution source ou binaire. La seconde est plus simple à installer, mais elle n'est pas disponible pour toutes les plate-formes (En fait, elle est disponible pour toutes les plate-formes habituelles).

Installation Binaire

Après avoir téléchargé le fichier :

procéder à son désarchivage. Il y a de nombreuses méthodes, voici celle que je préfère :

1. décomprer d'abord :
   gunzip -dfv mysql-Version.tar.gz
2. puis extraire les fichiers de l'archive :
   tar -xvf mysql-Version.tar
   

   En lançant la commande tar à partir du répertoire /usr/local, les fichiers de l'archive seront placés dans
   /usr/local/mysql-3.20.32a-pc-linux-gnu-i586

   Ce n'est pas un nom très pratique, c'est pourquoi il est recommandé (conformément au guide d'installation) d'établir un lien symbolique vers ce répertoire.

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

   Le répertoire mysql contient alors :

   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-xr-x 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

   et avec un cd mysql nous nous retrouvons dans le répertoire des binaires de MySQL. Si tout s'est bien passé, vous êtes prêts à lancer le serveur.

Installation à partir des Sources

Décompresser et désarchiver les sources comme à la section précédente.

cd mysql-Version

./configure

make install

La distribution source est livrée avec beaucoup de documentation sur le processus d'installation. On trouve des informations sur les bogues connus, des notes spécifiques aux plate-formes, plusieurs suggestions de paramétrage pour différentes configurations et même une collection de FAQ. Si le processus d'installation se déroule correctement à la première tentative (et ce serait bien la première fois) le résultat sera semblable au répertoire des binaires de la distribution précompilée.

Une installation à partir des sources n'est recommandée qu'aux utilisateurs ayant une bonne expérience dans l'installation et la compilation de programmes et qui ont assez de temps et de patience pour résoudre les quelques problèmes qui ne manqueront pas d'apparaître lors de cette procédure.

Premiers Pas. Administration (Sécurité)

Après l'installation du serveur sur votre système avec l'une ou l'autre des méthodes décrites précédemment, le répertoire suivant :

devrait contenir ces fichiers et répertoires:

-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

Pour plus d'information sur l'installation du serveur, consultez les fichiers README, TODO, INSTALL, mysql-faq, etc., qui sont particulièrement complets et efficaces (cet article se base en partie sur eux).

Le répertoire /data contiendra toutes les bases que vous allez créer sur le système, chacune placée dans un sous-réperoire séparé. L'installation initiale crée par défaut les fichiers de la base dédiée aux fonctions de sécurité du serveur dont le nom est “mysql”.

Il y a plusieurs exemples de code SQL dans /bench . Notez que la distribution source contient un plus grand nombre d'examples que les installations binaires.

Dans le répertoire /share se trouvent les messages d'erreur du serveur dans chacune des langues supportées.

/include and /lib contiennent les en-têtes et les bibliothèques de la distribution.

Sans surprise, /bin contient tous les exécutables, dont les plus importants sont:

Un shell SQL (utilisant GNU readline). Il peut être utilisé en intéractif ou non.

Outils d'Administration. Création/Suppression de bases. Information sur les processus et les versions.

Le “deamon” SQL. Il doit s'exécuter de façon continue.

Consultation d'information concernant une base, une table ou un champ.

Lance “mysqld”.

Script qui contrôle les droits associés à une combinaison : machine, utilisateur et base de donnée.

Utilitaire d'assistance au rapport de bogues trouvés dans le serveur.

Crée des tables immenses avec des droits par défaut, il est exécuté en général après chaque nouvelle installation.

Contrôle, optimise et corrige les tables.

Sécurité

Le système de sécurité de MySQL garantit que chaque utilisateur ne peut réaliser que les tâches qui lui ont été explicitement autorisées (ni plus ni moins).

Le système choisit les droits pour une transaction selon “QUEL UTILISATEUR” à partir de “QUELLE MACHINE” est connectée à “QUELLE BASE”. Le système de permission est basé, pourquoi pas, sur le contenu de 3 tables, “USER”, “HOST” and “DB” de la base “mysql”.

Les colonnes de ces trois tables sont :

On peut choisir d'autoriser ou non les commandes SELECT, INSERT, UPDATE, et DELETE pour chaque table.

Il est également possible de contrôler l'utilisation de CREATE et de DROP (supprimer) sur les tables et les bases.

Un autre droit intéressant concerne l'accès access aux commandes d'administration comme “shutdown”, “reload”, “process”, etc.

Les permissions en vigueur peuvent être vérifiées avec le script “mysqlaccess”.

Un HOST doit être “une machine locale”, une adresse IP ou une expression SQL. Si la colonne host est laissée vide dans la table “db” cela signifie une machine“ quelconque #148; de la table “host”. Si par contre c'est dans la table #147;host” ou “user” que la colonne host est vide, cela ignifie que toutes les machines peuvent étabir une connection TCP vec le serveur.

Db est le nom de la base de données.

Une colonne “USER” vide signifie un utilisateur quelconque.

Premiers pas

La méthode la plus rapide pour démarrer le serveur est de lancer la commande suivante :

La même opération peut être réalisée avec le script safe_mysql, comme l'explique le guide d'installation, et d'une façon ou d'une autre ces fichiers exécutent le deamon “mysqld”.

Comme on le comprend facilement, il est nécessaire de lancer le serveur avant de réaliser une quelconque opération sur la base ; lorsque le serveur est actif, on peut exécuter des commandes comme “mysqladmin” dont la syntaxe est :

où OPTIONS peut être :

-f, --force Ne demande pas confirmation à l'utilisateur pour effacer une table.

-?, --help Affiche ce message d'aide.

-h, --host=# Connection au serveur.

-p, --password[=...] Mot de passe d'accès au serveur.

-P, --port=... Numéro de port à utiliser pour la connexion.

-S, --socket=... Fichier socket à utiliser pour la connexion

-u, --user=# Nom d'utilisateur pour la connexion si différent de l'utilisateur courant.

-V, --version Affiche des informations sur la version courante du serveur.

et où command est une ou plusieurs des commandes suivantes :

• create database_name
  Crée une nouvelle base.
• drop database_name
  Supprime la base spécifiée et toutes ses tables.
• kill process_id
  Tue un procesus associé à mysql
• processlist
  Affiche une liste des processus exécuté sur le serveur.
• shutdown
  Arrête le serveur
• status
  Affiche des informations courantes sur le serveur
• version
  Affiche le numéro de version du serveur

  Par exemple en exécutant :

  mysqladmin create newdatabase
  

  une nouvelle base nommée "newdatabase" est créée.

  Nous pouvons voir les processus actifs sur le serveur avec :

  mysqladmin processlist
  

  Mysqlshow est une autre commande importante qui affiche la liste des bases existantes, et en l'exécutant sans options, on obtient :

  > mysqlshow
  
  

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

  
  

  Le langage SQL avec MySQL

  Dans l'introduction, nous avons déjà indiqué que ce serveur est considéré comme un des plus rapides de sa catégorie pour de grandes et des petites quantités de données et nous avons également mentionné que ces performances s'expliquaient par l'abandon de certaines fonctionnalités du langage SQL qui me paraissent importantes. En particulier, les triggers et la logique transactionnelle ont été laissés de côtés.

  Les Triggers ne sont rien d'autres que des courtes portions de code qui sont déclenchées (fired), exécutées quand une certaine opération est exécutée sur la base (une mise à jour, une suppression,...). Evidemment le test des conditions associés aux triggers comme leur gestion consomme des ressources système et c'est la seule raison pourquoi ils n'ont pas été implementés.

  La cohérence des données entre les tables est très importante dans une base de données relationnelle. SQL offre un outil plus ou moins simple pour assurer cette cohérence : la "Logique Transactionnnelle". Il revient au serveur de fournir les mécanismes de verrouillage de fichiers comme de consolidation et de régression de la base. Voilà, MySQL ne supporte pas les transactions dans l'objectif d'améliorer la vitesse du serveur (du moins, c'est ce que dit la documentation), la seule assistance disponible est celle des commandes LOCK tables / UNLOCK tables qui nous permettent de verrouiller des tables aux autres utilisateurs mais pas d'annuler les opérations réalisées sur les données.

  Connaissant maintenant les limitations du serveur, nous allons passer en revue quelques commandes SQL, non pour les analyser elle-même mais pour voir comment le serveur les implémente.

  Après le lancement du serveur, nous sommes prêts à envoyer des requêtes. Par exemple, créons une base nommée "people" qui est composée de trois tables "clients" "states" "counties". C'est un exemple très simple et pas vraiment utile mais il nous donnera une idée de la façon de manipuler les données dans un cas réel. Tout d'abord, il faut dire que des opérations sur la base peuvent être réalisés de multiples manières : via une API en C, C++, JAVA ou OBDC si nous travaillons sous Windows95 (encore une fois pardon), et nous pouvons également utiliser le shell fourni avec la distribution. J'opterai pour cette dernière solution car elle nous suffira et nous évitera des descriptions spécifiques à un langage dans le cadre de cet article.

  Le shell Mysql peut être lancé en exécutant :

  mysql databasename
  
  

  après avoir obtenu le prompt du shell, nous pouvons commencer à envoyer des commandes au serveur.

  Le shell peut également être utilisé en mode non intéractif par la commande :

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

  qui envoie une requête SQL au serveur.

  Pour créer la base "people" de notre exemple, nous exécutons la commande :

  mysqladmin create people
  
  

  Puis lançons le shell avec

  mysql people
  
  

  Maintenant, nous pouvons commencer à lancer des commandes au serveur concerné via le shell server, par exemple si l'on souhaite voir la liste des tables existantes dans la base :

  > show tables /g
  
  

  le système répond par :

  
  Database: people
  

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

  Toutes les commandes destinées au serveur se terminent par /g lorsqu'elles sont saisies dans le shell, ce dernier reconnait ainsi la fin de la commande et l'envoie au serveur qui réalise le traitement.

  Naturellement, nous n'aurions pas pu obtenir la réponse précédente si nous n'avions pas préalablement créé les tables correspondantes avec la commande CREATE. Une commande CREATE typique ressemble à :

  	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          
  

  
  

  Si ensuite nous lançons :

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

  nous obtenons :

  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) |      |     |         |       |
  +------------------+----------+------+-----+---------+-------+
  

  Après cela, nous pourrions commencer à insérer des données dans chacune des tables. Utilisons la commande SQL INSERT indépendemment de tout autre langage de programmation ou API :

  Pour insérer un enregistrement dans les tables clients, counties et states, exécutons :

  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

  
  

  Pour conclure cette aperçu des commandes SQL, nous allons retrouver les lignes que nous avons insérées dans les tables de la base courante. Commençons par rechercher les enregistrements de la table clients qui vérifient une condition, d'abord sur la table 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

  Nous avons ouvert cet article en expliquant que notre sujet consistait à montrer les principales caractéristiques d'un serveur SQL particulier, que nous ne voulions pas transformer ce texte en une liste de recettes de cuisine et de commandes pour MySQL. Au lieu de cela, nous voulions étudier les fonctionnalités et les limitations de ce logiciel. Ce n'est qu'en connaissant en profondeur ce genre d'application que l'on peut vraiment profiter de toutes les fonctionnalités qu'elle propose. MySQL ne dispose pas d'implémentation des triggers et de la logique transactionnelle, ce qui rend la gestion des données (insertions, mises à jour, suppressions d'enregistrements) très complexes dans le cas d'applications multi-utilisateurs utilisant de nombreuses tables interdépendantes. Néanmoins, je recommande ce serveur pour des applications nécessitant des accès très rapides à de grandes bases de données.

  Pour conclure, je souhaite mentionner que la plupart des informations de cet article proviennent de la documentation de MySQL incluse dans la distribution, de plusieurs articles de la presse spécialisée ainsi que d'un manuel SQL d'IBM déjà jaunissant.