Compte-Rendu

Cours MySQL Préparer par: Baldé Mamadou Oury

Formateur: Sacko
Fait le: 10/02/2022

1) Présentation de la formation
a) Présentation

Connaissances préalables
 Connaissances du système d’exploitation Linux/UNIX
 Expérience professionnelle du langage SQL
 Expérience professionnelle des bases de données relationnelles
 Expérience en matière de conception de bases de données.

Objectifs
 Comprendre et décrire l’architecture de MySQL
 Installer et mettre à jours MySQL
 Administrer et configurer MySQL
 Utiliser les outils MySQL tel que MySQL WorkBench
 Comprendre et mettre en œuvre les différents moteurs de stockage (InnoDB, MyISAM,
MEMORY, CSV …)
 Comprendre et gérer la sécurité sous MySQL
 Comprendre et mettre en œuvre la réplication sous MySQL
 Comprendre et décrire les techniques d’optimisation de MySQL
 Réaliser les opérations de sauvegarde et restauration
b) L’environnement

Prés – requis
 Espace disque de 20
 OS Linux Centos 6.x ou Windows (Server, XP, …)
 2) Présentation de MySQL

Historique de MySQL
 Crée par la société TCX
 Le 23 mai 1995 : Apparition de la 1ère version de MySQL
 Maintenue par la société suédoise MySQL AB
 Floue sur l’origine du nom MySQL
 Le 16 janvier 2008 : MySQL AB a été racheté par Sun Microsystems pour 1 milliard de dollars
 2009 : acquisition de Sun Microsystems par Oracle Corporation après autorisation de la
commission européenne.
 2009 : création de MariaDB en tant que projet Open Source.

MySQL
Système de gestion de base de données Open Source
 Rapide, robuste et facile d’utilisation
 Architecture client/serveur
 Fourni avec de nombreux outils
 Comptabilité avec de nombreux langages de programmation
 Forte interopérabilité avec le langage PHP
 Multiplateformes (Unix, Linux et Windows
 Interrogeable via SQL

Les apports de la version 5

 Les procédures stockées
 Les fonctions
 Les triggers
 Les curseurs
 Les vues
 Possibilité d’émuler les vues matérialisées
 Partitionnement des tables
 Réplication au niveau lignes

3) Installation de MySQL
a) Pré-installation

Quelle version utiliser ?

Les différentes versions à utiliser sont :
 GA (Generally Availabe)

-Stable
-Utilisable en production
 RC (Release Candidate)

-Relativement stable
-Non préconisée pour la production
 Alpha ou bêta

-Pour test des nouvelles fonctionnalités

 Communautaire
 Disponible sous licence GNU GPL v2
 Entreprise

-Licence commerciale
Fourni avec : du support Oracle
Des outils d’aide à l’administration (Query Analyzer, MySQL Entreprise Backup, etc.,)
NB : les deux versions sont identiques en terme de fonctionnement

 32 bits

Limité dans l’utilisation de la mémoire ‘dépend de l’OS)

Non recommandé pour la production
 64 bits

Lève les contraintes de limite d’utilisation de la mémoire

Recommandé pour la production

Mode d’installation
 A partir des sources

Assez complexe à mettre en œuvre

Plus de flexibilité dans les options d’installation
 A partir des packages (binaire précompilé)

Simple à mettre en œuvre

Recommandé

Autres éléments à prendre en considération

 La collation (Latin1…)
 L’encodage (UTF8…)
 Le répertoire d’installation
 Le répertoire de stockage des données
 L’utilisateur propriétaire du serveur MySQL

b) Installation via package

Caractéristiques
-La plus simple
-Nécessite deux packages au minimum
Mysql-server-<version>. Rpm
Mysql-client-<version>. Rpm
Exemple de commande sous centos : shell> yum install mysql-server mysql-client

Dans notre cas, on va d’abord télécharger le package rpm qui contient les repository.
Les repository ce sont les entrées pour l’utilisateur yum pour savoir l’endroit où il veut aller chercher les
packages rpm. On va utiliser la commande wget.
 Installation de mysql et vérification de l’ajout des repository

Lancement de l’installation de mysql via la commande yum

On doit retrouver un script dans le répertoire /etc/ init.d/ via :

Démarrage de l’instance mysql

Une instance mysql à défaut tourne sur le 3306

On peut se connecter à mysql via :

 On peut vérifier si notre instance est accessible et si elle tourne bien si le 3306 via :

c) Installation à partir des binaires

Caractéristiques
 Permet d’obtenir la dernière version officielle du serveur MySQL
 Ne dépend pas de la distribution

Pour cette installation, on va lancer aussi la commande de téléchargement directement à partir du site
de Microsoft via :

Ensuite, se repositionner dans le répertoire sources pour lancer la commande de téléchargement

Après, on va de tarer le fichier via la commande tar : tar zxvvf mysql-5.6.21-linux-glibc2.5

*86_64.[Link].
Aller voir le contenu du répertoire qui vient d’être créé via :

Création de l’utilisateur mysql

On fait un pwd puis un ls pour lister le contenu du répertoire. Ensuite, on va déplacer le contenu du
répertoire dtarer vers /usr/local/mysql/.
 d) Installation sous Windows

Deux solutions
 Utiliser l’installeur

-Simple et rapide
Prérequis : installation de .Net Framework 4.0
 Utiliser les binaires

-Nécessite de créer un fichier de configuration de l’instance

Note : téléchargement de MySQL à partir du site [Link]

e) Mise à jour de MySQL

Bonnes pratiques
 En terme de version

-Limitation du nombre d’écart en terme de version

Possibilité de passer d’une version mineure à une autre version mineure sans passer par toutes les
versions mineures
-Passer par toutes les versions majeures dans le cas d’une mise à jour d’une version majeure à une autre
vision majeure.
 La préparation

-Lire la documentation de la version cible

-Réaliser une sauvegarde complète systématiquement avant toute opération de mise à jour (logique
et physique)
 Les étapes

-Sauvegarde logique et physique

-Arrêt du serveur MySQL
Mise à jour des binaires MySQL
Démarrage de MySQL
Vérification des tables
Mysql_uprade (vérifie toutes les tables + réparation si nécessaire + adaptation des droits)

f) Installation de la base fact

 Sources (téléchargement à partir de la plateforme Alporm)

-Exécution du script « [Link] »

Shell >mysql – uroot < </cemin_du_script> [-P] < numero_port> [-p] < </chemin_du_script>
 Vérification si MySQL tourne bien sur 3306

Connexion à MySQL

Affichage de la liste des bases de données disponibles

Permission accordée à mysql pour pouvoir exécuter le script

Exécution du script

Affichage de la liste des bases de données après l’exécution du script

 La liste des tables dans la base de données fact

4) Architecture de MySQL
a) Instance MySQL

Une instance est caractérisée par :

 Un fichier de configuration ([Link] pour UNIX/Linux et [Link] pour Windows)
 Un processus serveur mysqld
 Une socket (accès local) ou port d’écoute TCP/IP (pour défaut 3306)
 Un moteur MySQL (basedir)
 Un répertoire de stockage des données (datadir)
 Un cache mémoire (avec des sous caches)
 Des fichiers de logs (log binaire, log d’erreur, log des requêtes lentes, log général)
 Des bases de données (mysql, information schéma et performance _schéma)

Configuration type du fichier [Link]

[mysql]
Port=3307

[mysqld]
Bind-address=[Link] l’instance acceptera les connexions à partir de toutes les interfaces.
Port=3307
Basedir=/usr
Datadir=/u101/mysql/data/instance01
Socket=/u101/mysql/sockets/instance01/[Link]
Log-error=/u101/mysql/log/instance01/[Link]

Les étapes d’initialisation d’une nouvelle instance

 Création de l’arborescence
 Attribution des droits
 Création du fichier [Link]
 Exécution du script mysql_install_db pour initialiser les bases systèmes (mysql)

Shell> mysql_install_db –basedir=<chemin_moteur> --datadir=<chemin_de_stockage>

 Démarrage de l’instance
 Shell> mysqld –defaults-file=<chemin/fichier_my.cnf

Cas Pratique

1) Création de l’arborescence

2) Attribution des droits

3) Création d’un nouveau sur le port 3307

4) Exécution du script mysql_install_db

5) Démarrage de l’instance

& à la fin : c’est un démarrage en tâche de fond.

6) Utilisation du socket pour se connecter à Mysql

 Pour afficher le port sur lequel est connecté mon instance, on utilise la commande :

b) Arrêt et démarrage d’une instance

Démarrage de MySQL
 Sous UNIX/LINUX

-En utilisant le script /etc/init.d/mysqld

Shell> /etc/init. d/mysqld start | restart
-En utilisant le script mysqld_safe
Shell> mysqld_safe –defaults-file=<path> --basedir=<chemin_moteur> --datadir=<chemin_data> --log-
error=<chemin_log> --user=<user_mysql>
-En utilisant directement le programme mysqld>
 Sous Windows

-En utilisant le gestionnaire de services

-Via la commande : net start <nom_service>

Les différentes commandes qu’on peut utiliser avec le script mysqld de démarrage :

Il faut savoir que ce script est créé automatiquement lors de l’installation via la méthode package.

Démarrage de l’instance en utilisant le script mysqld_safe

Pour ça, on va utiliser le fichier d’initialisation /etc/[Link], le paramètre basedir, le log_error et

l’utilisateur.
 Démarrage avec le démon mysqld
On arrête l’instance en cours et on procède comme suit :

Démarrage et l’arrêt de l’instance sous Windows

Aller dans le panneau de configuration => Outils d’administration => services => nom instance.
Pour arrêter ou pour démarrer l’instance, on fait clique droit et puis cliquer sur arrêt ou démarrer.
En ligne de commande, on utilise la commande net start <nom_service>.

Arrêt de MySQL
 Sous UNIX/LINUX

-En utilisant le script /etc/init.d/mysqld

Shell>/etc/init.d/mysqld stop
-En utilisant le programme mysqladmin
Shell> mysqladmin [ -u user ] [ -p ] [ -h hostname ] shutdown
 Sous Windows

-En utilisant le gestionnaire de services

- Via la commande : net stop <nom_service>

On peut aussi arrêter l’instance mysqld via :

c) Le cache MySQL

Architecture (cache d’une instance) : 2 types

  Mémoire partagée : l’ensemble des utilisateurs (processus clients ou le démon mysqld) vont
utiliser ces zones. Elles sont initialisées au démarrage de l’instance c’est-à-dire réserver. Ces
différentes zones sont :

Key_buffer_size : c’est le cache index des tables

Innodb_buffer_pool_size : les données des tables sont stockées avec le moteur innodb dans cette zone.
Table_cache : La définition des tables est stockée dans cette zone.
Query_cache : stocke les requêtes de types select et leurs résultats.
Elles sont partagées par tous les processus mysql.
 Mémoire par thread : chaque processus client aura sa zone mémoire par rapport à la taille
définie pour chaque paramètre. Elle est allouée dynamiquement. Les mémoires par thread
sont :

tmp_table_size : contient les données de tables temporaires

sort_buffer_size : contient les données triées de chaque utilisateur.
read_buffer_size : contient les données lues par les utilisateurs.
Les mémoires par thread sont allouées par processus.

Affichage des différentes zones de mémoires

On peut aussi voir les paramètres des tables via :

 Le paramètre de sort_buffer_size est :

d) Les logs MySQL

Différents types de log

 Les los binaires (binary log) : permettent de stocker l’ensemble des requêtes qui modifient
des données.
 Les requêtes d’erreurs (error log) : permettent de tracer les éventuelles erreurs de
démarrage, d’arrête voir de tout dysfonctionnement lié à l’instance.
 Les logs générales (general query log) :
 Les logs de requêtes lentes (slow query log) : permettent de tracer les requêtes lentes qui
dépasseront un certain seuil en termes de durée d’exécution.

Le log binaire
Fonction
 Stocke sous format binaire toutes les requêtes qui modifient les données

INSERT, UPDATE, DELETE, DROP, CREATE, ALTER…

 Fichier binaire
 Utilisé pour la réplication
 Utilisé pour la reprise après sinistre (restauration)
 Non activité par défaut

Activation : ajouter le paramètre log-bin [=nomdufichier] dans le fichier [Link]

Fonctionnement
 Journal incrémental (commence à .000001)
 Un nouveau fichier est généré
- A chaque redémarrage du serveur
- Si la valeur du paramètre max_ bnlog_size_binlog est atteinte
- Exécution de la commande FLUSHLOGS

Paramétrage et commandes
 Paramètre binlog-do-db (choix des bases de données à journaliser)
 Paramètre binlog-ignore-db (choix des bases de données à ne pas journaliser)
 Commande SHOW MASTER STATUS (vérifie si le log binaire est activé)
 Commande SHOW BINARY LOGS (affiche tous les logs binaires du serveur)
 Commande RESET MASTER (permet de réinitialiser les fichiers du journal binaire)
 Commande SHOW BINLOG EVENTS IN <’fichier_binaire’> (contenu du fichier de log)
 Le programme mysqlbinlog permet de visualiser le contenu des journaux binaires
- Possibilité de n’afficher qu’une partie
- Possibilité de fournir à mysql la sortie de mysqlbinlogg

Purge
 Pas automatique
  Plusieurs solutions
- Paramètre expire_logs_days= <jour> (en nombre de jour)
- Suppression manuelle
 PURGE BINARY LOGS BEFORE (par rapport à un intervalle de temps)
 PURGE BINARY LOGS TO (avec un numéro de journal)
 RESET MASTER (supprime tout et repart de 1)

Cas Pratique

On va se connecter à l’instance mysql via :

Le show master status est vide : ça veut dire que le log binary est désactivé.

1) Avant l’activation du log binaire, on va créer une arborescence qui va contenir l’ensemble de
nos logs

Ensuite, on va arrêter l’instance et la redémarrer via :

Ensuite, pour voir le fichier de log binaire généré par mysql, on va aller dans /u102/mysql/binlog et
après faire un ls -lrt.

Après, on va se connecter à mysql via mysql -uroot et ensuite taper la commande show master status.

Le show master status remonte le nom du fichier courant et la position du dernier caractère.
Pour le changement de la position, on va se connecter à la base de données fact, créer une table et
ensuite faire des insertions dans cette table.
Du coup, la position est passée de 120 à 643.
La commande flush logs permet la position d’avant les insertions du fichier actuel courant.

Pour afficher les informations d’un fichier, on utilise la commande show binlog events in.

Pour avoir un affichage sur colonne, on ajoute ‘ \G sur la syntaxe ci-dessus.

Pour afficher l’ensemble des fichiers journaux qui ont été générés sous mysql, on tape la commande
show binary logs.
On peut utiliser la commande RESET MASTER pour réinitialiser les fichiers du journal binaire.

● Le log binaire
Fonctionnement
- Journal incrémental (commence à .000001)
- Un nouveau fichier est généré
A chaque redémarrage du serveur
Si la valeur du paramètre max_binlog_size est atteinte
Exécution de la commande FLUSH LOGS

● Le log d’erreurs
Fonction
- Log les problèmes rencontrés lors du démarrage, de l’exécution ou de l’arrêt de mysqld
Créer automatiquement par le serveur
Nommé [Link] sous Linux et [Link] sous Windows.
- Paramètre log-error = </chemin/nom_fichier.err>

Pour la personnalisation de notre propre fichier log d’erreurs, on va passer sur le serveur pour créer un
directory qui va contenir notre log d’erreur.

Sachant qu’on va démarrer avec mysqld_safe, pour modifier le log d’erreur on va passer en tant que
root pour éditer le fichier [Link] et ensuite l’emplacement et le nom du fichier d’erreur mysql.
 ● Le log des requêtes lentes
Fonction
- Log toutes les requêtes qui ont pris plus de long_query_time (en secondes) à s’exécuter
Non activé par défaut
- Activation : ajouter le paramètre slow_query_log [=nondufichier] dans [Link]
Possibilité de tracer les requêtes qui n’utilisent pas d’index
- Paramètre log_queries_not_using_indexes
Possibilité de spécifier l’emplacement des fichiers de logs
- Paramètre slow_query_log_file, log_output= (TABLE, FILE, [FILE, TABLE], NONE)
➔ TABLE=mysql.slow_log

On va modifier le fichier /etc/[Link] en ajoutant les paramètres du log des requêtes lentes.

Pour le long_query nous aurons :

Ensuite pour dépasser les 1 seconde dans le but de voir si notre requête est bien tracée, on va créer un
produit cartésien entre les différentes tables.
Pour ça, on va aller dans la base fact et ensuite taper la requête cartésienne.
Select * from clients, commandes, employes, details_commandes, produits, catégories ;

Toutes les requêtes qui dépasseront une seconde seront tracées dans le fichier [Link]

● Le log général
Fonction
- Enregistre les événements reçus par mysqld (requêtes, connexion/déconnexion)
Activable à chaud (pramètre general_log)
- SET GLOBAL general_log= ‘ON’;
Possibilité de spécifier l’emplacement des fichiers de logs
- Paramètres : general_log_file, log_output= (TABLE, FILE, [FILE, TABLE], NONE)
TABLE=mysql.general_log

Pour la réalisation de notre cas pratique dans cette partie, on va ajouter dans le fichier /etc/[Link] le
paramètre:

Ensuite, on va activer le log général via:

Rédurection des traces vers la table générale

On va aller dans la base fact via use fact. Après, sélectionner deux tables à savoir la table categories et
la table employes. L’objectif c’est de voir après si nos requêtes ont été tracés dans la base mysql ainsi
que la connexion et la déconnexion.

Le select * from general_log permet de retrouver l’ensemble des opérations et des commandes qu’on a
utilisées.
On va voir aussi dans le fichier au niveau du système d’exploitation si les traces ont été générés.
 e) Les Bases de données sous MySQL

Racine datadir
Mysql
Performance_schema
Test Bases de données

[Link]
Ib_logfile0
Ib_logfile1 Journaux
Ibdata1 Binaires pour le
Fichier data pour moteur innodb
les tables innodb
Les fichiers log files sont créés automatiquement à chaque fois qu’on crée une instance.

Généralités
 Une base MySQL est constituée d’un répertoire identique au nom de la base
 Le répertoire contient des fichiers
- Extension : .frm (structure de la table)
 L’emplacement des bases est spécifié via le paramètre datadir
 Une base = schéma
 Il existe trois bases de données par défaut lors de l’installation

- Mysql= base système

- Information_schema= dictionnaire de données (virtuelle)
- Performance_schema (depuis a version 5.5)
 Pour savoir où sont stockées mes bases de données par défaut, on n’a besoin de la commande :

Caractéristiques
 Le nom de la base de données est sensible à la casse
- Lower_case_table_names=1 (pour forcer la création en minuscule)
 Pour travailler, une session doit posséder une base de données courante.
 Une référence à une table sans préfixe désigne cette table dans la base de données courante
 Pour connaître la base de données courante

Mysql> SELECT database () ;

 Pour changer la base de données courante

Mysql> use <nom_base>

On peut faire un select sur la table catégories qui se trouve dans la base de données fact en préfixant
simplement le nom de l’objet par le nom du schéma.

Le changement de la base de données via use :

 Création et suppression

 La commande CREATEDATABASE permet de créer une base de données.

Syntaxe générale
Mysql> CREATEDATABASE <nom_base> ;

 La commande DROP DATABASE permet de supprimer une base de données

Syntaxe générale
Mysql> DROP DATABASE <nom_base> ;
 5) Architecture de MySQL
a) Les méthodes de configuration

Présentation

 Configuration lors de la compilation avec le script « configure »

 Dans le fichier lors de configuration [Link] ([Link] sous Windows)
 Directement en tant que paramètre avec le programme mysqld
 D’une manière dynamique à chaud

Configuration via le fichier [Link]

Localisation
 Linux
 /etc/, /etc/mysql, SYSCONFDIR, $MYSQL_HOME/ et ~/,. SYSCONFDIR

 Visualisation la liste des répertoires utilisés par MySQL

$ mysqld –help –verbose | grep less

 Windows

 WINDIR\[Link], WINDIR\[Link], C:\[Link], C:\[Link], INSTALLDIR\[Link] et INSTALLDIR\

[Link]

Note : le fichier [Link] est utilisé par tous les programmes fournis par MySQL (mysqld, mysql,

mysqldump, mysqld_safe, myisamchk…)

Organisation du fichier [Link]

 Organiser en section

- Chaque section correspond à un nombre de programme sous la forme [<nom_programme>]

- My_print_defaults<nom_section> permet de visualiser les opérations d’une section

 Chaque paramètre est de la forme

- Nom_paramètre= valeur
  Pour les options binaires, il suffit de spécifier le nom du paramètre

- enable-innodb

 Les lignes en commentaires commencent par « # » ou un « ; »

 Possibilité d’inclure des fichiers

- ! include <nom_fichier.cnf> et ! includedir <chemin>

Modèles de fichier [Link]

 [Link] (poste de travail)

 [Link] (poste de travail)

 [Link] (serveur avec moins de 2Go de RAM)

 [Link] (pour les serveurs avec 2Go de RAM)

 [Link] (pour les serveurs avec 4Go de RAM)

Cas pratique :

Les méthodes de configuration

Recherche fichier de configuration par [Link]

L’affichage de la configuration du fichier par défaut via la commande my_print_defaults

On peut le faire aussi sur mysqld_safe via :

● Modèles de fichier [Link]

- [Link] (poste de travail)
- [Link] (poste de travail)
- [Link] (serveur avec moins de 2 Go de RAM)
- [Link] (pour les serveurs avec 2Go de RAM)
- [Link] (pour les serveurs avec 4Go de RAM)
Avant le changement du prompt, on va copier le fichier /etc/[Link] dans /var/lib/mysql/

Pour le changement du prompt on va modifier notre fichier /etc/[Link] en ajoutant :

Ensuite, on va arrêter l’instance pour la redémarrer après.

Déplacement du fichier [Link] dans [Link] via:

On va rajouter ensuite le paramètre mysq_home dans le but de spécifier le chemin qui stocke le fichier
[Link]. De ce fait, on va positionner dans le home de l'utilisateur courant via pwd ~

Ajouter dans .bash_profile le chemin ci-dessous.

Après, on ressource le fichier via la commande :

On réenregistre le paramètre du prompt dans le fichier [Link], après faire un shutdown pour ensuite
redémarrer.

Pour la configuration de la section mysql, on va enlever le paramètre du prompt dans le fichier [Link]
pour ensuite copier le fichier dans section_mysql.cnf.

Rééditer le fichier [Link] tout en ajoutant le paramètre ci-dessous

Après un shutdown + redémarrage via mysql_safe &

● Paramétrage via le programme mysqld

Les paramètres sont précédés par « --»
Les mêmes que ceux spécifiés dans le fichier « [Link] »
Pour connaître la liste exhaustive des paramètres
mysql - - help - -verbose
Possibilité de spécifier un fichier en paramètre à mysqd
– - -defaults-file=<[Link]>
– --defaults-extra-file=<[Link]>

L’affichage des différents paramètres qu’on peut utiliser avec mysqld via la commande mysql - - help

Démarrage de l’instance mysql directement avec mysqld

● Paramétrage dynamique de MySQL

Deux niveaux de modifications
- Au niveau session : SET SESSION <paramètre> =<valeur>
Toutes les variables n’ont pas une portée au niveau session
 - Au niveau serveur : SET GLOBAL <paramètre> =<valeur>
Toutes les variables n’ont pas une portée
Nécessite d’avoir le privilège SUPER
Stockés en cache uniquement (perte après redémarrage du serveur)

Modification de la zone de tri sort_buffer_size

Modification au niveau global

Pour que nos configurations deviennent persistantes par exemple il faudra qu’on rajoute le paramètre
sort_buffer_size directement dans le fichier [Link]

Ensuite, on redémarre puis on tape la commande show global variables like pour rendre définitif la
valeur de sort_buffer_size.
 b) Affichage de la configuration

Les méthodes de visualisation des paramètres MySQL

 Directement dans le fichier [Link]
 En utilisant la commande SHOW GLOBAL VARIABLES
 En utilisant les tables GLOBAL_VARIABLES du schéma virtuel INFORMATION_SCHEMA
-Pour visualiser les variables niveau session, il faut utiliser la table SESSION_VARIABLES
 En utilisant la commande SELECT@@global.nom_variable
 En utilisant la commande STATUS pour afficher l’état du serveur.

Affichage de la valeur du paramètre datadir

On rajoute le paramètre ci-dessous dans le fichier .bash_profile.

Si on ne connaît pas le nom exact du paramètre, on procède comme suit :

Pour l’affichage du paramètre du port :

Seconde méthode pour l’affichage de la valeur datadir :

3ème méthode d’affichage :

En se positionnant dans la base information_schema, on procède comme suit pour l’affichage de la

valeur datadir

On peut afficher la taille du paramètre sort_buffer_size de la session en cours et non de l’instance par :

Pour l’instance, on utilise la commande : show global variables.

c) Les principaux paramètres MySQL

 Configuration des logs
Paramètres Description
log-bin Activation des logs binaires
log_output Permet de définir le type de journalisation (table, fichier ou aucun)
slow_query_log_file Permet de définir l’emplacement du journal des requêtes lentes
slow_query_log Activation du journal des requêtes lentes
long_query_time Permet de définir la durée d’exécution d’une requête avant de la journaliser
log-error permet de spécifier le journal des erreurs
general_log_file. Permet de spécifier le journal des traces générales.

 Configuration de l’instance

Paramètres Description
Port Numéro de port d’écoute pour les connexions TCP/IP
Socket Socket UNIX pour les connexions locales
Basedir Emplacement du moteur MySQL
Datadir Emplacement de stockages des données
Tmpdir Emplacement pour stocker les objets temporaires
Default_storage_engime Moteur de stockage du serveur
Defaults-file Fichier d’initialisation de l’instance
Max_connections Nombre de connexions maximum à l’instance

 Configuration du cache mémoire

Paramètres Description
Innodb_buffer_pool_size Taille du cache pour les données et indexes innodb
Innodb_file_per_table Permet de créer un fichier .ibd par table
Innodb_log_buffer_size Taille du cache des journaux d’innodb
Innodb_log_files_in_group Nombre de fichier journaux d’innodb
Max_heap_table_size Taille maximale des tables de type MEMORY
Tmp_table_size Taille maximale en mémoire des tables temporaires

6) Clients et outils MYSQL

a) L’outil MySQL
  Caractéristiques :
Permet d’exécuter les commandes SQL : mysql [options]
- Les principales options
Options Description
--help L’aide en ligne
-h La machine qui héberge l’instance MySQL
-u Le user qui se connecte à MySQL
-p Le mot de passe
-e Le script à l’extérieur
-P Le pot d’écoute de l’instance
-D Nom de la base

Exécution d’un script en utilisant le client MySQL

On va charger un script dans la base de données gescom créé nouvellement.

Pour ça on va aller dans tmp et ensuite exécuter notre script.

Le contenu du [Link]

Ensuite, on procède comme suit :

Exportation des données d’une table ou d’une requête vers un fichier :
Pour cette application, on va exporter la table catégories de la base données fact vers un fichier du
nom /tmp/[Link] via :

Ci-dessous le contenu du script pour voir si réellement nos données ont été exporter.

On peut aussi filtrer cette exportation via :

b) L’outil MySQL show

 Caractéristiques
Permet de lister la liste des bases, tables et colonnes
- Utilise les mêmes options de connexion que le client MySQL
Syntaxe :
Mysqlshow [OPTIONS] | [BASE] | [TABLE]| [CHAMP]
Affichage des informations sur le champ code_catégories :

c) L’outil mysqladmin
 Caractéristiques
Utilise les mêmes options de connexion que le client MySQL

Commandes Description
Create Permet de créer une base de données
Drop Permet de supprimer une base de données
Pling Permet de tester l’accès à une instance MySQL
Processlist Permet d’afficher les sessions en cours
Refresh Vide de la mémoire toutes les tables, puis ferme et réouvre les fichiers de logs
Status Affiche des statistiques d’utilisation de l’instance
Shutdown Permet d’arrêter une instance
Variables Affiche la liste des variables de l’instance
Version Affiche la version de MySQL

Ça veut dire que les démons mysqld tournent bien.

Affichage de la liste des sessions et des requêtes en cours :

Affichage des status via :

Le filtrage pour les variables :

Mysqladmin permet également de créer des bases de données.

Suppression de la base virtuo via mysqladmin :

d) Les interfaces graphiques d’administration

 Les principaux outils graphiques
 PHPMyAdmin (Open Source)

Pré-requis :
- Un serveur web : Apache, S
- Un interpréteur PHP
 Plus maintenus
- MySQL Administrator
- MySQL Query Browser
- MySQL Migration Toolkit
 MySQL Workbench (anciennement MySQL Administrator)
- Téléchargement: [Link]
- Documentation : [Link]
- Démos : [Link]
- MySQL Workbench Community : gratuit
- MySQL Workbench Standard : environ 80€/an (version pro, incluant notamment des
fonctionnalités de synchronisation, de validation et de documentation).
- Différence entre les 2 versions : http : //[Link]/products/workbench/[Link]
- Multiplateforme : Windows, Linux et Mac OS

MySQL est installé sur un poste mais pas sur un serveur.

MySQL Workbench (outil unifié)
 Permet
- Créer, modifier ou supprimer des objets
- De gérer les comptes utilisateurs
- D’effectuer toutes les opérations d’administration d’une base de données
- De faire de la modélisation
- Du développement (Develop)
- De l’administration (Administer)
- De migrer des données d’un autre SGBDR vers MySQL

Dans le cadre de l’exercice, on va créer un utilisateur qui a un accès à partir d’une machine distante.
Octroie privilèges à admfact : tous les droits sur la base de données de fact.

Privilèges à root : tous les privilèges à root qui peut accéder à n’importe quelle machine sur l’ensemble
des objets de toutes les bases de données.

Reverse Engineer : permet de se connecter à une base de données existante et de rapatrier le modèle
de données.

7) Les types de données MySQL

 Les types de données

Trois grands types

- Les types numériques
- Les types Date et heure
- Les types de chaînes
Les types numériques
- Types de données entiers

Type Intervalle Taille (octets) Description

TINYYINT[(TM)] -127 à 128 1 Entiers très courts
TINYYINT[(TM)] UNSIGNED 0 à 255 1 Entiers très courts
SMALLINT[(TM)] UNSIGNED -32768 à 32767 2 Entiers très courts
MEDIUMIN[(TM)] -8388608 à 8388607 3 Entiers très courts
MEDIUMIN[(TM)] UNSIGNED 0 à 16777215 3 Entiers très courts
INT[(TM)] -2^31 à 2^31-1 4 Entiers
INT[(TM)] UNSIGNED 0 à 2^32-1 4 Entiers
BIGINT[(TM)] -2^^63 0 2^63-1^ 8 Entiers larges
BIGINT[(TM)] UNSIGNED 0 à 2^64-1 8 Entiers larges
 - Types de données à virgule flottante

Type Intervalle Taille (octets) Description

FLOAT (précision) Dépend de la précision Varie <=24 pour un nombre
simple>24 et <=53 pour un nombre
en double précision
FLOAT[(TM, P)] + ou -1.175494351E -38 4 Simple précision= FLOAT(4)
E
A + ou -3.402823466 +38
DOUBLE[TM,P] + ou -1.797693134862357E -308. 8 Double précision=FLOAT(8)
à + ou -2.2250738585072014E -308
DECIMAL[(TM, P)] varie Enregistré dans un CHAR

Les types Date et heure

- Types de données date et heure

Type Intervalle Description

Date 1000-01-01 à 9999-12-31 Date affichée au format YYYY-MM-DD
TIME -838 :59 :59 à 838 :59 :59 Heure affichée au format HH :MM :SS
DATETIME 1000-01-01 00 :00 :00 Date et heure affichées au format YYYY-MM-DD HH :MM :SS
à 9999-12-3 23 :59 :59
TIMESTAMP[(TM)] 1970-01-01 00 :00 :00 Horodatage
YEAR[(TM)] 70 à 69 (1970 à 2069) Année affichés sur 2 ou 4 digits

Les types de chaînes

- Types de données de chaîne classique

Type Intervalle Description

[NATIONAL] CHAR(TM) 1 à 255 Chaîne de longueur fixe
[NATIONAL] VARCHAR(TM) 1 à 255 Chaîne de longueur variable
 Création d’un champ de type timestamp : dans ce cas MySQL insert automatiquement dans le champ
date_com.
NB : pour plusieurs champs de types timestamp seule la première colonne pourra bénéficier de cette
insertion automatique.

- Types de données de chaîne TEXT et BLOB

Type Intervalle Description

TINYBLOD 255 (2^8-1) Objet binaire court
TINYTEXT 255 (2^8-1) Text court
BLOB 65535 (2^16-1) Objet binaire de taille normale
TEXT 65535 (2^^16-1) Texte de taille normale
MEDIUMBLOB 16777215 (2^^24-1) Objet binaire de taille moyenne
MEDIUMTEXT 16777215 (2^^24-1) Texte de taille moyenne
LONGBLOB 4294967295 (2^32-1) Objet binaire de grande taille
LONGTEXT 4294967295 (2^32-1) Texte de grande taille

- Types de données de chaîne ENUM et SET

Type Nombre de valeur MAX Description

ENUM (‘valeur1’,’valeur2’,’val 65535 Les valeurs doivent obligatoirement
être contenu dans la liste
SET (‘valeur1’,’valeur2’, …) 64 Text court

Enum : permet de spécifier une liste de valeur acceptée dans un champ spécifié.

 Optimisation du type

Shell> SELECT colonne from TABLE PROCEDURE ANALYSE()\G

Pour savoir si le type de données du champ code_categories est optimale, on va procéder comme suit :
 8) Obtention des métadonnées
 La base INFORMATION_SCHEMA
 Constitue le « dictionnaire de données » MySQL
 Disponible depuis la version 5.0.2 de MySQL
 Les objets sont des vues (non modifiable)
 Base de données (schéma) virtuelle : pas de fichiers physiques
 Fournis un accès aux métadonnées sur :
* Les bases
* Les tables, les colonnes, les contraintes, les vues, etc., des
bases de données stockées sur le serveur MySQL
* Les types de données des colonnes

- Les principales vues

Vues Description
SCHEMATA Fournit des informations sur les bases de données
TABLES Fournit des informations sur les tables
COLUMNS Fournit des informations sur les colonnes de tables
STATISTIC Fournit des informations sur les tables d’index
USER_PRIVILEGES Fournit des informations sur les droits globaux
SCHEMA_PRIVILÈGES Fournit des informations sur les droits des tables
TABLES_PRIVILEGES. Fournit des informations sur les droits des tables
COLUMN_PRIVILEGES Fournit des informations sur les droits les droits reliés aux colonnes
CHARACTER_SETS Fournit des informations sur les jeux de caractères disponibles
ROUTINES Fournit des informations sur les procédures stockées et les fonctions
VIEWS Fournit des informations sur les vues dans les bases
Affichage de la structure de la vue schemata :

Vue tables :
Pour l’affichage des jeux de caractères disponibles :

Création de vue :
On peut récupérer toutes les vues de la base de données fact via :

9) Moteurs de stockage
a) Présentation des moteurs de stockage
 Caractéristiques
 Un moteur de stockage, c’est quoi ?

Spécifie la manière dont seront stockés et utilisés les données

Les plus utilisés sont : INNODB, MYISAM et MEMORY
 Architecture pluggable storage engine

Possibilité d’utiliser plusieurs moteurs dans une même base de données

Le choix du moteur se fait lors de la création de la table
Le choix du moteur est réversible (ALTER TABLE)
 Le choix du moteur
 Dépend de :
 La manière de stocker les données
 La criticité des données
 La gestion ou pas des transactions
 La stratégie de sauvegarde/restauration
 Les types de verrous
 La persistante des données
 La réplication.
 Les moteurs les plus connus
 INNODB
 MYISAM
 MEMORTY
 ARCHIVE
 CSV : permet de stocker les données au format CSV
 FEDERATED : permet d’accéder à partir d’un serveur MySQL à des données distantes.
 Quelques commandes utiles

Commandes Description
SHOW ENGINES Affiche la liste des moteurs disponibles
SELECT * FROM INFORMATION_SCHEMA.ENGINES; Idem que SHOW ENGINES
SHOW CREATE TABLE<nom_table>; Affiche des informations sur la structure d’une table
SHOW TABLE STATUS LIKE ‘CATEGORIES’\G Fournit des informations sur la structure dune table
SELECT TABLE_SCHEMA, TABLE_NAME, Fournit des informations sur les tables d’une
base de données en utilisation la base INFORMATION_SCHEMA
TABLE_NAME, TABLE_TYPE, ENGINES FROMT TABLES
WHERE TABLE_SCHEMA=’<nom base>’;

Pour la récupération des métas data, on peut utiliser aussi la base information_schéma pour
afficher les métas data via :
Affichage des tables déjà existante :
Dans la base fact, on peut afficher la structure de la table catégories via :

Pour afficher toutes les informations concernant la table catégories, on utilise la commande show table
status.

Activation de l’affichage étendue sur la table catégories :

Avec la base information_schéma, on procède comme suit pour afficher le moteur de chaque base de
données.

Création d’une table avec moteur :

 b) Le moteur INNODB
 Caractéristiques
 Moteur par défaut
 Les données et les indexes sont stockés dans des fichiers appelés espace de table (tablespace)
 InnoDB fournit à MySQL un gestionnaire de table transactionnelle

Conforme à la norme ACID (Atomique, Cohérent, Isolé et Durable)

 Prise en compte de COMMIT et ROLLBACK
 Capacités de restauration après crash
 InnoDB utilise un verrouillage au niveau des lignes
 Fournit des lectures cohérentes comme Oracle, sans verrous
 Supportent les contraintes de clés étrangères (FOREINGN KEY) et l’intégrité référentielle
 Possibilité de réaliser des sauvegardes à chaud (logique et physique)
 Gestion du stockage du moteur InnoDB
 MySQL enregistre la structure de table dans le fichier .frm (dans le répertoire de base de
données)
 Deux types de stockage pour les données
Un fichier par table avec l’extension .ibd (par défaut depuis la version 5.6.6)
- Innodb_file_per_table =ON
- Possibilité de stocker le fichier .ibd dans un emplacement spécifique (depuis la version 5.6)
CREATE TABLE <specification> DATA DIRECTORY=’<chemin>’.

CREATE INDEX <nom_index> ON <nom_table> (<nom_champ>) INDEX DIRECTORY=’<chemin>’;

Dans un ou plusieurs tablespaces (fichiers ibdata)
- Innodb_data_file_path = ibdata1:12M:autoextend
Contient le dictionnaire de données et les undo logs pour l’annulation des transactions

Création de la base db_innodb :

Ensuite, on va créer une table dans la base db_innodb

Au niveau du stockage physique, on aura :

Vérification du paramètre innodb_file_per

Chaque table créé dans une base de données aura deux fichiers à savoir : un fichier qui contient la
structure avec une extension .frm et un fichier qui porte le nom de la table avec l’extension .ibd qui
contiendra les données de la table.

Stockage dans un fichier .ibd version 5.6 :

Pour ça, on va créer une arborescence via :

Création d’une table dans la base db_innodb :

Au niveau du stockage physique, on retrouve :

La structure [Link] est stockée dans /var/lib/mysql/db_innodb et le fichier qui contient les
données [Link] est stocké dans /u101/mysql/data/.

Pour les anciennes versions de MySQL, l’ensemble des données d’une table et des indexes sont stocké
dans les fichiers ibdata via le paramètre : innodb_data_file_path

Stockage physique pour le fichier ibdata :

Il faut savoir qu’il est possible de créer plusieurs fichiers data et seul le dernier sera en autoextend.

Rajout de fichiers ibdata :

Premièrement, il faut arrêter l’instance MySQL en cours via mysqladmin -uroot shutdown.
Ensuite, on procède comme suit :

Après on redémarre l’instance pour voir le fichier ajouté.

 Architecture globale du moteur Innodb
 Utilise deux journaux de transactions : ib_logfile0 et ib_logfile1
Permet de maintenir la cohérence des données après un arrêt intempestif
Nombre de fichiers journaux défini dans le paramètre innodb_log_files_in_group
Possibilité de modifier la taille des fichiers journaux innodb_log_file_size
 Utilise des « undo logs » pour l’annulation des transactions (rollback)
Stockés dans le fichier spécifié par le paramètre innodb_data_file_path
Possibilité (depuis la 5.6) de séparer les « undo logs » dans un tablespace dédié
- Innodb_undo_directory : répertoire pour le tablespace des « undo logs »
- Innodb_undo_logs : nombre de roolback segments du tablespace
 - Innodb_undo_talespaces : nombre de tablespace contenant des « undo logs »

MySQL utilise les fichiers ib_logfile0 et ib_logfile1 pour réaliser une restauration automatique après un
crash.

On peut modifier la taille de chaque fichier journal et la taille maximale d’un fichier journal est de 500M.

 Fonctionnement du moteur Innodb

Value=1 ça veut dire que le paramètre est activé.

Les paramètres qui permettent d’agir sur les journaux de transactions sont : innodb_log_files_in_group
et innodb_log_file_size.
Les deux caches à configurer pour MySQL sont : innodb_log_buffer_size et innodb_buffer_pool_size.
Le premier enregistre chaque transaction qui modifie les données avant de l’écrire dans les fichiers
journaux de MySQL.
Le second, spécifie la zone de travail partagée. MySQL va charger d’abord les données dans le cache de
données avant de faire la modification ou de renvoyer les données à l’utilisateur.
Vérification : pour voir si le moteur Innodb gère bien tout ce qui est contrainte d’intégrité référentielle
et voir aussi s’il positionne bien un verrou au niveau ligne.
Pour ça dans la base db_innodb, on va créer deux tables :

Ajout d’une contrainte d’intégrité : MySQL respecte l’intégrité référentielle.

MySQL gère bien la notion de commit et de rollback.

Du coup, la donnée devient persistante.

Ensuite, pour voir si MySQL crée un verrou au niveau de la ligne, on va ouvrir deux sessions.
Sur la première, on travaille sur l’utilisateur Drissi et sur la seconde session, on va travailler sur
Harabazan.
Session1 :

Session2 :

Si on essaie de travailler sur le même enregistrement dans la session2, on reste bloquer car MySQL crée
un verrou au niveau de la ligne avec le moteur Innodb.

Pour enlever le verrou, il faut faire un rollback dans la première session de notre enregistrement.

c) Le moteur MyISAM
 Caractéristiques générales du moteur MyISAM
 Ses particularités sont :
- Rapidité en lecture
-Recherche full texte
-Verrouillage au niveau des tables
-Pas de sauvegarde à chaud
 MyISAM utilise trois fichiers
-Un fichier pour la structure de la table (.frm) par table :
<nom_table>.frm
 -Un fichier de données (.MYD) : <nom_table>.MYD
-Un fichier d’index (.MYI) par table : <nom_table>.MYI
 Non transactionnel
 Ne supporte pas les clés étrangères

Dans ce cas, nous allons créer deux tables dans la base de données db_myisam en fin de
pouvoir pratiquer.

Affichage de la structure de la table

Lorsqu’on crée une table avec le moteur MyISAM, trois fichiers sont créés automatiquement
par MySQL. Un fichier pour la structure portant l’extension .frm, un pour les indexes dont
l’extension est .MYI et le dernier pour les data et ayant comme extension .MYD
Au niveau transactionnel : MySQL ne supporte pas les clés étrangères avec le moteur MyISAM.
Ajout d’une contrainte d’intégrité :

Ensuite, on va vérifier si le moteur MyISAM supporte les transactions ou pas.

On constate que le rollback n’a pu annuler nos inserts donc par conclusion le moteur MyISAM
ne supporte pas les commit et les rollback. D’ailleurs c’est pourquoi on dit que le moteur
MyISAM est un moteur transactionnel.

 Types de format pour les tables MyISAM

 Statique (par défaut sauf pour les types VARCHAR, VARBINARY, BLOB et TEXT)
-Rapide
-Moins sensible aux crashs
 Dynamique
-Prend moins de place sur le disque

-Moins rapide et plus sensible aux crashs étant donné les entêtes nécessaires
 Compressé

-La table utilise très peu d’espace

-En lecture seule
-Les performances en lectures sont peu impactées
-Compression avec myisampack et décompression + mise à jour des indexes avec myisamchk.

Création d’une table statique :

Fixed : veut dire que je suis sur une table statique. Tous les enregistrements ont la même taille.

Création d’une table dynamique :

Création d’une table au format MyISAM compressée :

Ensuite , on va faire des insertions dans la table tb_compress via :

Après on tape la commande ci-dessous pour voir le nombre de ligne inséré.

On peut voir aller dans le répertoire /var/lib/mysql/db_myisam pour voir les différents tableaux de
compress.

Compression via :

Ensuite faire un ls -lrt pour voir la taille de la compression.

Pour décompresser, on utilise le programme :

d) Le moteur MEMORY
 Caractéristiques :
 Existe depuis la version 3.23.25
 Moteur non-transactionnel et non support des clés étrangères
 Utilise des indexes hachés au lieu du standard B-Tree
 Les indexes et les données des tables sont stockées en mémoire uniquement (sauf la
structure .frm)
 Avantage : table extrêmement rapide
 Inconvénient : un plantage du serveur mène à une perte des données
 Domaine d’application :
Données volatiles
Tables temporaires
Fort besoin en accès rapide aux données ;
Stockage physique :
Seul le fichier qui contient la structure sera stocké physiquement.

Création d’index :
En faisant des insertions dans la table tb_memory, nous obtenons :

Sauvegarde de ces données sur une table physique : pour ça on va créer une table via :

NB : nous perdons toutes les données après avoir fait un arrêt puis un redémarrage.

e) Les autres moteurs

  Le moteur Archive
 Utilise un fichier .frm pour la structure et un fichier .ARZ pour les données
 Compression des données lors du stockage
 Utilisé pour stocker des données brutes
 Moteur non-transactionnel et non support des clés étrangères
 Seuls deux commandes sont possibles : SELECT et INSERT
 Domaines d’application :
Enregistrement de logs

Affichage de la structure de la table logs :

Les caractéristiques de la table logs :

On ne peut pas faire de delete sur une table qui a été créé avec le moteur archive.
Stockage physique :

NB : Le moteur archive ne supporte pas les indexes.

 Le moteur CSV
 Stock les données au format CSV (Comma Separated Values)
 La table est représentée par
- Un fichier <nom_table>.CSV pour les données, un fichier <nom_table>.frm pour la structure et
un fichier <nom_table>.CSM pour l’état et le nombre de ligne
 Non transactionnel
 Pas de sauvegarde à chaud
 Refus d’index
 Verrou au niveau table

Insertions des données dans la table formateurs :

Stockage physique :

 Le moteur MERGE
 Offre la possibilité de grouper des tables de structures identique pour en faciliter la
manipulation
 Une table MERGE est identifiable
Par un fichier d’extension .frm qui est la structure de la table de définition et un fichier
d’extension .MRG qui contient la liste des fichiers index (.MYI)
 Toutes les tables utilisées par la table MERGE doivent se trouver dans la même base de données
que la table MERGE.

Insertion dans nos différentes tables :

Création d’une table qui contiendra les deux autres avec le moteur Merge :

NB : le moteur merge ne peut être créer que sur des tables de types MYISAM.
Stockage physique :

 Le moteur FEDERATED
 Permet de déporter les données sur un serveur distant
  Seule une copie de la définition de la table est stockée sur le serveur local
 Accède à des tables dans une base de données distante, plutôt que dans des fichiers locaux
 Avantages
Permet de répartir la charge
Pointer sur plusieurs bases de données distantes si elles acceptent les accès distants
Supporte les indexes
 Autres moteurs
 NDB Cluster : plus disponible dans la version classique. Il est à télécharger.
 XtraDB

 Récapitulatif des caractéristiques pour les principaux moteurs

Moteurs Types de verrous Gestion des transactions Stockage sur Sauvegarde à

disque chaud
Innodb Ligne Ok Ok Ok
MyISAM Table * Ok *
Memory Table * * *
Archive Table * Ok *
CSV Table * Ok *
Federate Table * Ok *
d
NDB Table Ok Ok Ok
Cluster
XtraDB Table Ok Ok Ok

10) Transactions et verrous

a) Les transactions
 MySQL est dit ACID
 Normes ACID

Atomicité
Cohérence
Isolation
Durabilité

 MySQL est autocommit par défaut

- Show variables like ‘%commit%’;
En autocommit, à chaque fois que je tape une instruction qui modifie les données à savoir un update,
delete ou insert, MySQL fait un commit implicite. Il n’est pas possible d’annuler les modifications.

 Qu’est ce qu’une transaction?

 C’est une ou plusieurs commandes LMD (INSERT, UPDATE et DELETE)
 Une transaction se termine toujours par un COMMIT ou un ROLLBACK

BEGIN ;

START TRANSACTION ;
ROLLBACK

UPDATE

INSERT DELETE Fin

transaction

COMMIT
Fin de la transaction
précédente

Fichiers
Data
Les données sont persistantes après le commit donc on peut plus faire de rollback. Une transaction qui
commence par un begin ou start se termine obligatoirement par un rollback ou un commit.

Le Rollback annule toutes les modifications qui ont été saisi après le begin.

 Notion de SAVEPOINT

Les savepoints permettent de positionner des points d’arrêts entre différentes instructions dans
une même transaction. Ils nous donnent la possibilité d’annuler les modifications qui arrivent
après un savepoint défini.
Annulation pour les données 3 et 4 :

b) Les niveaux d’isolation

 MySQL offre quatre niveaux d’isolation
- REPEATABLE-READ (par défaut)
- READ-COMMITTED : permet de lire que les données qui ont été validées.
- READ-UNCOMMITTED: pas utilisé
- SERIALIZABLE

Identique à REPEATABLE-READ et toutes les factures sont converties en SELECT … LOCK IN SHARE MODE
 Pour connaître le niveau d’isolation du serveur MySQL
- show global variables like ‘%isolation%’;
- select @@global.tx_isolation ;

Au niveau session :

Au niveau de l’instance :
En mode REPEATABLE READ, MySQL nous garantit d’avoir le même résultat tant qu’on ne sort pas de la
transaction begin.
En créant une table1 dans la première session

Ensuite, dans la session2, on va commencer une transaction via la commande begin

En faisant des inserts dans la table1 au niveau de la session1 :

Nous obtenons toujours le même résultat au niveau de la session2.

En sortant de la transaction via la commande rollback, nous obtenons dans la session2 :

Pour pouvoir supporter le REPEATABL-READ ainsi que le niveau d’isolation READ-COMMITED, on va

modifier la variable binlog via :
Démarrer une transaction puis changer le niveau d’isolation :

NB : tant qu’on ne fait pas de commit, les autres utilisateurs ne verront pas les données en cours de
modification dans les autres sessions.
Refaire la même chose dans la seconde session :

Dès qu’une transaction est validée par un commit, elle sera visiblement directement par les autres
utilisateurs.
 Possibilité de modifier le niveau d’isolation :
 Au niveau instance
-Dans le fichier [Link]

Paramètre transaction-isolation= {READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-

READ |}
-Avec la commande SET GLOBAL
 Au niveau session

Avec la commande SET GLOBAL

 Syntaxe de la commande SET
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|
REPEATABLE READ|SERIALIZABLE}

c) Les verrous
 Méthodes de verrouillage

- Verrouillage au niveau table pour les moteurs (MyISAM, MEMORY)

- Verrouillage au niveau ligne (InnoDB)
Mécanisme de MVCC (Multiversion Concurrency Control)
 Types de verrous
- Verrous en lecture (partagé)
- Verrous en écriture (exclusif)
 Le processus de verrouillage
-S’il n’y a pas de verrou sur la table, pose un verrou en écriture dessus.
-Si non, soumet une requête de verrouillage dans la queue de verrous d’écriture

Dans une première session :

Ensuite, démarrer une transaction sur la même session :

Tant qu’on fait de commit ou de rollback, MySQL va laisser le verrou sur la ligene7.
Mise à jour d’une autre ligne sur la seconde session :

Modification de la ligne7 qui est en cours de modification sur la session1 dans la session2 :

On reste bloquer sur cette ligne dans la seconde session car le verrou n’a pas encore été enlevé.
Pour enlever le verrou, on fait un commit ou un rollback.

 Verrou explicite
 La commande à utiliser est LOCK TABLES
- READ pose un verrou partagé ;
 - WRITE pose un verrou exclusif ;
- READ_LOCAL pose un verrou partagé permettant les insertions concurrentes pour les
tables MyISAM

Posons un verrou sur la table catégories d’une manière explicite :

Dans la seconde session, on va essayer de faire un insert :

Cet insert ne fonctionnera pas car le verrou est posé sur la table catégories.
Pour enlever le verrou, on va taper la commande ci-dessous :

Ensuite, on pourra insérer facilement notre ligne dans la table catégorie et sur la session2.

Posons un verrou exclusif sur la table catégories :

NB : un verrou écriture est prioritaire sur un verrou de lecture.

On peut inverser l’ordre de priorité des verrous via la commande ci-dessous :

Dans ce cas, les verrous en lecture sont prioritaires aux verrous en écriture.

11) Le partitionnement
a) Présentation du partitionnement 
Introduction :
Permet de diviser une table physique en plusieurs partitions en fonction des données
qu’elle contient.
 Pourquoi partitionner ?
 Pour gérer la montée ben charge
 Pour gérer de gros volumes de données
 Pour limiter les accès aux niveaux des IO
 Pour paralléliser les opérations de lecture et d’écriture au niveau disque
 Effacer rapidement les données (scaling back)
  Les prérequis pour utiliser le partitionnement

Mysql> SHOWPLUGINS
 Les contraintes liées au partitionnement
 Toutes les partitions d’une même table doivent utiliser le même moteur de stockage
 Limiter à 8192 partitions sous-partitions comprises.

 Les principaux types de partitionnement

 Le partitionnement de type RANGE
 Le partitionnement de type LIST
 Le partitionnement de type HASH
 Le partitionnement de type KEY

b) Le partitionnement par RANGE

La syntaxe pour créer notre table de partition_range est la suivante :

Affichage de la structure de la table commande_partition_range :

Chargement de la table commandes_partition_range via :

Vérification du stockage physique :

Le nombre d’enregistrement par année dans la table commandes_partition_range :

Répartition des données dans une table partitionnée :

 c) Le partitionnement par LIST :
 Caractéristiques
 Permet de définir une liste de nombres entiers unique pour chaque partition
 MySQL déclenche une erreur lors de l’insertion avec des valeurs non compris dans la liste des
valeurs partitionnées.
 Syntaxe :

CREATE TABLE <nom_table> (

Champ1 <type> <contrainte>,
…)
PARTITION BY LIST (<expression>)
PARTITION <nom_partition> VALUES IN (NULL),
PARTITION <nom_partition> VALUES IN (<valeur>, <valeur>…)

Le partitionnement de type LIST COLUMNS

 Caractéristiques
 Permet de séparer physiquement les données d’une table en fonction d’une ou plusieurs
colonnes (ENTIER, CHAR, VARCHAR, BINARY ou VARBINARY)
 Pas de possibilité d’avoir une expression comme critère de partitionnement
 La comparaison lors de l’affection des données se fait sur l’ensemble des valeurs

Création d’une table qui contiendra un certain nombre de partition par continent :

Affichage de la structure de la table clients_partition_list :

Chargement des données dans la table :

Vérification de la répartition par rapport au code_pays :

Vérification des informations sur la table partitionnée :

 d) Le partitionnement de type HASH :
 Caractéristiques
- Permet de distribuer d’une manière équitable les données d’une table sur un nombre de
partitions défini.
 Syntaxe

CREATE TABLE <nom_table> (

Champ1 <type> <contrainte>,
… )
PARTITION BY HASH (<expression>)
PARTITIONS <nombre_partition> ;

Affichage de la structure de la table :

Chargement de données dans la table table_partition_hash :

Rechargement de la table à partir d’elle-même : insertions de 4096 lignes

Récupération des informations dans la base :

Les partitions que MySQL scanne :

Sur quelle partition se trouve la valeur 200 :

e) Le partitionnement de type KEY :

 Caractéristiques
- Presque identique au type HASH
Différences
-Clé de partitionnement vide supporté
- La clé de partitionnement peut être de type chaîne
- Les données ne sont pas distribuées de manière homogène sur toutes les partitions.
 Syntaxe
CREATE TABLE <nom_table> (
 Champ1 <type> <contrainte>,
….. )
PARTITION BY KEY (<liste_de_colonne>)
PARTITIONS <nombre_partitions>;

Chargement de la table:

Récupération des informations dans la table :

f)Le sous-partitionnement
 Caractéristiques 
 Consiste à partitionner une partition de type RANGE ou LIST
 Les sous-partitions doivent êtres de type HASH ou KEY
 Syntaxe
CREATE TABLE <nom_table> (
Champ1 <type> <contrainte>,
…. )
PARTITION BY RANGE | LIST (<expression>)
SUBPARTITION BY HASH | KEY (<expression)
SUBPARTITION <nom_partition> (
<definition_des_partitions>
)
Structure de la table:

Chargement de la table:

Verification physique:
 12) Gestion des comptes utilisateurs
a) Gestion des utilisateurs
 Presentation
 Basé sur un système de privilèges simple et performant
 Chaque utilisateur qui souhaite se connecter doit s’authentifier auprès du serveur MySQL
 Aucun lien entre les utilisateurs MySQL et les utilisateurs Linux ou Windows
 L’identificatin est une combinaison de trois critères
-l’hôte (la machine) depuis lequel la connexion et initiée
- un nom d’utilisateur
- un mot de passe.
 Caractéristiques
  Deux utilisateurs sont créés après l’installation
- root : super utilisateur (sans mot de passe)
- L’utilisateur anonyme, c’est-à-dire tout utilisateur non connu du système.
 Système d’authentification
 Les utilisateurs et les privilèges sont stockés la base de données MySQL
 Les tables suivantes sont utilisées pour gérer les privilèges des utilisateurs

User, db, host, tables_priv, columns_priv, procs_priv

 Accès au serveur
- Contrôle de l’accès en deux temps

1. Vérification de l’identité de l’utilisateur à sa connexion (via l’hôte de connexion, le nom de

l’utilisateur et le mot de passe).
2. Vérification de chacune des requêtes envoyées au serveur pour s’assurer (l’utilisateur a les
privilèges nécessaires.

 Description des étapes de connexion

Etape1 :
- MySQL utilise la table user pour vérifier qu’un utilisateur est autorisé à se connecter
- Les champs (Host, User et Password) sont utilisées pour valider la connexion.
Etape2 :
-MySQL vérifie chaque requête envoyée afin de s’assurer que l’utilisateur a les privilèges
nécessaires à son exécution.
- Ces privilèges sont vérifiés à partir des tables user, db, table_priv et columns_priv

 Création d’un compte utilisateur

Syntaxe :
CREATE USER ‘utilisateur’@’hôte’ IDENTIFIED BY ‘mot_de_passe’;
 Modification du mot de passe
 Syntaxe :
SET PASSWORD FOR ‘utilisateur’@’hôte’=PASSWORD(‘mot_de_passe’);
 Suppression d’un compte utilisateur
Syntaxe:
DROP USER ‘utilisateur’@’hôte’;

Se connecter à partir de la machine locale :

Se connecter en tant que nordine :

Suppression du user qui se connecte à partir de la machine locale :

Adam permet de se connecter à n’importe quelle machine qui se trouve sur le domaine .[Link] :

Hamid peut se connecter à n’importe quelle machine du réseau 192.168.1 :

Caractères génériques :

Modification mot de passe de nordine :

Suppression de l’utilisateur nordine :

b) La gestion des privilèges :

 Les commandes GRANT/REVOKE

GRANT
 Permet d’attribuer des privilèges
 Permet de créer des utilisateurs
Syntaxe générale
GRANT privilèges [columns], …
ON item | [*] | [*.*]
TO username [IDENTIFIED] BY ‘password’]
[WITH GRANT OPTION]

Création de l’utilisateur avec la commande GRANT :

*.*= tous les objets de toutes les bases de données de l’instance.

Attribution des droits à david dans la base fact :

Ensuite, se connecter en tant que david dans la base de données fact et puis faire nos selects :
REVOKE
 Permet de retirer des privilèges
 Ne permet pas de supprimer des utilisateurs
 Syntaxe :
REVOKE privilèges [columns] | ALL, …
ON item | [*] | [*.*]
FROM username ;

Retrait de tous les privilèges à l’utilisateur David :

Pour pouvoir utiliser la commande, on se connecte en tant que root :

La commande SHOW GRANTS

 Permet de visualiser les droits des utilisateurs

Syntaxe générale
SHOW GRANTS FOR username ;

Vérification des requêtes :

 MySQL vérifie chaque requête envoyée afin de s’assurer que l’utilisateur a les privilèges
nécessaires à son exécution.
 Ces privilèges sont vérifiés à partir des tables user, db, table_priv et columns_priv
  Transcription du calcul fait sur une requête
Accès = privilèges globaux OU privilèges db OU privilèges de table OU privilèges de
colonnes.

Les privilèges globaux (instance)

 Stockés dans la table user de la base MySQL
Liste :
- CREATE TEMPORARY TABLES, CREATE USER, FILE, GRANT OPTION, LOCK TABLES, PROCESS,
PROXY, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW SCHEMAS/DATABASES,
SHUTDOWN et SUPER

Octroie de privilèges au niveau instance :

En donnant à David, le privilège de select sur tous les objets de toutes les bases de données, on
aura :

Pour retirer le privilège de select à david sur l’ensemble des objets de toutes les bases, on
procède comme suit :
Les privilèges au niveau bases
 Stockés dans la table db de la base MySQL

Liste
- ALTER, CREATE, CREATE TEMPORARY TABLE, CREATE WIEW, DROP, EVENT, GRANT
OPTION, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW et UPDATE.

Octroie de privilège d’update, insert et delete à Adam dans la base données fact.

Retrait de privilèges :

Les privilèges au niveau objets (tables) et colonnes 

 Stockés dans les tables tables_priv et columns_priv de la base MySQL

Liste au niveau des tables

- ALTER, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT, TRIGGER et
UPDATE
Liste au niveau des colonnes
-INSERT, SELECT et UPDATE
Octroie de privilèges d’update, delete et insert à Adam dans la base de données tables_priv :

Dans la table columns_priv :

Les privilèges au niveau procédures et fonctions stockées

 Stockés dans la table procs_priv de la base MySQL
Liste
 -CREATE ROUTINE, ALTER ROUTINE, EXECUTE et GRANT OPTION

13) Gestion de la sécurité

Les bonnes pratiques
 Suppression des comptes anonymes
 Mot de passe et privilèges
 Audit des comptes avec le privilège FILE et PROCESSLIST
 Cryptage des données sensibles (PASSWORD(), ENCRYPT(), ENCODE(), DECODE() ou MD5() )
 Sécuriser l’échange avec SSH et SSL

1 Suppression des comptes anonymes

On va se connecter à MySQL, aller dans user pour voir nos comptes anonymes et ensuite faire la
suppression de ces comptes.

On utilise la commande delete pour supprimer les comptes anonymes via :

Attribution d’un mot de passe à l’utilisateur root : elle se fait via la commande set password :

Suppression de l’accès aux bases de données dont le nom commence par test :
Pour ça, on procède comme suit :
Ensuite, suppression les bases de données test via la commande delete :

La commande flush privilèges : oblige MySQL à relire les tables privilèges pour prendre en compte les
modifications effectuées.

2 Mot de passe et privilèges :

Tout utilisateur qui a un privilège FILE peut écrire ou lire des fichiers sur disque dur.
Vérification des utilisateurs qui ont le privilège file_priv : on procède comme suit :

Seul l’utilisateur root a le privilège File.

Le privilège processlist : permet à l’utilisateur qui a ce privilège de voir le mot de passe d’un autre
utilisateur.
Seul root a le privilège de process_priv

Les plug-ins
 Plug-ins d’authentification (à partir de MySQL 5.6)
 Mysql_native_password
-par défaut
 Mysql_old_password
-méthode de hachage des versions < MySQL 4.1.1
 Sha256_password
- Utilisation de l’algorithme de hachage SHA-256
- Paramètre authentification-plugin de la section on [mysqld]
- Paramétrable lors de la création de l’utilisateur

Affichage des plugins d’authentification utilisés par défaut :

Il est possible de modifier le plugin d’authentification par défaut soit en rajoutant le paramètre
authentification-plugin dans le fichier de configuration [Link] ou soit dans le fichier de configuration de
l’instance MySQL ou lors de la création d’un utilisateur.

Modification du plugin d’authentification par défaut :

 Plug-ins de validation des mots de passe

 Validate_password
-Permet de tester le degré de sécurité d’un mot de passe
 Utilise 6 variables
. Validite_password_policy_number
. Low ou 0, MEDIUM ou 1, STRONG ou 2
. Validite_password_length
. Validate_password_number_count
. Validate_password_mixed_case_count
. Validate_password_special_char_count

En faisant un show plugins, on se rencontre que validate_password n’existe pas donc on va l’installer
via :

Affichage du contenu des différents paramètres :

  Plugins de validation des mots de passe

La fonction VALIDATE_PASSWORD_STRENGTH()
-Permet de renvoyer un code en fonction du niveau de sécurité du mot de passe
. 0 : taille < 4
. 25 : <= 4 et < à la valeur de validate_password_length
. 50: si validate_password_policy_number=’LOW’ est respectée
. 75: si validate_password_policy_number=’MEDIUM’ est respectée
. 100: si validate_password_policy_number=’STRONG’ est respectée

 Expiration du mot de passe

 Possibilité de forcer le changement d’un mot de passe
ALTER USER username PASSWORD EXPIRE;

Vérification de la création de Mohamed

En forçant Mohamed à changer de mot de passe via :

Dans ce le password_expired de Mohamed dévient Y à la place de N.

Sur MySQL, il est possible de forcer un utilisateur à changer de mot de passe lors de la prochaine
connexion au serveur ou de la prochaine requête.

 Utilitaire de configuration des mots de passe

 Outil mysql_config_editor
-Nouveauté MySQL 5.6
- Utilitaire en ligne de commande
 - Permet de stocker les informations d’authentification dans un fichier crypté nommé ~/.
[Link].
- Permet de se connecter à MySQL sans avoir à connaître ou taper un mot de passe MySQL
- Les commandes
Set, print, remove, reset

Création du fichier mysql_config_editor :

[Link] est un fichier crypté. Pour afficher les informations qui se trouvent dans ce fichier, on va
utiliser la commande :

Ensuite rajouter une connexion :

Après on peut se connecter directement à MySQL en utilisant :

Création de l’utilisateur oury via :

Connexion avec :

Vérification des bornes d’entées :

Connexion avec :

Suppression d’une entrée dans le fichier : on utilise la commande remove

14) Maintenance des tables sous MySQL

 La maintenance du serveur MySQL
 Défragmentation des tables
 Mise à jour des statistiques
 Détection des données corrompus
 Réparation des tables endommagés
  La commande CHECK TABLE
 Vérifie la structure
 Vérifie le contenu des tables
 Verrouillage des tables en lecture
Moteurs supportés
-MyISAM
- InnoDB
- Archive et CSV
 La commande CHECK TABLE

Syntaxe
CHECK TABLE table1 [, table2 ][option [option]]
Options
-QUICK : ne cherche pas les enregistrements orphelins
-FAST : Ne vérifie pas les tables dont la fermeture ne s’est pas effectuée proprement.
-CHANGED : Ne vérifie que les tables qui ont subi des modifications depuis la dernière
vérification.
- MEDIUM (défaut) : vérifie les enregistrements et calcule une clé d’intégrité (checksum).
- EXTENDED : vérifie les enregistrements et calcule une clé d’intégrité pour chaque
enregistrement.

Retourne une table avec quatre champs

-Table
- Op (toujours check)
- Msg_type (status=état de la table, error=message d’erreur, info(information),
warning=message avertissement)
- Msg_text
Check : est une commande qui peut être utiliser en cas de souci sur des tables.

 La commande REPAIR TABLE

 Corrige des tables en erreurs (CHECK TABLE)
-Moteurs supportés
MyISAM
Archive et CSV
-Syntaxe
REPAIR TABLE table1 [, table2] [NO_WRITE_TO_BINLOG] [QUICK] [EXTENDED] [USE_FRM]

Pour l’option quick seul l’index est réparé. Extended permet de mieux gérer les indexes formés de
chaine longue et USE_FRM est disponible à partir de la version 4.2 MySQL, elle permet de récréer le
fichier d’index à partir des informations contenues dans le fichier qui contient la structure de la
table .frm.

La commande repair table ne supporte pas le moteur Innodb c’est pourquoi, on va créer des tables via :
  La commande CHECKSUM TABLE
 Calcul la somme de contrôle (checksum) d’une table (droit de select)
 Moteur supporté
-InnoDB, CSV
Syntaxe
CHECKSUM TABLE table1 [, table2] [QUICK] [EXTENDED]

 La commande ANALYZE TABLE

 Analyse et enregistre les statistiques sur une ou plusieurs tables
 Moteurs supportés
- MyISAM
- InnoDB
 Verrou en lecture pour les tables MyISAM
Syntaxe
ANALYZE TABLE table1 [, table2] [NO_WRITE_TO_BINLOG]

 La commande OPTIMISE TABLE

 Permet de réduire la fragmentation
Récupère l’espace inutilisé
Trie-les indexes
Met à jour les statistiques
 Moteurs supportés

-MyISAM
Syntaxe
OPTIMISE [ NO_WRITE_TO_BINLOG] TABLE table1 [, table2]
  Utilitaire myisamchk
 Équivalent à [CHECK | REPAIR | ANALYZE] TABLE
 Travail directement sur les fichiers MyISAM au niveau système
 Syntaxe
Myisamchk [options] table1 [table2]
Sur les indexes on peut utiliser : dans ce cas MySQL ne fait que l’opération de vérification.

Pour trouver les différentes options de Myisamchk, on tape la commande ci-dessous :

On peut réparer une table avec l’option -r via :

Sur tables :

Sur l’ensemble des indexes :

Pour calculer les statistiques, on utilise l’option analyse (a) via :

Autres options de myisamchk :

Nb : myisamchk permet de réaliser les différentes opérations de maintenance d’une table à savoir :
l’analyse, la réparation, la vérification et l’optimisation.

 Utilitaire mysqlcheck
 Équivalent à [CHECK | REPAIR | ANALYZE] TABLE
 Travail directement au niveau base ou table
 Syntaxe
 Mysqlcheck [options] | --all-databases | databases base1 [base2]

Son utilisation nécessite l’ouverture de la base.

Pour retrouver toutes ses options, on utilise la commande mysqlcheck –help.

Vérification de toutes les tables de la base de données fact : on procède comme suit

Pour toutes les bases, on utilise la commande ci-dessous :

15) Import et Export des données sous MySQL

 SELECT … INTO OUTFILE

-Permet d’exporter les données lues dans un fichier texte sur le serveur
- Nécessite d’avoir le privilège FILE
- Si le fichier existe déjà l’opération échoue
- Syntaxe

SELECT champ1, …INTO OUTFILE ‘fichier’ FIELDS TERMINATED BY séparateur OPTIONNALY

ENCLOSED BY délimiteur LINES TERMINATED BY séparateur FROM table;

Pour exporter les données de la table article vers un fichier du système d’exploitation, on
procède comme suit :

Ensuite, il nous faut outfile pour l’export :

On retrouve le fichier qui a été généré via :

 Chargement des données

 LOAD DATA INFILE
Syntaxe
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE ‘file_name.txt’
[REPLACE | IGNORE]
INTO TABLE tbl_name [FIELDS
[TERMINATED BY ‘\t’] [[OPTIONALLY]
ENCLOSED BY”] [ESCAPED BY ‘\ \’]] [LINES
[STARTING BY “] [TERMINATED BY ‘\n’]]
[IGNORE number LINES]
[(col_name, …)]

Rechargement de données à partir d’un fichier plat dans une table : pour ça, on va d’abord
supprimer tous les éléments de la table article via :

Ensuite, rechargement du fichier [Link] à l’aide de la commande ci-dessous :

 Mysqldump
 Outil de sauvegarde logique
Syntaxe
Mysqldump -u username -p [OPTIONS] dbname [tables]
Mysqldump [OPTIONS] - -databases [OPTIONS] dbname1 [dbname2, …]
Mysqldump [OPTIONS] - -all-databases [OPTIONS]

Utilisations des options les plus courantes :

Pour ça, on va exporter la base de données fact via la commande mysqldump
Pour toutes les bases de données, on procède comme suit :

 Import d’une sauvegarde logique

Syntaxe
Mysql [OPTIONS] dbname < [Link]
Mysql [OPTIONS] dbname -e ‘fichiersql’

On va restaurer le contenu de la base de données fact comme suit :

Toutes les tables de la base de données fact ont été restaurées.

Exportation d’un serveur vers un autre de manière automatique : on va utiliser une seule instance ;
Dans notre cas, on va sauvegarder les données de la base fact vers une 2 ème base de mon instance.

Redirection du résultat de mysqdump vers le client mysql ou le serveur cible : on va utiliser la syntaxe ci-
dessous :

La base fact et la base fact_bkp ont le même contenu.

16) Programmation dans MySQL

a) Les procédures stockées
 La notion de routine

Programmes stockés dans le moteur MySQL

-Les procédures stockées et les fonctions
Fonctions
 - Centraliser le traitement des requêtes
- Une meilleure gestion de la sécurité
- De meilleurs temps de traitement
- Faciliter le développement et l’évolution des applications
 Langage LMD et LDD
 Langage LMD et LDD
 Déclaration des variables avec DECLARE et SET
 Utilisation des opérateurs logiques et des fonctions SQL prédéfinis
 Utilisations des fonctions de contrôle (IF, ELSE, LOOP, CASE …)
 Utilisation de curseurs pour le parcours et le traitement des lignes
 Qu’est-ce qu’une procédure stockée ?

Programme (routine) qui n’a pas de valeur de retour

Syntaxe
CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE nom_procedure ([parametre1 [, …]])
[Caractéristique]
[BEGIN] corps_routine [END]
 Les paramètres

[ IN | OUT | INOUT] param_name type

Type: type MySQL valide
 Les caractéristiques

LANGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL
DATA} | SQL SECURITY {DEFINER | INVOKER} | COMMENT ‘string’

 Commandes sur les procédures stockées

 Suppression
Syntaxe
DROP PROCEDURE IF EXISTE nom_procstock;
 Modification
Syntaxe
ALTER PROCEDURE IF EXISTE nom_procstock;

Réalisation d’une procédure stockée :

1. Changer le délimiter via :

2. Creation de la procedure:
Affichage de l’ordre de création d’une procédure stockée :
Affichage de l’ordre de création d’une procédure stockée :

Vérification :

Création d’une procédure stockée qui supprime une table : on procède comme suit :
Suppression :

b) Les fonctions
 Qu’est-ce qu’une fonction ?
 Programme (routine) qui renvoi un résultat
 Syntaxe
CREATE
[DEFINER = {user |CURRENT_USER}]
FUNCTION nom_fonction ([parametre1 [, …]])
[RETURNS type]
[caractéristique]
[BEGIN] corps_routine [END]
 Les caractéristiques

LANGUAGE SQL | [NOT] DETERMINISTIC | {CONTAINS SQL | NO SQL |READS SQL DATA |MODIFIES SQL
DATA} |SQL SECURITY {DEFINER |INVOKER} | COMMENT ‘string’

 Commandes sur les procédures stockés

 Suppression
-Syntaxe
DROP FUNCTION IF EXISTE nom_fonction;
 Modification
-Syntaxe
ALTER FUNCTION IF EXISTE nom_fonction;
Création d’une fonction qui va retourner le salaire annuel + la commission d’un employeur en fonction
de paramètre.

Tester notre fonction via :

Affichage de la structure de la fonction :

Pour obtenir beaucoup plus d’information sur la fonction, on procède comme suit :
 c) Les triggers
 Qu’est-ce qu’un trigger (déclencheur)
 Associé à une table
 S’active lorsqu’un événement particulier survient
- BEFORE (avant) ou AFTER (après),
. INSERT, UPDATE, DELETE
 Cas d’utilisation
 Log des utilisateurs/changements
 Exécuter des règles métiers
 Renforcer les contraintes d’intégrité
 Dupliquer des données
 Améliorer les performances et les changements
 Création d’un trigger
 Syntaxe
CREATE TRIGGER déclencheur
BEFORE | AFTER
INSERT | DELETE | UPDATE ON nom_table
FOR EACH ROW
[condition]
Action

Creation d’un trigger

La liste des employés qui ont traités des commandes :

Un trigger qui va empêcher la suppression d’un employer tant que cet employer se trouve dans le
champ no_employe de la commande : on va procéder comme suit :
1. Faire des copies des tables employés et commandes
 2. Création du trigger

Mettre en place un trigger de log :

Conserver la valeur avant et après modification et à quelle date a été réalisé la mise à jour et par quel
utilisateur

Mettre à jour le salaire de l’employé :

Augmentation du salaire de Davolio de 1000 et diminution du salaire de Fuller de 1000

 Informations sur les triggers

 Vue
INFORMATION_SCHEMA.TRIGGERS
 Suppression d’un trigger
Syntaxe
DROP TRIGGER déclencheur ;

d) Le planificateur d’événements
 Qu’est-ce que le planificateur d’événements
 Permet d’automatiser des tâches
-Intervalles réguliers
- Heure fixe
  Tourne sous forme de processus léger (thread)
 Nécessite le privilège EVENT
 Activation
SHOW GLOBAL VARIABLES LIKE ‘event_scheduler’;
SET GLOBAL event_scheduler =1;

Activation d’event_scheduler via:

 Creation d’un événement

 Syntaxe
CREATE
[DEFINER = {user |CURRENT_USER}]
EVENT [IF NOT EXISTS] nom_événement
ON SCHEDULE moment
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE |DISABLE ON SLAVE]
[COMMENT ‘commentaire’]
DO
Corps_evenement.

Moment:
AT timestamp [+ INTERVAL intervalley] …
| EVERY interval
[STARTS timestamp [+ INTERVAL intervalley] …]
[ENDS timestamp [+ INTERVAL intervalley] …]

Création d’une table qui va s’exécuter toutes les minutes :

Le stockage des métadonnées se trouve dans la base information_schéma

Création d’un événement qui va pouvoir insérer une ligne dans la table test_events dans 1min :

Une procédure qui va insérer une ligne dans la table test_events :

Ensuite, on va créer une tâche qui exécute la procédure via :

 Modification d’un événement

 Syntaxe
ALTER EVENT nom_événement …
 Suppression d’un événement
Syntaxe
DROP EVENT [IF EXISTS] nom_événement;
  Les métadonnées
Information_schema.events

17) Sauvegarde et restauration sous MySQL

a) Introduction
 Les questions à se poser

 Que sauvegarder
 A quelle fréquence
 La tolérance de perte des données
 La durée de rétention des sauvegardes
 Les bonnes pratiques
 Sauvegarder le fichier de configuration de MySQL
 Activer les journaux binaires (log-bin)
 Tester les sauvegardes
b) Les sauvegardes logiques
 Les outils de sauvegardes
 SELECT … INTO OUTFILE
 Mysqldump
 Sauvegarde cohérente avec mysqldump
 Options
Moteur MYSIAM
--opt (intègre l’option –lock-tables)
--lock-all-tables (pour les exports multi bases)
Moteur InnoDB
--single-transaction

Exportation de la base fact en ayant un export de données cohérentes :

Exportation de toutes les bases de données : avoir un export cohérent

Exportation des tables qui utilisent uniquement le moteur InnoDB d’une manière cohérente :
 c) Les sauvegardes physiques
 Caractéristiques
 Copie physique de tous les fichiers (datadir) au niveau OS
 Peut être réalisée base ouverte ou fermée

 Sauvegarde fichiers MyISAM

 Les fichiers à sauvegarder : .frm, .MYD et .MYI
 Base ouverte
-Nécessite un verrou au niveau table : LOCK TABLE <nom> READ ;
-Possibilité de verrouiller toutes les tables
LOCK TABLES <table1> READ, <table2> READ, …;
- Possibilité de verrouiller toutes les tables de l’instance
FLUSH TABLES WITH READ LOCK;
-Nécessite d’enlever le verrou après la sauvegarde : UNLOCK TABLES ;

Verrouillage de la table commande_myisam via :

Pour enlever le verrou, on tape la commande unlock :

Sauvegardes MyISAM
 Les étapes
  Pose d’un verrou sur les tables à sauvegarder
LOCK TABLE
 Forcer la synchronisation entre le cache MySQL et les fichiers
FLUSH TABLE
 Copie des fichiers .frm, .MYD et .MYI (tar, cp, gzip, cpio, …)
 Déverrouillage des tables
UNLOCK TABLES ;

Sauvegardes des tables commandes_myisam et employes_myisam :

1. Verrouillage des tables

2. On va ouvrir une autre session :

On va sauvegarder l’ensemble des fichiers pour les tables commandes_myisam et employes_myisam via
la commande tar :

Affichage du contenu de mon tar via :

Cela étant, on fait le unlock table dans la première session pour mettre aux utilisateurs de pouvoir
travailler sur les deux tables commandes_myisam et employes_myisam.

Sauvegarde fichiers InnoDB

 Les fichiers à sauvegarder
-innodb_data_file_path : .frm, ibdata<numéro>
-innodb_file_per_table: .frm, <nom_table>. ibd
 Base ouverte
-Nécessite un verrou au niveau table : LOCK TABLE <nom> READ ;
-Possibilité de verrouiller toutes les tables
 -LOCK TABLES <table1> READ, <table2> READ, …;
-Possibilité de verrouiller toutes les tables de l’instance
FLUSH TABLES WITH READ LOCK;
-Nécessité d’enlever le verrou après la sauvegarde : UNLOCK TABLES ;

Les fichiers à sauvegarder ce sont des fichiers qui portent l’extension .frm et .ibd

Sauvegarde fichiers InnoDB

 Les étapes
 Poser un verrou global en lecture sur l’ensemble des tables
FLUSH TABLES WITH READ LOCK
 Sauvegarde de tous les fichiers au niveau de l’OS (cp, tar, gzip, cpio, …)
 Déverrouillage des tables
UNLOCK TABLES

Sauvegarder l’ensemble des données des tables de la base de données fact :

On fait un flush dans la première session via :

Ensuite, on procède comme suit dans la seconde session :

Affichage du tar :
 d) La restauration
 Dépend du type de sauvegarde
-Restauration à partir d’une sauvegarde à froid

-Restauration à partir d’un export

 Type de restauration
-Restauration FULL, base, table
-Restauration PITR
Nécessite l’utilisation des journaux binaires
Utilisation de l’outil mysqlbinlog

Régénération de la base de données fact via :

Insertion d’une ligne dans la table catégories :

Réalisation d’une sauvegarde : mais avant tout, on vérifie d’abord si le répertoire bakup existe.
Ensuite, on réalise le backup full avec l’outil mysqldump.
Éditer le fichier via :

Le premier fichier journal qu’on devrait réappliquer si on restaure le fichier /bakup/backup_pitr.sql est :
‘mysql3306-binlog.000003’

Ensuite, on fait trois insertions dans la table catégories de la base de données fact via :

Le show master status est censé nous retourner mysql3306-binlog.000007.

L’utilisation de l’outil mysqlbinlog :
On restaure via :

Rejouer l’ensemble des fichiers journaux qui contient les différentes transactions que nous avons tapés.
On procède comme suit :

18) Réplication
a) Introduction
 Qu’est-ce que la réplication ?
 Permet de disposer de plusieurs serveurs avec les mêmes données
 Utilise les logs binaires
 Pourquoi utiliser la réplication
 Montée en charge
 Répartition de la charge
 Haute disponibilité
 Sauvegarde
 Les différentes topologies
 Maître/Esclave
 Maître/Maître
-Actif – Actif
-Actif – Passif
 Les modes de réplication
 SBR (Statement Based Replication : antérieure à MySQL 5.1)
 RBR (Row Based Replication : à partir de MySQL 5.1)
 La réplication semi-synchrone (depuis la version 5.5)
-Nécessite l’activation sur le maître et les esclaves

Installation du plugin :
 b) Mise en œuvre de la réplication :
 Les étapes
 Configuration du maître
-Désactivation des firewalls
- logbin, bind-address, server-id
-Création d’un utilisateur avec les droits REPLICATION SLAVE et REPLICATION CLIENT
 Configuration de l’esclave
-Désactivation des firewalls
-Bind-address, server-id
-Création d’un utilisateur avec les droits REPLICATION SLAVE et REPLICATION CLIENT
-Configuration avec CHANGE MASTER TO MASTER_HOST, …;
-Démarrage de la réplication avec START SLAVE ;

c) Switchover
 Les principales variables de configuration
 Server-id
 Log-bin
 Binlog-do-db, binlog-do-table, binlog-ignore-db, binlog-ignore-table
 Replicate-do-db, replicate-ignore-db, replicate-do-table, replicate-ignore-table
 Expire_log_days
 Sync_binlog
 …
 Les commandes SQL
 SHOW MASTER STATUS
 SHOW SLAVE STATUS
 START | STOP SLAVE {IO_THREAD |SQL_THREAD}
 SHOW BINARY LOGS
 PURGE BINARY LOG options
 RESET MASTER
 RESET SLAVE
 CHANGE MASTER TO
 Les étapes
 Arrêter les écritures sur le nouveau maître et activer les logs binaires
 Relever la position des journaux binaires sur le futur maître (SHOW MASTER STATUS)
 Configurer la réplication sur l’ancien maître (CHANGE MASTER TO)
 Configurer la réplication sur l’ancien maître (START SLAVE)
 Arrêter la réplication sur le nouveau maître (STOP SLAVE)

19) L’optimisation des performances

Les éléments de performances

  Matériel
-CPU, Mémoire, Disque (type, RAID 0-1-5-10)
 Logiciel
-Caches MySQL, paramètres MySQL, OS, méthode de synchronisation
 Applicatif
-Schéma de données, indexes, plan d’exécution

Éléments logiciels
 Le cache de requête
-query_cache_type (active ou pas le cache de requête)
0= pas de cache
1= met en cache toutes les requêtes sauf celles qui ont le flag SELECT S_NO_CACHE
2= met en cache seulement les requêtes qui comportent le flag SELECT SQL_CACHE
- query_cache_size (taille du cache de requête)
-RESET QUERY CACHE (réinitialise le cache de requête)
-query_cache_limit (taille maximale des données mis en cache)
 Le cache de table
-table_open_tables
-open_table_definitions

Le query_cache_type est désactivé. Pour l’activer, on va d’abord arrêter l’instance puis rajouter le
paramètre query_cache_type=1 dans le fichier /etc/[Link] et ensuite redémarrer l’instance.

Cette requête ne permet pas de stocker le résultat dans le cache. On peut forcer la requête à stocker le
résultat dans le cache en utilisant la syntaxe ci-dessous :

 MyISAM
-key_buffer_size (taille du cache d’index)
 InnoDB
-innodb_buffer_size (taille du cache mémoire)
-innodb_log_buffer_size (taille du tampon de log)
 Paramètre par client
 -sort_buffer_size (taille du tampon de tri)

 Élément applicatif

Les indexes
Quand créer un index ?
 Sur un champ utiliser dans une clause WHERE, ORDER BY ou GROUP BY
 Sur des tables assez volumineuses
 Sur les clés étrangères
 Un index implicite est créé lors de la création d’un PRIMARY KEY

Les types d’index

 Index uniques
 Clé primaire
 Index sur plusieurs colonnes
 Index sur un préfixe de colonne
La commande EXPLAIN
-Permet d’afficher le plan d’exécution d’une requête

1. Création d’une table :

2. Création d’une procédure :

 3. Injection des données

Explain permet d’afficher le plan d’exécution. Elle permet aussi de vérifier si une requête utilise ou non
un index.

4. Création d’index 
@