MySQL

Qu'est-ce qu'une base de données ?

Une base de données permet de stocker et de récupérer des données.

Ces données peuvent être extrêmement variées et l'organisation de celles-ci également.

Il peut s'agir de données très structurées (on parle alors de base de données relationnelles) ou au contraire très peu structurées (comme parfois dans le noSQL).

Les systèmes de gestion de base de données

Les systèmes de gestion de base de données (SGBD ou DBMS en anglais) sont des ensembles de programme qui permettent de principalement gérer l’organisation et l’accès aux données.

Ils permettent d’ajouter, de lire, de mettre à jour et de supprimer les données, c’est-à-dire de réaliser les opérations CRUD (Create, Read, Update, Delete).

Les autres tâches gérés par ces systèmes sont principalement :

• Le contrôle d’accès : authentification et autorisation des opérations de lecture, d’écriture et d’administration de la base de données.
• Vérification de la cohérence : vérification des opérations (contrôle d’unicité, règles d’intégrité référentielle etc).
• Assurer la durabilité : réplication (les mêmes données sont écrites sur plusieurs disques / serveurs) et les sauvegardes ponctuelles.
• Journalisation des opérations : chaque opération est enregistrée dans un fichier de log appelé journal qui permet d’annuler ou de terminer l’opération en cas d’erreur ou de crash.
• Indexation : les index sont maintenus à jour à chaque opération.

Il existe aujourd’hui environ 400 systèmes de gestion de base de données actifs. Cela fait donc un large choix !

Le SQL et le noSQL

Le SQL (Structured Query Language) est un langage permettant de gérer les bases de données relationnelles.

Ce type de base de données est apparu dans les années 1980 et 1990 avec principalement Oracle (1980), IBM Db2 (1983), Microsoft SQL Server (1989), PostgreSQL (1989) et MySQL (1995).

Il est standardisé depuis 1986 et le dernier standard est SQL:2023.

Le NoSQL (signifiant non-SQL, ou non relationnel ou encore not only SQL suivant les sources) est un terme désignant les bases de données qui ne sont pas relationnelles, c’est-à-dire qu’elles n’utilise pas uniquement des tables relationnelles.

Elles sont apparues dans les années 2000 avec l’explosion du volume de données à traiter (pensez Facebook, Google, Youtube, Amazon etc). Nous sommes alors passés de base de données de quelques téraoctets à des bases de données faisant plusieurs pétaoctets (milliers de téraoctets).

MySQL

MySQL a été développé par la société suédoise MySQL AB, achetée par Sun Microsystems, qui est maintenant une partie d'Oracle Corporation.

MySQL est considéré comme la base de données open source la plus populaire au monde, se positionnant juste derrière Oracle Database selon DB-Engines.

Il est au cœur de nombreuses applications très sollicitées telles que Facebook, X (Twitter), Netflix, Uber, Airbnb, Shopify, Ebay et Booking.com, grâce à sa capacité à alimenter efficacement les applications les plus accessibles (source).

Étant open source, MySQL bénéficie d'un développement collaboratif avec sa communauté depuis plus de 25 ans, garantissant un large support pour vos applications ou langages de programmation préférés.

Son logo représente un dauphin nommé Sakila, choisi lors d'un concours. La base de données est codée en C et C++.

MySQL se distingue comme un système de gestion de base de données relationnelles, organisant les données en tables séparées pour optimiser la vitesse et la flexibilité. Il utilise le langage SQL, standardisé pour l'accès aux bases de données, permettant une intégration aisée dans divers environnements de programmation.

Les principaux SGBD relationnels

Oracle : SGBD relationnel et multi-modèles, très puissant et largement utilisé dans les grandes entreprises pour des applications critiques. Il offre une grande variété de fonctionnalités pour la gestion des données, la réplication, et la haute disponibilité. Cependant, Oracle peut être complexe à apprendre pour un débutant et coûteux (voir très coûteux) à implémenter.

MySQL : également un SGBD relationnel et multi-modèles, MySQL est connu pour sa simplicité et sa facilité d'utilisation. Il est largement utilisé dans les applications Web et est une bonne option pour les débutants qui souhaitent apprendre les bases du SQL. MySQL offre un excellent équilibre entre fonctionnalités et simplicité.

Microsoft SQL Server : ce SGBD relationnel et multi-modèles est fortement intégré avec les produits Microsoft, ce qui le rend idéal pour les environnements Windows. Il propose une large gamme de fonctionnalités pour le développement d'applications d'entreprise, la business intelligence, et l'analyse de données. SQL Server peut être plus accessible pour ceux qui sont déjà familiers avec l'écosystème Microsoft Server (Azure etc).

PostgreSQL : PostgreSQL est un SGBD relationnel et multi-modèles qui est très respecté pour sa conformité aux standards SQL, sa robustesse, et son support de fonctionnalités avancées comme les types de données géospatiales.

IBM Db2 : Db2 est un SGBD relationnel et multi-modèles conçu pour les charges de travail d'entreprise, offrant des performances élevées et une sécurité robuste. Il peut être plus complexe et moins commun pour débuter, mais il est puissant pour des applications spécifiques.

Snowflake : Snowflake est un SGBD relationnel conçu pour le cloud, offrant une séparation entre le stockage et le calcul, ce qui permet une scalabilité et une flexibilité élevées. Il est relativement nouveau mais gagne rapidement en popularité pour les applications de données dans le cloud.

SQLite : SQLite est un SGBD relationnel léger qui est intégré dans une large variété d'applications logicielles. Sa simplicité et son autonomie le rendent idéal pour des projets de petite taille, des applications mobiles, ou comme base pour apprendre les principes de base du SQL.

Organisation des données avec MySQL

Dans MySQL, comme dans beaucoup de systèmes de gestion de bases de données relationnelles (SGBDR), les données sont organisées et structurées de manière hiérarchique.

Cette structure permet de stocker les données de manière efficace, de faciliter leur interrogation et leur manipulation.

Schéma MySQL

Un schéma MySQL, souvent appelé aussi base de données, est le niveau le plus élevé de l'organisation dans MySQL.

Il représente une collection de tables qui contiennent des données, ainsi que des relations entre ces tables, des vues, des procédures stockées, et d'autres éléments de base de données.

Dans la pratique, le terme "base de données" est plus fréquemment utilisé, mais le concept reste le même.

Un serveur MySQL peut contenir plusieurs bases de données (ou schémas), chacune étant isolée des autres, ce qui permet de gérer différents projets ou applications sur le même serveur.

Table

Une table est une collection de données organisées en colonnes et en rangées, suivant un modèle relationnel.

Chaque table dans MySQL est associée à un schéma spécifique et contient des informations sur un type d'entité spécifique.

Par exemple, une table utilisateurs pourrait contenir des informations sur les utilisateurs d'une application, avec des colonnes pour l'identifiant de l'utilisateur, son nom, son adresse e-mail, etc.

Colonnes

Les colonnes (ou champs) d'une table définissent le type de données que la table peut stocker.

Chaque colonne est définie avec un type de données spécifique, comme INT pour les nombres entiers, VARCHAR pour les chaînes de caractères, DATE pour les dates, etc.

Les colonnes définissent également d'autres propriétés, telles que si une valeur est obligatoire (NOT NULL), si elle est unique (UNIQUE), ou si elle s'auto-incrémente (AUTO_INCREMENT).

La définition des colonnes détermine la structure de la table et comment les données peuvent être stockées et interrogées.

Rangées

Les rangées (ou lignes) d'une table représentent les enregistrements ou les instances de l'entité définie par la table.

Chaque rangée contient des valeurs spécifiques pour chaque colonne de la table.

Par exemple, dans une table utilisateurs, chaque rangée représenterait un utilisateur spécifique, avec des valeurs dans les colonnes pour son identifiant, son nom, son adresse e-mail, etc.

Les rangées sont le niveau le plus bas de données dans la base de données, et c'est là que les données réelles sont stockées.

Résumé

• Schéma (base de données) : le conteneur de niveau supérieur qui contient des tables et d'autres objets de base de données, servant d'espace de travail organisé pour stocker les données.
• Table : une structure qui organise les données en colonnes et rangées, représentant un type d'entité spécifique dans la base de données.
• Colonnes : les attributs ou propriétés d'une table, définissant le type et les caractéristiques des données qui peuvent être stockées dans chaque colonne.
• Rangées (lignes) : les enregistrements individuels dans une table, chaque rangée contenant des valeurs pour chaque colonne associée à un enregistrement spécifique.

Les composants principaux

Nous allons lister les composants principaux car vous verrez très fréquemment ces termes et c'est important d'avoir une vue d'ensemble avant de démarrer.

• Serveur de base de données (MySQL Server) : le serveur de base de données est le cœur du système et gère l'accès aux données, les transactions, la concurrence, le cache (buffer pool), etc.
• Le moteur de stockage (InnoDB ou MyISAM) gère la façon dont les données sont stockées, récupérées et mises à jour.
• Client MySQL : les clients MySQL sont des applications qui se connectent au serveur de base de données pour effectuer des opérations sur les données. Ils peuvent être des applications graphiques (GUI), des interfaces en ligne de commande (CLI), des applications web ou des services backend :
• Interface Graphique Utilisateur (GUI) : les interfaces graphiques, comme MySQL Workbench, fournissent une interface utilisateur visuelle pour interagir avec le serveur MySQL, permettant aux utilisateurs de créer des requêtes, de gérer des bases de données et de visualiser des données sans utiliser directement le SQL.
• Interface en Ligne de Commande (CLI) : l'interface en ligne de commande est un client texte qui permet aux utilisateurs de se connecter au serveur MySQL et d'exécuter des commandes SQL directement.
• Pilotes (drivers) : les pilotes sont des bibliothèques logicielles qui permettent aux applications de se connecter et d'interagir avec le serveur de base de données en utilisant divers langages de programmation comme PHP, Java, Python, JavaScript etc.
• Object-Relational Mapping (ORM) : les ORM sont des bibliothèques qui permettent de manipuler les données comme des objets dans le code d'une application, tout en gérant la communication avec la base de données en arrière-plan. Ils fournissent une abstraction pour éviter d'écrire du SQL brut et pour faciliter l'intégration des données dans l'application.

Moteur de stockage : InnoDB

Par défaut, MySQL utilise le moteur InnoDB.

Nous allons voir ses caractéristiques principales.

InnoDB est un moteur transactionnel, ce qui signifie qu'il garantit l'ACID (Atomicité, Cohérence, Isolation, Durabilité). Chaque transaction est traitée de manière à garantir ces principes, avec un système de commit et de rollback bien établi. Nous reverrons ces notions dans un chapitre plus avancé.

Il utilise le contrôle de concurrence multiversion (MVCC) pour gérer l'accès concurrent aux données. Cela permet à plusieurs transactions de lire et écrire dans la base de données sans se bloquer mutuellement, en gardant des versions multiples des données pour assurer l'isolation des transactions.

Il maintient en mémoire un cache appelé le buffer pool, où il stocke les données et index fréquemment utilisés. Cela améliore les performances en réduisant le besoin d'accès aux données sur le disque.

Il utilise un système de journalisation pour garantir la durabilité des transactions. Les modifications apportées aux données sont d'abord écrites dans le journal de transactions. En cas de crash du système, InnoDB peut se remettre d'un état cohérent en rejouant le journal.

InnoDB a également un mécanisme robuste de récupération après crash, utilisant son journal de transactions et son système de rollback segment pour restaurer la base de données dans un état cohérent.

Aller plus loin : fichiers utilisés par InnoDB

Vous pouvez passer rapidement, nous n'irons pas du tout voir ces fichiers dans la formation, c'est très avancé mais permet simplement d'avoir une meilleure compréhension.

S'il vous prend l'envie d'aller voir les fichiers créés par le moteur de stockage, vous retrouverez principalement :

ibdata1 [alternatif] : ce fichier est le fichier de données système InnoDB global. Il contient les données et les index des tables InnoDB lorsque le paramètre innodb_file_per_table est désactivé. Il stocke également les données internes telles que les informations de tablespace, les undo logs et les données de buffer pool. Pas par défaut.

.ibd : les fichiers avec l'extension .ibd sont des fichiers de tablespace individuels pour chaque table InnoDB lorsque innodb_file_per_table est activé. Chaque fichier .ibd contient les données et les index associés à une table spécifique.

ib_buffer_pool : ce fichier sauvegarde le statut actuel du buffer pool InnoDB pour permettre une restauration rapide après un redémarrage du serveur.

ib_logfile0 et ib_logfile1 : ce sont des fichiers de journalisation (log files) qui enregistrent toutes les modifications apportées aux données pour permettre la récupération après un crash. InnoDB utilise une écriture de journal circulaire.

ibtmp1 : fichier temporaire utilisé par InnoDB pour les tables temporaires et les opérations de tri.

.cfg : fichiers de configuration pour les fichiers .ibd qui stockent des métadonnées pour des fonctions spécifiques comme le transport de tablespaces entre différentes instances de serveur.

mysql.ibd : depuis MySQL 8.0, les métadonnées des schémas, des tables, des index, etc., sont stockées dans le dictionnaire de données, qui est en lui-même stocké dans le tablespace mysql.ibd.

undo tablespaces : fichiers tels que undo_001 et undo_002 qui stockent les informations d'annulation pour les transactions InnoDB. Ces tablespaces peuvent être configurés pour s'agrandir et se réduire automatiquement.

binary logs (binlog) : fichiers binaires qui enregistrent les modifications apportées à la base de données pour la réplication et la récupération après sinistre.

redologs : fichiers qui enregistrent les modifications non encore appliquées à la base de données, permettant la récupération des données en cas de panne soudaine.

general log et slow query log : journaux enregistrant respectivement toutes les requêtes et les requêtes lentes exécutées par le serveur.

auto.cnf : fichier de configuration contenant l'identifiant unique du serveur pour la réplication.

Environnements pour un serveur MySQL

Installation locale

C'est ce que nous verrons dans les prochaines leçons.

Nous installerons le serveur MySQL sur notre système d'exploitation local pour apprendre à utiliser MySQL.

MySQL as a Service

MySQL as a Service désigne une offre de MySQL hébergé et géré par un fournisseur de services cloud.

Dans ce modèle, le fournisseur s'occupe de la maintenance, des mises à jour, de la sauvegarde, et de la sécurité de la base de données, permettant aux développeurs de se concentrer sur le développement d'applications sans se soucier de la gestion de la base de données. Il en existe plusieurs dizaines mais nous mentionnerons les principaux.

• Avantages : Facilité de déploiement, haute disponibilité, scalabilité, et réduction des coûts opérationnels.
• Exemples : OVHcloud, Oracle MySQL HeatWave, Microsoft Azure MySQL, Google Cloud MySQL, Amazon RDS pour MySQL.

Utilisation de l'image Docker

Docker est une plateforme de conteneurisation permettant de packager une application et ses dépendances dans un conteneur virtuel qui peut être exécuté sur n'importe quel système.

Image Docker MySQL : c'est une image officielle préconfigurée contenant tout le nécessaire pour exécuter un serveur MySQL.

Les images Docker facilitent le déploiement de MySQL en permettant de lancer une instance de base de données MySQL dans un conteneur Docker avec peu ou pas de configuration.

Les outils en ligne de commande (CLI)

Les interfaces en ligne de commande (CLI) offrent une manière directe et puissante d'interagir avec les systèmes de gestion de bases de données comme MySQL.

Les principaux outils CLI utilisés avec MySQL sont les suivants :

Le CLI officiel de MySQL

L'interface en ligne de commande permet aux utilisateurs d'interagir avec MySQL en saisissant des commandes textuelles.

Cela inclut l'exécution de requêtes SQL, la gestion des bases de données et des utilisateurs, ainsi que la modification des configurations du serveur MySQL.

Le principal outil CLI fourni par MySQL est le client mysql, qui permet aux utilisateurs de se connecter à un serveur MySQL et d'interagir directement avec les bases de données à partir du terminal.

MySQL Shell

MySQL Shell est aussi officiellement maintenu par Oracle. C'est un CLI plus récent et plus avancé.

C'est un outil avancé pour l'usage interactif et l'administration de la base de données MySQL.

Il supporte trois modes de programmation : JavaScript, Python et SQL, offrant ainsi une flexibilité remarquable aux administrateurs et développeurs pour gérer MySQL.

MySQL Shell peut être utilisé pour une gamme étendue de tâches administratives et d'accès, y compris la configuration de la réplication, l'exécution de requêtes, et l'administration des schémas de bases de données.

Sa capacité à exécuter des scripts dans différents langages de programmation en fait un outil particulièrement puissant pour automatiser les tâches et interagir avec MySQL de manière plus intuitive que les interfaces en ligne de commande traditionnelles.

Percona Toolkit

Percona Toolkit est une collection d'outils en ligne de commande développés en Perl, conçus pour les bases de données MySQL.

Ces outils offrent des fonctionnalités avancées pour l'administration et la maintenance de MySQL, incluant la vérification de la réplication, la réparation de données corrompues, l'automatisation des tâches répétitives, et l'amélioration des performances du serveur.

Le Percona Toolkit est disponible dans plusieurs distributions Linux, comme CentOS et Debian, avec des paquets également disponibles pour Fedora et Ubuntu.

Interfaces graphiques (GUI)

Les GUI pour MySQL permettent aux développeurs et aux administrateurs de bases de données d'interagir avec MySQL de manière visuelle, sans avoir à utiliser la ligne de commande.

MySQL Workbench

C'est l'interface graphique officielle développée par Oracle pour MySQL.

Elle permet la conception de bases de données, l'exécution de requêtes SQL, la gestion des utilisateurs, la visualisation des performances, et plus encore.

C'est ce que nous utiliserons dans la formation.

Avantages principaux de Workbench

• Intégration étroite avec MySQL :
• Développé par Oracle : en tant que produit développé par le mainteneur de MySQL, MySQL Workbench bénéficie de mises à jour synchronisées avec les dernières versions de MySQL, assurant une compatibilité sans faille.
• Accès aux dernières fonctionnalités : les utilisateurs ont accès immédiat aux dernières fonctionnalités et optimisations de MySQL, ce qui permet de tirer pleinement parti des innovations du système de gestion de base de données.
• Conception visuelle de bases de données :
• Interface graphique intuitive : l'interface permet de modéliser visuellement les schémas de bases de données, rendant la conception accessible même aux personnes moins familiarisées avec le SQL.
• Outils de modélisation avancés : les utilisateurs peuvent non seulement créer et modifier des schémas, mais également générer des scripts SQL directement à partir du modèle, simplifiant le processus de développement.
• Développement SQL et administration :
• Éditeur SQL complet : avec la coloration syntaxique, le complètement automatique et les outils de débogage, l'éditeur SQL facilite l'écriture et l'optimisation des requêtes.
• Outils d'administration intégrés : la configuration des serveurs, la gestion des utilisateurs, et la surveillance des performances sont intégrées dans une interface unique, centralisant la gestion de MySQL.
• Gestion de la performance
• Surveillance en temps réel : les tableaux de bord offrent une vue en temps réel de la performance du serveur, aidant à identifier et à résoudre rapidement les problèmes de performance.
• Optimisation des requêtes : l'outil propose des conseils et des analyses pour optimiser les requêtes SQL, améliorant ainsi l'efficacité des opérations de base de données.
• Migration de base de données
• Assistance à la migration : MySQL Workbench fournit un assistant de migration guidant les utilisateurs à travers le processus de migration des données d'autres SGBD vers MySQL, réduisant les obstacles techniques.
• Compatibilité étendue : prend en charge la migration depuis plusieurs sources, y compris Microsoft SQL Server, PostgreSQL et Oracle, facilitant le passage à MySQL.
• Sécurité
• Gestion des utilisateurs et des privilèges : permet une gestion fine des droits d'accès et des profils d'utilisateurs, renforçant la sécurité des données.
• Support SSL : supporte le chiffrement SSL pour les connexions, protégeant les données en transit entre le client et le serveur MySQL.

Autres outils GUI populaires

• Adminer : outil PHP pour la gestion de MySQL, offrant une interface web simple pour les opérations de base de données.
• DBeaver : IDE multiplateforme gratuit pour les développeurs et les administrateurs de base de données, supportant MySQL parmi d'autres.
• HeidiSQL : application Windows légère permettant la gestion, la maintenance, et l'exploration de bases de données MySQL.
• LibreOffice Base : composant de la suite LibreOffice pour la gestion de bases de données, compatible avec MySQL.
• Navicat : outil puissant de gestion de base de données offrant une interface intuitive pour la gestion de MySQL.
• phpMyAdmin : application web très populaire pour la gestion de MySQL, offrant une interface web complète.
• SQLyog : outil de gestion de MySQL Windows offrant une interface riche pour l'optimisation et la gestion des bases de données.
• Toad for MySQL : outil de développement et de gestion MySQL facilitant l'optimisation des bases de données pour les développeurs.

Apprenez en plus en suivant notre formation très complète !