Skip navigation links
6.3.7 Connecting to MySQL Remotely from Windows with SSH »
Section Navigation      [Toggle]

6.3.6.4. Setting Up SSL Certificates for MySQL

This section demonstrates how 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.

Following the third example, instructions are given for using the files to test SSL connections. You can also use the files as described in Section 6.3.6.2, “Using SSL Connections”.

Example 1: Creating SSL Files from the Command Line on Unix

The following example shows a set of commands to create MySQL server and client certificate and key files. You will need to 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
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

# Create CA certificate
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 1000 \
         -key ca-key.pem -out ca-cert.pem

# Create server certificate, remove passphrase, and sign it
shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout server-key.pem -out server-req.pem
shell> openssl rsa -in server-key.pem -out server-key.pem
shell> openssl x509 -req -in server-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it
shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout client-key.pem -out client-req.pem
shell> openssl rsa -in client-key.pem -out client-key.pem
shell> openssl x509 -req -in client-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

After generating the certificates, verify them: 

mysql> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Example 2: Creating SSL Files Using a Script on Unix

Here is an example script that shows how to set up SSL certificates for MySQL: 

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-cert.pem \
    -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/monty/openssl/private/cakey.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 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/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/monty/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  -policy policy_anything -out $DIR/server-cert.pem \
    -config $DIR/openssl.cnf -infiles $DIR/server-req.pem

# Sample output:
# Using configuration from /home/monty/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/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/monty/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  -policy policy_anything -out $DIR/client-cert.pem \
    -config $DIR/openssl.cnf -infiles $DIR/client-req.pem

# Sample output:
# Using configuration from /home/monty/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
#

cnf=""
cnf="$cnf [client]"
cnf="$cnf ssl-ca=$DIR/ca-cert.pem"
cnf="$cnf ssl-cert=$DIR/client-cert.pem"
cnf="$cnf ssl-key=$DIR/client-key.pem"
cnf="$cnf [mysqld]"
cnf="$cnf ssl-ca=$DIR/ca-cert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf
Example 3: Creating SSL Files on Windows

Download OpenSSL for Windows. 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 will be 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++ 2008 Redistributables', cancel the setup and download one of the following packages as well, again depending on your architecture (32-bit or 64-bit):

After installing the additional package, restart the OpenSSL setup.

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:

  1. On the Windows desktop, right-click the My Computer icon, and select Properties.

  2. Select the Advanced tab from the System Properties menu that appears, and click the Environment Variables button.

  3. Under System Variables, select Path, then click the Edit button. The Edit System Variable dialogue should appear.

  4. Add ';C:\OpenSSL-Win32\bin' to the end (notice the semicolon).

  5. Press OK 3 times.

  6. 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:\>

Depending on your version of Windows, the preceding instructions might be slightly different.

After OpenSSL has been installed, use instructions similar to those from from Example 1 (shown earlier in this section), with the following changes:

  • Change the following Unix commands: 

    # Create clean environment
shell> rm -rf newcerts
shell> mkdir newcerts && cd newcerts

    On Windows, use these commands instead: 

    # Create clean environment
shell> md c:\newcerts
shell> cd c:\newcerts

  • When 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.

Testing SSL Connections

To test SSL connections, start the server as follows, where $DIR is the path name to the directory where the sample my.cnf option file (or my.ini on Windows) is located: 

shell> mysqld --defaults-file=$DIR/my.cnf &

Then invoke a client program using the same option file: 

shell> mysql --defaults-file=$DIR/my.cnf

If you have a MySQL source distribution, you can also test your setup by modifying the preceding my.cnf file to refer to the demonstration certificate and key files in the SSL directory of the distribution.

Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Jason Dixon on May 29 2003 3:12am[Delete] [Edit]

Seeing as x.509 is such a confusing pain in the butt, here's a brief howto for setting up the certs properly.

1) Create the CA (private key and public cert), index.txt and serial files as mentioned above.
2) Create your server key.
3) Create your server cert.
4) Create your client key. Make sure your commonName is *different* from that of your server. They must be unique.
5) Create your client cert.
6) Copy the client key, client cert, and CA cert to your client.

That's basically it. Make sure that each end specifies their own cert, key, and the CA cert when connecting. See the previous "Basics" section notes for details on connecting using these keys.

Posted by Orin Dodge on March 30 2005 4:03pm[Delete] [Edit]

If you get an error when trying to sign the client certificate, delete the line of data from the file 'index.txt' in the directory 'openssl' and try it again.

tip source:
http://www-unix.globus.org/mail_archive/discuss/2005/01/msg00359.html

Posted by David Martin on August 3 2005 7:55pm[Delete] [Edit]

I got the error "failed to update database
TXT_DB error number 2", and removing the contents of the index.txt file is not necessary. Apparently the key database uses the commonName as a unique identifier. When I was setting up Client and Server keys the first one always would work up through signing, but the second owe would not sign. I used my name for commonName and it didnt work, but when I used something like "MyProject Client" and "MyProject Server" it worked. (dtm)

Posted by Rahul Gupta on October 23 2006 6:54pm[Delete] [Edit]

MySQL-SSL Configuration on Windows Machine
1. Install MySQL.
2. Extract OpenSSL.
3. Create a file '$OpenSSL/serial.txt', that contains "01"
4. Create a file '$OpenSSL/index.txt'
5. Set '$OpenSSL/bin' in %PATH%
6. Generation of Certificate Authority(CA)
>openssl req -new -x509 -keyout "$OpenSSL/ca-key.pem" -out "$OpenSSL/ca-cert.pem" -config "$OpenSSL/openssl.cnf"

Note: If you were requested to enter "PEM pass", please enter different "PEM pass" in the following steps.
Note: Organization name of Certificate Athority should not match with server/client organization name.

7. Create server certificates
>openssl req -new -keyout "$OpenSSL/server-key.pem" -out "$OpenSSL/server-req.pem" -days 3600 -config "$OpenSSL/openssl.cnf"
>openssl rsa -in "$OpenSSL/server-key.pem" -out "$OpenSSL/server-key.pem"
>openssl x509 -req -days 3600 -CA "$OpenSSL/ca-cert.pem" -CAkey "$OpenSSL/ca-key.pem" -CAserial "$OpenSSL/serial.txt" -in "$OpenSSL/server-req.pem" -out "$OpenSSL/server-cert.pem"

8. Create client certificates
>openssl req -new -keyout "$OpenSSL/client-key.pem" -out "$OpenSSL/client-req.pem" -days 3600 -config "$OpenSSL/openssl.cnf"
>openssl rsa -in "$OpenSSL/client-key.pem" -out "$OpenSSL/client-key.pem"
>openssl x509 -req -days 3600 -CA "$OpenSSL/ca-cert.pem" -CAkey "$OpenSSL/ca-key.pem" -CAserial "$OpenSSL/serial.txt" -in "$OpenSSL/client-req.pem" -out "$OpenSSL/client-cert.pem"

9. To start MySQL server daemon
OpenSSL>mysqld --ssl-ca=ca-cert.pem --ssl-cert=server-cert.pem --ssl-key=server-key.pem

10. To start MySQL client daemon
OpenSSL>mysqld --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

11. Create user in MySQL database that requires SSL
mysql> GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE SSL;

Posted by Ivan Grigoriev on July 17 2008 5:34pm[Delete] [Edit]

In order to run MySQL as Service on Windows platform with SSL enabled just add this lines in [mysqld] section:

ssl-key=C:/Program Files/MySQL/MySQL Server 5.0/cert/server-key.pem
ssl-cert=C:/Program Files/MySQL/MySQL Server 5.0/cert/server-cert.pem
ssl-ca=C:/Program Files/MySQL/MySQL Server 5.0/cert/ca-cert.pem

Where "C:/Program Files/MySQL/MySQL Server 5.0/cert/" is a path to certs described above.

Posted by Kevin Weslowski on October 27 2008 3:15pm[Delete] [Edit]

Note that on windows, when you specify the path to your certs (or any file path, for that matter), use the forward slash instead of the traditional windows backslash:

RIGHT: C:/path/to/certs
WRONG: C:\path\to\certs

The error log doesn't even complain about this on mysqld startup, which would make this hard to track down...

Posted by Warren Melnick on August 17 2010 5:32pm[Delete] [Edit]

If you are getting "ERROR 2026 (HY000): SSL connection error" when trying to connect and can not figure out why, it probably means that you just pressed Enter when openssl asked you all of the questions. You can not have the same Common Name for both the client and the server or the connection will fail with this generic error. Wipe out your certificates, start again and actually answer the questions, being sure to give a different Common Name for each step of the process.

Posted by Hellen Zhe-L on December 21 2010 4:24am[Delete] [Edit]

on Mac Snow Leopard, server side config file (i.e. my.cnf) needs:
server-cert.pem
server-key.pem
ca-cert.pem

client side only needs:
client-cert.pem
client-key.pem

and it works

Posted by Kenni Lund on March 27 2011 11:35pm[Delete] [Edit]

"DISABLED" problem:

To people who are using a recent OpenSSL v1.0 and a recent MySQL v5.5.x and are already using the absolute path on Windows, but STILL gets "DISABLED" for both "have_openssl" and "have_ssl":

Do *NOT* follow the instructions on this page on how to create the server certificates - they don't work.

Instead use the following commands:
Create a file called serial.txt which contains the text "01" and save it in the directory for the certificates. Run the following commands in the same directory:

> openssl req -new -x509 -keyout "ca-key.pem" -out "ca-cert.pem"
(if you're asked to enter a PEM-password, enter a password which is at least 4 characters long).

> openssl req -new -keyout "server-key.pem" -out "server-req.pem" -days 3600
(if you're asked to enter a PEM-password, enter another password which is at least 4 characters long).

> openssl rsa -in "server-key.pem" -out "server-key.pem"
> openssl x509 -req -days 3600 -CA "ca-cert.pem" -CAkey "ca-key.pem" -CAserial "serial.txt" -in "server-req.pem" -out "server-cert.pem"

After restarting the MySQL service, SSL is now working :)
Credits goes to Rahul Gupta who posted an alternative installation guide above, 5 years ago :)

Posted by Chris Calender on December 6 2011 3:57pm[Delete] [Edit]

Here is a simple, 5-step process for setting up SSL for MySQL. Excellent for Windows users:

http://www.chriscalender.com/?p=448

Add your own comment.