0

Nouveautés MySQL 8.0 : Variables persistés

twitterlinkedinmail

La nouvelle version MySQL 8.0 est sortie en Septembre 2016 et la dernière version disponible sortie en Février 2019 est la 8.0.15

Elle implémente beaucoup de nouvelles fonctionnalités et améliorations comme :

– Variables persistés (set persist)
– Dictionnaire de données transactionnel
– Support des rôles SQL
– Common Table Expression (CTE)
– Fonctions de fenêtrage (windowing)
– Indexes descendants et invisibles
– Histogrammes – Statistiques
– Groupes de Ressources
– Nouveau character set UTF8MB4 (emojis)
– Configuration automatique de variables (innodb_dedicated_server)

Ce premier article se fera sur les variables persistés en MySQL 8.0.14

MySQL permet de modifier les paramètres via des fichiers de configuration comme my.cnf ou my.ini, les modifications prenant effet au prochain redémarrage du serveur MySQL.
Cependant, cela signifie le redémarrage du serveur et cela est devenu de moins en moins facile car cela augmente le temps d’indisponibilité du serveur MySQL.

Grâce à SET PERSIST, on peut modifier de nombreuses variables avec un effet visible et cela survivra au redémarrage du serveur. De plus, elle permet de gérer les paramètres du serveur lorsque la modification du fichier de configuration (my.ini) est compliquée, par exemple à cause des droits du fichier ou quand on n’a pas accès au système de fichiers dans le Cloud.

MySQL 8.0 permet aussi avec SET PERSIST de définir la plupart des variables en lecture seule. Les nouvelles valeurs seront prises en compte lors du prochain démarrage du serveur.
Cependant, il reste certaines variables en lecture seule qui ont été laissées intentionnellement non réglables.

Toute variable GLOBAL ou DYNAMIC peut être modifiée avec l’instruction SET PERSIST. Les paramètres seront conservés dans un fichier mysqld-auto.cnf qui sera créé dans le datadir lors de la première exécution SET PERSIST.
Ce fichier sera lu lors du démarrage du serveur et il sera le dernier fichier à être appliqué au démarrage. Il sera donc prioritaire au fichier my.ini et aux options en ligne de commande.

Cette fonctionnalité permet également d’avoir une meilleur gestion des opérations manuelles sur les fichiers de configuration et de déployer plus facilement les configurations dans des bases sur le Cloud par exemple.

 

Voici plusieurs exemples montrant le changement de paramètres en utilisant ou non la fonctionnalité, suivi d’un redémarrage du serveur MySQL sur Windows 7.

Avant modification :

mysql> select @@global.version;
+-----------+
| @@version |
+-----------+
| 8.0.14    |
+-----------+
1 row in set (0.00 sec)

mysql> select @@global.max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 151 |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@global.wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
| 28800 |
+----------------+
1 row in set (0.00 sec)

mysql> select @@global.sort_buffer_size;
+--------------------+
| @@sort_buffer_size |
+--------------------+
| 262144 |
+--------------------+
1 row in set (0.00 sec)

mysql> select @@global.tmpdir;
+-------------------------------------------------+
| @@tmpdir |
+-------------------------------------------------+
| C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp |
+-------------------------------------------------+
1 row in set (0.00 sec)

 

L’ajout d’un nouveau fichier de configuration pour modifier les configurations de variable avec SET PERSIST, peut nous emmêler les pinceaux quand il faut savoir si une variable a été modifiée dans le fichier de configuration my.ini ou mysqld-auto.cnf.

C’est pourquoi, une nouvelle table de performance_schema appelée variables_info est également implémentée en MySQL 8. Celle-ci va nous permettre de voir la source et la configuration des variables qui ont été mise en place.

On peut y voir les différentes variables avec leur source (explicit ou compiled), ainsi que le chemin de la variable si elles utilisent my.ini :

mysql> select * from performance_schema.variables_info where variable_name like 'max_conn%';
+--------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+--------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+
| max_connect_errors | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 1 | 4294967295 | NULL | NULL | NULL |
| max_connections | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 1 | 100000 | NULL | NULL | NULL |
+--------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+
2 rows in set (0.01 sec)

mysql> select * from performance_schema.variables_info where variable_name like 'wait_timeout';
+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+
| wait_timeout | COMPILED | | 1 | 2147483 | NULL | NULL | NULL |
+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.variables_info where variable_name like 'sort_buffer_size';
+------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+
| sort_buffer_size | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 32768 | 4294967295 | NULL | NULL | NULL |
+------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.variables_info where variable_name like 'tmpdir';
+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+
| tmpdir | COMPILED | | 0 | 0 | NULL | NULL | NULL |
+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+
1 row in set (0.01 sec)

mysql> SET PERSIST max_connections=50;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST wait_timeout=600;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST sort_buffer_size=524288;

mysql> SET PERSIST tmpdir='C:/Tmp/tmpdir';
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable

mysql> SET GLOBAL tmpdir='C:/Tmp/tmpdir';
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable

Comme dit précédemment avant les tests, il semblerait que plusieurs variables (avec des chiffres) de configuration de type EXPLICIT peuvent être modifiées de manière dynamique mais pas toutes, comme certaines de type COMPILED pour des modifications de répertoires (tmpdir : ne marche pas avec SET PERSIST | wait_timeout : marche avec SET PERSIST).

 

Puisque la variable tmpdir ne peut pas être modifié de manière dynamique avec SET PERSIST ou SET GLOBAL, il nous faut donc ajouter son entrée dans le fichier de configuration C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Modifier le fichier de configuration my.ini et ajouter une entrée pour tmpdir :

# Path to the temporary tables
tmpdir=C:/Tmp/tmpdir

 

Redémarrer le serveur MySQL ou bien le Service Windows MySQL pour la prise en compte des nouvelles valeurs pour les paramètres persistés, ici le paramètre tmpdir dans le fichier my.ini.

mysql> restart;
Query OK, 0 rows affected (0.00 sec)

 

Après modification :

On voit bien la bonne prise en compte des variables modifiées.

mysql> select global.max_connections;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
mysql> select @@global.max_connections;
+-------------------+
| @@max_connections |
+-------------------+
| 50 |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@global.wait_timeout;
+-----------------------+
| @@global.wait_timeout |
+-----------------------+
| 600 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select @@global.sort_buffer_size;
+---------------------------+
| @@global.sort_buffer_size |
+---------------------------+
| 524288 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select @@global.tmpdir;
+-----------------+
| @@global.tmpdir |
+-----------------+
| C:/Tmp/tmpdir |
+-----------------+
1 row in set (0.00 sec)

 

On peut voir les changements dans la source, le chemin et la date des variables modifiées avec SET PERSIST pointant vers mysqld-auto.cnf ainsi que celle en EXPLICIT dans le fichier my.ini :

mysql> select * from performance_schema.variables_info where variable_name like 'max_conn%';
+--------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+--------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+
| max_connect_errors | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 1 | 4294967295 | NULL | NULL | NULL |
| max_connections | PERSISTED | C:\ProgramData\MySQL\MySQL Server 8.0\Data\mysqld-auto.cnf | 1 | 100000 | 2019-02-15 17:48:57.277699 | root | localhost |
+--------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+
2 rows in set (0.01 sec)

mysql> select * from performance_schema.variables_info where variable_name like 'wait_timeout';
+---------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+---------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| wait_timeout | PERSISTED | C:\ProgramData\MySQL\MySQL Server 8.0\Data\mysqld-auto.cnf | 1 | 2147483 | 2019-02-15 17:49:07.287713 | root | localhost |
+---------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.variables_info where variable_name like 'sort_buffer_size';
+------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+
| sort_buffer_size | PERSISTED | C:\ProgramData\MySQL\MySQL Server 8.0\Data\mysqld-auto.cnf | 32768 | 4294967295 | 2019-02-18 11:16:13.401398 | root | localhost |
+------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.variables_info where variable_name like 'tmpdir';
+---------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+---------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
| tmpdir | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL |
+---------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
1 row in set (0.01 sec)

 

Une variable persistante peut être mise en place avec une certaine valeur mais pour autant, on voudrait remettre la valeur par défaut de celle-ci mais on ne s’en souvient plus.

Ce n’est pas grave car on peut réinitialiser la variable en lui attribuant la valeur DEFAULT. Cela va l’enlever de mysqld-auto.cnf et remettre la configuration précédente du fichier de configuration my.ini

Voici un exemple de changement de la variable thread_cache_size avec SET PERSIST, puis d’un retour arrière avec DEFAULT :

mysql> select @@global.thread_cache_size;
+----------------------------+
| @@global.thread_cache_size |
+----------------------------+
| 10 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SET PERSIST thread_cache_size=30;
Query OK, 0 rows affected (0.02 sec)

mysql> restart;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.thread_cache_size;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***

+----------------------------+
| @@global.thread_cache_size |
+----------------------------+
| 30 |
+----------------------------+
1 row in set (0.05 sec)

mysql> select * from performance_schema.variables_info where variable_name like 'thread_cache_size';
+-------------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+-------------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| thread_cache_size | PERSISTED | C:\ProgramData\MySQL\MySQL Server 8.0\Data\mysqld-auto.cnf | 0 | 16384 | 2019-02-18 15:36:25.584870 | root | localhost |
+-------------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
1 row in set (0.00 sec)

mysql> SET PERSIST thread_cache_size=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> restart;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.thread_cache_size;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***

+----------------------------+
| @@global.thread_cache_size |
+----------------------------+
| 9 |
+----------------------------+
1 row in set (0.05 sec)

 

Pour autant, un Bug existait en 8.0 et 8.1 sur les variables persistés concernant general_log et long_slow_query : https://bugs.mysql.com/bug.php?id=82807

Après un test sur la variable general_log, il s’avère que le bug ait été corrigé car la valeur est bien enregistrée dans mysqld-auto.cnf après un restart :

mysql> select @@global.general_log;
+----------------------+
| @@global.general_log |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';
+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
| general_log | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL |
| slow_query_log | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL |
+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
2 rows in set (0.01 sec)

mysql> set persist general_log=1;
Query OK, 0 rows affected (0.02 sec)

mysql> select @@global.general_log;
+----------------------+
| @@global.general_log |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)

mysql> restart;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.general_log;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***

+----------------------+
| @@global.general_log |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.05 sec)

mysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';
+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| general_log | PERSISTED | C:\ProgramData\MySQL\MySQL Server 8.0\Data\mysqld-auto.cnf | 0 | 0 | 2019-02-18 16:16:39.149481 | root | localhost |
| slow_query_log | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL |
+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
2 rows in set (0.01 sec)

 

Cependant, si vous souhaitez supprimer toutes les variables persistées de mysqld-auto.cnf, vous devez utiliser RESET PERSIST.

Par contre, si vous souhaitez supprimer une variable spécifique de mysqld-auto.cnf, vous devez mettre le nom de la variable après RESET PERSIST.

Par exemple avec la variable general_log :

mysql> reset persist general_log;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';
+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
| general_log | PERSISTED | C:\ProgramData\MySQL\MySQL Server 8.0\Data\mysqld-auto.cnf | 0 | 0 | 2019-02-18 16:16:39.149481 | root | localhost |
| slow_query_log | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL |
+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+
2 rows in set (0.01 sec)

mysql> restart;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***

+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |
+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
| general_log | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL |
| slow_query_log | EXPLICIT | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini | 0 | 0 | NULL | NULL | NULL |
+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+
2 rows in set (0.07 sec)

mysql> select @@global.general_log;
+----------------------+
| @@global.general_log |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)

 

Vous en savez donc plus sur cette nouveauté SET PERSIST de MySQL 8.0 qui fait beaucoup penser à l’option scope=spfile lors de la modification d’une variable système sur une base Oracle.

 

De prochains articles sur les autres nouveautés de MySQL 8.0 seront testés et mise en lignes sur le blog Capdata.

Restez donc informé des derniers articles en vous abonnant à la Newsletter 😉

Erwan Ollitrault

Continuez votre lecture sur le blog :

twitterlinkedinmail

Capdata team

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.