MySQL 8.0: Persisted Variables

 

14 August, 2018Jesper Krogh

Contents

• Persisting Variables
• mysqld-auto.cnf and variables_info
• Backup and Restore
• PERSIST_ONLY Variables

Tweet

MySQL 8.0 introduced a new feature that allows you to persist configuration changes from inside MySQL. Previously you could execute SET GLOBAL to change the configuration at runtime, but you needed to update your MySQL configuration file in order to persist the change. In MySQL 8.0 you can skip the second step. This blog discuss how this works and how to backup and restore the configuration.

Using SET PERSIST to set a variable and the persisted_variables table in the Performance Schema to get a list of persisted variables.

Persisting Variables

You persist changes with either the SET PERSIST or SET PERSIST_ONLY statement. The different is that SET PERSIST_ONLY only updates the configuration whereas SET PERSISTessentially combines SET GLOBAL and SET PERSIST_ONLY.

Information

Some variables such as innodb_buffer_pool_instances can only use PERSIST_ONLY, i.e. it requires a restart to make the changes take effect. Still others, such as datadir, requires cryptographically signed SET statementswhich are available in MySQL 8.0.14 and later.

mysqld-auto.cnf and variables_info

The persisted variables are stored in the file mysqld-auto.cnf located in the data directory using the JSON format. It includes more information than just the persisted value. It also includes information such as who made the change and when. An example file is:

shell$ cat mysqld-auto.cnf { "Version" : 1 , "mysql_server" : { "sort_buffer_size" : { "Value" : "32768" , "Metadata" : { "Timestamp" : 1534230053297668 , "User" : "root" , "Host" : "localhost" } } , "join_buffer_size" : { "Value" : "131072" , "Metadata" : { "Timestamp" : 1534230072956789 , "User" : "root" , "Host" : "localhost" } } , "mysql_server_static_options" : { "slave_parallel_type" : { "Value" : "LOGICAL_CLOCK" , "Metadata" : { "Timestamp" : 1534230099583642 , "User" : "root" , "Host" : "localhost" } } } } }

Since it is JSON, it is easy to reformat to make easier to read, for example:

shell$ cat mysqld-auto.cnf | python -m json.tool {     "Version": 1,     "mysql_server": {         "join_buffer_size": {             "Metadata": {                 "Host": "localhost",                 "Timestamp": 1534230072956789,                 "User": "root"             },             "Value": "131072"         },         "mysql_server_static_options": {             "slave_parallel_type": {                 "Metadata": {                     "Host": "localhost",                     "Timestamp": 1534230099583642,                     "User": "root"                 },                 "Value": "LOGICAL_CLOCK"             }         },         "sort_buffer_size": {             "Metadata": {                 "Host": "localhost",                 "Timestamp": 1534230053297668,                 "User": "root"             },             "Value": "32768"         }     } }

This information is also available from the performance_schema.variables_info table:

mysql> SELECT VARIABLE_NAME, VARIABLE_SOURCE, sys.format_path(VARIABLE_PATH) AS Path,               SET_TIME, SET_USER, SET_HOST          FROM performance_schema.variables_info   WHERE VARIABLE_NAME IN ('join_buffer_size', 'slave_parallel_type', 'sort_buffer_size'); +---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+ | VARIABLE_NAME       | VARIABLE_SOURCE | Path                                      | SET_TIME                   | SET_USER | SET_HOST  | +---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+ | join_buffer_size    | DYNAMIC         |                                           | 2018-08-14 17:08:15.526750 | root     | localhost | | slave_parallel_type | PERSISTED       | @@datadir/mysqld-auto.cnf/mysqld-auto.cnf | 2018-08-14 17:01:39.583642 | root     | localhost | | sort_buffer_size    | PERSISTED       | @@datadir/mysqld-auto.cnf                 | 2018-08-14 17:00:53.297668 | root     | localhost | +---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+ 3 rows in set (0.36 sec)

Notice that the source for join_buffer_size is DYNAMIC whereas the two other variables have the source set to PERSISTED. Why? After all they all three existed in the mysqld-auto.cnf file. DYNAMIC means that the variable was changes since the last restart either using SET GLOBAL or SET PERSIST. Another thing to be aware of is that variables changed with SET PERSIST_ONLY will not show up in variables_info until after the next restart. I will soon get back to show a way to get the variables that have been persisted in one way or another.

Backup and Restore

As a simple way to back up the configuration is simply copy the mysqld-auto.cnf file to a safe location. Similarly, you can restore the configuration by copying it back.

However, what if you want most of the configuration but not everything or you want to edit some of the values? In that case you need another way of exporting the configuration as you should not manually edit mysqld-auto.cnf.

Warning

Do not edit the mysqld-auto.cnf file manually. It should only be changed with SET PERSIST and SET PERSIST_ONLY. If there are any errors in the file, MySQL will refuse to start.

Fortunately as it turns out, it is easy to export all persisted variables. The table performance_schema.persisted_variables includes all variables that has either been read from mysqld-auto.cnf or has been changed with SET PERSIST or SET PERSIST_ONLYsince the last restart. The table include the persisted values. For example:

mysql> SELECT * FROM performance_schema.persisted_variables; +---------------------+----------------+ | VARIABLE_NAME       | VARIABLE_VALUE | +---------------------+----------------+ | sort_buffer_size    | 32768          | | join_buffer_size    | 131072         | | slave_parallel_type | LOGICAL_CLOCK  | +---------------------+----------------+ 3 rows in set (0.01 sec)

This can be used to create SET statements that can be used to recreate the configuration on another instance. For example:

1 2 3 4 5 6

SELECT CONCAT('SET PERSIST_ONLY ', VARIABLE_NAME, ' = ',               IF(VARIABLE_VALUE REGEXP '^([0-9]+|ON|OFF|YES|NO)$',                  VARIABLE_VALUE,                  QUOTE(VARIABLE_VALUE)), ';'              ) AS SetStmt   FROM performance_schema.persisted_variables;

Using the mysql command-line client, you can avoid the column names and table format by using the --skip-column-names and --batch options:

shell$ mysql --skip-column-names --batch \              -e "SELECT CONCAT('SET PERSIST_ONLY ', VARIABLE_NAME, ' = ', IF(VARIABLE_VALUE REGEXP '^([0-9]+|ON|OFF|YES|NO)$', VARIABLE_VALUE, QUOTE(VARIABLE_VALUE)), ';') FROM performance_schema.persisted_variables;" \              > config.sql

Note

On Microsoft Windows ensure everything is on one line and the backslashes are removed.

Now the file config.sql contains an export of the persisted variables:

shell$ cat config.sql SET PERSIST_ONLY sort_buffer_size = 32768; SET PERSIST_ONLY join_buffer_size = 131072; SET PERSIST_ONLY slave_parallel_type = 'LOGICAL_CLOCK';

This example creates SET PERSIST_ONLY statement as those will work with all persistable variables. When you replay the SET statements, it will require a restart of MySQL for the changes to take effect. If you want to use SET PERSIST where possible, then you need to take into consideration whether the variable support SET PERSIST.  A list of variables that require SET PERSIST_ONLY are included at the end.

The configuration can now be restored as:

mysql> SOURCE config.sql Query OK, 0 rows affected (0.00 sec)   Query OK, 0 rows affected (0.00 sec)   Query OK, 0 rows affected (0.01 sec)   mysql> RESTART; Query OK, 0 rows affected (0.00 sec)

PERSIST_ONLY Variables

As promised, I will conclude with a list of persistable variables that only supports SET PERSIST_ONLY. As of MySQL 8.0.12 without any plugins installed, the variables are:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107

back_log binlog_gtid_simple_recovery disabled_storage_engines disconnect_on_expired_password ft_max_word_len ft_min_word_len ft_query_expansion_limit innodb_adaptive_hash_index_parts innodb_api_disable_rowlock innodb_api_enable_binlog innodb_api_enable_mdl innodb_autoinc_lock_mode innodb_buffer_pool_chunk_size innodb_buffer_pool_instances innodb_doublewrite innodb_flush_method innodb_force_recovery innodb_ft_aux_table innodb_ft_cache_size innodb_ft_min_token_size innodb_ft_server_stopword_table innodb_ft_sort_pll_degree innodb_ft_total_cache_size innodb_ft_user_stopword_table innodb_log_file_size innodb_log_files_in_group innodb_monitor_disable innodb_monitor_enable innodb_monitor_reset innodb_monitor_reset_all innodb_numa_interleave innodb_open_files innodb_page_cleaners innodb_purge_threads innodb_read_io_threads innodb_rollback_on_timeout innodb_sort_buffer_size innodb_sync_array_size innodb_tmpdir innodb_use_native_aio innodb_write_io_threads large_pages log_slave_updates log_syslog log_syslog_facility log_syslog_include_pid log_syslog_tag lower_case_table_names max_digest_length metadata_locks_cache_size metadata_locks_hash_instances myisam_mmap_size myisam_recover_options mysqlx_bind_address mysqlx_port mysqlx_port_open_timeout mysqlx_socket mysqlx_ssl_ca mysqlx_ssl_capath mysqlx_ssl_cert mysqlx_ssl_cipher mysqlx_ssl_crl mysqlx_ssl_crlpath mysqlx_ssl_key ngram_token_size old open_files_limit performance_schema performance_schema_digests_size performance_schema_error_size performance_schema_events_stages_history_long_size performance_schema_events_stages_history_size performance_schema_events_statements_history_long_size performance_schema_events_statements_history_size performance_schema_events_transactions_history_long_size performance_schema_events_transactions_history_size performance_schema_events_waits_history_long_size performance_schema_events_waits_history_size performance_schema_max_cond_classes performance_schema_max_digest_length performance_schema_max_file_classes performance_schema_max_file_handles performance_schema_max_memory_classes performance_schema_max_mutex_classes performance_schema_max_rwlock_classes performance_schema_max_socket_classes performance_schema_max_sql_text_length performance_schema_max_stage_classes performance_schema_max_statement_classes performance_schema_max_statement_stack performance_schema_max_thread_classes performance_schema_session_connect_attrs_size rbr_exec_mode relay_log_recovery relay_log_space_limit report_host report_password report_port report_user skip_name_resolve skip_show_database slave_skip_errors ssl_cipher table_open_cache_instances thread_handling thread_stack tls_version

Tweet