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;
    • ...
$ 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]

 1. ?, --help          Display this help and exit.
 2. I, --help          Synonym for -?
 3. h, --host=name     Connect to host.
 4. u, --user=name     User for login if not current user.
 5. 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.
 6. D, --database=name Database to use.
 7. e, --execute=name  Execute command and quit. (Disables --force and history
                      file)
 8. f, --force         Continue even if we get an sql error.
 9. H, --html          Produce HTML output.
 10. X, --xml           Produce XML output
 $ 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]

 1. A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.
 2. -add-drop-database Add a 'DROP DATABASE' before each create.
 3. -add-drop-table    Add a 'drop table' before each create.
 4. h, --host=name     Connect to host.
 5. l, --lock-tables   Lock all tables for read.
 6. 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.
 7. u, --user=name     User for login if not current user.

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:

CREATE TABLE toto
(
 ...
) ENGINE=InnoDB;

ou après:

ALTER TABLE nom_de_la_table
ENGINE=InnoDB;

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:
-- Desactivation de AUTOCOMMIT
mysql> SET AUTOCOMMIT 0;

mysql> instruction ...;

mysql> COMMIT;

-- Reactivation du mode AUTOCOMMIT
mysql> SET AUTOCOMMIT 1;
  • soit en suspendant le mode AUTOCOMMIT le temps 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;
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)

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

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();
         }
      }
   }

}