Rechercher dans le manuel MySQL
5.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 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.4.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.4.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 servermy.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:
- 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:
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-nonpersistible-system-variables.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.