|
PS: This article is based on MySQL8.0.0. Currently the only the first developer version has been released, so it may be subject to subsequent changes.
I believe many may have such an experience: the dynamic modifications to some configuration items at the instance startup are not implemented in the configuration file. As a result, the changes are lost once the instance is restarted. Or for services deployed on the cloud (such as RDS), usually users do not have the permission to modify the configuration file, leading to inflexible configuration settings. Now, with MySQL8.0, you don't need to worry about this problem, as you can select to make the configurations persistent. Corresponding worklog: WL#8688 Enable this function to use specific syntax SET PERSIST to set any global variables for dynamic modifications, for example: mysql> SET PERSIST innodb_flush_log_at_trx_commit = 1; Query OK, 0 rows affected (0.00 sec) mysql> SET PERSIST sync_binlog = 1; Query OK, 0 rows affected (0.00 sec) At this time, check the default my.cnf file and you will find no modifications. But in the data directory, a file with a different version from the previous MySQL version appears: $ls -lh mysqld-auto.cnf -rw-r----- 1 zhaiwx users 81 Sep 17 18:09 mysqld-auto.cnf It's in JSON format, so it can be parsed by the embedded parser. $cat mysqld-auto.cnf { "mysql_server": {"innodb_flush_log_at_trx_commit": "1" , "sync_binlog": "1" } } The configuration in mysqld-auto.cnf is of a higher priority than that in my.cnf. If the same configuration appears in both files, the log in mysqld-auto.cnf file should be followed. Note: even if the configuration value you modified through SET PERSIST contains no changes, it will be logged into the mysqld-auto.cnf file. But you can remove the log from the mysqld-auto.cnf file through setting the value to the default value: mysql> SET PERSIST innodb_flush_log_at_trx_commit = DEFAULT; Query OK, 0 rows affected (0.00 sec) ===== $cat mysqld-auto.cnf { "mysql_server": {"sync_binlog": "1" } } You can identify the source of configuration item values through a new PS table performance_schema.variables_info. It mainly involves the following categories: enum enum_variable_source { COMPILED= 1, GLOBAL, SERVER, EXPLICIT, EXTRA, MYSQL_USER, LOGIN, COMMAND_LINE, PERSISTED, DYNAMIC }; System table code: storage/perfschema/table_variables_info.cc ================== mysql> select * from performance_schema.variables_info where variable_source = 'PERSISTED'\G *************************** 1. row *************************** VARIABLE_NAME: sync_binlog VARIABLE_SOURCE: PERSISTED VARIABLE_PATH: /u01/my80/data/mysqld-auto.cnf MIN_VALUE: 0 MAX_VALUE: 4294967295 1 row in set (0.00 sec) If you don't want the configuration in mysqld-auto.cnf to take effect, you can disable the read-only parameter persisted_globals_load at startup, so that configuration items in mysqld-auto.cnf won't be loaded. Main code: Commit f2bc0f89b7f94cc8fe963d08157413a01d14d994 Main entry function (8.0.0): The interface functions are mostly defined in sql/persisted_variable.cc: Load content of mysqld-auto.cnf at startup: Persisted_variables_cache::load_persist_file(); Parse validity through JSON and save to memory Set the configurations read from the file: Persisted_variables_cache::set_persist_options When you run the SET PERSIST command, call Persisted_variables_cache::set_variable to update the value in the memory Write the value to the mysqld-auto.cnf file: Persisted_variables_cache::flush_to_file |
|
