Documentation Home
MySQL 9.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 41.2Mb
PDF (A4) - 41.3Mb
Man Pages (TGZ) - 262.8Kb
Man Pages (Zip) - 368.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.4 Reference Manual  / ...  / MySQL Server Administration  / The MySQL Server  / Using System Variables  /  Nonpersistible and Persist-Restricted System Variables

7.1.9.4 Nonpersistible and Persist-Restricted System Variables

SET PERSIST andSET PERSIST_ONLY enable global system variables to be persisted to themysqld-auto.cnf option file in the data directory (seeSection 15.7.6.1, “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 persist-restricted:

  • 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. Persist-restricted system variables can be persisted withSET PERSIST_ONLY, but only by users for which the following conditions are satisfied:

For example,protocol_version is read only and set only by the server, so it cannot be persisted under any circumstances. On the other hand,bind_address is persist-restricted, so it can be set by users who satisfy the preceding conditions.

The following system variables are nonpersistible. This list may change with ongoing development.

audit_log_current_sessionaudit_log_filter_idcaching_sha2_password_digest_roundscharacter_set_systemcore_filehave_statement_timeouthave_symlinkhostnameinnodb_versionkeyring_hashicorp_auth_pathkeyring_hashicorp_ca_pathkeyring_hashicorp_cachingkeyring_hashicorp_commit_auth_pathkeyring_hashicorp_commit_ca_pathkeyring_hashicorp_commit_cachingkeyring_hashicorp_commit_role_idkeyring_hashicorp_commit_server_urlkeyring_hashicorp_commit_store_pathkeyring_hashicorp_role_idkeyring_hashicorp_secret_idkeyring_hashicorp_server_urlkeyring_hashicorp_store_pathlarge_files_supportlarge_page_sizelicenselocked_in_memorylog_binlog_bin_basenamelog_bin_indexlower_case_file_systemndb_versionndb_version_stringpersist_only_admin_x509_subjectpersisted_globals_loadprotocol_versionrelay_log_basenamerelay_log_indexserver_uuidskip_external_lockingsystem_time_zoneversion_commentversion_compile_machineversion_compile_osversion_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 thanpersist_only_admin_x509_subject andpersisted_globals_load. This list may change with ongoing development.

audit_log_fileaudit_log_formatauto_generate_certsbasedirbind_addresscaching_sha2_password_auto_generate_rsa_keyscaching_sha2_password_private_key_pathcaching_sha2_password_public_key_pathcharacter_sets_dirdatadirft_stopword_fileinit_fileinnodb_buffer_pool_load_at_startupinnodb_data_file_pathinnodb_data_home_dirinnodb_dedicated_serverinnodb_directoriesinnodb_force_load_corruptedinnodb_log_group_home_dirinnodb_page_sizeinnodb_read_onlyinnodb_temp_data_file_pathinnodb_temp_tablespaces_dirinnodb_undo_directorylc_messages_dirlog_errormecab_rc_filenamed_pipepid_fileplugin_dirportrelay_logreplica_load_tmpdirsecure_file_privsha256_password_auto_generate_rsa_keyssha256_password_private_key_pathsha256_password_public_key_pathshared_memoryshared_memory_base_nameskip_networkingslave_load_tmpdirsocketssl_cassl_capathssl_certssl_crlssl_crlpathssl_keytmpdirversion_tokens_session_number

To configure the server to enable persisting persist-restricted system variables, use this procedure:

  1. Ensure that MySQL is configured to support encrypted connections. SeeSection 8.3.1, “Configuring MySQL to Use Encrypted Connections”.

  2. 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. SeeSection 8.3.3, “Creating SSL and RSA Certificates and Keys”.

  3. Start the server withpersist_only_admin_x509_subject set to the designated Subject value. For example, put these lines in your servermy.cnf file:

    [mysqld]persist_only_admin_x509_subject="subject-value"

    The format of the Subject value is the same as used forCREATE USER ... REQUIRE SUBJECT. SeeSection 15.7.1.3, “CREATE USER Statement”.

    You must perform this step directly on the MySQL server host becausepersist_only_admin_x509_subject itself cannot be persisted at runtime.

  4. Restart the server.

  5. Distribute the SSL certificate that has the designated Subject value to users who are to be permitted to persist persist-restricted system variables.

Suppose thatmyclient-cert.pem is the SSL certificate to be used by clients who can persist persist-restricted system variables. Display the certificate contents using theopenssl command:

$> openssl x509 -text -in myclient-cert.pemCertificate:    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...

Theopenssl 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:

/C=US/ST=IL/L=Chicago/O=MyOrg/OU=client/CN=MyCN

Configure the servermy.cnf file with the Subject value:

[mysqld]persist_only_admin_x509_subject="/C=US/ST=IL/L=Chicago/O=MyOrg/OU=client/CN=MyCN"

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-key and--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:

$> mysql --ssl-key=myclient-key.pem --ssl-cert=myclient-cert.pem --ssl-ca=mycacert.pem

Assuming that the user has sufficient privileges to useSET 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