<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="nonpersistible-system-variables">
      </a>
      7.1.9.4 Nonpersistible and Persist-Restricted System Variables
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045263737632">
  </a>
  <a class="indexterm" name="idm46045263736144">
  </a>
  <a class="indexterm" name="idm46045263734656">
  </a>
  <a class="indexterm" name="idm46045263733616">
  </a>
  <p>
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
        PERSIST
    </code>
   </a>
   and
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
        PERSIST_ONLY
    </code>
   </a>
   enable global system variables to be
        persisted to the
   <code class="filename">
    mysqld-auto.cnf
   </code>
   option
        file in the data directory (see
   <a class="xref" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    Section 15.7.6.1, “SET Syntax for Variable Assignment”
   </a>
   ).
        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:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Session system variables cannot be persisted. Session
            variables cannot be set at server startup, so there is no
            reason to persist them.
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      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.
     </p>
    </li>
    <li class="listitem">
     <p>
      A global system variable might be intended only for internal
            use.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Nonpersistible system variables cannot be persisted under any
        circumstances. Persist-restricted system variables can be
        persisted with
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
        PERSIST_ONLY
    </code>
   </a>
   , but only by users for which the
        following conditions are satisfied:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_persist_only_admin_x509_subject">
       <code class="literal">
        persist_only_admin_x509_subject
       </code>
      </a>
      system variable is set to an SSL certificate X.509 Subject
            value.
     </p>
    </li>
    <li class="listitem">
     <p>
      The user connects to the server using an encrypted
            connection and supplies an SSL certificate with the
            designated Subject value.
     </p>
    </li>
    <li class="listitem">
     <p>
      The user has sufficient privileges to use
      <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
       <code class="literal">
        SET
            PERSIST_ONLY
       </code>
      </a>
      (see
      <a class="xref" href="system-variable-privileges.html" title="7.1.9.1 System Variable Privileges">
       Section 7.1.9.1, “System Variable Privileges”
      </a>
      ).
     </p>
    </li>
   </ul>
  </div>
  <p>
   For example,
   <a class="link" href="server-system-variables.html#sysvar_protocol_version">
    <code class="literal">
     protocol_version
    </code>
   </a>
   is read only and set only by the server, so it cannot be
        persisted under any circumstances. On the other hand,
   <a class="link" href="server-system-variables.html#sysvar_bind_address">
    <code class="literal">
     bind_address
    </code>
   </a>
   is
        persist-restricted, so it can be set by users who satisfy the
        preceding conditions.
  </p>
  <p>
   The following system variables are nonpersistible. This list may
        change with ongoing development.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa37206850"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">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</code></pre>
  </div>
  <p>
   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
   <a class="link" href="server-system-variables.html#sysvar_persist_only_admin_x509_subject">
    <code class="literal">
     persist_only_admin_x509_subject
    </code>
   </a>
   and
   <a class="link" href="server-system-variables.html#sysvar_persisted_globals_load">
    <code class="literal">
     persisted_globals_load
    </code>
   </a>
   .
        This list may change with ongoing development.
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa64829189"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">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</code></pre>
  </div>
  <p>
   To configure the server to enable persisting persist-restricted
        system variables, use this procedure:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Ensure that MySQL is configured to support encrypted
            connections. See
      <a class="xref" href="using-encrypted-connections.html" title="8.3.1 Configuring MySQL to Use Encrypted Connections">
       Section 8.3.1, “Configuring MySQL to Use Encrypted Connections”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      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
      <a class="xref" href="creating-ssl-rsa-files.html" title="8.3.3 Creating SSL and RSA Certificates and Keys">
       Section 8.3.3, “Creating SSL and RSA Certificates and Keys”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Start the server with
      <a class="link" href="server-system-variables.html#sysvar_persist_only_admin_x509_subject">
       <code class="literal">
        persist_only_admin_x509_subject
       </code>
      </a>
      set to the designated Subject value. For example, put these
            lines in your server
      <code class="filename">
       my.cnf
      </code>
      file:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa11948215"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">persist_only_admin_x509_subject</span><span class="token attr-value"><span class="token punctuation">=</span>"<em class="replaceable">subject-value</em>"</span></code></pre>
     </div>
     <p>
      The format of the Subject value is the same as used for
      <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
       <code class="literal">
        CREATE USER ...
            REQUIRE SUBJECT
       </code>
      </a>
      . See
      <a class="xref" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
       Section 15.7.1.3, “CREATE USER Statement”
      </a>
      .
     </p>
     <p>
      You must perform this step directly on the MySQL server host
            because
      <a class="link" href="server-system-variables.html#sysvar_persist_only_admin_x509_subject">
       <code class="literal">
        persist_only_admin_x509_subject
       </code>
      </a>
      itself cannot be persisted at runtime.
     </p>
    </li>
    <li class="listitem">
     <p>
      Restart the server.
     </p>
    </li>
    <li class="listitem">
     <p>
      Distribute the SSL certificate that has the designated
            Subject value to users who are to be permitted to persist
            persist-restricted system variables.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Suppose that
   <code class="filename">
    myclient-cert.pem
   </code>
   is the SSL
        certificate to be used by clients who can persist
        persist-restricted system variables. Display the certificate
        contents using the
   <span class="command">
    <strong>
     openssl
    </strong>
   </span>
   command:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa53201499"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">openssl</span> x509 <span class="token property">-text</span> <span class="token property">-in</span> myclient-cert<span class="token punctuation">.</span>pem
Certificate<span class="token punctuation">:</span>
    Data<span class="token punctuation">:</span>
        Version<span class="token punctuation">:</span> 3 <span class="token punctuation">(</span>0x2<span class="token punctuation">)</span>
        Serial Number<span class="token punctuation">:</span> 2 <span class="token punctuation">(</span>0x2<span class="token punctuation">)</span>
    Signature Algorithm<span class="token punctuation">:</span> md5WithRSAEncryption
        Issuer<span class="token punctuation">:</span> C<span class="token attr-value"><span class="token punctuation">=</span>US,</span> ST<span class="token attr-value"><span class="token punctuation">=</span>IL,</span> L<span class="token attr-value"><span class="token punctuation">=</span>Chicago,</span> O<span class="token attr-value"><span class="token punctuation">=</span>MyOrg,</span> OU<span class="token attr-value"><span class="token punctuation">=</span>CA,</span> CN<span class="token attr-value"><span class="token punctuation">=</span>MyCN</span>
        Validity
            Not Before<span class="token punctuation">:</span> Oct 18 17<span class="token punctuation">:</span>03<span class="token punctuation">:</span>03 2018 GMT
            Not After <span class="token punctuation">:</span> Oct 15 17<span class="token punctuation">:</span>03<span class="token punctuation">:</span>03 2028 GMT
        Subject<span class="token punctuation">:</span> C<span class="token attr-value"><span class="token punctuation">=</span>US,</span> ST<span class="token attr-value"><span class="token punctuation">=</span>IL,</span> L<span class="token attr-value"><span class="token punctuation">=</span>Chicago,</span> O<span class="token attr-value"><span class="token punctuation">=</span>MyOrg,</span> OU<span class="token attr-value"><span class="token punctuation">=</span>client,</span> CN<span class="token attr-value"><span class="token punctuation">=</span>MyCN</span>
<span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   The
   <span class="command">
    <strong>
     openssl
    </strong>
   </span>
   output shows that the certificate
        Subject value is:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa89486080"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">C=US, ST=IL, L=Chicago, O=MyOrg, OU=client, CN=MyCN</code></pre>
  </div>
  <p>
   To specify the Subject for MySQL, use this format:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-none"><div class="docs-select-all right" id="sa95332428"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">/C=US/ST=IL/L=Chicago/O=MyOrg/OU=client/CN=MyCN</code></pre>
  </div>
  <p>
   Configure the server
   <code class="filename">
    my.cnf
   </code>
   file with the
        Subject value:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa31022196"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">persist_only_admin_x509_subject</span><span class="token attr-value"><span class="token punctuation">=</span>"/C=US/ST=IL/L=Chicago/O=MyOrg/OU=client/CN=MyCN"</span></code></pre>
  </div>
  <p>
   Restart the server so that the new configuration takes effect.
  </p>
  <p>
   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.
  </p>
  <p>
   To use X.509, clients must specify the
   <a class="link" href="connection-options.html#option_general_ssl-key">
    <code class="option">
     --ssl-key
    </code>
   </a>
   and
   <a class="link" href="connection-options.html#option_general_ssl-cert">
    <code class="option">
     --ssl-cert
    </code>
   </a>
   options to connect.
        It is recommended but not required that
   <a class="link" href="connection-options.html#option_general_ssl-ca">
    <code class="option">
     --ssl-ca
    </code>
   </a>
   also be specified so
        that the public certificate provided by the server can be
        verified. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa32908749"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token constant">--ssl-key</span><span class="token attr-value"><span class="token punctuation">=</span>myclient-key.pem</span> <span class="token constant">--ssl-cert</span><span class="token attr-value"><span class="token punctuation">=</span>myclient-cert.pem</span> <span class="token constant">--ssl-ca</span><span class="token attr-value"><span class="token punctuation">=</span>mycacert.pem</span></code></pre>
  </div>
  <p>
   Assuming that the user has sufficient privileges to use
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
        PERSIST_ONLY
    </code>
   </a>
   , persist-restricted system variables can
        be persisted like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92871296"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">PERSIST_ONLY</span> <span class="token keyword">socket</span> <span class="token operator">=</span> <span class="token string">'/tmp/mysql.sock'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span></code></pre>
  </div>
  <p>
   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:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34162008"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">PERSIST_ONLY</span> <span class="token keyword">socket</span> <span class="token operator">=</span> <span class="token string">'/tmp/mysql.sock'</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1238 (HY000)<span class="token punctuation">:</span> Variable 'socket' is a non persistent read only variable</span></code></pre>
  </div>
 </div>
 <br/>
</div>
