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

5.6.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 5.6.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. For testing, you can press Enter to all prompts. 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 > ca-cert.pem

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

# Create client certificate
shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > 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 of the following packages, depending on your architecture (32-bit or 64-bit):

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 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\bin to the Windows System Path variable of your server:

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

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

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

  4. Add ';C:\OpenSSL\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 the instructions from Example 1 (shown earlier in this section), with the following changes:

  • Change the follow 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.

  • For references to my.cnf option files, substitute my.ini instead.

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


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

Add your own comment.