La version 8 de MySQL a vu apparaître une nouvelle fonctionnalité qui permet de limiter l’usage des fichiers my.cnf.
Il n’est en théorie presque plus nécessaire de faire des modifications dans le fichier my.cnf. La plupart des paramètres peuvent être passés via une requête SQL, ce qui permet de le faire à distance. C’est un point intéressant pour les solutions cloud ou hébergement.
MySQL peut lire un certain nombre de fichiers dans un certain ordre et dépend des systèmes d’exploitation :
Par défaut
/etc/my.cnf
~/my.cnf ou ~/.my.cnf
$datadir/my.cnf
Avant systemd, le script fourni pouvait aussi lire un my.cnf dans le basedir, là où se trouvent les binaires.
Dans certains cas, des paramètres sont présents un peu partout et certains administrateurs ne comprennent pas pourquoi tel ou tel paramètre n’est pas pris en compte.
Nous allons voir comment faire une conversion des variables pour avoir une gestion automatisée (depuis un fichier de configuration issu d’un cluster innodb, mais le principe sera toujours le même.
Certaines valeurs ne sont peut-être pas optimales mais c’est simplement pour l’exemple) :
Le fichier d’origine :
datadir=/work/mysql/data socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid server_id = 1 table_open_cache = 6000 sort_buffer_size = 1M binlog_cache_size = 64k binlog_stmt_cache_size = 64k innodb_purge_threads=2 #linux only innodb_flush_method=O_DIRECT innodb_log_buffer_size = 128M innodb_log_file_size = 1G innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G max_heap_table_size = 128M tmp_table_size = 128M # Variables d'environnement character_set_server=utf8 collation_server=utf8_unicode_ci #options pour innodb cluster binlog_checksum = NONE enforce_gtid_consistency = 1 gtid_mode = ON log_bin=binlog relay_log = relay_log log_slave_updates = 1 master_info_repository = TABLE relay_log_info_repository = TABLE transaction_write_set_extraction = XXHASH64 binlog_format = ROW disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE report_port = 3306 # Gestion des logs binaires par rapport a un nombre de jours (on garde 5 jours de logs) expire_logs_days=5 # sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ANSI" ### # parametres recuperes de dsi-sap-ew ### max_allowed_packet=512M wait_timeout = 6000 group_concat_max_len=20480
Certains paramètres ne sont pas modifiables via SET PERSIST, pour des raisons simples. Le fichier généré mysqld-auto.cnf se trouve par défaut dans le datadir, cependant pour le lire encore faut-il que MySQL sache où se situe le datadir si ce n’est pas l’emplacement par défaut.
Cette liste de variables qui ne peuvent être positionnées que dans le my.cnf :
Pour le reste nous allons voir comment faire la conversion.
Une tentative de modification d’un paramètre non « persistable » :
mysql> set persist log_error='/var/log/mysqld.log';
ERROR 1238 (HY000): Variable 'log_error' is a read only variable
Cependant certaines variables en read_only peuvent être persistées sans être modifiées à chaud avec le mot clé PERSIST_ONLY, un exemple :
set persist_only innodb_log_file_size = 1*1024*1024*1024;
On prend les variables une à une (il est cependant possible de faire un script) :
mysql> set persist server_id=1;
Query OK, 0 rows affected (0,00 sec)
mysql> set persist table_open_cache = 6000;
Query OK, 0 rows affected (0,00 sec)
mysql> set persist sort_buffer_size = 1M;
ERROR 1232 (42000): Incorrect argument type to variable ‘sort_buffer_size’
Une nouvelle erreur apparaît, elle est liée au fait qu’en SQL il n’est pas possible de préciser une unité (le problème est général aux commandes SET, ce qui est un peu dommage). Il est donc nécessaire de faire une adaptation toute simple :
mysql> set persist sort_buffer_size = 1*1024*1024;
Query OK, 0 rows affected (0,00 sec)
On multiplie simplement par 1024 pour chaque unité que l’on souhaite, la base de départ étant l’octet :
1 octet
1*1024 => 1ko
1*1024*1024 => 1Mo
1*1024*1024*1024 => 1G etc.
En avançant, une nouvelle erreur apparaît, c’est un cas particulier parce que nous sommes sur un cluster innodb uniquement (idem pour un Group Replication seul).
mysql> set persist transaction_write_set_extraction = XXHASH64;
ERROR 3093 (HY000): The write set algorithm cannot be changed when Group replication is running.
Dans notre cas nous ne souhaitons pas faire le changement de ce paramètre à cause des impacts potentiels, bien que cette valeur soit déjà positionnée.
On avance encore :
mysql> set persist expire_logs_days=5; Query OK, 0 rows affected, 1 warning (0,00 sec) mysql> show warnings; +---------+------+----------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | '@@expire_logs_days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead. | +---------+------+----------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec)
Ce paramètre pose problème car il est déprécié. Il est remplacé par binlog_expire_logs_second. Nous avons voulu le vérifier avec un redémarrage de l’instance, et cette dernière refuse de démarrer car les deux paramètres sont positionnés et créent un conflit visible uniquement au redémarrage. C’est regrettable, surtout dans la mesure ou il est déconseillé de faire des modifications du fichier mysqld-auto.cnf à la main, mais c’est ce que nous avons dû faire pour relancer l’instance. Les données de ce fichier sont au format JSON et toute erreur de syntaxe empêche le redémarrage.
Une fois tous les paramètres passés avec SET PERSIST vous pouvez supprimez toutes les lignes correspondantes du ou des fichier(s) de configuration my.cnf. Redémarrez ensuite l’instance pour vérification. L’expérience nous a montré qu’il est préférable de le faire.
Si jamais vous souhaitez revenir à la valeur par défaut sans avoir à relire la documentation, vous pouvez utiliser la valeur DEFAULT :
SET PERSIST sort_buffer_size = DEFAULT;
Cependant l’enregistrement sera toujours présent dans le fichier mysqld-auto.cnf car tous les changements sont horodatés ce qui est intéressant afin de voir à quel moment les changements de configuration ont été réalisés.
Il est aussi possible de faire un « reset » du fichier mysqld-auto.cnf avec la commande RESET PERSIST
Pour vérifier le positionnement des variables et depuis quels fichiers elles sont lues, il existe une vue avec toutes les informations y compris les utilisateurs et les machines depuis lesquels ont été réalisées les modifications :
select VARIABLE_NAME,VARIABLE_SOURCE,VARIABLE_PATH,SET_TIME,SET_USER,SET_HOST from performance_schema.variables_info +----------------------------+-----------------+----------------------------+----------------------------+------+-----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | SET_TIME | USER| SET_HOST | +----------------------------+-----------------+----------------------------+----------------------------+------+-----------+ | auto_increment_increment | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-06 12:41:18.547034 | root | server2 | | auto_increment_offset | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-06 12:41:18.541982 | root | server1 | | binlog_cache_size | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-09 15:03:50.737455 | root | localhost | | binlog_expire_logs_seconds | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-09 15:41:19.014268 | root | localhost | | binlog_format | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-09 15:09:20.864440 | root | localhost | | binlog_stmt_cache_size | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-09 15:04:17.019304 | root | localhost | | character_set_server | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-09 15:06:26.631685 | root | localhost | | collation_server | PERSISTED | /work/mysql/data/mysqld-auto.cnf | 2018-08-09 15:06:55.841685 | root | localhost | | datadir | GLOBAL | /etc/my.cnf | NULL | NULL | NULL | +----------------------------+-----------+----------------------------------+----------------------------+------+-----------+
On peut voir toutes les informations associées aux variables depuis cette vue.