MySQL 8.0引入了一项新功能，允许您从MySQL内部持久化配置更改。以前您可以执行SET GLOBAL以在运行时更改配置，但您需要更新MySQL配置文件以保持更改。在MySQL 8.0中，您可以跳过第二步。本博客讨论了它的工作原理以及如何备份和恢复配置。
使用SET PERSIST在Performance Schema中设置变量和persisted_variables表以获取持久变量列表。
坚持变量
您可以使用SET PERSISTor SET PERSIST_ONLY语句保留更改。不同的是，SET PERSIST_ONLY只更新配置，而SET PERSIST基本上组合SET GLOBAL和SET PERSIST_ONLY。
注意：某些变量如innodb_buffer_pool_instances只能使用PERSIST_ONLY，即需要重启才能使更改生效。还有一些，比如datadir目前还没有坚持下去。
mysqld-auto.cnf和variables_info
持久化变量mysqld-auto.cnf使用JSON格式存储在数据目录中的文件中。它包含的信息不仅仅是持久值。它还包括诸如谁做出改变以及何时做出改变等信息。示例文件是：
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" } } } } }
由于它是JSON，因此很容易重新格式化以便于阅读，例如：
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"
}
}
}
此信息也可从performance_schema.variables_info表中获得：
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 | | 2026-03-13T15:24:41+00:00.526750 | root | localhost |
| slave_parallel_type | PERSISTED | @@datadir/mysqld-auto.cnf/mysqld-auto.cnf | 2026-03-13T15:24:41+00:00.583642 | root | localhost |
| sort_buffer_size | PERSISTED | @@datadir/mysqld-auto.cnf | 2026-03-13T15:24:41+00:00.297668 | root | localhost |
+---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+
3 rows in set (0.36 sec)
请注意，源join_buffer_size是，DYNAMIC而另外两个变量的源设置为PERSISTED。为什么？毕竟他们三个都存在于mysqld-auto.cnf文件中。DYNAMIC意味着变量是自上次重启以来使用SET GLOBAL或更改的SET PERSIST。另一件需要注意的事情是，变更后的变量在下次重启之后SET PERSIST_ONLY才会显示出来variables_info。我很快就会回过头来展示一种方法来获取以某种方式持久存在的变量。
备份还原
备份配置的简单方法是将mysqld-auto.cnf文件复制到安全位置。同样，您可以通过复制来恢复配置。
但是，如果您想要大部分配置而不是所有配置或者您想要编辑某些值，该怎么办？在这种情况下，您需要另一种导出配置的方法，因为您不应手动编辑mysqld-auto.cnf。
警告：请勿mysqld-auto.cnf手动编辑文件。它应该只用SET PERSIST和改变SET PERSIST_ONLY。如果文件中有任何错误，MySQL将拒绝启动。
幸运的是，事实证明，导出所有持久变量很容易。表performance_schema.persisted_variables包括所有已从上次重新启动或自上次重新启动以来已读取mysqld-auto.cnf或已更改的变量。该表包含持久值。例如：SET PERSISTSET PERSIST_ONLY
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)
这可用于创建SET可用于在另一个实例上重新创建配置的语句。例如：
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;
使用mysql命令行客户端，可以使用--skip-column-names和--batch选项来避免列名和表格式：
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
注意：在Microsoft Windows上，确保所有内容都在一行上，并删除反斜杠。
现在该文件config.sql包含持久变量的导出：
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';
此示例创建SET PERSIST_ONLY语句，因为它们将与所有可持久变量一起使用。重放SET语句时，需要重新启动MySQL才能使更改生效。如果您想尽可能使用SET PERSIST，那么您需要考虑变量是否支持SET PERSIST。SET PERSIST_ONLY最后包含需要的变量列表。
现在可以将配置恢复为：
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变量
正如所承诺的那样，我将以一系列仅支持的可持续变量结束SET PERSIST_ONLY。从没有安装任何插件的MySQL 8.0.12开始，变量是：
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