MySQL 8.0 – SET PERSIST command

Hi guys. MySQL 8 DMR was released today and it has some cool new features. One of those is the ability to persist dynamic changed variables/configurations across restarts. It’s very usefull if you change variables dynamically. It’s saves you the trouble of edit a cnf file every time you run a SET on mysql (or even when you don’t have access to those files).

The new syntax will be as follow:

SET PERSIST option=value;
SET @@persist.option=value;

MySQL will create a new file named mysqld-auto.cnf located on it’s DATADIR folder. This file will contain all PERSISTENT variables and will be loaded after all other files (my.cnf / –defaults-file / ~/.my.cnf / …) Which means that values from mysqld-auto.cnf will take place in case of the same option be present on multiple files.

Those variables can be UNSET by setting it to it’s default value, or manually edit the file on disk(not advised).

This feature can be controlled by the persisted-globals-load config. It is set to ON by default. If you set it to OFF (persisted-globals-load=OFF), MySQL will ignore all variables present on mysqld-auto.cnf file.

Variables can be listed from performance_schema.variables_info table. On column variable_source we can filter the ones that are persistent or from other source:

  • COMPILED – Compiled on mysql source (default values)
  • GLOBAL – Part of global file
  • SERVER – Part of global $MYSQL_HOME/my.cnf file
  • EXPLICIT – Part of –defaults-file option file
  • EXTRA – Part of defaults-extra-file option file
  • USER – Part of ~/.my.cnf
  • LOGIN – Part of login path option file
  • COMMAND_LINE – Command line options
  • PERSISTED – part of persistent mysqld-auto.cnf file
  • DYNAMIC – variables set dynamically after server start

So we can get useful information from this table. For example, we can list all variables that were dynamically changed after the server started:

select * from performance_schema.variables_info where variable_source like 'DYNAMIC';

MySQL 8.0 is available for Download at http://dev.mysql.com/downloads/mysql/8.0.html. Be aware that it’s not a production release and should only be used for testing purposes. YET!

Give MySQL 8.0 a try !

Liked? Help and Share!
This entry was posted in MySQL. Bookmark the permalink.