Configuration et options

• Informations sur la base de données et les requêtes
• Sommaire

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
• Modification des variables système avec SET
• Options au démarrage du client mysql
• Options au démarrage du serveur mysqld
• Fichiers de configuration

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.

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

1

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.

1

SHOW VARIABLES LIKE '%auto_increment%';

Exemple 2 : affichage de la valeur de unique_checks.

1

SHOW VARIABLES LIKE 'unique_checks';

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

Exemple

1

SELECT @@autocommit;

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

1
2
3
4
5

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 SELECT, et 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.

1
2
3

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 :

1
2
3

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

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.

1
2

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;

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

1
2

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

1
2

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 donc capable.

Deux syntaxes sont possibles avec SET :

1
2
3
4
5

SET niveau nom_variable = valeur;

-- OU

SET @@niveau.nom_variable = valeur;

Exemples

1
2

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

1
2
3
4

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 :

1

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

1

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

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

1

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.

1

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.

1
2
3
4
5

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

#OU

mysql -h localhost -u sdz -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.

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

1

mysql -u sdz -p elevage --skip-column-names

1
2

SELECT id, nom, espece_id, prix
FROM Race;

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.

Mais si ! Au tout 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) :

1

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 :

1

mysqld

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

1

mysqld_safe

Les options du serveur

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

Quelques exemples :

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.

1

mysqld -C greek

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

1

SHOW GLOBAL VARIABLES LIKE 'character_set_server';

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.

Pour Linux et MacOS

Les emplacements suivants sont parcourus, dans l'ordre.

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 :

Exemple

1

mysql -u sdz -p -default-extra-file=/Users/taguan/Documents/SdZ/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 qu'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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

#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

1
2
3
4
5
6
7
8
9

[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 la 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.

• Informations sur la base de données et les requêtes
• Sommaire