Skip navigation links

Additional languages
5.5.7.5 Connecting to MySQL Remotely from Windows with SSH »
Section Navigation      [Toggle]

5.5.7.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. Both examples use the openssl command that is part of OpenSSL.

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 non-empty 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

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/cacert.pem \
    -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/cacert.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/cacert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf

To test SSL connections, start the server as follows, where $DIR is the pathname 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 [name withheld] on June 25 2003 11:37am[Delete] [Edit]

As far as I can tell, MySQL requires a client certificate. If this is the case, I don't think it should be. You should be able to use SSL with only a server certificate. For example, I want to use SSL for encryption, but, I want to authenticate the client using a password and not a client certificate because client certificates are difficult to manage.

Posted by Jamal Simpson on December 28 2003 10:57am[Delete] [Edit]

That's not the case actually, continue reading into the next section and you will see the kinds of encrypted connections allowed.

You can require SSL but not X509 or you can require both.

Posted by Mike Kemp on June 17 2004 7:27pm[Delete] [Edit]

When generating the CA add the command option "-days 365" or the CA will default to expire after 30 days

openssl req -new -x509 -days 365 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
-config $DIR/openssl.cnf

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;



Add your own comment.