Bases de données MySQL - Administrationbliaudet.free.fr/IMG/pdf/01-MySQL-Install-Config-Moteurs.pdf · 2019. 2. 9. · Exercice 31 3.3 : Les variables d ... variables d’état du

BASES DE DONNÉES – Administration - Configuration - page 1/60 - Bertrand LIAUDET

Bases de données MySQL - Administration

Installation – configuration – Moteurs Bertrand LIAUDET

SOMMAIRE

SOMMAIRE 1

0 - PROBLEMATIQUE D’ADMINISTRATION 50-1 : Introduction 5

Typologie des utilisateurs d’une BD : 5Les 7 compétences d’administration 5La maîtrise du serveur : le manuel de référence ! 5

0-2 - Les outils pour l’administration 7Remarque générale 7mysql 7MySQL Shell (nouveauté post 2015 environ) 7MySQLWorkbench 7PHP-MyAdmin 8MySQL Entreprise Edition 8MySQLCluster 8

0-3 - Télécharger et installer MySQL Community Edition 10Téléchargement 10Installation sous Windows 10Installation sous linux 10

1 - RESULTATS DE L’INSTALLATION 111.1 : Les 2 principaux répertoires : basedir et datadir 11

basedir 11datadir 12

1.2 : BD et utilisateurs par défaut 13Versions 5.5 et 5.6 13Versions 5.7 et 8.0 14

1.3 : Les programmes installés 164 programmes pour le démarrage 16La calculettes SQL 162 programmes d’administration 16


1 programme de sauvegarde 171 programme d’importation 174 programmes d’optimisation et de réparation (tous indépendants du serveur) 182 programmes utilitaires 18

2 - A FAIRE APRES INSTALLATION 192.1 : Démarrer le serveur 19

Remarque générale 19Démarrage sous Windows 19Démarrage sous Linux 20

2.2 : Arrêter le serveur – « Pinguer » le serveur 21Remarque générale 21Arrêter et pinguer le serveur sous Windows 21Arrêter et pinguer le serveur sous Linux 21

2.3 : Sécuriser les comptes MySQL initiaux 222.4 : Démarrer le serveur automatiquement 24

Sous Windows 24Sous Linux 24

3 - CONFIGURATION DU SERVEUR 263.1 : Les fichiers de configuration : my.cnf (linux) ou my.ini (Windows) 26

Présentation 26Où placer les fichiers de configuration ? 27Les fichiers types 27

3.2 : Les variables systèmes 28Présentation 28Variables globales et variables locales 28Consultation des variables système 28Modifications des variables système 29Quelques variables système du serveur 30Exemple : choix d’un moteur InnoDB par défaut 31Exercice 31

3.3 : Les variables d’état du serveur 32Consultation des valeurs des variables d’état 32Consultation de certaines valeurs des variables d’état 32Remise à zéro des variables d’état 32Quelques variables d’état du serveur 32

3.4 : Options mysqld en ligne de commande 34Présentation 34Quelques options de mysqld 34

3.5 : Les modes SQL du serveur : sql-mode 36Présentation 36Regroupement de modes : ANSI et STRICT_TRANS_TABLES 36


ONLY_FULL_GROUP_BY 37Standardisation de MySQL : exécution en mode ONLY_FULL_GROUP_BY 37Exercices 38

4 - MYSQLADMIN ET CALCULETTE MYSQL 394.1 : Mysqladmin 39

Présentation 39Syntaxe 39Commandes 39

4.2 : La calculette mysql 41Présentation 41Syntaxe 41Options 41Utiliser un fichier contenant des commandes, produire des résultats dans un fichier 42Exporter les données à partir de MySQL : SELECT … INTO OUTFILE 42Importer les données à partir de MySQL : LOAD DATA INFILE 43

5 - MYSQLDUMP - SAUVEGARDE 44C:\ mysql dump 44

6 - LA COMMANDE SHOW 46META-BASE 46DDL 46VARIABLES SYSTEME 46DROITS 46CARACTERES 46MOTEUR 46DEBOGAGE 47

7 – STOCKAGE ET MOTEURS DE STOCKAGE 487.1 : Consultation des données sur le disque 48

Principes 48Exercices 48

7.2 : Les différents moteurs de stockage 48Présentation 48Avantages et inconvénients 49

7.3 : Le moteur MyISAM 49Caractéristiques principales 49Type par défaut 49Quelques caractéristiques particulières des tables MyISAM 49Format de stockage des tables MyISAM 50

7.4 : Le moteur MERGE 52


7.5 : Le moteur MEMORY 527.6 : Les tables compressées : myisampack 527.7 : Le moteur ARCHIVE 537.8 : Le moteur InnoDB 53

Caractéristiques principales 53

8 - PROGRAMMATION DES TABLES : RAPPELS 54Gestion des bases de données 54Création des tables 54

Création des tables 54Les contraintes d’intégrité 55Paramétrage des contraintes d’intégrité référentielle 56Nommer les contraintes : CONSTRAINT nomContrainte 57

Modification des tables 57Modification des attributs 57Modification des contraintes d'intégrité 57Suppression de la clé primaire 58

Destruction des tables 58Récupération du code sous mysql : show create table 58

Exemple 1 : mysql> show create table dept; 58Exemple 2 : mysql> show create table emp; 59

Passage d’un moteur à un autre 59MyISAM et InnoDB 59

9 - TP 60EXERCICES d’ADMINISTATION MySQL 60

Prise en main en ligne de commande : 60 Edition janvier 2019


0 - PROBLEMATIQUE D’ADMINISTRATION

0-1 : Introduction

Typologie des utilisateurs d’une BD :

• L’utilisateur final • Le développeur • L’administrateur

Les 7 compétences d’administration 1) Installation, configuration et contrôle du serveur. Architecture du SGBD. 2) Politique de sécurité 1 : Gestion des utilisateurs. Les utilisateurs et les privilèges.

Dictionnaire des données. 3) Politique de sécurité 2 : Gestion des sauvegardes et des restaurations. Organisation des

fichiers. Outils adaptés. Gestion des journaux. 4) Amélioration des performances : Optimisation – Tuning (paramétrage, accordage). Index

et plan d’exécution (explain). Paramétrage du serveur. Choix d’un moteur de stockage (spécifique MySQL).

5) Migration et intégration des données : Importer des données. Exporter des données. 6) Réplication (duplication des données-BD) – Cluster (plusieurs serveurs-machines pour

une même BD-données) 7) Savoir se documenter ! Utiliser la documentation officielle en ligne.

La maîtrise du serveur : le manuel de référence ! Le manuel de référence offre de nombreuses ressources pour une bonne maîtrise du serveur. Il faut s’en servir ! http://bliaudet.free.fr/IMG/pdf/MySQL-refman-5.0-fr.pdf https://dev.mysql.com/doc/refman/5.5/en/ https://dev.mysql.com/doc/refman/5.5/en/server-administration.html https://dev.mysql.com/doc/refman/5.6/en/ https://dev.mysql.com/doc/refman/5.6/en/server-administration.html https://dev.mysql.com/doc/refman/5.7/en/ https://dev.mysql.com/doc/refman/5.7/en/server-administration.html https://dev.mysql.com/doc/refman/8.0/en/ https://dev.mysql.com/doc/refman/8.0/en/server-administration.html La page d’accueil présente le plan détaillé. On trouve aussi la liste des chapitres de niveau 1 sur la gauche.


Par exemple : MySQL Server Administration : ce chapitre contient les principaux éléments d’administration, dont le mysql system database (la BD mysql). On trouve aussi : • MySQL Data Dictionnary • MySQL Perfomance Schema • Replication • Optimization • The InnoDB Storage Engine • Backup and Recovery • Etc.


0-2 - Les outils pour l’administration

Remarque générale L’administration est une tâche de gestion d’un existant : la BD et ses utilisateurs. Les outils professionnels sont payants. Leur utilisation nécessite un contexte professionnel : une ou plusieurs BD déjà utilisées par des utilisateurs (des applications ou des développeurs ou plus rarement des utilisateurs finaux). On va présenter dans ce document les principales notions permettant d’administrer la BD en les mettant en œuvre dans des contextes très simplifiés et avec un simple client mysql.

mysql

Présentation mysql est le client de base fourni à l’installation du SGBD mysql. Il permet de se connecter à un serveur local ou distant. Il permet de passer toutes les commandes comprises par le SGBD. Il permet aussi de passer des commandes dans des scripts sans IHM comme par exemple le chargement d’une BD.

MySQL Shell (nouveauté post 2015 environ) https://dev.mysql.com/doc/refman/5.5/en/mysql-shell-userguide.html https://dev.mysql.com/doc/refman/8.0/en/mysql-shell-userguide.html

Présentation MySQL Shell is an advanced client and code editor for MySQL Server. In addition to the provided SQL functionality, similar to mysql, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL.

Site https://dev.mysql.com/downloads/shell/

MySQLWorkbench https://dev.mysql.com/doc/refman/5.5/en/workbench.html https://dev.mysql.com/doc/refman/8.0/en/workbench.html

Présentation MySQL Workbench provides a graphical tool for working with MySQL servers and databases. MySQL Workbench fully supports MySQL versions 5.5 and higher. MySQL Workbench fournit aux DBAs et aux développeurs un environnement de développement intégré pour :


• La conception de la BD • Le développement SQL (MySQL Workbench remplace MySQL Query Browser) • L’administration de la BD

Site http://www.mysql.fr/downloads/workbench/

PHP-MyAdmin

Présentation Les environnements intégrés PHP-MySQL (LAMP, WAMP, XAMPP, EasyPHP) offrent toujours un outil d’administration de la BD type « PHP-MyAdmin ».

MySQL Entreprise Edition https://dev.mysql.com/doc/refman/5.5/en/mysql-enterprise.html https://dev.mysql.com/doc/refman/8.0/en/mysql-enterprise.html

Présentation MySQL Entreprise Edition est la version « entreprise » payante de MySQL. Elle offre des outils d’administration plus performants et permet d’atteindre les niveaux de sécurité, de fiabilité et de disponibilité les plus élevés.

Site https://www.mysql.com/fr/products/enterprise/

Fiche produit en français http://www.mysql.fr/products/enterprise/mysql-datasheet.fr.pdf

Démo https://www.youtube.com/watch?v=A7dBB8_yNJI

MySQLCluster

Présentation MySQL Cluster Edition est la version « entreprise » payante de MySQL. Elle offre, en plus de MySQL Entreprise Edition, une meilleure disponibilité des données par du clustuting et de la réplication. Elle est utile pour des usages critiques de la BD. Comme MySQL Entreprise Edition, MySQL Cluster offre des outils d’administration performant avec des outils de clustering et de réplication.

Site https://www.mysql.com/fr/products/cluster/

Fiche produit en français

Ø http://www.mysql.fr/products/cluster/mysql-cluster-datasheet.fr.pdf


Démo https://www.youtube.com/watch?v=A7dBB8_yNJI


0-3 - Télécharger et installer MySQL Community Edition

Téléchargement

Site https://dev.mysql.com/downloads/

Version GA La version GA est la « Generally Available Releases En janvier 2019, c’est la MySQL Community Server 5.8.0.14 (c’était la .13 en décembre 2018).

Anciennes versions 5.5 5.6 5.7

Installation sous Windows Article de novembre 2018 : http://bliaudet.free.fr/article.php3?id_article=307

Installation sous linux Les versions varient selon les versions de Linux (Ubuntu, Debian, Suse, etc.).

Eléments d’installation pour une version Suse : fichier RMP On utilise un fichier RPM. Pour voir tous les fichiers présents dans un paquet RPM, lancez :

shell> rpm -qpl MySQL-VERSION.i386.rpm

Pour effectuer une installation standard minimale, lancez :

Shell> rpm –i MySQL-server-VERSION.i386.rpm Shell> rpm –i MySQL-client-VERSION.i386.rpm

Le fichier RPM fait l’installation et particulièrement : 1. Place les données dans /var/lib/mysql 2. Crée un compte de connexion pour un utilisateur nommé mysql (s’il n’existe pas déjà) afin

de l’utiliser pour exécuter le serveur MySQL. 3. Crée les entrées appropriées dans /etc/init.d/ (ou dans /etc/rc.d/init.d selon la version linux)

pour lancer le serveur automatiquement au démarrage. Remarque : si vous avez déjà effectué une installation auparavant, on a intérêt à faire une sauvegarde des fichiers de démarrage précédents.


1 - RESULTATS DE L’INSTALLATION

Résultats de l’installation : • 1.1 : Les 2 principaux répertoires : basedir et datadir • 1.2 : Les BD et les utilisateurs d’origine selon les versions • 1.3 : Les programmes installés

1.1 : Les 2 principaux répertoires : basedir et datadir

basedir

Présentation Le basedir est le répertoire d’installation du serveur (donc du programme serveur, mysql.exe sous Windows), des bibliothèques dont il a besoin et des outils qu’il propose.

Afficher la valeur de basedir dans un client mysql C’est une variable de niveau Serveur : on y accède par @@

Select @@basedir; //

Ou Show variables like ‘%basedir%’; //

On peut aussi filtrer seulement sur dir, et on obtient toutes les variables concernant les « dir » Show variables like ‘%dir%’; //

Sous Windows

Ø Valeur du basedir sous Windows C:\mysql\mysql-5.5.62-winx64 C:\mysql\mysql-5.6.42-winx64 C:\mysql\mysql-5.7.24-winx64 C:\mysql\mysql-8.0.13-winx64

Ø Contenu du basedir sous Windows

Sous Linux

Ø Valeur du basedir sous Linux /usr : contient les programmes.

Ø Contenu sous Linux /usr/sbin : mysqld, mysqlmanager /usr/bin : tous les autres programmes /usr/scripts : contient éventuellement les scripts (mysqld_safe, etc.)


datadir

Présentation Le datadir est le répertoire qui contient les données. Par défaut il est installé dans le basedir. Mais on pourra ensuite le mettre ailleurs.

Afficher la valeur de datadir dans un client mysql C’est une variable de niveau Serveur : on y accède par @@

Select @@datadir; //

Ou Show variables like ‘%datadir%’; //

On peut aussi filtrer seulement sur dir, et on obtient toutes les variables concernant les « dir » Show variables like ‘%dir%’; //

Sous Windows

Ø Valeur du datadir sous Windows C:\mysql\mysql-5.5.62-winx64\data C:\mysql\mysql-5.6.42-winx64\data C:\mysql\mysql-5.7.24-winx64\data C:\mysql\mysql-8.0.13-winx64\data

Sous Linux

Ø Valeur du datadir sous Linux /var/lib/mysql : contient les bases de données.

Contenu technique du datadir L’organisation des fichiers et des répertoires dans le datadir est propre à MySQL. On aborde cette question dans un autre chapitre.


1.2 : BD et utilisateurs par défaut L’installation du serveur crée des BD et des utilisateurs par défaut. La situation est différentes selon les versions installées.

Versions 5.5 et 5.6

Les BD Affichage des BD installées par défaut

show databases ;

Ø la BD « mysql » Elle contient une première version du dictionnaire des données, particulièrement les tables d’utilisateurs et de droits. Les tables de la BD mysql sont de type MyISAM. https://dev.mysql.com/doc/refman/5.5/en/system-database.html https://dev.mysql.com/doc/refman/5.6/en/system-database.html

Ø la BD « information schéma » Elle contient le dictionnaire des données. Les tables de la BD info sont de type MyISAM. https://dev.mysql.com/doc/refman/5.5/en/information-schema.html https://dev.mysql.com/doc/refman/5.6/en/information-schema.html

Ø la BD « performance schéma » Elle contient des informations sur l’exécution du serveur. Les tables de la BD perf. sont de type « performance_schema ». https://dev.mysql.com/doc/refman/5.5/en/performance-schema.html https://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

Ø la BD « test » Elle ne contient aucune table. Elle est là pour faire des tests.

Les utilisateurs Affichage des utilisateurs installés par défaut

select user, host, password from mysql.user ;

Par défaut, il n’y a aucun password pour aucun utilisateur.

Ø 3 utilisateurs root : Ils sont équivalents. Ils ne peuvent se connecter que de la machine serveur. • [email protected] • root@::1 • root@localhost

Ø 1 utilisateur anonyme: Il peut se connecter à partir de la machine serveur, sans nom et sans mot de passe. • ‘’@localhost


Versions 5.7 et 8.0

Les BD Affichage des BD installées par défaut

show databases ;

Ø la BD « mysql » Elle contient une première version du dictionnaire des données, particulièrement les tables d’utilisateurs et de droits. Les tables de la BD mysql sont de type MyISAM. https://dev.mysql.com/doc/refman/5.7/en/system-database.html https://dev.mysql.com/doc/refman/8.0/en/system-database.html

Ø la BD « information schéma » Elle contient le dictionnaire des données. Les tables de la BD info sont de type ENGINE ou des vues. https://dev.mysql.com/doc/refman/5.7/en/information-schema.html https://dev.mysql.com/doc/refman/8.0/en/information-schema.html

Ø la BD « performance schéma » Elle contient des informations sur l’exécution du serveur. Les tables de la BD perf. sont de type «PERFORMANCE_SCHEMA». https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html

Ø la BD « sys» Elle contient des vues qui facilitent l’utilisation de performance schéma. Les tables de la BD sys sont des vues. https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html

Les utilisateurs

Ø Affichage des utilisateurs installés par défaut select user, host from mysql.user ;

Par défaut, il n’y a aucun password pour aucun utilisateur.

Ø 3 ou 4 utilisateurs système: Ils ne peuvent se connecter que de la machine serveur. • mysql.infoschema@localhost : que sur la version 8.0 • mysql.session@localhost • mysql.sys@localhost • root@localhost

Ø Remarques • Il n’y a plus de password visible dans la table « mysql.user » : c’est un choix de sécurité. • Il n’y a plus d’utilisateur anonyme : c’est choix de sécurité. • Il n’y a qu’un seul utilisateur root.


• Les 3 utilisateurs mysql.infoschema, mysql.session et mysql.sys sont des utilisateurs pour le système. On ne peut pas s’y connecter. Ils servent à manipuler les tables dictionnaire.

• Si ces utilisateurs viennent à manquer, on peut les récréer :

+----------------------------------------------------------------------+ | Grants for mysql.sys@localhost | +---------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'mysql.sys'@'localhost' | | GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost' | | GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost' |

+-----------------------------------------------------------------------+ | Grants for mysql.session@localhost | +-----------------------------------------------------------------------+ | GRANT SUPER ON *.* TO 'mysql.session'@'localhost' | | GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost' | | GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost' | +-----------------------------------------------------------------------+

GRANT supplémentaire pour la version 8.0 GRANT PERSIST_RO_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `mysql.session`@`localhost`

+-------------------------------------------------------+ | Grants for mysql.infoschema@localhost | +-------------------------------------------------------+ | GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost` | +-------------------------------------------------------+


1.3 : Les programmes installés https://dev.mysql.com/doc/refman/8.0/en/programs-client.html Chaque programme MySQL accepte différentes options. Mais chaque programme dispose de l'option --help qui vous donne une description complète des options du programme.

4 programmes pour le démarrage https://dev.mysql.com/doc/refman/8.0/en/programs-server.html

mysqld Le démon SQL (c’est-à-dire, le serveur MySQL). Pour utiliser les programmes clients, ce programme doit fonctionner, car les clients viendront se connecter dessus. See Section 5.2, « Configuration de MySQL ».

mysqld_safe Un script de démarrage du serveur. mysqld_safe tente de démarrer mysqld-max s'il existe, et sinon mysqld. See Section 5.1.3, « safe_mysqld, le script père de mysqld ».

mysql.server Un script de démarrage du serveur. Ce script est utilisé sur les systèmes qui ont un dossier contenant des services système. Il invoque mysqld_safe pour démarrer le serveur.

mysqld_multi Un script de démarrage qui peut lancer ou arrêter différentes instances du serveur, installées sur le système.

La calculettes SQL https://dev.mysql.com/doc/refman/8.0/en/mysql.html

mysql Le client en ligne de commande, pour envoyer des requêtes à MySQL, interactivement, ou en batch.

2 programmes d’administration

mysqladmin https://dev.mysql.com/doc/refman/8.0/en/mysqladmin.html Un utilitaire pour réaliser des opérations d'administration de la base, telles que les créations de bases, le rafraîchissement des tables de droits, l'écriture des tables sur le disque et la réouverture des fichiers de log. à mysqladmin permet aussi de lire la version, les processus et les informations de statut du serveur. See Section 8.4, « mysqladmin, administration d'un serveur MySQL ». mysqladmin permet de faire ce qu’on fait avec mysql mais en passant les commandes en lignes (on peut donc écrire des scripts). Ex : suppression de la BD test : mysqladmin –u root –p password drop test


mysqlshow https://dev.mysql.com/doc/refman/8.0/en/mysqlshow.html mysqlshow permet de faire ce qu’on fait avec mysql et le show mais en passant les commandes en lignes (on peut donc écrire des scripts).

1 programme de sauvegarde https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

mysqldump Exporte une base de données MySQL dans un fichier sous la forme de requêtes SQL, ou de fichiers texte, avec la tabulation comme séparateur. See Section 8.8, « mysqldump, sauvegarde des structures de tables et les données ».

1 programme d’importation https://dev.mysql.com/doc/refman/8.0/en/mysqlimport.html

mysqlimport Importe les fichiers textes dans les tables, en utilisant la commande LOAD DATA INFILE. See Section 8.10, « mysqlimport, importer des données depuis des fichiers texte ».


4 programmes d’optimisation et de réparation (tous indépendants du serveur)

mysqlcheck Un client d'entretien de tables, qui vérifie, répare, analyse et optimise les tables. See Section 8.7, « Utiliser mysqlcheck pour l'entretien et la réparation ». Ce programme est indépendant du serveur mysqld.

myisamchk Un utilitaire pour décrire, vérifier, optimiser et réparer les tables MyISAM. Ce programme est indépendant du serveur mysqld.

myisampack Un utilitaire qui compresse les tables MyISAM pour produire des tables en lecture seule très compactes. See Section 8.2, « myisampack, le générateur de tables MySQL compressées en lecture seule ». Ce programme est indépendant du serveur mysqld.

mysqlbinlog Utilitaire de lecture des requêtes au format binaire. Peut être utilisé après un crash, sur une vieille sauvegarde. See Section 8.5, « mysqlbinlog, Exécuter des requêtes dans le log binaire ». Ce programme est indépendant du serveur mysqld.

2 programmes utilitaires

perror Un utilitaire qui affiche la signification des codes d'erreur MySQL. See Section 8.12, « perror, expliquer les codes d'erreurs ». Ce programme est indépendant du serveur mysqld.

replace replace modifie des chaînes dans des fichiers, ou sur l'entrée standard. See Section 8.13, « L'utilitaire de remplacement de chaînes replace ».


2 - A FAIRE APRES INSTALLATION

A faire après l’installation : • 1.1 : Démarrer le serveur. • 1.2 : Arrêter le serveur, « pinguer » le serveur. • 1.3 : Sécuriser les comptes MySQL initiaux. • 1.4 : Paramétrer le démarrage automatique du serveur.

2.1 : Démarrer le serveur

Remarque générale Il y a de nombreuses façons de démarrer le serveur. On peut le faire avec des outils ou directement en ligne de commandes. Dans le paragraphe 3 on verra comment faire en sorte que le serveur démarre automatiquement au démarrage de l’OS. Si on a installé MySQL avec un logiciel d’installation (un .MSI sous Windows), il est possible que tout soit déjà fait.

Démarrage sous Windows

Principes généraux

Ø Le serveur Pour démarrer le serveur, il faut passer des options avec « -- » derrière le nom du serveur. Pour démarrer le serveur en console, on écrit :

mysqld –-console

Si l’OS ne reconnaît pas le serveur (mysqld), c’est que le PATH n’a pas été paramétré. On peut le paramétrer en y ajoutant le chemin : basedir/bin. On peut aussi démarre le serveur en console en précisant le chemin. Par exemple :

C:\mysql\mysql-5.5.62-winx64\bin\mysqld –-console

A noter que le programme lancé ne « rend pas la main » : c’est un serveur. Il attend une commande d’un client. Il ne rend pas la main car il n’est pas démarré « en arrière plan ». Pour ça, il faudra utiliser les services Windows. A noter que le démarrage peut ne pas fonctionner. Dans ce cas, il faut lire les messages dans l’ordre. Certains messages disent des choses du genre : « Please use --explicit_defaults_for_timestamp ». Dans ce cas, il faut ajouter l’option derrière le – console. D’autres messages disent des choses du genre :

C:\mysql\mysql-5.7.24-winx64\bin\mysqld --initialize-insecure

Pour initiliser le répertoire des données. Remaque : pour connaître la version du serveur du fichier mysqld, on peut faire :

C:\mysql\mysql-5.5.62-winx64\bin\mysqld --version


Ø Un client Une fois le serveur démarré, on démarre un client :

mysql –uroot –p

ou C:\mysql\mysql-5.5.62-winx64\bin\mysql –uroot -p

Dans le client, on vérifie qu’on est connecté : Select version() ; // donne la version du serveur. Select @@basedir ; // affiche le basedir. Show databases ; // affiche les bases enregistrées dans le serveur.

Versions 5.5 et 5.6 mysqld --console

Version 5.7 et 8.0 mysqld --initialize-insecure mysqld --console

Démarrage sous Linux Attention : il faut travailler directement avec mysqld et / ou mysqld_safe, et non pas avec un « restart » ou un script du même genre.

shell>bin/mysqld_safe


2.2 : Arrêter le serveur – « Pinguer » le serveur

Remarque générale Une fois le serveur démarré, on peut l’arrêter ou vérifier qu’il tourne.

Arrêter et pinguer le serveur sous Windows

Vérifier que le serveur tourne : chercher mysqld.exe dans les tâches C:> tasklist /FI "IMAGENAME eq mysqld.exe"

Pinguer le serveur C:> mysqladmin ping –uroot -p

Autres communication avec le serveur via mysqladmin : C:> mysqladmin -- version C:> mysqladmin version status proc –uroot -p C:> mysqladmin variables

Arrêter le serveur C:> mysqladmin shutdown –uroot –p

Exercice Démarrez le serveur Pinguez le serveur Arrêter le serveur Pinguez le serveur Démarrer le serveur Démarrez 2 clients Affichez les processus en cours

Arrêter et pinguer le serveur sous Linux

Vérifier que le serveur tourne : chercher mysqld dans les processus shell>ps –ef | grep mysqld

Pour le reste, les commandes sont identiques sous Linux et sous Windows, ou le contraire !


2.3 : Sécuriser les comptes MySQL initiaux

L’utilisateur anonyme En version 5.5 et 5.6, il y a un utilisateur anonyme qui peut se connecter à partir de localhost. Quels sont les droits de cet utilisateur :

mysql> show grants for ''@localhost; +--------------------------------------+ | Grants for @localhost | +--------------------------------------+ | GRANT USAGE ON *.* TO ''@'localhost' | +--------------------------------------+

Il n’a que le droit de se connecter. A quelle BD peut-il accéder ?

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+

Il ne peut accéder qu’aux BD information_schema et test. La BD information_schema c’est le dictionnaire pour ses données et les données système. Il peut consulter le contenu de la BD mais pas le modifier. La BD test est par contre à sa disposition. Il peut créer des tables et des tuples à l’intérieur. Cet utilisateur est sécurisé : il ne peut pas faire grand chose et ne peut se connecter qu’à partir de localhost. Il sert à faire des tests. Si on veut le supprimer, on peut toujours faire un :

mysql> drop user ‘’@localhost;

Attribuer des mots de passe aux comptes root via mysqladmin Dans toutes les versions, le compte root n’a pas de mot de passe. On peut donner des mots de passe avec mysqladmin :

C:> mysqladmin password motDePasse –u root -p

motDePasse sans apostrophes. Si on veut retirer le password, on écrira :

C:> mysqladmin password –u root -p

Attribuer des mots de passe aux comptes root via un set password, versions 5.5 et 5.6 mysql> set password for 'root'@'localhost' = password('root');

Si on veut retirer le password, on écrira : mysql> set password for 'root'@'localhost' = password('');

A noter l’usage de la fonction “password” qui permet de crypter le password. Si on veut visualiser l’utilisateur avec son password, on écrira :

mysql> select user, host, password from mysql.user where user ='root';


+------+-----------+-------------------------------------------+ | user | host | password | +------+-----------+-------------------------------------------+ | root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | | root | 127.0.0.1 | | | root | ::1 | | +------+-----------+-------------------------------------------+

Ø En passant par la BD mysql mysql> update mysql.user set password = password('mdp') where

user='root'; mysql> flush privileges;

La fonction password permet de cripter le password. Flush privileges permet la prise en compte effective de la modification. Il faut éviter de faire ça !!! On évite de manipuler directement la BD mysql !!!

Attribuer des mots de passe aux comptes root via un set password, versions 5.7 et 8 Pour ajouter un password à l’utilisateur root on écrira :

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

La valeur du IDENTIFIED BY correspond au password. Il sera crypté automatiquement. Il n’y a plus d’attribut “password” dans la table mysql.user. On ne peut plus afficher le password. Si on veut retirer le password, on écrira :

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

Exercice Attribuez un mot de passe à root. Vérifiez en ouvrant un nouveau client. Remettez l’ancien mot de passe (rien). Vérifiez en ouvrant un nouveau client.


2.4 : Démarrer le serveur automatiquement

Sous Windows Il faut gérer un service Windows. En cherchant « services » on accède à la fenêtre de gestion des services. La commande :

sc create mysql5562 binpath= "c:\mysql\mysql-5.5.62 winx64\bin\mysqld.exe --console " type=own

permet de créer un service nommé : mysql5562 Ce service est visible dans la fenêtre de gestion des services. On peut faire passer son type de démarrage à automatique. Pour supprimer un service Windows :

sc delete mysql5562

Pour démarrer un service Windows en ligne de commande :

sc start mysql5562

A noter que la commande n’est pas envoyée en arrière plan.

Exercice Créer un service windows et contrôler le.

Sous Linux

Démarrage http://dev.mysql.com/doc/refman/5.0/fr/automatic-start.html On peut démarrer le serveur • en lançant mysqld directement • en lançant mysql_safe qui fait appel à mysqld : http://dev.mysql.com/doc/refman/5.0/fr/mysqld-safe.html • en lançant mysql_server qui fait appel à mysql-safe : http://dev.mysql.com/doc/refman/5.0/fr/mysql-server.html

shell>mysql.server start

Arrêt shell>bin/mysqladmin –u root shutdown

ou shell>mysql.server stop


Démarrage automatique Pour lancer et arrêter automatiquement MYSQL sur votre serveur, vous devez ajouter les commandes de lancement et d'arrêt dans les bons endroits de vos fichiers /etc/rc*. Avec les paquets Linux RPM (MySQL-server-VERSION.rpm), le script mysql.server est installé sous le nom /etc/init.d/mysql. Après installation du script, les commandes doivent être activées pour fonctionner au lancement du système, sur votre système d'exploitation. Sous Linux, vous pouvez utiliser chkconfig :

shell> chkconfig --add mysql

Sur certains systèmes Linux, les commandes suivantes sont aussi nécessaires pour activer totalement le script mysql :

shell> chkconfig --level 345 mysql on

Pour d’autres systèmes, consultez la documentation de votre système d’exploitation.


3 - CONFIGURATION DU SERVEUR

3.1 : Les fichiers de configuration : my.cnf (linux) ou my.ini (Windows) Dans le datadir, regardez les fichiers de configuration par défaut qui sont proposés. On trouve le lien suivant : http://dev.mysql.com/doc/mysql/en/option-files.html Ca ramène à la vesion 8, et on trouve l’équivalent dans les autres versions. https://dev.mysql.com/doc/refman/8.0/en/option-files.html https://dev.mysql.com/doc/refman/5.7/en/option-files.html https://dev.mysql.com/doc/refman/5.6/en/option-files.html https://dev.mysql.com/doc/refman/5.5/en/option-files.html

Présentation

Principes https://dev.mysql.com/doc/refman/5.5/en/glossary.html#glos_configuration_file https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_configuration_file https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_configuration_file https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_configuration_file Les fichiers my.cnf (Linux) ou my.ini (Windows) sont des fichiers de configuration appelés aussi « option file ». Ils permettent de définir des éléments de configuration du serveur ou des clients.

Syntaxe Ils sont au format ASCII Ils sont constitués de plusieurs sections. Chaque section commence par l’option : [section] Les sections correspondent au serveur ou à des clients : [mysqld], [mysql], [mysqladmin], etc. Les lignes de commentaires commencent par un #

Exemple # Example MySQL config file for small systems. [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock language = french


Où placer les fichiers de configuration ? On peut ajouter un fichier my.ini dans le BASEDIR et préciser dedans ce qu’on souhaite ajouter à la configuration par défaut.

Exemple et exercice : modification de la langue Modifier la langue du serveur. Le fichier peut dont par exemple ne faire que 2 lignes :

[mysqld] language = french

Vérifiez que le serveur a bien pris en compte la nouvelle langue.

Les fichiers types La version 5.5 de MySQL propose 4 fichiers types : my-small.ini, my-medium.ini, my-large.ini, my-huge.ini, my-innodb-heavy-4G.ini Les configuration « small » jusqu’à « huge » servent en fonction de la mémoire vive nécessaire pour faire tourner le serveur. “huge” est adaptée à un serveur entièrement dédié à MySQL. La configuration « innodb-heavy » est adapté à : 4GB RAM, InnoDB only, ACID, few connections, heavy queries.


3.2 : Les variables systèmes

Présentation Le serveur (mysqld) et les clients (mysql) conservent des variables système qui indiquent de quelle manière ils se trouvent configurés. Toutes les variables système ont des valeurs par défaut : elles peuvent valoir ‘’. Elles ne valent jamais NULL.

Variables globales et variables locales Il existe deux sortes de variables : • Les variables globales valent pour tous les clients du serveur : « global ». • Les variables locales valent uniquement pour un client donné : « local » ou « session ». Certaines variables ont une valeur locale et une valeur globale. Certaines variables n’ont qu’une valeur globale. A noter que la majorité des variables ont la même valeur en global et en local.

Consultation des variables système

Afficher toutes les variables de la session mysql> Show variables

C’est un équivalent à : mysql> Show session variables

C’est un équivalent à : mysql> Show local variables

Afficher toutes les variables du serveur mysql> Show global variables

C’est un équivalent à : shell>mysqladmin variables –uroot -p

Afficher certaines variables locales Pour repérer informations sur des répertoires :

mysql> Show variables like ‘%dir%’;

Pour connaître la taille des buffers : mysql> Show variables like ‘%buffer%’;

Pour les informations sur les tables : mysql> Show variables like ‘%table%’;

Afficher une variable locale mysql> Show variables like ‘sql_mode’;

C’est un équivalent à : mysql> Select @@sql_mode;

C’est un équivalent à :


mysql> Select @@session.sql_mode;

C’est un équivalent à : mysql> Select @@local.sql_mode;

Afficher une variable global mysql> Show global variables like ‘sql_mode’;

C’est un équivalent à : mysql> Select @@global.sql_mode;

Modifications des variables système La valeur d’une variable système peut être définie de trois façons : • au démarrage du serveur • en paramétrant le fichier de configuration • dynamiquement dans la calculette SQL avec la commande SET. Cette dernière possibilité

n’est pas possible pour toutes les variables système (par exemple, la langue ne peut pas être modifiée dynamiquement).

Au démarrage du serveur Option --nom_var=valeur au lancement de mysqld.

Ø Exemple shell> mysqld –-key_buffer_size=32M

Via la commande SET de la calculette SQL Passer localement en InnoDB par défaut :

mysql> set local default_storage_engine = “InnoDB”;

C’est un équivalent à : mysql> set @@default_storage_engine = “InnoDB”;

C’est un équivalent à : mysql> set @@local.default_storage_engine = “InnoDB”;

C’est un équivalent à : mysql> set @@session.default_storage_engine = “InnoDB”;

Passer globalement en InnoDB par défaut :

mysql> set global default_storage_engine = “InnoDB”;

Via les fichiers de configuration Mettre les messages en français :

[mysqld] language = French

Définition de valeur maximum On peut ajouter le préfixe « maximum-» devant le nom d’une variable pour définir la valeur maximum pour cette variable. Option --maximum-nom_var=valeur au lancement de mysqld.


Quelques variables système du serveur

basedir Chemin vers le répertoire d’installation.

datadir Chemin vers le répertoire des données.

language Langue utilisée.

log Il existe de nombreuses variables contenant le mot clé « log ». Certaines permettent de définir des répertoires pour des fichiers de log. Par exemple le fichier de log des requêtes lentes (slow_query_log), le fichier général de log.

max_connections Cette option permet de définir le nombre maximum de clients.

skip_show_database Cette option permet de limiter la visibilité des databases aux utilisateurs qui ont les droits.

sql-mode Permet de donner une valeur à sql-mode. Cf § suivant.

tmpdir Chemin vers le répertoire des fichiers temporaires.

default_storage-engine Type de table (c’est-à-dire moteur de stockage) par défaut. Equivalent à table-type.

version Affiche les informations sur la version


Exemple : choix d’un moteur InnoDB par défaut Les installations standard de MySQL paramètrent le moteur InnoDB par défaut. Toutefois, dans les installations WAMP, c’est le moteur MyISAM qui est paramétré par défaut. Comment passer à un moteur InnoDB par défaut ?

En paramétrant le fichier de configuration [mysqld] default_storage_engine=InnoDB

Si le fichier my.ini n’existe pas, on peut le créer avec ces seules instructions. Puis on place le fichier dans le datadir. Cette solution est facile à mettre en œuvre avec WAMP.

On démarrant le serveur avec une option On peut aussi lancer le serveur avec une option :

shell> mysqld -–default_storage_engine =InnoDB

Cette solution est compliquée à mettre en œuvre avec WAMP.

En paramétrant le client ou le serveur pendant qu’ils fonctionnent La plupart des paramètres de mysqld (options de démarrage) peuvent être définis sans arrêter le serveur en modifiant des variables du système.

Mysql> SELECT @@global.default_storage_engine; +---------------------------------+ | @@global.default_storage_engine | +---------------------------------+ | MyISAM | +---------------------------------+ 1 row in set (0.00 sec) Mysql> SET GLOBAL default_storage_engine = ‘InnoDB’ ; Mysql> SET LOCAL default_storage_engine = ‘InnoDB’ ; Mysql> SELECT @@global.default_storage_engine; +---------------------------------+ | @@global.default_storage_engine | +---------------------------------+ | InnoDB | +---------------------------------+ 1 row in set (0.01 sec)

Le GLOBAL affecte les opérations de tous les clients qui se connecteront. Mais ça n’affecte pas le client courant. Le LOCAL affecte le client courant.

Exercice Vérifiez votre quel est votre moteur par défaut. Créer une BD et une table sans préciser le moteur. Vérifiez qu’elle a été créée avec le moteur par défaut. Changer de moteur par défaut. Créez une deuxième table. Vérifiez qu’elle a été crée avec le nouveau moteur par défaut.


3.3 : Les variables d’état du serveur http://dev.mysql.com/doc/refman/5.0/fr/show-status.html http://dev.mysql.com/doc/refman/5.0/fr/server-status-variables.html Le serveur conserve de nombreuses variables d’état qui fournissent des informations concernant ses opérations.

Consultation des valeurs des variables d’état

mysql> Show status

C’est un équivalent à : shell> mysqladmin extended-status

Consultation de certaines valeurs des variables d’état

mysql> Show status like ‘threads%’;

Remise à zéro des variables d’état

mysql> flush status

Quelques variables d’état du serveur

Aborted_clients Nombre de connexions interrompus parce que le client était mort sans fermer correctement la connexion.

Flush_commands Nombre de flush exécutés

Handler_update Nombre de mises à jour de la BD

Innodb_rows_updated Nombre de mises à jour de la BD en InnoDB

Sort_rows Nombre de lignes triées.

Threads_connected Nombre de connexions en cours

Threads_created Nombre maximum de connexions qu’il y a eu.


Uptime Durée de fonctionnement du serveur, en secondes.

Uptime_since_flush_status Durée depuis le dernier flush statut.


3.4 : Options mysqld en ligne de commande

Présentation http://dev.mysql.com/doc/refman/5.0/fr/command-line-options.html mysqld est lancé automatiquement par certains scripts dont le « restart ». Toutefois, en tant qu’administrateur, il faut pouvoir contrôler l’usage direct de « mysqld ». On peut lancer mysqld, mysql_safe et tous les scripts appelant mysqld avec des options. Les options de lancement du serveur correspondent pour la plupart aux variables système du serveur. On liste ici quelques options importantes. Pour plus de détails, il faut se référer au manuel de référence. Les options se passent avec « -- » suivi du nom complet, ou un « - » suivi de l’initiale.

Quelques options de mysqld

- - ansi utilise la syntaxe standard ANSI au lieu de la syntaxe MySQL. Cette option intègre le « ONLY_FULL_GROUP_BY »

- - basedir=chemin, -b chemin Chemin vers le répertoire d’installation.

- - datadir=chemin, -h chemin Chemin vers le répertoire des données.

- - default-table-type=type Type de table (c’est-à-dire moteur de stockage) par défaut.

- - language=nom_langue, -L nom_langue

- - log[=fichier], -l chemin [fichier] Journalise les connexions et les requêtes dans le fichier spécifié. Par défaut : nom_hôte.log est le fichier de log. Il y a d’autres sorte de fichiers de log. Cf. documentation.

- - skip-grant-tables Permet de relancer le serveur sans gestion des droits des utilisateurs. Utile quand on a perdu le mot de passe de root !

- - skip-innodb Désactive le moteur de stockage innodb. Cette option économise de la mémoire et accélère certaines opérations. A utiliser uniquement si on n’a pas de tables innodb.

- - skip-show-database


Cette option permet de limiter la visibilité des databases aux utilisateurs qui ont les droits.

- - sql-mode=valeur[,valeur …] Permet de donner une valeur à sql-mode. Cf § suivant.

- - transaction-isolation=niveau Définit le niveau d’isolation des transactions, qui peut-être READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ ou SERIALIZABLE.

- - tmpdir=chemin, -t chemin Chemin vers le répertoire des fichiers temporaires.

- - user={nom_utilisateur | id_utilisateur }, -u {nom_utilisateur | id_utilisateur } Exécuter mysqld sous une identité précisé par un nom ou un id. Cette identité fait référence à un compte de connexion système (linux) et non pas à un utilisateur MySQL. Cette option est obligatoire quand mysqld est démarré sous l’identité root. Cf. le chapitre sur la sécurité.

- - version, -V Affiche les informations sur la version et quitte. Equivalent à mysqladmin - V


3.5 : Les modes SQL du serveur : sql-mode

Présentation

Les modes SQL du serveur : https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html MySQL inclut des extensions qu’on ne trouve pas dans d’autres bases de données SQL. Si elles sont utilisées, le code ne sera pas portable vers d’autres serveurs SQL. La variable « sql-mode » peut prendre plusieurs valeurs qu’on appelle des « modes » et qui paramètrent les caractéristiques du SQL du serveur et qui permettent de mieux gérer la compatibilité entre SGBD.

Les valeurs de sql_mode par défaut Version 5.5 : slq_mode = ‘’ Version 5.6 : sql_mode= ‘NO_ENGINE_SUBSTITUTION’ Version 5.7

mysql> select @@sql_mode; +-----------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------+ |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE, | |NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER, | |NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------+

Version 8 : mysql> select @@sql_mode; +-----------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------+ |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE, | |NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------+

Regroupement de modes : ANSI et STRICT_TRANS_TABLES https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html#sql-mode-important Mysql définit des mots clés regroupant plusieurs « modes ».

ANSI Equivalent à : REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY, ANSI Le mode ANSI contient un ONLY_FULL_GROUP_BY

STRICT_TRANS_TABLES Equivalent à un ensemble de mode élémentaires.


Pour gérer un environnement transactionnel de type InnoDB. Le mode STRICT_TRANS_TABLES ne contient pas de ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY Le mode ONLY_FULL_GROUP_BY n'autorise pas les requêtes dont la clause GROUP BY fait référence à une colonne qui n'est pas sélectionnée. C’est la situation standard du SQL (Toutefois ce mode est une nouveauté en MySQL 4.0.0.)

Sans ONLY FULL GROUP BY, on peut écrire : SELECT O.NO, L.editeur, count(*) FROM Livres L JOIN OEuvres O on O.NO=L.NO GROUP BY O.NO;

Ce qui ne veut rien dire.

Il aurait fallu écrire par exemple : SELECT O.NO, count(*) FROM Livres L JOIN OEuvres O on O.NO=L.NO GROUP BY O.NO;

A noter qu’on peut aussi écrire légitimement (avec un ONLY_FULL_GROUP_BY) : SELECT O.NO, O.auteur, count(*) FROM Livres L JOIN OEuvres O on O.NO=L.NO GROUP BY O.NO;

O.auteur n’est pas un attribut du GROUP BY mais le serveur se charge de savoir qu’il dépend fonctionnellement de O.NO : on peut donc le projeter sans risque d’incohérence.

Standardisation de MySQL : exécution en mode ONLY_FULL_GROUP_BY

En paramétrant le fichier de configuration [mysqld] sql_mode=ONLY_FULL_GROUP_BY

On démarrant le serveur avec une option shell> mysqld --ansi

C’est équivalent à : --transaction-isolation =SERIALIZABLE --sql-mode=REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES,

IGNORE_SPACE, ONLY_FULL_GROUP_BY

En paramétrant le client ou le serveur pendant qu’ils fonctionnent La plupart des paramètres de mysqld (options de démarrage) peuvent être définis sans arrêter le serveur en modifiant des variables du système.

Ø Solution 1 : Mysql> SELECT @@global.sql_mode ; +-------------------+ | @@global.sql_mode |


+-------------------+ | | +-------------------+ Mysql> SET GLOBAL sql_mode = ‘ONLY_FULL_GROUP_BY’ ; Mysql> SET LOCAL sql_mode = ‘ONLY_FULL_GROUP_BY’ ; mysql> SELECT @@global.sql_mode ; +--------------------+ | @@global.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+

Le GLOBAL affecte les opérations de tous les clients qui se connecteront. Mais ça n’affecte pas le client courant. Le LOCAL affecte le client courant.

Ø Solution 2 : On ajoute le ONLY_FULL_GROUP_BY à l’existant

set @@sql_mode = concat( @@sql_mode, ',ONLY_FULL_GROUP_BY'); set @@global.sql_mode = concat( @@global.sql_mode, ',ONLY_FULL_GROUP_BY');

A noter la présence d’une virgule comme séparateur et le fait que le ONLY_FULL_GROUP_BY se retrouve à la fin. A noter aussi que si l’ancien sql_mode contient déjà un ONLY_FULL_GROUP_BY, alors ça ne change rien.

Exercices Testez les requêtes proposées. Faîtes marcher la première requête. Faites-en sorte que la première requête ne fonctionne plus.


4 - MYSQLADMIN ET CALCULETTE MYSQL

4.1 : Mysqladmin https://dev.mysql.com/doc/refman/5.5/en/mysqladmin.html

Présentation mysqladmin est un utilitaire pour exécuter des commandes d'administration. Vous pouvez l'utiliser pour vérifier la configuration du serveur, créer et effacer des bases, etc.

Syntaxe La syntaxe de mysqladmin est :

shell> mysqladmin [OPTIONS] command [command-option] command ...

Commandes Le mysqladmin actuel supporte les commandes suivantes :

shutdown Eteint le serveur.

ping Vérifie si mysqld fonctionne ou pas.

version Affiche la version du serveur.

password new-password Spécifie un nouveau mot de passe. Modifie l'ancien mot de passe en new-password pour le compte que vous utilisez lors de la connexion avec mysqladmin.

create databasename Crée une nouvelle base.

drop databasename Efface une base et toutes ces tables.

variables Affiche les variable disponibles.

extended-status Affiche un message de statut du serveur très complet.

flush-hosts


Vide tous les hôtes mis en cache.

flush-logs Vide de la mémoire tous les logs.

flush-privileges Recharger les tables de droits (identique à la commande reload).

flush-status Remet à zéro les variables de statut.

flush-tables Vide de la mémoire toutes les tables.

flush-threads Vide les threads de cache. Nouveau en MySQL 3.23.16.

kill id, id,... Termine un thread MySQL.

processlist Affiche la liste des processus du serveur. Cela revient à la commande SHOW PROCESSLIST. Si --verbose est utilisé, le résultat est le même que SHOW FULL PROCESSLIST.

reload Recharge les tables de droits.

refresh Vide de la mémoire toutes les tables, puis ferme et réouvre les fichiers de logs.

slave-start Démarre l'esclave de réplication.

status Affiche le message de statut court du serveur.

slave-stop Eteint l'esclave de réplication.


4.2 : La calculette mysql https://dev.mysql.com/doc/refman/5.5/en/mysql.html

Présentation La calculette mysql est l’utilitaire qui permet d’exécuter toutes les commandes SQL.

Syntaxe La syntaxe de mysql est :

shell> mysql [dbname] [–option] [-option] [...]

dbname pour se connecter directement à une base de données (équivalent d’un USE).

Options

-u username ou --user=username spécifier le nom d’utilisateur

-p pwd ou --password=pwd spécifier le nom d’utilisateur

-h hote ou --host=hote spécifier le nom de l’hôte du serveur

-? ou --help afficher l’aide

-t résultats sous forme de tableau (défaut)

-H résultats sous forme de tableau HTML

-X résultats en XML

-E résultats sous forme verticale (liste : mode « \G » par défaut)

-B version « batch » : il n’y a de résultats que pour les « select ». Les résultats sont des tableaux avec tabulations entre les champs.

database mysql se connecte directement dans la database (fait un use database).


> toto.txt idem que le précédent, mais les résultats des « select » vont dans le fichier toto.txt qu’on pourra ouvrir avec EXCEL.

< toto.txt exécute le contenu du fichier dans myql

-e « requête » exécute une requête et quitte mysql

Utiliser un fichier contenant des commandes, produire des résultats dans un fichier

Dans l’OS: C :> mysql dbname < fichier.sql –u user –p

La commande exécute les commandes contenues dans le fichier puis quitte mysql. Mysql rentre directement dans la BD dbname. Les résultats s’affiche à l’écran On peut aussi renvoyer les résultats dans un fichier :

C :> mysql dbname < fichier.sql >resultat.txt –u user –p

A partir de mysql : mysql> source fichier.sql

ou bien mysql> \. fichier.sql

Exercice Chargez la BD BiblioInnoDB. Ecrivez un script qui fait un select des livres avec les œuvres. En ligne de commande, récupérer le résultat du scipt dans un fichier « résultat.txt ».

Exporter les données à partir de MySQL : SELECT … INTO OUTFILE mysql> SELECT * INTO OUTFILE ‘c:/dept.csv’ fields terminated by ‘,’ FROM dept;

Le fichier sera un .csv exploitable sous Excel, par exemple.

Chemin absolu mysql> SELECT * INTO OUTFILE ‘c:/dept.csv’ fields terminated by ‘,’ FROM dept;

Chemin relatif : DATADIR mysql> SELECT * INTO OUTFILE ‘./dept.csv’ fields terminated by ‘,’ FROM dept;


Chemin relatif : DATADIR / BDused mysql> SELECT * INTO OUTFILE ‘dept.csv’ fields terminated by ‘,’ FROM dept;

Exercice Chargez la BD BiblioInnoDB. Exporter un select des livres avec les œuvres dans un fichier csv appelé livres.csv. Ouvrez le avec Excel ou un équivalent.

Importer les données à partir de MySQL : LOAD DATA INFILE mysql> LOAD DATA INFILE ‘c:/dept.csv’ INTO TABLE dept fields terminated by ‘,’;

http://dev.mysql.com/doc/refman/5.0/fr/load-data.html On peut importer des données en précisant le format de séparation des champs. Par exemple: les fichiers “csv” produit par Excel utilisent la virgule comme séparateur. Attention : La table correspondant au fichier importé doit avoir le même schéma que celle dans laquelle les tuples sont insérés.

Chemin absolu mysql> LOAD DATA INFILE ‘c:/dept.csv’ INTO TABLE dept fields terminated by ‘,’;

Chemin relatif : DATADIR mysql> LOAD DATA INFILE ‘./dept.csv’ INTO TABLE dept fields terminated by ‘,’;

Chemin relatif : DATADIR / BDused mysql> LOAD DATA INFILE ‘dept.csv’ INTO TABLE dept fields terminated by ‘,’;

Chemin relatif : DATADIR / BD au choix mysql> LOAD DATA INFILE ‘./empdept/dept.csv’ INTO TABLE dept fields terminated by ‘,’;

Avec les options REPLACE ou IGNORE, quand une donnée rentre avec une clé primaire existant déjà, elle remplacera l’ancienne ou elle sera ignorée. Sinon, l’opération s’arrête en erreur.

LOAD DATA INFILE ‘nomFichier’ REPLACE INTO TABLE nomTableOUTFILE


5 - MYSQLDUMP - SAUVEGARDE

C:\ mysql dump https://dev.mysql.com/doc/refman/5.5/en/mysqldump.html https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html Mysqldump est une commande qui permet de produire le code SQL permettant de recréer entièrement la BD.

Trois usages de mysqldump

Ø Sauvegarder des BD C:\ mysqldump [options] --databases DB1 [DB2 DB3...]

Ø Sauvegarder toutes les BD C:\ mysqldump [options] --all-databases

Affichage à l’écran ou redirection dans un fichier

C:\ mysqldump –uroot –p nomBD

La commande affiche le code SQL de la BD nomBD.

C:\ mysqldump –uroot –p nomBD > nomFichier

La commande écrit le code SQL de la BD nomBD dans le fichier nomFichier.

Usage courant de MYSQLDUMP pour sauvegarder et recharger une BD Commande usuelle de sauvegarde d’une BD :

C:\ mysqldump --opt nomBD > backupFile.sql

L’option --opt inclut l’option --quick et l’option --lock-tables, entre autres. Pour recharger le fichier de sauvegarde :

C:\ mysql nomBD < backupFile.sql

Usage courant de MYSQLDUMP pour sauvegarder toutes les BDs Commande usuelle de sauvegarde de toutes les BD :

C:\ mysqldump --opt --all-databases > allBD.sql

Pour recharger le fichier de sauvegarde de toutes les BD :

C:\ mysql < allBDql

Usage spécial de MYSQLDUMP : ne sauvegarder que le schéma de la BD -- no-data

Exercice


Chargez la BD biblio MyISAM et faites un DUMP de la BD avec les données, puis un DUMP de la seule structure. Chargez la BD biblio InnoDB et faites un DUMP de toutes les BD.


6 - LA COMMANDE SHOW

META-BASE SHOW TABLE STATUS [FROM nomBD] Donne des informations sur toutes les tables d’une BD.

Exercice Chargez la BD biblio et faites un show table status dessus.

DDL SHOW DATABASES [LIKE …] SHOW CREATE DATABASE nomBD SHOW TABLES SHOW CREATE TABLE nomTable SHOW COLUMNS FROM nomTable ó DESC nomTable SHOW INDEX FROM nomTable

VARIABLES SYSTEME SHOW STATUS [ LIKE …] SHOW VARIABLES [LIKE …] SHOW [FULL] PROCESSLIST

https://dev.mysql.com/doc/refman/5.5/en/kill.html KILL CONNECTION id : pour arrêter une connection. KILL QUERY id : pour arrêter les traitement d’une connection.

Exercice Démarrez un serveur. Ouvrez 3 clients. Consulter les processus dans un client. Arrêtez un client avec un kill connection. Chargez la BD biblio et faites un show table status dessus.

DROITS SHOW GRANTS FOR user SHOW PRIVILEGES

CARACTERES SHOW CHARACTER SET SHOW COLLATION

MOTEUR SHOW ENGINES


DEBOGAGE SHOW ERRORS SHOW WARNINGS


7 – STOCKAGE ET MOTEURS DE STOCKAGE

7.1 : Consultation des données sur le disque

Principes Selon le moteur choisi, MySQL enregistre différemment les données sur le disque. Quel que soit le moteur, on trouve dans le datadir : Des dossiers qui correspondent aux BDs. Dans les dossiers on trouve en MyISAM un fichier .MYD (MYData), un fichier .MYI (MYIndex) et un troisième ficher qui décrit la structure de la table (.frm, .sdi etc.) En InnoDB, on ne trouve dans les répertoires que le fichier qui décrit la structure de la table. Dans le datadir, on trouve aussi des gros fichiers dans lesquels les données sont organisées essentiellement en InnoDB (ib_logfile0, undo_001, ib_data1, etc.). L’organisation est variable selon les versions. Il faut éviter de toucher à ces données directement si on veut éviter de bloquer la BD.

Exercices Chargez la BD biblioMyISAM et regardez le résultat dans le datadir. Chargez la BD biblioMyInnoDB et regardez le résultat dans le datadir. Comparez les résultats.

7.2 : Les différents moteurs de stockage https://dev.mysql.com/doc/refman/5.5/en/storage-engines.html https://dev.mysql.com/doc/refman/5.6/en/storage-engines.html https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

Présentation MySQL supporte plusieurs moteurs de stockage, qui gère différents types de tables. Le moteur de tables originale était ISAM. Ce moteur a été remplacé par le moteur MyISAM. • Le moteur MyISAM est une version améliorée de ISAM. • Le moteur MEMORY (anciennement appelé HEAP) propose des tables stockées en

mémoire. • Le moteur MERGE permet le regroupement de tables MyISAM identiques sous la forme

d'une seule table. Ces trois moteurs sont non transactionnels.


• Le moteurs InnoDB gère des tables transactionnelles. https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html

• NDBCluster est le moteur de stockage du cluster MySQL qui implémente des tables réparties sur plusieurs serveurs. https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster.html

Avantages et inconvénients

Avantages des tables transactionnelles • Plus sûr. Même si MySQL crashe ou que vous avez un problème matériel, vous pouvez

récupérer vos données, soit par un recouvrement automatique, soit à partir d'une sauvegarde combinée avec le log des transactions.

• Vous pouvez combiner plusieurs commandes et les accepter toutes d'un seul coup avec la commande COMMIT.

• Vous pouvez utiliser ROLLBACK pour ignorer vos modifications (si vous n'êtes pas en mode auto-commit) : si une mise à jour échoue, tout les changements seront annulés.

• Gère mieux les accès concurrents si la table reçoit simultanément plusieurs lectures.

Avantages des tables non-transactionnelles • Plus rapides • Utilisent moins d'espace disque • Utilisent moins de mémoire pour exécuter les mises à jour.

7.3 : Le moteur MyISAM

Caractéristiques principales Le moteur MyISAM est un moteur non transactionnel et qui ne prend pas en compte les contraintes de clé étrangère (FOREIGN KEY).

Type par défaut En général, MyISAM est le type de table par défaut, à moins d'avoir été spécifié autrement.

Quelques caractéristiques particulières des tables MyISAM

• Le nombre maximum d'index par table est de 64. • Le nombre maximum de colonnes par index est 16. • La taille maximum d'une clé est de 1000 octets. • Les colonnes BLOB et TEXT peuvent être indexées (Les types BLOB correspondent à est

un objet binaire de grande taille qui peut contenir une quantité variable de données. Les quatre types BLOB (TINYBLOB, BLOB, MEDIUMBLOB, et LONGBLOB) ne différent que par la taille maximale de données qu'ils peuvent stocker. Les 4 types TEXT


(TINYTEXT, TEXT, MEDIUMTEXT, et LONGTEXT) sont équivalents aux quatre types BLOB, mais ils sont insensibles à la casse en cas de comparaison et donc de tri).

• Les valeurs NULL sont autorisées dans une colonne indexée. • La gestion interne des colonnes AUTO_INCREMENT. MyISAM va automatiquement

modifier cette valeur lors d'une insertion ou d'une modification. • La valeur courante d'AUTO_INCREMENT peut être modifiée avec un ALTER TABLE ou

myisamchk. • Vous pouvez insérer de nouvelles lignes dans une table qui n'a aucun bloc vide dans le

fichier de données, en même temps que d'autres threads lisent le fichier de données (insertion simultanée). Un bloc vide peut provenir d'une modification de ligne à format dynamique (les données sont maintenant plus petites). Lorsque tous les blocs vide sont à nouveau utilisés, les insertions suivantes peuvent être simultanées.

• Chaque colonne de caractères peut avoir un jeu de caractères distinct. • Support du vrai type VARCHAR jusqu’à 64 Ko (sous peu).

Format de stockage des tables MyISAM MyISAM supporte 3 formats de stockage différents : FIXED (statique), DYNAMIC et COMPRESSED. Les formats FIXED et DYNAMIC sont choisis automatiquement selon le type de la colonne utilisé. Le format COMPRESSED est créé avec l'outil myisampack. Quand vous créez une table avec CREATE ou en modifiez la structure avec ALTER vous pouvez, pour les tables n'ayant pas de champ BLOB forcer le type de table en DYNAMIC ou FIXED avec l'option de table ROW_FORMAT.

Caractéristiques des tables statiques Le format statique est le format par défaut. Il est utilisé lorsque la table ne contient pas de colonnes de type VARCHAR, BLOB, ou TEXT.

Ø Avantages • Très rapide. • Facile à mettre en cache. • Facile à reconstruire après un crash, car les enregistrements sont localisés dans des positions

fixées. • N'a pas à être réorganisé (avec myisamchk) sauf si un grand nombre de lignes est effacé et

que vous voulez retourner l'espace libéré au système d'exploitation.

Ø Inconvénients • Requière usuellement plus d'espace disque que les tables dynamiques : toutes les colonnes

CHAR, NUMERIC, et DECIMAL sont complétées par des espaces jusqu'à atteindre la longueur totale de la colonne.

Caractéristiques des tables dynamiques


Le format dynamique est utilisé si une table MyISAM contient des colonnes de taille variable : VARCHAR, BLOB, ou TEXT, ou si la table a été créée avec l’option ROW_FORMAT=DYNAMIC. Si un enregistrement devient plus grand, il est divisé en autant de parties que nécessaire, ce qui provoque une fragmentation de l’enregistrement.

Ø Avantages • Chaque enregistrement n’utilise que la quantité d’espace requise.

Ø Inconvénients • La fragmentation peut dégrader les performances des requêtes. Il faut exécuter un OPTIMIZE

TABLE ou myisqmchk –r de temps en temps pour améliorer les performances. • La fragmentation peut dégrader les performances de la reconstruction après crash.


7.4 : Le moteur MERGE http://dev.mysql.com/doc/refman/5.0/fr/merge-storage-engine.html Une table MERGE est une collection de tables MyISAM identiques qui peuvent être utilisaées à la manière d’une table unique. On peut l’apparenter à la notion de « vue ». L'exemple suivant vous montre comme utiliser les tables MERGE :

mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); mysql> INSERT INTO t1 (message) VALUES('Testing'),('table'),('t1'); mysql> INSERT INTO t2 (message) VALUES('Testing'),('table'),('t2'); mysql> CREATE TABLE total ( a INT NOT NULL AUTO_INCREMENT, message CHAR(20), INDEX(a)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Une table MERGE ne peut pas contenir de contrainte de type UNIQUE sur toute la table. Lorsque vous faites une insertion, les données vont dans la première ou la dernière table (suivant la méthode d'insertion INSERT_METHOD=xxx) et cette table MyISAM s'assure que les données sont uniques, mais rien n'est fait pour vérifier l'unicité auprès des autres tables MyISAM tables.

7.5 : Le moteur MEMORY http://dev.mysql.com/doc/refman/5.0/fr/memory-storage-engine.html Le moteur de stockage MEMORY (anciennement HEAP) crée des tables dont le contenu est stocké en mémoire. Les tables MEMORY sont très rapides et très utiles pour créer des tables temporaires. Toutefois, lorsque le serveur ferme, toutes les données stockées dans les tables MEMORY sont perdues. Par contre, les tables continuent d’exister. Leurs définitions sont stockées dans les fichiers .frm sur le disque.

7.6 : Les tables compressées : myisampack http://dev.mysql.com/doc/refman/5.0/fr/myisampack.html On peut compresser les tables MyISAM avec l'utilitaire myisampack pour réduire leur taille sur le disque. Généralement, myisampack compresse le fichier avec un gain de 40 à 70 %. Les tables compressées peuvent être décompressées avec myisamchk. Les tables compressées sont en lecture seule.


7.7 : Le moteur ARCHIVE http://dev.mysql.com/doc/refman/5.0/fr/archive-storage-engine.html Le moteur de table ARCHIVE est utilisé pour stocker de grande quantité de données, sans index, et de manière très économique. Le moteur ARCHIVE ne supporte que les commandes INSERT et SELECT : aucun effacement, remplacement ou modification. Les enregistrements sont compressé au moment de leur insertion. Vous pouvez utiliser la commande OPTIMIZE TABLE pour analyser la table, et compresser encore plus.

7.8 : Le moteur InnoDB http://dev.mysql.com/doc/refman/5.0/fr/innodb.html

Caractéristiques principales Moteur transactionnel : compatibilité ACID complète. Prise en compte des contraintes de clé étrangère (FOREIGN KEY). Performances maximales dors du traitement de grands volumes de données. Aucune limite de taille de table, même sur des systèmes d’exploitation dont la taille de fichier est limitée à 2Go.


8 - PROGRAMMATION DES TABLES : RAPPELS

Gestion des bases de données

mysql>CREATE database NomBD; mysql>USE NomBD; mysql>DROP database if exists NomBD;

Création des tables

Création des tables

Syntaxe MySQL En première approche, la commande a la syntaxe suivante :

CREATE table NomTable ( attribut_1 type [contrainte d’intégrité], attribut_2 type [contrainte d’intégrité], … , attribut_n type [contrainte d’intégrité] , [contrainte d’intégrité] ) ENGINE moteur;

Les contraintes sont facultatives. L’ordre dans la liste est au choix.

Exemple

CREATE TABLE DEPT ( ND integer primary key auto_increment, NOM varchar(14), VILLE varchar(13) ); CREATE TABLE EMP ( NE integer primary key auto_increment, NOM varchar(10) not NULL, JOB varchar(9), DATEMB date, SAL float(7,2), COMM float(7,2), ND integer not null, foreign key(ND) references DEPT(ND), NEchef integer , foreign key(NEchef) references EMP(NE) ) ENGINE InnoDB;

Ø Variante :


CREATE TABLE EMP ( NE integer auto_increment, NOM varchar(10), JOB enum ('PRESIDENT','MANAGER', 'SALESMAN', 'CLERK',

'ANALYST'), DATEMB date, SAL float(7,2) check (sal >1000), -- mysql ne gère pas le

check ! COMM float(7,2) default 100, ND integer not null, NEchef integer, primary key(NE) ) ENGINE InnoDB; CREATE TABLE DEPT ( ND integer auto_increment, NOM varchar(14), VILLE varchar(13), primary key(ND) ) ENGINE InnoDB; ALTER TABLE EMP ADD constraint KEYND foreign key(ND) references

DEPT(ND); ALTER TABLE EMP ADD constraint KEYNECHEF foreign key(NEchef)

references EMP(NE);

Dans la variante, on crée les FOREIGN KEY avec un ALTER TABLE après la création des tables. Cette technique évite les problème d’ordre dans la création des tables.

Les contraintes d’intégrité

Liste des contraintes d’intégrité Ø PRIMARY KEY : permet de définir les clés primaires. Cette contrainte garantit le fait que

la valeur est différente de NULL et qu’elle est unique dans la table. Ø FOREIGN KEY : permet de définir les clés étrangères. Cette contrainte fait référence à une

clé primaire dans une autre table. Ø NOT NULL : impose le fait que la valeur de l’attribut doit être renseignée. Ø UNIQUE : impose le fait que chaque tuple de la table doit, pour l’attribut concerné, avoir

une valeur différente de celle des autres ou NULL. Ø DEFAULT : permet de définir une valeur par défaut. Ø CHECK :permet de définir un ensemble de valeurs possible pour l’attribut. Cette contrainte

garantit le fait que la valeur de l’attribut appartiendra à cet ensemble. MySQL ne gère pas cette contrainte.

Ø ENUM :permet de définir un ensemble de valeurs possible pour l’attribut. Ce n’est pas une contrainte à proprement parler.

Conséquences des contraintes d’intégrité Ø PRIMARY KEY, NOT NULL, UNIQUE et CHECK : ces quatre contraintes ont le même

type de conséquence : si on cherche à donner une valeur à un attribut qui n’est pas conforme à ce qui est précisé dans la définition de l’attribut (valeur NULL s’il est défini NOT NULL ou PRIMARY KEY, valeur existant déjà s’il est défini UNIQUE ou PRIMARY KEY, valeur n’appartenant pas au domaine spécifié par le CHECK), alors le SGBD renvoie un message d’erreur et ne modifie pas la base de données. Ainsi, un premier niveau de cohérence des données est maintenu.


Ø DEFAULT : donne une valeur par défaut si il n’y a pas de saisie.

Paramétrage des contraintes d’intégrité référentielle

Suppression d’un tuple (1) dont la clé primaire est référencée par d’autres tuples (2) Trois cas peuvent se présenter :

• Soit on interdit la destruction du tuple (1). Il faudra commencer par détruire les tuples (2) pour pouvoir supprimer le tuple (1). C’est la situation par défaut.

• Soit le système supprime le tuple (1) et les tuples (2) pour qu’il n’y ait plus de tuples qui fassent référence au tuple (1). Dans ce cas on ajoute : ON DELETE CASCADE à la définition de la clé étrangère.

Exemple :

ND integer not null, foreign key(ND) references DEPT(ND) on delete cascade

Dans notre exemple, cela signifie que si on supprime un département, on supprimera aussi tous les employés du départements… ce qui n’est certainement pas un bon choix de modélisation !

• Soit le système supprime le tuple (1) et met la clé étrangère des tuples (2) à NULL pour qu’il n’y ait plus de tuples qui fassent référence au tuple (1). Dans ce cas on ajoute : ON DELETE SET NULL à la définition de la clé étrangère.

Exemple :

ND integer not null, foreign key(ND) references DEPT(ND) on delete set NULL

Dans notre exemple, cela signifie que si on supprime un département, les employés du département auront désormais la valeur NULL comme numéro de département. C’est possible à condition que le numéro de département de l’employé ne soit pas déclaré NOT NULL.

Modification d’une clé primaire d’un tuple (1) référencée par d’autres tuples (2) Deux cas peuvent se présenter :

• Soit on interdit la modification du tuple (1). Il faudra commencer par modifier les tuples (2) pour pouvoir modifier le tuple (1). C’est la situation par défaut.

• Soit le système modifie le tuple (1) et les tuples (2) pour qu’ils fassent correctement référence au tuple (1).

Dans ce cas on ajoute : ON UPDATE CASCADE à la définition de la clé étrangère. Exemple :

ND integer not null, foreign key(ND) references DEPT(ND) on update cascade

Dans notre exemple, cela signifie que si on modifie la clé primaire d’un département, on modifiera aussi les numéros de départements des employés de ce département.


Nommer les contraintes : CONSTRAINT nomContrainte On peut nommer les contraintes, ce qui permettra ensuite de désactiver et de réactiver les contraintes en y faisant références par leur nom. Pour cela, il suffit d’ajouter « CONSTRAINT nomContrainte » devant la déclaration de la contrainte.

Modification des tables

Modification des attributs

Ajouter un ou plusieurs attributs à la table : ALTER TABLE NomTable ADD ( attribut_1 type [contrainte], attribut_2 type [contrainte], … , attribut_n type [contrainte] );

Modifier un attribut de la table ALTER TABLE NomTable MODIFY attribut_1 type [contrainte] ;

La modification permet d’annuler les contraintes de type NOT NULL ou auto_increment.

Supprimer un attribut de la table ALTER TABLE NomTable DROP attribut ;

Ø Attention La modification et la destruction des attributs doivent être manipulées avec prudence : une table peut contenir des milliers de données. Il ne faut pas les supprimer ou modifier une table sans précaution.

Modification des contraintes d'intégrité

Ajouter une contrainte ALTER TABLE NomTable ADD CONSTRAINT [contrainte] ;

Ø Exemple ALTER TABLE emp ADD CONSTRAINT KEYND foreign key(ND) references DEPT(ND);

Suppression d’une contrainte nommée


ALTER TABLE NomTable DROP type de contrainte nom_de_contrainte;

Ø Exemple ALTER TABLE emp DROP foreign key KEYND;

Récupérer le nom des contraintes : show create table maTable Si on n’a pas nommé les contraintes à la création, MySQL les nomme automatiquement. Pour récupérer le nom, il faut utiliser la commande

Show create table maTable

On obtient alors le nom de la contrainte derrière le mot clé CONSTRAINT.

Suppression de la clé primaire ALTER TABLE NomTable DROP primary key ;

On ne peut supprimer la clé primaire que si ce n’est pas un auto incrément, et uniquement si elle n’est pas référencée par une clé étrangère.

Destruction des tables

DROP TABLE NomTable ;

Attention : La modification et la destruction des tables doit être manipulées avec prudence : une table peut contenir des milliers de données. Il ne faut pas les supprimer ou modifier une table sans précaution.

Récupération du code sous mysql : show create table La commande show create table nomTable permet de récuperer le code de création d’une table.

Exemple 1 : mysql> show create table dept;

| emp | CREATE TABLE `dept` ( `ND` int(11) NOT NULL auto_increment, `NOM` varchar(14) default NULL, `VILLE` varchar(13) default NULL, PRIMARY KEY (`ND`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1

Remarques : 1. le n° du prochain ID automatique sera 41. 2. InnoDB est un choix de SGBD. C’est celui par défaut. 3. Le code est exploitable directement pour créer une table.


Exemple 2 : mysql> show create table emp;

| emp | CREATE TABLE èmp` ( `NE` int(11) NOT NULL auto_increment, `NOM` varchar(10) default NULL, `JOB` varchar(9) default NULL, `DATEMB` date default NULL, `SAL` float(7,2) default NULL, `COMM` float(7,2) default NULL, `ND` int(11) NOT NULL, `NEchef` int(11) default NULL, PRIMARY KEY (`NE`), KEY `ND` (`ND`), KEY `NEchef` (`NEchef`), CONSTRAINT èmp_ibfk_1` FOREIGN KEY (`ND`) REFERENCES `dept`

(`ND`), CONSTRAINT èmp_ibfk_2` FOREIGN KEY (`NEchef`) REFERENCES èmp`

(`NE`) ) ENGINE=InnoDB AUTO_INCREMENT=7944 DEFAULT CHARSET=latin1

Remarques : 1. Le n° du prochain ID automatique sera 7944. 2. Les clés étrangères sont déclarées en plusieurs étapes : avec KEY et avec CONSTRAINT 3. Le code est exploitable directement pour créer une table.

Passage d’un moteur à un autre

MyISAM et InnoDB On peut modifier le moteur d’une table :

Passage en MyISAM ALTER TABLE emp TYPE MyISAM ;

Pour passer en d’InnoDB à MyISAM, il ne doit pas y avoir de clé étrangère. Il faut donc commencer par supprimer les contraintes nommées de clé étrangère.

Passage en InnoDB ALTER TABLE emp TYPE InnoDB ;

Le passage de MyISAM a InnoDB est possible directement.


9 - TP

EXERCICES d’ADMINISTATION MySQL

Prise en main en ligne de commande :

1. Démarrer un serveur (mysqld) 2. Pinguer le serveur (mysqladmin) 3. Arrêter le serveur (mysqladmin) 4. Pinguer le serveur (mysqladmin) 5. Démarrer le serveur (mysqld) 6. Démarrer 1 client root (mysql) 7. Dans le client, affichez la version du serveur, les databases, les utilisateurs ; 8. Ajouter un mot de passe à root si il n’en avait pas. 9. Démarrer un nouveau client. Arrêtez-le. 10. Supprimer le mot de passe de root. 11. Démarrer un nouveau client. Arrêtez-le. 12. Dans un fichier de configuration, mettez la langue en français, MyISAM comme moteur par

défaut (storage_engine ou default_storage_engine), SQL_MODE à vide ‘’ ; 13. Arrêtez le serveur. 14. Démarrez le serveur. 15. Vérifiez dans un client mysql que les paramètres du fichier de configuration ont bien été pris

en compte. 16. Chargez la BD bibliopardefaut. 17. Vérifiez les moteurs des tables. 18. Vérifiez l’état des contraintes d’intégrité des tables. 19. Supprimer la BD. 20. Changer le moteur par défaut pour le client uniquement : passez-le en InnoDB. 21. Affichez la valeur de moteur par défaut du client et du serveur. Vérifiez qu’elles sont

différentes. 22. Chargez la BD bibliopardefaut. 23. Vérifiez les moteurs des tables. 24. Vérifiez l’état des contraintes d’intégrité des tables. 25. Faites une sauvegarde de la BD. 26. Enregistrez la liste complètes des livres (NL, titre, auteur, éditeur) dans un ficher .csv.

On peut aussi tester les requêtes avec Group By quand on a un SQL_MODE à vide et quand on a un SQL_MODE à ONLY_FULL_GROUP_BY.

Documents

Bases de données MySQL - Administrationbliaudet.free.fr/IMG/pdf/01-MySQL-Install-Config-Moteurs.pdf · 2019. 2. 9. · Exercice 31 3.3 : Les variables d ... variables d’état du