<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="creating-ssl-rsa-files-using-mysql">
      </a>
      8.3.3.1 Creating SSL and RSA Certificates and Keys using MySQL
     </h4>
    </div>
   </div>
  </div>
  <p>
   MySQL provides these ways to create the SSL certificate and key
        files and RSA key-pair files required to support encrypted
        connections using SSL and secure password exchange using RSA
        over unencrypted connections, if those files are missing:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The server can autogenerate these files at startup, for
            MySQL distributions.
     </p>
    </li>
   </ul>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Server autogeneration helps lower the barrier to using SSL by
          making it easier to generate the required files. However,
          certificates generated by this method are self-signed, which
          may not be very secure. After you gain experience using these,
          consider obtaining certificate and key material from a
          registered certificate authority.
   </p>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    If a client connecting to a MySQL server instance uses an SSL
          certificate with the
    <code class="literal">
     extendedKeyUsage
    </code>
    extension (an X.509 v3 extension), the extended key usage must
          include client authentication (
    <code class="literal">
     clientAuth
    </code>
    ).
          If the SSL certificate is only specified for server
          authentication (
    <code class="literal">
     serverAuth
    </code>
    ) and other
          non-client certificate purposes, certificate verification
          fails and the client connection to the MySQL server instance
          fails. There is no
    <code class="literal">
     extendedKeyUsage
    </code>
    extension in SSL certificates generated by MySQL Server. If
          you use your own client certificate created in another way,
          ensure any
    <code class="literal">
     extendedKeyUsage
    </code>
    extension
          includes client authentication.
   </p>
  </div>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="creating-ssl-rsa-files-using-mysql.html#creating-ssl-rsa-files-using-mysql-automatic" title="Automatic SSL and RSA File Generation">
       Automatic SSL and RSA File Generation
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="creating-ssl-rsa-files-using-mysql.html#creating-ssl-rsa-files-using-mysql-ssl-and-rsa-file-characteristics" title="SSL and RSA File Characteristics">
       SSL and RSA File Characteristics
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="creating-ssl-rsa-files-using-mysql-automatic">
       </a>
       Automatic SSL and RSA File Generation
      </h5>
     </div>
    </div>
   </div>
   <p>
    For MySQL distributions compiled using OpenSSL, the MySQL
          server has the capability of automatically generating missing
          SSL and RSA files at startup. The
    <a class="link" href="server-system-variables.html#sysvar_auto_generate_certs">
     <code class="literal">
      auto_generate_certs
     </code>
    </a>
    ,
    <a class="link" href="server-system-variables.html#sysvar_sha256_password_auto_generate_rsa_keys">
     <code class="literal">
      sha256_password_auto_generate_rsa_keys
     </code>
    </a>
    ,
          and
    <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_auto_generate_rsa_keys">
     <code class="literal">
      caching_sha2_password_auto_generate_rsa_keys
     </code>
    </a>
    system variables control automatic generation of these files.
          These variables are enabled by default. They can be enabled at
          startup and inspected but not set at runtime.
   </p>
   <p>
    At startup, the server automatically generates server-side and
          client-side SSL certificate and key files in the data
          directory if the
    <a class="link" href="server-system-variables.html#sysvar_auto_generate_certs">
     <code class="literal">
      auto_generate_certs
     </code>
    </a>
    system
          variable is enabled, no SSL options are specified, and the
          server-side SSL files are missing from the data directory.
          These files enable encrypted client connections using SSL; 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>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       The server checks the data directory for SSL files with
              the following names:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa94524176"><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">ca.pem
server-cert.pem
server-key.pem</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       If any of those files are present, the server creates no
              SSL files. Otherwise, it creates them, plus some
              additional files:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa98950606"><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">ca.pem               Self-signed CA certificate
ca-key.pem           CA private key
server-cert.pem      Server certificate
server-key.pem       Server private key
client-cert.pem      Client certificate
client-key.pem       Client private key</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       If the server autogenerates SSL files, it uses the names
              of the
       <code class="filename">
        ca.pem
       </code>
       ,
       <code class="filename">
        server-cert.pem
       </code>
       , and
       <code class="filename">
        server-key.pem
       </code>
       files to set the
              corresponding system variables
              (
       <a class="link" href="server-system-variables.html#sysvar_ssl_ca">
        <code class="literal">
         ssl_ca
        </code>
       </a>
       ,
       <a class="link" href="server-system-variables.html#sysvar_ssl_cert">
        <code class="literal">
         ssl_cert
        </code>
       </a>
       ,
       <a class="link" href="server-system-variables.html#sysvar_ssl_key">
        <code class="literal">
         ssl_key
        </code>
       </a>
       ).
      </p>
     </li>
    </ol>
   </div>
   <p>
    At startup, the server automatically generates RSA
          private/public key-pair files in the data directory if all of
          these conditions are true: The
    <a class="link" href="server-system-variables.html#sysvar_sha256_password_auto_generate_rsa_keys">
     <code class="literal">
      sha256_password_auto_generate_rsa_keys
     </code>
    </a>
    or
    <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_auto_generate_rsa_keys">
     <code class="literal">
      caching_sha2_password_auto_generate_rsa_keys
     </code>
    </a>
    system variable is enabled; no RSA options are specified; the
          RSA files are missing from the data directory. These key-pair
          files enable secure password exchange using RSA over
          unencrypted connections for accounts authenticated by the
    <code class="literal">
     sha256_password
    </code>
    (deprecated) or
    <code class="literal">
     caching_sha2_password
    </code>
    plugin; see
    <a class="xref" href="sha256-pluggable-authentication.html" title="8.4.1.3 SHA-256 Pluggable Authentication">
     Section 8.4.1.3, “SHA-256 Pluggable Authentication”
    </a>
    , and
    <a class="xref" href="caching-sha2-pluggable-authentication.html" title="8.4.1.2 Caching SHA-2 Pluggable Authentication">
     Section 8.4.1.2, “Caching SHA-2 Pluggable Authentication”
    </a>
    .
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       The server checks the data directory for RSA files with
              the following names:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa1248432"><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">private_key.pem      Private member of private/public key pair
public_key.pem       Public member of private/public key pair</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       If any of these files are present, the server creates no
              RSA files. Otherwise, it creates them.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the server autogenerates the RSA files, it uses their
              names to set the corresponding system variables
              (
       <a class="link" href="server-system-variables.html#sysvar_sha256_password_private_key_path">
        <code class="literal">
         sha256_password_private_key_path
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_sha256_password_public_key_path">
        <code class="literal">
         sha256_password_public_key_path
        </code>
       </a>
       ;
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_private_key_path">
        <code class="literal">
         caching_sha2_password_private_key_path
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_public_key_path">
        <code class="literal">
         caching_sha2_password_public_key_path
        </code>
       </a>
       ).
      </p>
     </li>
    </ol>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="creating-ssl-rsa-files-using-mysql-ssl-and-rsa-file-characteristics">
       </a>
       SSL and RSA File Characteristics
      </h5>
     </div>
    </div>
   </div>
   <p>
    SSL and RSA files created automatically by the server have
          these characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       SSL and RSA keys have a size of 2048 bits.
      </p>
     </li>
     <li class="listitem">
      <p>
       The SSL CA certificate is self signed.
      </p>
     </li>
     <li class="listitem">
      <p>
       The SSL server and client certificates are signed with the
              CA certificate and key, using the
       <code class="literal">
        sha256WithRSAEncryption
       </code>
       signature
              algorithm.
      </p>
     </li>
     <li class="listitem">
      <p>
       SSL certificates use these Common Name (CN) values, with
              the appropriate certificate type (CA, Server, Client):
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa22795556"><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">ca.pem:         MySQL_Server_<em class="replaceable">suffix</em>_Auto_Generated_CA_Certificate
server-cert.pm: MySQL_Server_<em class="replaceable">suffix</em>_Auto_Generated_Server_Certificate
client-cert.pm: MySQL_Server_<em class="replaceable">suffix</em>_Auto_Generated_Client_Certificate</code></pre>
      </div>
      <p>
       The
       <em class="replaceable">
        <code>
         suffix
        </code>
       </em>
       value is based on
              the MySQL version number.
      </p>
      <p>
       For files generated by the server, if the resulting CN
              values exceed 64 characters, the
       <code class="literal">
        _
        <em class="replaceable">
         <code>
          suffix
         </code>
        </em>
       </code>
       portion of the name is omitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       SSL files have blank values for Country (C), State or
              Province (ST), Organization (O), Organization Unit Name
              (OU) and email address.
      </p>
     </li>
     <li class="listitem">
      <p>
       SSL files created by the server are valid for ten years
              from the time of generation.
      </p>
     </li>
     <li class="listitem">
      <p>
       RSA files do not expire.
      </p>
     </li>
     <li class="listitem">
      <p>
       SSL files have different serial numbers for each
              certificate/key pair (1 for CA, 2 for Server, 3 for
              Client).
      </p>
     </li>
     <li class="listitem">
      <p>
       Files created automatically by the server are owned by the
              account that runs the server.
      </p>
     </li>
     <li class="listitem">
      <p>
       On Unix and Unix-like systems, the file access mode is 644
              for certificate files (that is, world readable) and 600
              for key files (that is, accessible only by the account
              that runs the server).
      </p>
     </li>
    </ul>
   </div>
   <p>
    To see the contents of an SSL certificate (for example, to
          check the range of dates over which it is valid), invoke
    <span class="command">
     <strong>
      openssl
     </strong>
    </span>
    directly:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa27926201"><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">openssl x509 <span class="token property">-text</span> <span class="token property">-in</span> ca<span class="token punctuation">.</span>pem
openssl x509 <span class="token property">-text</span> <span class="token property">-in</span> server-cert<span class="token punctuation">.</span>pem
openssl x509 <span class="token property">-text</span> <span class="token property">-in</span> client-cert<span class="token punctuation">.</span>pem</code></pre>
   </div>
   <p>
    It is also possible to check SSL certificate expiration
          information using this SQL statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa92944486"><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">SHOW</span> <span class="token keyword">STATUS</span> <span class="token operator">LIKE</span> <span class="token string">'Ssl_server_not%'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Variable_name         <span class="token punctuation">|</span> Value                    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Ssl_server_not_after  <span class="token punctuation">|</span> Apr 28 14:16:39 2027 GMT <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> Ssl_server_not_before <span class="token punctuation">|</span> May  1 14:16:39 2017 GMT <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
