Documentation Home
MySQL 9.2 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.5Mb
PDF (A4) - 40.6Mb
Man Pages (TGZ) - 259.6Kb
Man Pages (Zip) - 366.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.2 Reference Manual  /  ...  /  Nonpersistible and Persist-Restricted System Variables

7.1.9.4 Nonpersistible and Persist-Restricted System Variables

SET PERSIST and SET PERSIST_ONLY enable global system variables to be persisted to the mysqld-auto.cnf option file in the data directory (see Section 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 with SET 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.

Press CTRL+C to copy
audit_log_current_session audit_log_filter_id caching_sha2_password_digest_rounds character_set_system core_file have_statement_timeout have_symlink hostname innodb_version keyring_hashicorp_auth_path keyring_hashicorp_ca_path keyring_hashicorp_caching keyring_hashicorp_commit_auth_path keyring_hashicorp_commit_ca_path keyring_hashicorp_commit_caching keyring_hashicorp_commit_role_id keyring_hashicorp_commit_server_url keyring_hashicorp_commit_store_path keyring_hashicorp_role_id keyring_hashicorp_secret_id keyring_hashicorp_server_url keyring_hashicorp_store_path 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 than persist_only_admin_x509_subject and persisted_globals_load. This list may change with ongoing development.

Press CTRL+C to copy
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 datadir 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 lc_messages_dir log_error mecab_rc_file named_pipe pid_file plugin_dir port relay_log replica_load_tmpdir 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:

  1. Ensure that MySQL is configured to support encrypted connections. See Section 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. See Section 8.3.3, “Creating SSL and RSA Certificates and Keys”.

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

    Press CTRL+C to copy
    [mysqld] persist_only_admin_x509_subject="subject-value"

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

    You must perform this step directly on the MySQL server host because persist_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 that 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:

Press CTRL+C to copy
$> 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:

Press CTRL+C to copy
C=US, ST=IL, L=Chicago, O=MyOrg, OU=client, CN=MyCN

To specify the Subject for MySQL, use this format:

Press CTRL+C to copy
/C=US/ST=IL/L=Chicago/O=MyOrg/OU=client/CN=MyCN

Configure the server my.cnf file with the Subject value:

Press CTRL+C to copy
[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:

Press CTRL+C to copy
$> mysql --ssl-key=myclient-key.pem --ssl-cert=myclient-cert.pem --ssl-ca=mycacert.pem

Assuming that the user has sufficient privileges to use SET PERSIST_ONLY, persist-restricted system variables can be persisted like this:

Press CTRL+C to copy
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:

Press CTRL+C to copy
mysql> SET PERSIST_ONLY socket = '/tmp/mysql.sock'; ERROR 1238 (HY000): Variable 'socket' is a non persistent read only variable