PERSIST_ONLY enable global system variables to be
persisted to the
file in the data directory (see Section 184.108.40.206, “SET Syntax for Variable Assignment”).
However, not all system variables can be persisted, or can be
persisted only under certain restrictive conditions. Here are
some reasons why a system variable might be nonpersistible or
Session system variables cannot be persisted. Session variables cannot be set at server startup, so there is no reason to persist them.
A global system variable might involve sensitive data such that it should be settable only by a user with direct access to the server host.
A global system variable might be read only (that is, set only by the server). In this case, it cannot be set by users at all, whether at server startup or at runtime.
A global system variable might be intended only for internal use.
Nonpersistible system variables cannot be persisted under any
circumstances. As of MySQL 8.0.14, persist-restricted system
variables can be persisted with
PERSIST_ONLY, but only by users for which the
following conditions are satisfied:
persist_only_admin_x509_subjectsystem variable is set to an SSL certificate X.509 Subject value.
The user connects to the server using an encrypted connection and supplies an SSL certificate with the designated Subject value.
is read only and set only by the server, so it cannot be
persisted under any circumstances. On the other hand,
persist-restricted, so it can be set by users who satisfy the
The following system variables are nonpersistible. This list may change with ongoing development.
audit_log_current_session audit_log_filter_id character_set_system core_file have_statement_timeout have_symlink hostname innodb_version large_files_support large_page_size license locked_in_memory log_bin log_bin_basename log_bin_index lower_case_file_system ndb_version ndb_version_string persist_only_admin_x509_subject persisted_globals_load protocol_version relay_log_basename relay_log_index server_uuid skip_external_locking system_time_zone version_comment version_compile_machine version_compile_os version_compile_zlib
Persist-restricted system variables are those that are read only
and can be set on the command line or in an option file, other
This list may change with ongoing development.
audit_log_file audit_log_format auto_generate_certs basedir bind_address caching_sha2_password_auto_generate_rsa_keys caching_sha2_password_private_key_path caching_sha2_password_public_key_path character_sets_dir daemon_memcached_engine_lib_name daemon_memcached_engine_lib_path daemon_memcached_option datadir default_authentication_plugin ft_stopword_file init_file innodb_buffer_pool_load_at_startup innodb_data_file_path innodb_data_home_dir innodb_dedicated_server innodb_directories innodb_force_load_corrupted innodb_log_group_home_dir innodb_page_size innodb_read_only innodb_temp_data_file_path innodb_temp_tablespaces_dir innodb_undo_directory innodb_undo_tablespaces keyring_encrypted_file_data keyring_encrypted_file_password lc_messages_dir log_error mecab_rc_file named_pipe pid_file plugin_dir port relay_log relay_log_info_file secure_file_priv sha256_password_auto_generate_rsa_keys sha256_password_private_key_path sha256_password_public_key_path shared_memory shared_memory_base_name skip_networking slave_load_tmpdir socket ssl_ca ssl_capath ssl_cert ssl_crl ssl_crlpath ssl_key tmpdir version_tokens_session_number
To configure the server to enable persisting persist-restricted system variables, use this procedure:
Ensure that MySQL is configured to support encrypted connections. See Section 6.3.1, “Configuring MySQL to Use Encrypted Connections”.
Designate an SSL certificate X.509 Subject value that signifies the ability to persist persist-restricted system variables, and generate a certificate that has that Subject. See Section 6.3.3, “Creating SSL and RSA Certificates and Keys”.
Start the server with
persist_only_admin_x509_subjectset to the designated Subject value. For example, put these lines in your server
You must perform this step directly on the MySQL server host because
persist_only_admin_x509_subjectitself cannot be persisted at runtime.
Restart the server.
Distribute the SSL certificate that has the designated Subject value to users who are to be permitted to persist persist-restricted system variables.
myclient-cert.pem is the SSL
certificate to be used by clients who can persist
persist-restricted system variables. Display the certificate
contents using the openssl command:
shell> openssl x509 -text -in myclient-cert.pem Certificate: Data: Version: 3 (0x2) Serial Number: 2 (0x2) Signature Algorithm: md5WithRSAEncryption Issuer: C=US, ST=IL, L=Chicago, O=MyOrg, OU=CA, CN=MyCN Validity Not Before: Oct 18 17:03:03 2018 GMT Not After : Oct 15 17:03:03 2028 GMT Subject: C=US, ST=IL, L=Chicago, O=MyOrg, OU=client, CN=MyCN ...
The openssl output shows that the certificate Subject value is:
C=US, ST=IL, L=Chicago, O=MyOrg, OU=client, CN=MyCN
To specify the Subject for MySQL, use this format:
Configure the server
my.cnf file with the
Restart the server so that the new configuration takes effect.
Distribute the SSL certificate (and any other associated SSL files) to the appropriate users. Such a user then connects to the server with the certificate and any other SSL options required to establish an encrypted connection.
To use X.509, clients must specify the
--ssl-cert options to connect.
It is recommended but not required that
--ssl-ca also be specified so
that the public certificate provided by the server can be
verified. For example:
shell> mysql --ssl-key=myclient-key.pem --ssl-cert=myclient-cert.pem --ssl-ca=mycacert.pem
Assuming that the user has sufficient privileges to use
PERSIST_ONLY, persist-restricted system variables can
be persisted like this:
mysql> SET PERSIST_ONLY socket = '/tmp/mysql.sock'; Query OK, 0 rows affected (0.00 sec)
If the server is not configured to enable persisting persist-restricted system variables, or the user does not satisfy the required conditions for that capability, an error occurs:
mysql> SET PERSIST_ONLY socket = '/tmp/mysql.sock'; ERROR 1238 (HY000): Variable 'socket' is a non persistent read only variable