4.5.7.3 Setting Up SSL Certificates for MySQL - MySQL

Here is an example for setting 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

(optional)
#
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 a passphrase from the key (optional)
#
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.