MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
This section describes how to use the openssl command to set up SSL certificate and key files for use by MySQL servers and clients. The first example shows a simplified procedure such as you might use from the command line. The second shows a script that contains more detail. The first two examples are intended for use on Unix and both use the openssl command that is part of OpenSSL. The third example describes how to set up SSL files on Windows.
Note
There are easier alternatives to generating the files required for SSL than the procedure described here: Let the server autogenerate them or use the mysql_ssl_rsa_setup program (deprecated as of 8.0.34). See Section 8.3.3.1, “Creating SSL and RSA Certificates and Keys using MySQL”.
Important
Whatever method you use to generate the certificate and key files, the Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. Otherwise, the certificate and key files do not work for servers compiled using OpenSSL. A typical error in this case is:
ERROR 2026 (HY000): SSL connection error:
error:00000001:lib(0):func(0):reason(1)
Important
If a client connecting to a MySQL server instance uses an SSL
certificate with the extendedKeyUsage
extension (an X.509 v3 extension), the extended key usage must
include client authentication (clientAuth).
If the SSL certificate is only specified for server
authentication (serverAuth) and other
non-client certificate purposes, certificate verification
fails and the client connection to the MySQL server instance
fails. There is no extendedKeyUsage
extension in SSL certificates created using the
openssl command following the instructions
in this topic. If you use your own client certificate created
in another way, ensure any extendedKeyUsage
extension includes client authentication.
The following example shows a set of commands to create MySQL server and client certificate and key files. You must respond to several prompts by the openssl commands. To generate test files, you can press Enter to all prompts. To generate files for production use, you should provide nonempty responses.
# Create clean environment
rm -rf newcerts
mkdir newcerts && cd newcerts
# Create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 \
-key ca-key.pem -out ca.pem
# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pemAfter generating the certificates, verify them:
openssl verify -CAfile ca.pem server-cert.pem client-cert.pemYou should see a response like this:
server-cert.pem: OK
client-cert.pem: OKTo see the contents of a certificate (for example, to check the range of dates over which a certificate is valid), invoke openssl like this:
openssl x509 -text -in ca.pem
openssl x509 -text -in server-cert.pem
openssl x509 -text -in client-cert.pemNow you have a set of files that can be used as follows:
ca.pem: Use this to set thessl_casystem variable on the server side and the--ssl-caoption on the client side. (The CA certificate, if used, must be the same on both sides.)server-cert.pem,server-key.pem: Use these to set thessl_certandssl_keysystem variables on the server side.client-cert.pem,client-key.pem: Use these as the arguments to the--ssl-certand--ssl-keyoptions on the client side.
For additional usage instructions, see Section 8.3.1, “Configuring MySQL to Use Encrypted Connections”.
Here is an example script that shows how to set up SSL certificate and key files for MySQL. After executing the script, use the files for SSL connections as described in Section 8.3.1, “Configuring MySQL to Use Encrypted Connections”.
DIR=`pwd`/openssl
PRIV=$DIR/private
mkdir $DIR $PRIV $DIR/newcerts
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf
# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)
touch $DIR/index.txt
echo "01" > $DIR/serial
#
# Generation of Certificate Authority(CA)
#
openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/ca.pem \
-days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/jones/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information to be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:
#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
$DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/jones/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove the passphrase from the key
#
openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
#
# Sign server cert
#
openssl ca -cert $DIR/ca.pem -policy policy_anything \
-out $DIR/server-cert.pem -config $DIR/openssl.cnf \
-infiles $DIR/server-req.pem
# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL admin'
# Certificate is to be certified until Sep 13 14:22:46 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
$DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf
# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/jones/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# You are about to be asked to enter information that will be
# incorporated into your certificate request.
# What you are about to enter is what is called a Distinguished Name
# or a DN.
# There are quite a few fields but you can leave some blank
# For some fields there will be a default value,
# If you enter '.', the field will be left blank.
# -----
# Country Name (2 letter code) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:
#
# Remove the passphrase from the key
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
#
# Sign client cert
#
openssl ca -cert $DIR/ca.pem -policy policy_anything \
-out $DIR/client-cert.pem -config $DIR/openssl.cnf \
-infiles $DIR/client-req.pem
# Sample output:
# Using configuration from /home/jones/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated
#
# Create a my.cnf file that you can use to test the certificates
#
cat <<EOF > $DIR/my.cnf
[client]
ssl-ca=$DIR/ca.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem
[mysqld]
ssl_ca=$DIR/ca.pem
ssl_cert=$DIR/server-cert.pem
ssl_key=$DIR/server-key.pem
EOFDownload OpenSSL for Windows if it is not installed on your system. An overview of available packages can be seen here:
http://www.slproweb.com/products/Win32OpenSSL.html
Choose the Win32 OpenSSL Light or Win64 OpenSSL Light package,
depending on your architecture (32-bit or 64-bit). The default
installation location is C:\OpenSSL-Win32
or C:\OpenSSL-Win64, depending on which
package you downloaded. The following instructions assume a
default location of C:\OpenSSL-Win32.
Modify this as necessary if you are using the 64-bit package.
If a message occurs during setup indicating
'...critical component is missing: Microsoft Visual
C++ 2019 Redistributables', cancel the setup and
download one of the following packages as well, again
depending on your architecture (32-bit or 64-bit):
Visual C++ 2008 Redistributables (x86), available at:
http://www.microsoft.com/downloads/details.aspx?familyid=9B2DA534-3E03-4391-8A4D-074B9F2BC1BFVisual C++ 2008 Redistributables (x64), available at:
http://www.microsoft.com/downloads/details.aspx?familyid=bd2a6171-e2d6-4230-b809-9a8d7548c1b6
After installing the additional package, restart the OpenSSL setup procedure.
During installation, leave the default
C:\OpenSSL-Win32 as the install path, and
also leave the default option 'Copy OpenSSL DLL files
to the Windows system directory' selected.
When the installation has finished, add
C:\OpenSSL-Win32\bin to the Windows
System Path variable of your server (depending on your version
of Windows, the following path-setting instructions might
differ slightly):
On the Windows desktop, right-click the My Computer icon, and select .
Select the tab from the menu that appears, and click the button.
Under System Variables, select , then click the button. The dialogue should appear.
Add
';C:\OpenSSL-Win32\bin'to the end (notice the semicolon).Press OK 3 times.
Check that OpenSSL was correctly integrated into the Path variable by opening a new command console (Start>Run>cmd.exe) and verifying that OpenSSL is available:
Microsoft Windows [Version ...] Copyright (c) 2006 Microsoft Corporation. All rights reserved. C:\Windows\system32>cd \ C:\>openssl OpenSSL> exit <<< If you see the OpenSSL prompt, installation was successful. C:\>
After OpenSSL has been installed, use instructions similar to those from Example 1 (shown earlier in this section), with the following changes:
Change the following Unix commands:
# Create clean environment rm -rf newcerts mkdir newcerts && cd newcertsOn Windows, use these commands instead:
# Create clean environment md c:\newcerts cd c:\newcertsWhen a
'\'character is shown at the end of a command line, this'\'character must be removed and the command lines entered all on a single line.
After generating the certificate and key files, to use them for SSL connections, see Section 8.3.1, “Configuring MySQL to Use Encrypted Connections”.