Blog ENI : Toute la veille numérique !
💥 Un livre PAPIER acheté
= La version EN LIGNE offerte pendant 1 an !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici

Généralités sur MySQL

Introduction

MySQL s’est fait connaître à la fin des années 1990 comme étant un système de gestion de bases de données relationnelles de choix pour les petits projets web, profitant principalement de sa gratuité et de sa rapidité. Puis, dans le courant des années 2000, les géants du Web se sont tous mis à utiliser massivement MySQL. La suite a été plus difficile, puisque dans la seconde moitié des années 2000, tous ces grands acteurs ont été confrontés aux limitations de MySQL concernant sa scalabilité. C’est à ce moment qu’ont émergé de nombreuses solutions NoSQL. Mais finalement, MySQL a su évoluer rapidement au cours des dernières années, alors que les problèmes de jeunesse des solutions NoSQL sont devenus de plus en plus évidents.

Aujourd’hui, MySQL reste un choix très répandu pour les projets web, beaucoup moins pour les projets plus traditionnels. Quelles en sont les raisons ? Tout d’abord, MySQL est capable d’offrir de bonnes performances même sur des serveurs peu puissants. De plus, sa stabilité est excellente et, sur une instance correctement configurée, il est très rare de voir MySQL planter ou de perdre des données. Enfin, sa gratuité permet d’envisager des déploiements...

Architecture

1. Le serveur et les clients

Le serveur MySQL (mysqld) intercepte les requêtes émises par les clients, transforme ces requêtes en un plan d’exécution, récupère les données selon le plan d’exécution généré, et enfin retourne le résultat au client. Il se compose de plusieurs modules chargés de gérer :

  • Les protocoles de communication avec les clients (TCP/IP, socket, SSL...).

  • Les droits d’accès aux différentes ressources disponibles (voir chapitre Sécurité et gestion des utilisateurs).

  • Les caches afin de minimiser les accès disque.

  • Les différents types de journaux serveurs (binaire, requêtes lentes...).

  • L’analyse, l’optimisation et l’exécution des requêtes.

  • Le stockage des données.

Le diagramme suivant résume les différents modules du serveur :

images/01EI01N.png

Chaque distribution de MySQL contient plusieurs clients en ligne de commande pour interagir avec le serveur, les plus utilisés étant :

  • mysql : pour exécuter des requêtes. MySQL 8.0 contient également mysqlsh (MySQL Shell), une version plus évoluée du client mysql, qui remplacera peut-être le client mysql à terme.

  • mysqldump : pour effectuer des sauvegardes logiques (voir le chapitre Sauvegarde et restauration).

  • mysqladmin : pour effectuer...

Utilisation des ressources matérielles

1. Utilisation du disque

Les données sont stockées sur disque afin d’assurer leur persistance. Sur votre serveur MySQL vous retrouverez ainsi :

  • Les schémas (ou bases de données) qui sont représentés sur le disque par des répertoires du même nom. Ainsi, le schéma world a également pour nom de répertoire world.

  • Chaque table est en général représentée par un ou plusieurs fichiers dépendant du moteur de stockage choisi. Par défaut, les seuls fichiers présents ont l’extension .ibd indiquant qu’il s’agit de fichiers de données InnoDB.

  • Les journaux du serveur (journal binaire, journal des erreurs...), ceux des moteurs de stockage (ib_logfile0 pour InnoDB par exemple), de la réplication, le fichier de configuration…

  • Les déclencheurs (triggers).

À noter que les fichiers .frm représentant historiquement la structure des tables ont disparu depuis MySQL 8.0, mais uniquement pour les tables InnoDB. Pour plus de détails, voir la section Les moteurs de stockage dans ce chapitre.

Par défaut, tous ces fichiers sont stockés dans un unique répertoire appelé répertoire de données. L’emplacement de ce répertoire est configurable avec le paramètre datadir.

Il est possible de modifier la configuration de manière à...

Variantes de MySQL

1. MariaDB

MariaDB a été lancé en 2009 par le créateur de MySQL Monty Widenius en vue d’offrir une alternative à la version officielle développée par Oracle.

MariaDB intègre des améliorations venant de la communauté (moteur XtraDB de Percona, nombreux moteurs de stockage) ainsi que des fonctionnalités conçues directement par les développeurs de MariaDB, comme des changements dans l’optimiseur de requêtes.

MariaDB avait pour but d’être 100 % compatible avec la version MySQL d’Oracle, mais cette compatibilité est nettement cassée puisque certaines fonctionnalités ont été développées de manière indépendante par rapport à MySQL (par exemple : colonnes virtuelles, identifiants de transaction pour la réplication, JSON).

La numérotation des versions de MariaDB est également complètement indépendante de celle de MySQL. Il est donc difficile de savoir quelle version de MariaDB est à peu près équivalente à celle de MySQL.

En résumé, MariaDB est intéressant car il peut offrir des fonctionnalités que MySQL n’offre pas. Mais les incompatibilités avec MySQL peuvent rendre les migrations difficiles.

Pour plus d’informations, vous pouvez consulter la page du projet : http://www.mariadb.com

2. Percona Server

Percona a développé des correctifs pour ses clients depuis 2007, et petit à petit ces correctifs ont été rassemblés pour former une version dérivée de MySQL qui a été nommée Percona Server.

Percona Server est une version améliorée de MySQL dont l’un des objectifs est de rester...

Les moteurs de stockage

L’une des originalités de MySQL est le concept de moteurs de stockage. Chaque moteur doit offrir un socle commun de fonctionnalités, mais il est possible d’ajouter des fonctionnalités manquantes au serveur. Un exemple classique est celui des clés étrangères : bien que la syntaxe soit reconnue par le serveur, les clés étrangères ne déclenchent aucune action spécifique de la part du serveur. Néanmoins, le moteur InnoDB offre le support des clés étrangères, ce qui n’est pas le cas du moteur MyISAM par exemple.

Cette architecture a cependant des inconvénients. Les moteurs ne sont pas tous équivalents en termes de performances : si le moteur X possède une fonctionnalité qui n’est pas disponible avec votre moteur actuel, il n’est pas garanti que le moteur X soit aussi rapide. Les moteurs ne sont pas non plus tous équivalents en termes de sécurité des données : InnoDB, par exemple, a la capacité de récupérer automatiquement les données en cas d’arrêt inopiné, alors qu’un arrêt inopiné avec MyISAM sera sans doute synonyme de perte de données. Enfin, bien qu’il puisse être tentant de choisir un moteur de stockage pour chacune des tables, il n’est pas simple d’administrer un serveur pour lequel plusieurs moteurs de stockage sont utilisés : la mémoire physique du serveur doit être partagée entre les différents moteurs, les sauvegardes sont plus difficiles, diagnostiquer les problèmes est plus compliqué. Pour ces raisons, il est fortement recommandé de n’utiliser qu’un seul moteur de stockage. Sachez également qu’InnoDB est le seul moteur officiellement développé depuis de nombreuses années. MySQL 8.0 est d’ailleurs la première version pour laquelle toutes les tables utilisent par défaut InnoDB. Précédemment, MyISAM était toujours indispensable pour les tables système (tables de la base mysql, contenant par exemple les comptes utilisateurs).

Le choix du moteur se fait à la création de la table, dans l’ordre SQL CREATE TABLE en renseignant la clause ENGINE.

mysql> CREATE TABLE...

Verrous et transactions

Imaginez que vous ouvrez un fichier pour lire son contenu et qu’au même moment un autre utilisateur ouvre ce fichier pour le modifier. Vous risquez de lire un contenu incohérent, puisqu’une partie aura été modifiée en cours de route. Si vous voulez être certain de pouvoir lire le fichier sans que personne ne le modifie, vous devez le signaler aux autres utilisateurs.

Pour MySQL, la situation est identique : les accès concurrents sont autorisés, il est donc parfaitement possible qu’une requête lise un ensemble de lignes alors qu’une autre requête modifie certaines de ces lignes. Il est donc indispensable que chaque connexion MySQL indique aux autres connexions quelles sont les ressources qui ne doivent pas être modifiées. Cette notification se fait en posant des verrous.

Il existe deux sortes de verrous : les verrous en lecture, qui autorisent d’autres connexions à lire les mêmes données mais pas à les modifier, et les verrous en écriture, qui interdisent à toutes les autres connexions de lire ou d’écrire.

La différence entre ces deux types de verrous est simple à comprendre si vous repensez à l’exemple du fichier que vous voulez ouvrir. Si vous souhaitez simplement lire ce fichier, d’autres utilisateurs peuvent également ouvrir le fichier pour le lire. Mais personne n’a le droit de le modifier, sinon vous risquez de lire des données incohérentes. Et si vous souhaitez mettre à jour le contenu du fichier, vous ne voulez pas que d’autres utilisateurs puissent lire ou écrire dans celui-ci pour éviter que le contenu ne devienne incohérent et donc inexploitable.

Toujours en reprenant cette analogie avec le fichier ouvert en lecture ou écriture, si vous imaginez que le fichier a plusieurs sections, il est possible que vous permettiez à un autre utilisateur de modifier des sections que vous n’êtes pas en train de lire. Dans ce cas, ce que vous lirez sera toujours cohérent puisque personne n’en modifiera le contenu, et vous ferez attendre moins longtemps (voire pas du tout) les utilisateurs qui veulent faire des modifications dans le fichier.

Avec MySQL, le même principe existe : il est possible soit de verrouiller une table...