Analyse et Conception de Syst

1
Analyse et ConceptiondeSystèmes
Informatiques(ACSI)
2

• 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é

3
Sommaire

• Architecture
• Administration
• Efficacité
• Fiabilité
• Outils
• Reprise
• Sécurité

4
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.

5
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 (263).
• 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.

6
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.

7
Architecture à 2 niveaux

• 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.

Client
Client
Clients
Serveur
Serveur de BD
8
Architecture à 3 niveaux

• 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é.

Client
Client
Clients
Serveur
Serveur d'applications
Serveur de BD
9
Exemple 1  logiciel de gestion

• 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.

Client lourd
Client lourd
Clients
Serveur
Logiciel de gestion
Base de données
10
Exemple 2  site WEB

• 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
• MySQL  base de données
• PHP  langage de programmation

Navigateur
Navigateur
Clients
Serveur
Serveur WEB PHP et Apache
Base de données MySQL
11
Exemple 2  site WEB

• Dans cette architecture, Apache gère les
  connexions clientes.
• PHP est utilisé pour générer les pages Web de
  façon dynamique en interaction avec la base de
  données.
• 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.

Navigateur
Navigateur
Clients
Serveur
Serveur WEB PHP et Apache
Base de données MySQL
12
Exemple 2  site WEB

• 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.

Navigateur
Navigateur
Clients
Serveur
PhpMyAdmin Base de données MySQL
13
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

14
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.

15
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.

16
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 sur le
  serveur.

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

18
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.

19
Administration  fiabilité

• Pour corriger les anomalies rencontrées, il
  existe la commande REPAIR TABLE.
• REPAIR TABLE nomtable ,nomtable2option
• 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.

20
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
  sur leur volumétrie et leur taille.

21
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.

22
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.

23
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.

24
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.

25
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.

26
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
• lognom_fichier
• Le moteur doit être redémarré en cas de
  modification du fichier de configuration.

27
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-binnom_fichier
• Il faut utiliser l'application mysqlbinlog pour
  lire les fichiers de log car ils sont au format
  binaire.

28
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_queriesnom_fichier
• Il faut utiliser l'application mysqdumpslow pour
  lire les fichiers de log car ils sont au format
  binaire.

29
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.

30
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.

31
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
  requêtes complexes.

32
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.

33
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.

34
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.

35
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.

36
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.

37
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.

38
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.

39
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.

40
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.

41
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
• Limiter le nombre de connexions.

42
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.

43
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.

44
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.

45
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
  administrent la base de données.

46
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.

47
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.

48
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.

49
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.

50
Sécurité  Réseau
Navigateur
Navigateur

• 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.

Clients
Serveur
Port 3306
FIREWALL
Port 80
Port 80
PHP / Apache Port 3306 Base de données MySQL
51
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.

52
Sécurité  Réseau

• Dans cette architecture, SSH devra être utilisée
  directement par MySQL.
• Les données transmises seront alors cryptées.

Client
Client
Clients
Serveur
Protocole SSH Serveur de BD
53
Sécurité  Réseau

• 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.

Client
Client
Clients
Serveur
Protocole SSH Serveur d'application
Serveur de BD