Utilisation avancée de MySQL¶

MySQL: La suite logicielle¶

MySQL est une suite logicielle comprenant un grand nombre de programmes. Il opère dans un environnement réseau selon un schéma client/serveur.

Il comporte principalement:

le serveur (''mysqld'') qui gère l'accès aux bases de données sur la machine hôte. Le serveur tourne en tâche de fond (= daemon).
un ou plusieurs programmes clients. Parmi ceux-ci, on peut citer:
- ''mysql'' est un client texte qui permet une interaction directe avec le serveur MySQL;
- ''mysqldump'' permet la réalisation de sauvegarde;
- ''mysqladmin'' pour l'administration du serveur (gestion des droits, etc.);
- ''mysqlcheck'' pour la maintenance et la réparation des tables;
- ''mysqlimport'' pour l'importation de fichiers de données;
- ...

<code text| Fonctionnement du client mysql> $ mysql --help mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Copyright (C) 2002 MySQL AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license

Usage: mysql [OPTIONS] [database]

?, --help Display this help and exit.
I, --help Synonym for -?
h, --host=name Connect to host.
u, --user=name User for login if not current user.
p, --password[=name] Password to use when connecting to server. If password is not given it's asked from the tty. WARNING: This is insecure as the password is visible for anyone through /proc for a short time.
D, --database=name Database to use.
e, --execute=name Execute command and quit. (Disables --force and history file)
f, --force Continue even if we get an sql error.
H, --html Produce HTML output.
X, --xml Produce XML output </code>

<code text| Usage de mysqldump> $ mysqldump -? mysqldump Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (i486) By Igor Romanenko, Monty, Jani & Sinisa This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS]

A, --all-databases Dump all the databases. This will be same as --databases with all databases selected.
-add-drop-database Add a 'DROP DATABASE' before each create.
-add-drop-table Add a 'drop table' before each create.
h, --host=name Connect to host.
l, --lock-tables Lock all tables for read.
p, --password[=name] Password to use when connecting to server. If password is not given it's solicited on the tty. WARNING: Providing a password on command line is insecure as it is visible through /proc to anyone for a short time.
u, --user=name User for login if not current user. </code>

En dehors des programmes fournis par MySQL AB avec la version officielle de MySQL, il existe une multitude de logiciels clients plus ou moins gratuits. L'un des plus utilisés est phpMyAdmin.

Moteurs de stockage¶

Le serveur MySQL utilise des moteurs de stockage pour gérer les données des tables, chacun gérant un type de table particulier. Chaque type de table possède différentes caractéristiques et fonctions. A la construction d'une base de données avec MySQL, il est donc possible de choisir un moteur de stockage de MySQL en adéquation avec l'utilisation envisagée.

MySQL (version 5.1) supporte, entre autres, les moteurs de stockage suivants (liste non exhaustive):

MyISAM: le moteur par défaut de MySQL
InnoDB: utilisé pour les applications mettant en jeu des transactions. Il supporte les fonctions ACID (''COMMIT'' et ''ROLLBACK'')
Memory: stocke les tables en mémoire RAM pour en optimiser l'accès
Federated: permet de relier des serveurs MySQL séparés afin de créer une base de données logique globale.
NDBCluster: permet de distribuer une base de données sur plusieurs serveurs afin de fournir un haut degré de disponibilité.
etc.

La définition de l'utilisation d'un moteur de stockage s'effectue lors de la création d'une table: <code sql| Définition d'un moteur de stockage> CREATE TABLE toto ( ... ) ENGINE=InnoDB; </code> ou après: <code sql| Modification du moteur de stockage d'une table> ALTER TABLE nom_de_la_table ENGINE=InnoDB; </code>

Gestion des transactions¶

Contrairement au moteur MyISAM, le moteur de stockage InnoDB fournit des fonctionnalités transactionnelles. Elles permettent de regrouper en une transaction unique un groupement logique d'instructions géré par le serveur de base de données: toutes les instructions s'exécutent avec succès et sont considérés comme terminées (''COMMIT'') ou toutes les modifications opérées par les instructions sont supprimées si une erreur intervient ou à la demande du client (''ROLLBACK'').

InnoDB satisfait aux conditions de la compatibilité ACID.

Par défaut, MySQL active le mode ''AUTOCOMMIT'' qui amène chaque instruction à être immédiatement validée. En termes transactionnels, cela signifie que chaque instruction correspond à une transaction séparée.

Pour regrouper plusieurs instructions dans une unique transaction, le mode autocommit doit être désactivé. Ceci peut être effectué:

soit en désactivant explicitement le mode autocommit: <code sql|Désactivation du mode AUTOCOMMIT par SET AUTOCOMMIT> -- Desactivation de AUTOCOMMIT mysql> SET AUTOCOMMIT 0;

mysql> instruction ...;

mysql> COMMIT;

-- Reactivation du mode AUTOCOMMIT mysql> SET AUTOCOMMIT 1; </code>

soit en suspendant le mode AUTOCOMMIT le temps d'une transaction: <code sql|Suspension du mode AUTOCOMMIT pour l'accomplissement d'une transaction> -- les trois instructions suivantes sont equivalentes: mysql> BEGIN; ... mysql> COMMIT;

-- ou: mysq> BEGIN WORK; ... mysql> COMMIT; -- ou: mysql> START TRANSACTION; ... mysql> COMMIT; </code>

<code sql|Exemple d'utilisation de ROLLBACK> mysql> select * from joueurs where Prenom="John" AND Nom="Carew"; +--------+--------+-------+--------------+-------------------+-------------+ | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | | ------ | ------ | --- | ----- | ----------------- | ----------- | +--------+--------+-------+--------------+-------------------+-------------+ | 9 | John | Carew | Avant Centre | 1979-09-05 | Nor | | - | ---- | ----- | ------------ | ---------- | --- | +--------+--------+-------+--------------+-------------------+-------------+ 1 row in set (0.00 sec)

mysql> UPDATE joueurs SET Poste="Attaquant" WHERE Prenom="John" AND Nom="Carew"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from joueurs where Prenom="John" AND Nom="Carew"; +--------+--------+-------+-----------+-------------------+-------------+ | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | | ------ | ------ | --- | ----- | ----------------- | ----------- | +--------+--------+-------+-----------+-------------------+-------------+ | 9 | John | Carew | Attaquant | 1979-09-05 | Nor | | - | ---- | ----- | --------- | ---------- | --- | +--------+--------+-------+-----------+-------------------+-------------+ 1 row in set (0.00 sec)

mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE joueurs SET Poste="Avant Centre" WHERE Prenom="John" AND Nom="Carew"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from joueurs where Prenom="John" AND Nom="Carew"; +--------+--------+-------+--------------+-------------------+-------------+ | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | | ------ | ------ | --- | ----- | ----------------- | ----------- | +--------+--------+-------+--------------+-------------------+-------------+ | 9 | John | Carew | Avant Centre | 1979-09-05 | Nor | | - | ---- | ----- | ------------ | ---------- | --- | +--------+--------+-------+--------------+-------------------+-------------+ 1 row in set (0.00 sec)

mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec)

mysql> select * from joueurs where Prenom="John" AND Nom="Carew"; +--------+--------+-------+-----------+-------------------+-------------+ | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | | ------ | ------ | --- | ----- | ----------------- | ----------- | +--------+--------+-------+-----------+-------------------+-------------+ | 9 | John | Carew | Attaquant | 1979-09-05 | Nor | | - | ---- | ----- | --------- | ---------- | --- | +--------+--------+-------+-----------+-------------------+-------------+ 1 row in set (0.01 sec)

mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE joueurs SET Poste="Avant Centre" WHERE Prenom="John" AND Nom="Carew"; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)

mysql> select * from joueurs where Prenom="John" AND Nom="Carew"; +--------+--------+-------+--------------+-------------------+-------------+ | Numero | Prenom | Nom | Poste | Date_de_naissance | Nationalite | | ------ | ------ | --- | ----- | ----------------- | ----------- | +--------+--------+-------+--------------+-------------------+-------------+ | 9 | John | Carew | Avant Centre | 1979-09-05 | Nor | | - | ---- | ----- | ------------ | ---------- | --- | +--------+--------+-------+--------------+-------------------+-------------+ 1 row in set (0.00 sec)

</code>

Lors d'une transaction ACID, si un client modifie une table, un autre client ne peut modifier cette même table tant que le premier n'a pas validé (''COMMIT'') ou invalidé (''ROLLBACK'') sa transaction. Il y a utilisation implicite par le SGBDR de l'instruction ''LOCK TABLES''.

Utilisation de MySQL en Java¶

<code java|Un simple client Java> package mydbconnection;

/**

importation des outils sql (JDBC) / import java.sql.;

/**

* * @author gmonard */ public class exemple1 {

public static void main(String args[]) { Connection con = null; // l'objet permettant la connection au SGBDR String olurl = "jdbc:mysql://localhost:3306/ol"; String oluser = "oluser"; String olpassword = "olpassword"; /*

   * Tout d'abord il faut charger le "driver" adequat
   * C'est a dire la bonne bibliotheque chargee d'interagir avec la BDR
   */
  try
  {
     String driver = "com.mysql.jdbc.Driver";

     Class.forName(driver).newInstance();
  }
  catch(Exception e)
  {
     System.out.println("Echec du chargement du pilote MySQL");
     // on s'en va ...
     return;
  }

  /*

   * Connection avec le serveur MySQL
   */
  try
  {
     con = DriverManager.getConnection(olurl, oluser, olpassword);
  }
  catch(Exception e)
  {
     System.out.println("Echec de la connection");
     return;
  }

  /*

   * Preparation d'une commande SQL
   */
  try
  {
     String query = "SELECT Nom, Prenom, Numero FROM joueurs WHERE Prenom='Gregory'";
     Statement select = con.createStatement();
     ResultSet result = select.executeQuery(query);

     System.out.println("Resultats:");
     while (result.next())
     {
        String nom = result.getString(1); // premiere colonne de la reponse
        // il faut gerer les cas ou la reponse est NULL
        if (result.wasNull())
        {
           nom = "NULL";
        }
        String prenom = result.getString(2);
        if (result.wasNull())
        {
           prenom = "NULL";
        }
        int numero = result.getInt(3);
        if (result.wasNull())
        {
           numero = -1;
        }
        System.out.printf("%-20s %-20s %5d\n",nom, prenom, numero);
     }
  }
  catch(SQLException e)
  {
     e.printStackTrace();
  }

  /*

   * Terminaison de la connection
   */
  if (con != null)
  {
     try 
     {
        con.close();
     }
     catch(Exception e)
     {
        e.printStackTrace();
     }
  }

}

} </code>