Tel. ppt

Analyse et Conception
de
Systèmes Informatiques
(ACSI)

Cours 1 :
Présentation
L'algèbre relationnelle
Le modèle entité-relation (E-A)

Cours 2 :
Du modèle E-A au MPD
Le langage SQL

Cours 3 :
SQL avancé

Cours 4 :
Méthodes de modélisation

Cours 5 :
Administration et sécurité
2
Sommaire

Architecture

Administration


Efficacité

Fiabilité

Outils

Reprise
Sécurité
3
Architecture
Le système de base de données MySQL est une
application de type Client / Serveur.
Cette application est un logiciel libre basé sur une
licence Open Source GPL.
MySQL est écrit en C++. Il fonctionne sur de
nombreux systèmes d'exploitation (Windows,
Linux, Unix). Il supporte de nombreuses API
telles que C, C++, PHP, Java, …
Il dispose d'un driver ODBC sous Windows.
4
Architecture
MySQL est interrogeable par le langage SQL.
Il est capable de gérer des bases de données de
très grande taille.
La taille d'une table est limitée à 8 millions de To
(2^63).
Chaque table peut contenir jusqu'à 32 indexes
constitués de 16 colonnes au maximum.
Le nombre maximum de connexions n'a pas de
limite théorique mais est lié aux ressources
disponibles sur le serveur.
5
Architecture
Les clients du SGBD
Mysql peuvent être
des outils de requêtes
SQL, des logiciels ou
des sites WEB.
Dans tous les cas, le
SGBD permet de
gérer les connexions
ainsi que les accès
aux données.
6
Architecture à 2 niveaux
Client
Client
Clients
Serveur
Serveur de BD
L'architecture à 2
niveaux caractérise
les systèmes clients /
serveurs pour
lesquels la
communication est
faite directement,
sans intermédiaire.
C'est le cas avec un
requêteur tel que
MySQL Query
Browser.
7
Architecture à 3 niveaux
Client
Client
Clients
Serveur
Serveur d'applications
Serveur de BD
L'architecture à 3
niveaux ajoute un
serveur d'applications
également appelé
Middleware chargé
des communications
avec les clients.
Lui seul peut se
connecter au serveur
de BD ce qui accroît
la sécurité.
8
Exemple 1 : logiciel de gestion
Client lourd
Client lourd
Clients
Serveur
Logiciel de gestion
Base de données
Une entreprise a conçu son
propre logiciel de gestion.
Les différents clients sont
installées sur les postes
des utilisateurs.
Ils communiquent avec
l'application Serveur qui
est la seule à
communiquer avec la base
de données.
9
Exemple 2 : site WEB
Navigateur
Navigateur
Clients
Serveur
Serveur WEB
PHP et Apache
Pour les sites Internet, le
même principe est
généralement utilisé.
La majorité des sites utilisent
la configuration LAMP :
Linux : Système
Apache : serveur Web
Base de données
MySQL
MySQL : base de données
PHP : langage de
programmation
10
Exemple 2 : site WEB
Navigateur
Navigateur
Clients
Serveur
Dans cette architecture,
Apache gère les
connexions clientes.
Serveur WEB
PHP et Apache
PHP est utilisé pour générer
les pages Web de façon
dynamique en interaction
avec la base de données.
Base de données
MySQL
Dans la plupart des offres
d'hébergement, la base de
données, le serveur
Apache et le moteur PHP
sont sur le même serveur.
11
Exemple 2 : site WEB
Navigateur
Navigateur
Clients
Serveur
PhpMyAdmin
Base de données
MySQL
Lorsque l'on accède à la
base de données par le
biais de phpMyAdmin,
c'est l'application qui
communique avec la base
de données.
Pour des raisons de sécurité,
généralement, l'accès à la
base n'est possible que
par un client situé sur le
même serveur que le
SGBD.
12
Administration
Comme toute application, un SGBD doit être géré
et maintenu.
C'est le rôle du DBA (Data Base Administrator).
Celui-ci est chargé de :

S'assurer de l'efficacité de la base

Assurer un bon fonctionnement de la base

Permettre une reprise rapide en cas d'incident
13
Administration : efficacité
La définition du modèle est généralement confiée
aux développeurs de l'application associée à la
base de données.
Les développeurs créent des tables afin de
ranger les données de façon optimum.
Il arrive souvent que les performances ne soient
pas celles attendues ou que la volumétrie
impose des optimisations.
Le DBA a pour rôle de conseiller et d'orienter les
développeurs.
14
Administration : efficacité
Les principaux champs d'action du DBA sont :

Conseil sur la structure des tables

Conseil sur la création d'index

Recherche des requêtes trop longues et
proposition pour les optimiser

Recherche des verrous sur les tables et
propositions pour les limiter
Il est également de la responsabilité des
développeurs de ne pas mettre en place des
fonctionnalités trop lourdes pour la base.
15
Administration : fiabilité
La base de données est composée de tables
dont les données sont stockées dans des
fichiers. Au fur et à mesure des insertions,
suppressions, modifications, les fichiers se
fragmentent.
Les bases de données proposent généralement
des outils pour permettre de défragmenter les
fichiers ou de compacter les tables.
Il est fréquent que les DBA créent des scripts de
maintenance qui sont exécuté régulièrement
16
sur le serveur.
Administration : fiabilité
En MySQL, il existe plusieurs fonctions
permettant de vérifier, réparer optimiser ou
analyser des tables.
CHECK TABLE nomtable [,nomtable2] [option]
retourne une table dont les enregistrements
renseignent sur l'état des tables vérifiées.
Les options possibles sont :

QUICK : ne recherche pas les
enregistrements orphelins (qui ne répondent
pas aux contraintes d'intégrité référentielle)
17
Administration : fiabilité

FAST : Ne vérifie que les tables dont la
fermeture ne s'est pas déroulée correctement.

CHANGED : Ne vérifie que les tables qui ont
subi des modifications depuis la dernière
vérification.

MEDIUM : 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 chacun des
enregistrements. C'est la méthode la plus sûre
et donc la plus longue.
Cette commande ne fonctionne qu'avec les
moteurs MyIsam et InnoDB.
18
Administration : fiabilité
Pour corriger les anomalies rencontrées, il existe
la commande REPAIR TABLE.
REPAIR TABLE nomtable [,nomtable2][option]
L'option QUICK permet de ne réparer que l'index.
L'option EXTENDED permet de mieux gérer les
index formés de chaînes longues.
Cette commande ne fonctionne qu'avec le moteur
MyIsam.
19
Administration : fiabilité
La commande OPTIMIZE TABLE permet de
réduire la fragmentation des fichiers. Cela est
surtout nécessaire sur des tables contenant des
champs de taille variable (varchar, text, blob).
Cette commande agit sur 3 points :

Corrige et améliore le stockage des
enregistrements supprimés ou fragmentés

Trie des index

Met à jour les statistiques.
Les statistiques des tables sont des informations
20
sur leur volumétrie et leur taille.
Administration : fiabilité
Les statistiques sont essentielles pour le moteur de
base de données.
Pour chaque requête SQL, le moteur va définir un
plan d'exécution. Il y a toujours plusieurs façon de
faire en utilisant tel ou tel index par exemple.
C'est à partir des données de statistiques que le
moteur va choisir la meilleur façon de faire grâce à
des algorithmes relativement compliqués. Si les
statistiques ne sont pas à jour, il est alors possible
que le moteur choisisse un mauvais plan.
21
Administration : fiabilité
La syntaxe est :
OPTIMIZE TABLE nomtable [, nomtable2]
Cette commande ne fonctionne qu'avec les moteurs
MyIsam et BDB.
En cas d'utilisation avec le moteur InnoDB, le
système le remplace automatiquement par les
commandes alter table et analyze ce qui a pour
effet de recréer la table et les index et de mettre à
jour les statistiques.
22
Administration : fiabilité
Il existe également des programmes à lancer en
ligne de commande.
Le programme myisamchk permet d'effectuer
toutes les opérations de maintenance vue
précédemment.
Comme son nom l'indique, ce programme est
réservée aux tables de type MyIsam.
Ce programme doit toujours être lancé base
fermée, c'est à dire qu'aucun client ne doit se
connecter à la base pendant l'exécution de
l'application.
23
Administration : fiabilité
Il existe un autre programme à lancer en ligne de
commande : mysqlcheck.
Contrairement à myisamchk, celui-ci doit être
lancé base ouverte car il se connecte au
serveur.
Cet utilitaire peut être utilisé pour vérifier, réparer,
analyser et optimiser des tables ou des bases
de données complètes.
24
Administration : fiabilité
Comme toute application digne de ce nom,
MySQL génère des fichiers de logs.
Il est indispensable de consulter régulièrement
les logs pour s'assurer que le moteur ne
rencontre pas d'erreurs récurrentes.
Afin de résoudre certains problèmes, il est
possible de modifier le niveau de trace des
logs.
25
Administration : fiabilité
Le suivi général des requêtes :
Il est possible de préciser au moteur de logger
toutes les requêtes transmises au serveur.
Pour cela, il faut modifier le fichier de
configuration my.cnf pour qu'il contienne la
section suivante :
[mysqld]
log[=nom_fichier]
Le moteur doit être redémarré en cas de
modification du fichier de configuration.
26
Administration : fiabilité
Le suivi binaires des mises à jour :
Il est possible de préciser au moteur de logger toutes
les requêtes de mise à jour transmises au serveur.
Pour cela, il faut modifier le fichier de configuration
my.cnf pour qu'il contienne la section suivante :
[mysqld]
log-bin[=nom_fichier]
Il faut utiliser l'application mysqlbinlog pour lire les
fichiers de log car ils sont au format binaire.
27
Administration : fiabilité
Il est aussi possible d'activer le suivi des requêtes
lentes. Pour cela, il faut modifier le fichier de
configuration my.cnf pour qu'il contienne la section
suivante :
[mysqld]
log-slow_queries[=nom_fichier]
Il faut utiliser l'application mysqdumpslow pour lire
les fichiers de log car ils sont au format binaire.
28
Administration : fiabilité
Les fichiers de logs sont donc fondamentaux pour
assurer un suivi fiable de la base de données.
Il est utile de mettre en place un archivage
automatique de ces fichiers.
Il arrive souvent de devoir comprendre un
fonctionnement passé et ces fichiers sont les
seules informations dont disposent les DBA.
29
Administration : outils
Les DBA doivent être capable de savoir ce qui se
passe à tout moment sur leur base de données.
Pour cela, ils disposent d'un panel de
commandes permettant de consulter les
connexions, de voir ce que font les clients, de
vérifier les ressources, les requêtes, …
Il existe également des commandes de
consultations de structure des tables utiles aux
DBA.
30
Administration : outils
Les commandes DESCRIBE ou EXPLAIN suivi
du nom de la table permettent d'en consulter la
structure.
EXPLAIN peut également être utilisé pour
analyser les requêtes SQL.
En exécutant EXPLAIN suivi de la requête,
MySQL retourne le détail de l'action qu'il va
mener pour réaliser la requête.
Cet commande peut évidemment être utilisée par
les développeurs lors de la conception de
31
requêtes complexes.
Administration : outils
La fonction BENCHMARK permet de jouer N fois
une requête SQL.
BENCHMARK (nb_fois, SQL)
Cela permet de réaliser des tests périodiques
pour s'assurer que le moteur répond de façon
habituelle.
Note : seule la première requête accède aux
données. Les suivantes bénéficient du cache
utilisé par le moteur. Cette commande ne
permet donc pas de faire une moyenne des
temps d'exécution mais juste une statistique.
32
Administration : outils
Afficher les structures :
Les commandes SHOW DATABASES et SHOW
TABLES permettent de lister les bases et les
tables du moteur de base de données.
La commande SHOW COLUMNS décrit les
champs de la table spécifiée.
La commande SHOW INDEX affiche les index de
la table.
33
Administration : outils
Afficher l'activité :
La commande SHOW OPEN TABLES indique les
tables actuellement ouvertes. Ce sont les tables
sur lesquelles des requêtes sont actuellement
exécutées ou que MySQL garde en cache.
La commande SHOW PROCESSLIST montre
tous les processus du serveur MySQL.
L'instruction KILL permet de mettre fin à un des
processus.
34
Administration : reprise
Un autre rôle fondamental du DBA est de
permettre une reprise rapide en cas d'incident.
Une base de données comme tout système
informatique est susceptible de planter.
Même s'il est toujours possible de rencontrer un
bogue, il est plus fréquent de rencontrer des
problèmes machine ou matériel (problème avec
l'OS, panne machine, …)
La seule solution pour prévenir ce genre de
problème est de disposer de sauvegardes.
35
Administration : reprise
Le DBA dispose de plusieurs outils pour effectuer
des sauvegardes de la base de données.
L'utilitaire mysqlhotcopy permet de sauvegarder
directement les fichiers utilisés par la base.
L'inconvénient principal de cette application est
qu'elle doit être lancée sur le serveur bases
fermées.
Cette application va effectuer les actions
suivantes : verrouillage des tables, vidage des
caches et copie des répertoires.
36
Administration : reprise
L'utilitaire mysqldump permet de créer un fichier
de script SQL permettant de recréer la base
ainsi que tout son contenu.
Qu'elle que soit la méthode choisie, le DBA devra
mettre en place une politique de sauvegarde en
encapsulant ces commandes dans des scripts
exécutés régulièrement sur le serveur.
Il pourra également définir des tables plus
sensibles que d'autres pour lesquelles il
effectuera des sauvegardes plus fréquentes.
37
Administration : reprise
En cas de nécessité de restauration de la base
de données, il suffira de remplacer les
répertoires mis de côté par l'utilitaire
mysqlhotcopy ou de jouer le script SQL généré
par l'utilitaire mysqldump.
On appelle usuellement une sauvegarde un
DUMP.
38
Sécurité
La sécurité est essentielle dans toute application
et plus particulièrement dans un SGBD. Les
données sont souvent le trésor de l'entreprise.
Il est impensable qu'un utilisateur accède à des
données qui ne lui sont pas destinées.
Il est encore plus impensable qu'un utilisateur
malveillant parvienne à dérober les données,
les conséquences sont souvent dramatiques.
39
Sécurité : Anonymat
Un des premiers réflexes à avoir pour sécuriser la
base MySQL est de supprimer tout compte
anonyme.
La table système Mysql.user contient tous les
utilisateurs déclarés.
Il faut s'assurer qu'il n'y a pas d'utilisateur sans
mot de passe.
En cas de modification manuelle de cette table, il
faut exécuter 'Flush Privileges' pour que le
moteur les prennent en compte.
40
Sécurité : Gestion utilisateur
Il est indispensable de bien gérer les privilèges des
utilisateurs en ne lui permettant que le nécessaire.
Ceux-ci sont rangés dans 3 catégories :

Accès aux données
 Accès aux structures
 Droits administrateurs
Il est également possible pour chaque utilisateur:


de préciser la base accessible
limiter le nombre de requêtes ou mises à
jour
41
Sécurité : Gestion utilisateur
L'accès aux données permet de préciser si
l'utilisateur peut :

SELECT : lire des données

INSERT : ajouter des données

UPDATE : modifier des données

DELETE : supprimer des données

FILE : accéder aux fichiers du serveur
La majorité des utilisateurs devra avoir les quatre
premiers droits pour interagir avec la base de
données.
42
Sécurité : Gestion utilisateur
La deuxième catégorie permet de préciser les
droits au niveau de la structure : CREATE,
ALTER, DROP, ...
Les utilisateurs 'client' de l'application ne devront
jamais obtenir ces droits.
Ceux-ci sont généralement réservés aux
développeurs pour une de développement ou
d'intégration et aux DBA seuls pour une base
de production.
43
Sécurité : Gestion utilisateur
Enfin, MySQL permet de restreindre la
sollicitation de la base de données par
utilisateur. Ces fonctionnalités sont assez peu
utilisées mais permettent de :

Limiter le nombre de requêtes par heure

Limiter le nombre de mise à jour par heure

Limiter le nombre de connexions par heure

Limiter le nombre de connexions simultanées
Cela pour éviter toute saturation de la base de
données.
44
Sécurité : Gestion utilisateur
La troisième catégorie permet de préciser les
droits d'administration : création de base de
données, gestion des utilisateurs, arrêt
démarrage de la base, ...
Généralement, ceux-ci sont réservés aux DBA
quel que soit l'environnement de travail
(développement, intégration, recette, préproduction, production).
Il est également possible d'attribuer des droits
différents si plusieurs DBA de niveaux différents
45
administrent la base de données.
Sécurité : Stockage des données
Dans une base de données, il est important de
crypter les données sensibles.
En effet, tout utilisateur ayant les droits de lecture
sur une base peut tenter de lire des données
qui ne lui sont pas destinées.
Des données critiques comme des mots de
passe ou des coordonnées bancaires par
exemple ne doivent pas être utilisables en l'état.
MySQL propose des fonctions SQL de cryptage
et de décryptage.
46
Sécurité : Stockage des données
La fonction PASSWORD() permet d'encrypter
une chaîne de caractère. Il ne sera pas possible
de retrouver la valeur initiale de la chaîne. Il
sera donc nécessaire de comparer une
nouvelle chaîne cryptée de la même façon avec
la chaîne stockée en base.
Les fonctions ENCODE() et DECODE() reçoivent
en paramètre un mot de passe. Elles
permettent donc de retrouver la valeur initiale
sous réserve de connaître le mot de passe.
47
Sécurité : Stockage des données
Enfin, MySQL propose des méthodes de cryptage
ayant fait leurs preuves :

ENCRYPT() utilisant la fonction Unix crypt()

MD5() : cryptage sur 128 bits

SHA1() : cryptage sur 160 bits
Bien entendu, ces fonctions ralentissent les
temps d'exécution et doivent donc être utilisées
avec discernement.
48
Sécurité : Réseau
Comme toute application client/serveur, MySQL
communique par le biais de ports avec ses
clients.
Généralement, on sécurise l'accès à la base de
données en ne permettant la communication
qu'entre le serveur d'application et la base de
données. Pour cela, les deux applications
peuvent être hébergées sur le même serveur et
le port de communication MySQL bloqué de
l'extérieur par le biais d'un FireWaLL.
49
Sécurité : Réseau
Navigateur
Navigateur
Clients
Serveur
Port 3306
FIREWALL
Port 80
PHP / Apache
Port
3306
Base de données
MySQL
Dans cet exemple, le
FireWall est utilisé pour
empêcher l'accès de
l'extérieur au serveur sur le
port 3306 (MySQL). En
revanche, il ne gène par la
communication entre le
serveur PHP et la base de
données.
Il permet également la
communication entre
l'extérieur et le serveur
WEB par le port 80.
50
Sécurité : Communication
Par défaut, les données transitent entre le
serveur et le client sous forme non compressée.
Il est ainsi possible d'intercepter une
communication et de lire les données
transmises.
Il peut donc être bon de sécuriser l'échange des
données via un protocole tel que SSH.
51
Sécurité : Réseau
Client
Client
Clients
Serveur
Protocole SSH
Dans cette architecture,
SSH devra être
utilisée directement
par MySQL.
Les données
transmises seront
alors cryptées.
Serveur de BD
52
Sécurité : Réseau
Client
Client
Clients
Serveur
Protocole SSH
Serveur d'application
Serveur de BD
Dans cette architecture,
il n'est pas nécessaire
de crypter les
données depuis
MySQL.
C'est donc le serveur
d'application qui se
chargera de cette
tâche.
53