5.1.9.4 Nonpersistible and Persist-Restricted System Variables

Contents

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 13.7.5.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. As of MySQL 8.0.14, persist-restricted system variables can be persisted with SET PERSIST_ONLY, but only by users for which the following conditions are satisfied:

The persist_only_admin_x509_subject system 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.
The user has sufficient privileges to use SET PERSIST_ONLY (see Section 5.1.9.1, “System Variable Privileges”).

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_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 than persist_only_admin_x509_subject and persisted_globals_load. 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_subject set to the designated Subject value. For example, put these lines in your server my.cnf file:
```
[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 13.7.1.3, “CREATE USER Syntax”.
You must perform this step directly on the MySQL server host because persist_only_admin_x509_subject itself 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.

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:

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:

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

Configure the server my.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:

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 SET 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

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-nonpersistible-system-variables.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut