• 40 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

Ce cours existe en livre papier.

course.header.alt.is_certifying

Vous pouvez être accompagné et mentoré par un professeur particulier par visioconférence sur ce cours.

J'ai tout compris !

Mis à jour le 22/05/2019

Configuration et options

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Dans ce dernier chapitre, vous apprendrez à configurer MySQL :

  • en modifiant les variables système avec la commande SET  ;

  • en utilisant les options du logiciel client au démarrage d'une session ;

  • en utilisant les options du serveur lors de son démarrage ;

  • en modifiant le fichier de configuration de MySQL.

Nous commencerons bien sûr par voir ce qu'est exactement une variable système.

Notez que ces variables système sont nombreuses, tout comme les options disponibles au niveau du client et du serveur, ainsi que dans le fichier de configuration.
Pour ne pas vous donner de longues listes indigestes, je vous renverrai très souvent vers la documentation officielle au cours de ce chapitre, et ne vous donnerai que quelques exemples parmi les plus utilisés.

Variables système

Le comportement de MySQL est régi par une série de valeurs contenues dans les variables système. Elles déterminent par exemple à quelle valeur commencent les colonnes auto-incrémentées (par défaut : 1), de combien ces valeurs s'incrémentent à chaque insertion (par défaut : 1 également), le moteur de stockage par défaut, le temps d'attente acceptable lorsqu'une requête se heurte à un verrou, etc.

Voici quelques variables système. Vous verrez que vous en connaissez déjà quelques-unes.
Pour une liste complète, reportez-vous à la documentation officielle.

Nom

Définition

autocommit

Définit si le mode autocommit est activé ou non, donc si les requêtes sont automatiquement commitées ou s'il est nécessaire de les commiter pour qu'elles prennent effet.

character_set_client

Jeu de caractères (encodage) utilisé par le client MySQL.

default_week_format

Mode par défaut de la fonction WEEK().

foreign_key_checks

Définit si les contraintes de clé étrangère doivent être vérifiées.

ft_min_word_len

Taille minimale d'un mot pour qu'il soit inclus dans une recherche FULLTEXT.

last_insert_id

Valeur retournée par la fonction LAST_INSERT_ID().

max_connections

Nombre maximal de connexions autorisées au serveur.

storage_engine

Moteur de stockage par défaut.

tx_isolation

Définit le niveau d'isolation des transactions.

unique_checks

Définit si les contraintes d'unicité doivent être vérifiées.

Pour connaître les valeurs actuelles des variables système, on peut utiliser la commande suivante :

SHOW VARIABLES;

Si l'on a une idée de la variable que l'on cherche, on peut utiliser la clause LIKE, ou la clause WHERE.

Exemple 1 : variables en rapport avec l'auto-incrémentation.

SHOW VARIABLES LIKE '%auto_increment%';

Variable_name

Value

auto_increment_increment

1

auto_increment_offset

1

Exemple 2 : affichage de la valeur de unique_checks.

SHOW VARIABLES LIKE 'unique_checks';

Variable_name

Value

unique_checks

ON

On peut utiliser également une requête SELECT, en faisant précéder le nom de la variable de deux caractères @.

Exemple

SELECT @@autocommit;

@@autocommit

1

Niveau des variables système

Les variables système existent à deux niveaux différents :

  • Global : c'est la variable au niveau du serveur MySQL même.

  • Session : c'est la variable au niveau de la session.

Lorsque l'on démarre le serveur MySQL, les variables système sont donc initialisées à leur valeur par défaut (nous verrons comment modifier ces valeurs plus tard), au niveau global.
Lorsque l'on ouvre une session MySQL et que l'on se connecte au serveur, les variables système au niveau session sont initialisées à partir des variables système globales. Il est cependant possible de modifier les variables système, au niveau de la session ou directement au niveau global. On peut donc se retrouver avec des variables système différentes pour les deux niveaux.

Il est possible de préciser le niveau que l'on désire afficher avec SHOW VARIABLES, en ajoutant GLOBAL  ou SESSION  (ou LOCAL, qui est synonyme de SESSION).

SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;

SELECT @@GLOBAL.nom_variable;
SELECT @@SESSION.nom_variable;

Par défaut, si rien n'est précisé, ce sont les variables de session qui sont affichées.

Variables système n'existant qu'à un niveau

Il existe un certain nombre de variables pour lequel le niveau global n'existe pas. Ces variables sont initialisées au début de la connexion, et leur modification ne peut affecter que la session en cours.
La variable système autocommit, par exemple, n'existe que pour la session. De même que last_insert_id, ce qui est logique : LAST_INSERT_ID()  renvoie la valeur de la dernière auto-incrémentation réalisée par la session. Cela n'a donc pas de sens d'avoir cette variable à un niveau global.

De même, certaines variables système n'ont pas de sens au niveau de la session et n'y existent donc pas. C'est le cas par exemple de la variable max_connections, qui détermine le nombre maximum de sessions connectées simultanément au serveur.

Pour ces variables, le comportement de la commande SHOW VARIABLES  diffère du comportement de la commande SELECT @@nom_variable.

  • Si vous ne précisez pas le niveau voulu avec SHOW VARIABLES, vous aurez la valeur de la variable de session si elle existe, sinon, la valeur au niveau global. SELECT  agira de la même manière.

  • Si vous précisez un niveau et qu'une des variables n'existe pas à ce niveau, SHOW  donnera sa valeur à l'autre niveau (sans prévenir).

  • Par contre, si vous précisez un niveau avec SELECTet que la variable système n'y existe pas, une erreur est déclenchée.

Exemples :last_insert_id n'existe qu'au niveau de la session, max_connections n'existe qu'au niveau global.

SHOW VARIABLES LIKE 'last_insert_id';
SHOW SESSION VARIABLES LIKE 'last_insert_id';
SHOW GLOBAL VARIABLES LIKE 'last_insert_id';

Ces trois requêtes donneront exactement le même résultat :

Variable_name

Value

last_insert_id

0

SHOW VARIABLES LIKE 'max_connections';
SHOW SESSION VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Variable_name

Value

max_connections

151

Avec SELECT, si l'on ne précise pas le niveau, ou si l'on précise le bon niveau, le résultat est le même.

SELECT @@max_connections AS max_connections, 
        @@last_insert_id AS last_insert_id;
SELECT @@GLOBAL.max_connections AS max_connections, 
       @@SESSION.last_insert_id AS last_insert_id;

max_connections

last_insert_id

151

0

Par contre, si l'on précise le mauvais niveau, on obtient une erreur.

SELECT @@SESSION.max_connections;
SELECT @@GLOBAL.last_insert_id;
ERROR 1238 (HY000): Variable 'max_connections' is a GLOBAL variable
ERROR 1238 (HY000): Variable 'last_insert_id' is a SESSION variable

Modification des variables système avec SET

Comme pour les variables utilisateur et les variables locales, il est possible de modifier la valeur des variables système en utilisant la commande SET.

Cependant, toutes ne permettent pas ce genre de modification. La liste des variables système qui l'autorisent, appelées variables système dynamiques, se trouve dans la documentation officielle.

Pour modifier une variable au niveau global, il est nécessaire d'avoir le privilège global SUPER. À moins que vous n'ayez accordé ce privilège à l'un de vos utilisateurs, seul l'utilisateur "root" en est capable.

Deux syntaxes sont possibles avec SET  :

SET niveau nom_variable = valeur;
-- OU
SET @@niveau.nom_variable = valeur;

Exemples

SET SESSION max_tmp_tables = 5; -- Nombre maximal de tables temporaires 
SET @@GLOBAL.storage_engine = InnoDB; -- Moteur de stockage par défaut

On peut aussi omettre le niveau, auquel cas c'est la variable système au niveau de la session qui sera modifiée. Si elle n'existe qu'au niveau global, une erreur sera déclenchée.

Exemples

SET max_tmp_tables = 12;
SET @@max_tmp_tables = 8;

SET @@max_connections = 200;

Les deux premières commandes fonctionnent, mais la troisième échoue avec l'erreur suivante :

ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL

Effet de la modification selon le niveau

Il est important de se rendre compte de la différence qu'il y a entre modifier la valeur d'une variable système au niveau de la session et au niveau du serveur (niveau global).

Lorsque l'on modifie une variable système globale, la valeur de la même variable système au niveau de la session ne change pas.
Par conséquent, cela n'affecte pas du tout la session en cours.

Par contre, cela affectera toutes les sessions qui se connecteront au serveur après la modification (jusqu'à arrêt/redémarrage du serveur).

Exemple : on a modifié storage_engine au niveau global, cela n'a pas affecté storage_engine au niveau session.

SELECT @@GLOBAL.storage_engine, @@SESSION.storage_engine;

@@GLOBAL.storage_engine

@@SESSION.storage_engine

InnoDB

MyISAM

Si l'on crée une table dans la session courante, elle utilisera toujours le moteur MyISAM par défaut. Par contre, si l'on ouvre une autre session, les variables système de session étant initialisées à la connexion à partir des variables système du serveur, storage_engine aura la valeur InnoDB pour le serveur et pour la session. Par conséquent, toute table créée par cette nouvelle session utilisera InnoDB par défaut.

À l'inverse, modifier la valeur d'une variable système au niveau de la session n'affectera que la session. Toutes les sessions futures reprendront la valeur globale à l'initialisation des variables système de la session.

Les commandes SET spéciales

Pour certaines variables système, MySQL a créé des commandes SET  spéciales.

Exemples

SET NAMES encodage;

Cette commande modifie trois variables système au niveau de la session : character_set_client, character_set_connection et character_set_results.

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE }

Cette commande modifie la variable système tx_isolation au niveau demandé.

Options au démarrage du client mysql

Lorsque l'on se connecte au serveur avec un client mysql, on peut préciser une série d'options. Vous en connaissez déjà plusieurs : l'hôte, l'utilisateur et le mot de passe sont des options.

Pour rappel, ces options peuvent être données de deux manières : la manière courte, et la longue.

mysql --host=localhost --user=student --password=motdepasse

#OU

mysql -h localhost -u student -pmotdepasse

Pour préciser une option, on utilise donc --option[=valeur]. Certaines options permettent un raccourci, et certaines ne nécessitent pas qu'on leur donne une valeur.

En voici quelques-unes.

Option

Raccourci

Explication

--default-character-set=encodage

/

Définit l'encodage par défaut.

--delimiter=delim

/

Modifie le délimiteur.

--no-beep

-b

Le client n'émet plus de son en cas d'erreur.

--execute=requetes

-e requetes

Exécute les requêtes données (séparées par ;), puis quitte le client.

--init-command=requete

/

Exécute la requête donnée dès le démarrage du client.

--safe-updates ou --i-m-a-dummy

-U

N'exécute les requêtes UPDATE  et DELETE  que si les lignes à modifier/supprimer sont spécifiées explicitement grâce à une clause WHERE  sur un index, ou limitées par un LIMIT. Cela empêche l'exécution de commandes comme DELETE FROM nomTable;  qui supprime toutes les données de la table.

--skip-column-names

-N

Les noms des colonnes ne sont pas affichés dans les résultats.

--vertical

-E

Écrit les résultats de manière verticale (comme lorsque l'on termine une requête par \G).

Pour la liste complète, comme d'habitude, vous trouverez votre bonheur dans la documentation officielle.

Certaines options ont un effet sur les variables système, au niveau de la session. Par exemple, --default-character-set modifie trois variables système : character_set_client, character_set_connection et character_set_results. C'est donc l'équivalent de la commande SET NAMES encodage;.
D'autres, comme --no-beep, n'ont vraiment d'effet que sur le logiciel client.

Exemple

mysql -u student -p elevage --skip-column-names
SELECT id, nom, espece_id, prix
FROM Race;

1

Berger allemand

1

450.00

2

Berger blanc suisse

1

900.00

3

Singapura

2

950.00

4

Bleu russe

2

800.00

5

Maine Coon

2

700.00

7

Sphynx

2

1200.00

8

Nebelung

2

950.00

9

Rottweiller

1

600.00

Options au démarrage du serveur mysqld

Vous connaissez maintenant deux techniques permettant de modifier des variables système de session :

  • pendant la session avec SET @@SESSION.nomVariable = valeur  ;

  • au démarrage de la session avec les options du client mysql.

Pour les variables système globales, il existe l'équivalent :

  • vous savez déjà qu'il est possible de faire SET @@GLOBAL.nomVariable = valeur  ;

  • nous allons maintenant voir comment préciser des options au démarrage du serveur.

Démarrer le serveur ? On n'a jamais démarré le serveur !

Mais si ! Tout au début, lors de l'installation de MySQL, le serveur a été démarré. Selon votre configuration et/ou votre système d'exploitation, il a été démarré soit automatiquement, soit en tapant mysqld_safe ou mysqld en ligne de commande. Depuis, chaque fois que vous rallumez votre ordinateur, le serveur est démarré automatiquement (c'est en tout cas le comportement par défaut, modifiable à l'installation et/ou par la suite).

Pour stopper le serveur, on utilise l'utilitaire mysqladmin (en ligne de commande, bien sûr) :

mysqladmin -u root -p shutdown

Bien entendu, si vous n'avez pas défini de mot de passe pour l'utilisateur root (mauvaise idée !), enlevez le -p.

Et pour (re)démarrer le serveur :

mysqld

Ou, si vous êtes sous MacOS ou Linux, vous pouvez utiliser mysqld_safe, qui ajoute quelques options de sécurisation :

mysqld_safe
Les options du serveur

La syntaxe est la même que lors du démarrage du client : --option[=valeur].

Quelques exemples :

Option

Raccourci

Explication

--character-set-server=encodage

-C charset_name

Définit l'encodage par défaut utilisé par le serveur.

--default-storage-engine=type

/

Définit le moteur de stockage par défaut.

--default-time-zone=timezone

/

Définit le fuseau horaire à utiliser.

--init-file=nomFichier

/

Les requêtes définies dans le dossier donné sont exécutées au démarrage du serveur.

--language=langue

-L langue

Définit la langue à utiliser pour les messages d'erreur.

--transaction-isolation=niveau

/

Définit le niveau d'isolation des transactions.

La plupart des options du serveur (mais pas toutes) modifient les variables système globales.

Exemple : on lance le serveur en définissant l'encodage utilisé par celui-ci.

mysqld -C greek

Il suffit alors de démarrer une session pour tester notre nouvelle configuration.

SHOW GLOBAL VARIABLES LIKE 'character_set_server';

Variable_name

Value

character_set_server

greek

Fichiers de configuration

Si l'on veut garder la même configuration en permanence malgré les redémarrages de serveur et pour toutes les sessions, il existe une solution plus simple que de démarrer chaque fois le logiciel avec les options désirées : utiliser les fichiers de configuration.

Ceux-ci permettent de préciser, pour chacun des logiciels de MySQL (le client mysql, le serveur mysqld, l'utilitaire mysqladmin, etc.), une série d'options qui seront prises en compte à chaque démarrage du logiciel.

Emplacement du fichier

Lors de leur démarrage, les logiciels MySQL vérifient l'existence de fichiers de configuration à différents endroits.
Si plusieurs fichiers de configuration sont trouvés, ils sont tous utilisés. Si une option est spécifiée plusieurs fois (par plusieurs fichiers différents), c'est la dernière valeur qui est prise en compte (l'ordre dans lequel les fichiers de configuration sont lus est donné ci-dessous).
Les emplacements vérifiés sont différents selon que l'on utilise Windows ou Unix.

Il est tout à fait possible que ces fichiers de configuration n'existent pas. Dans ce cas, il suffit de le (ou les) créer avec un simple éditeur de texte.

Pour Windows

Dans l'ordre, trois emplacements sont utilisés.

Emplacement

Commentaire

WINDIR\my.ini, WINDIR\my.cnf

WINDIR est le dossier de Windows. Généralement, il s'agit du dossier C:\Windows. Pour vérifier, il suffit d'exécuter la commande suivante (dans la ligne de commande Windows) : echo %WINDIR%

C:\my.ini ou C:\my.cnf

-

INSTALLDIR\my.ini ou INSTALLDIR\my.cnf

INSTALLDIR est le dossier dans lequel MySQL a été installé.

Pour Linux et MacOS

Les emplacements suivants sont parcourus, dans l'ordre.

Emplacement

Commentaire

/etc/my.cnf

-

/etc/mysql/my.cnf

-

~/.my.cnf

~/ est le répertoire de l'utilisateur Unix. Dans un système avec plusieurs utilisateurs, cela permet de définir un fichier de configuration pour chaque utilisateur Unix (le fichier n'étant lu que pour l'utilisateur Unix courant).

Fichier de configuration fourni au démarrage

Que ce soit sous Windows ou sous Unix, il est également possible de donner un fichier de configuration dans les options au démarrage du logiciel. Dans ce cas, le fichier peut se trouver n'importe où, il suffit de fournir le chemin complet.

Voici les options permettant cela :

Option

Commentaire

--defaults-extra-file=chemin_fichier

Le fichier de configuration spécifié est utilisé en plus des éventuels autres fichiers de configuration. Ce fichier est utilisé en dernier, sauf sous Unix où le fichier localisé dans le dossier racine de l'utilisateur Unix est toujours utilisé en tout dernier.

--defaults-file=chemin_fichier

Seul ce fichier de configuration est utilisé, les autres sont ignorés.

Exemple

mysql -u student -p --defaults-extra-file=/Users/taguan/Documents/SQLtuto/mysqlConfig.cnf

Structure du fichier

Un fichier de configuration MySQL peut contenir trois types de lignes différentes :

  • option ou option=valeur : définit l'option à utiliser. C'est exactement la même syntaxe que pour les options à préciser lors du démarrage du logiciel, à ceci près que l'on omet les deux tirets -.

  • [logiciel] ou [groupe] : définit le logiciel ou le groupe auquel les options s'appliquent.

  • #commentaire ou ;commentaire : ligne de commentaire, elle sera ignorée. Notez qu'il est possible de commencer un commentaire au milieu d'une ligne avec #.

Exemple

#début des options pour le serveur mysqld
[mysqld]
character-default-set=utf8    # on modifie l'encodage du serveur
timezone='+01:00'             # on ajuste le fuseau horaire du serveur
default-storage-engine=InnoDB # on définit le moteur de stockage par défaut

#début des options pour le client mysql
[mysql]
character-set=utf8            # on modifie l'encodage client
no-beep                       # le silence est d'or
Les options

Toutes les options disponibles en ligne de commande lors du démarrage d'un logiciel sont utilisables dans un fichier de configuration (pour le logiciel correspondant).
On omet simplement les deux caractères - avant l'option, et les raccourcis ne peuvent pas être utilisés.

Si l'option nécessite que l'on précise une valeur, celle-ci peut, mais ne doit pas, être entourée de guillemets. Si la valeur donnée comprend un #, les guillemets seront nécessaires pour que ce caractère ne soit pas considéré comme le début d'un commentaire.

Par ailleurs, les espaces avant et après l'option sont ignorées, et les espaces autour du signe = sont autorisées (ce n'est pas le cas en ligne de commande).

Balise [logiciel] et [groupe]

Les balises [logiciel] et [groupe] permettent de spécifier à quel(s) logiciel(s) s'appliquent les options suivant cette balise (jusqu'à la balise [logiciel] ou [groupe] suivante, ou jusqu'à ce que la fin du fichier soit atteinte).

On peut donc soit donner le nom du logiciel concerné ([mysqld], [mysql], [mysqldump], etc.), soit donner le groupe [client].
Si le groupe [client] est spécifié, les options suivantes seront prises en compte par tous les logiciels clients. mysqldump, mysqladmin et mysql sont tous trois des logiciels clients.

Bien entendu, il est possible de spécifier une balise [client] pour les options communes à tous les clients, puis une balise [logiciel] pour les options spécifiques à un logiciel.

Exemple

[client]
port=3306   # on précise le port à utiliser pour tous les logiciels clients

[mysqld]
port=3306   # on précise le port aussi pour le serveur
character-default-set=utf8  

[mysql]
no-beep

Tous les logiciels clients, ainsi que le serveur utiliseront le port 3306, mais seul le logiciel mysql utilisera l'option no-beep.

En résumé
  • Les variables système sont des variables qui déterminent certains comportements de MySQL, certains paramètres.

  • Les variables système existent à deux niveaux : global (niveau serveur) et session.

  • On peut afficher les variables système avec SHOW VARIABLES  et avec SELECT @@nom_variable  (en précisant éventuellement le niveau désiré).

  • On peut modifier les variables système avec la commande SET @@nom_variable, en précisant éventuellement le niveau auquel on veut les modifier (par défaut, ce sera au niveau de la session).

  • On peut préciser des options au démarrage de la session et au démarrage du serveur, avec --option[=valeur].

  • Tous les paramètres de MySQL sont également définissables dans des fichiers de configuration.

Ce cours est maintenant terminé. J'espère que vous y aurez trouvé les informations dont vous aviez besoin.
Quoi qu'il en soit, la fin de ce cours ne doit surtout pas sonner la fin de votre apprentissage de MySQL, ou de la gestion de bases de données en général.

Aller plus loin
  • Certaines parties de ce cours sont volontairement superficielles. En particulier tout ce qui concerne la configuration du serveur et des logiciels clients. Le but était d'être le moins théorique possible, tout en vous donnant un aperçu relativement complet des énormes possibilités de MySQL. Il existe de nombreuses ressources sur internet, ainsi que de nombreux ouvrages d'un niveau plus avancé qui pourront vous en apprendre plus.

  • J'ai essayé de vous donner un maximum de pistes et de bonnes pratiques pour concevoir intelligemment vos bases de données, mais la conception d'une base de données est un vaste sujet. Si celui-ci vous intéresse, penchez-vous par exemple sur la théorie relationnelle, les formes normales, les modèles de données tels que le modèle Entité-Relation, la méthode Merise...

  • Même constat au niveau de l'optimisation des requêtes. Des ouvrages entiers ont été consacrés aux techniques d'optimisation utilisables avec MySQL.

  • Et tant et tant d'autres choses vous restent à découvrir.

Exemple de certificat de réussite
Exemple de certificat de réussite