User Guide - Using SSL for MySQL
- 16/01/2007 10:40 PM
Applicable Plans: All Standard VPS, All Advanced VPS, All Premier VPS
APP VPS ARE BEING TRANSITIONED TO A NEW SERVER, CONTACT sales@eapps.com FOR ASSISTANCE
MySQL version 5.0.27 and above supports secure (encrypted) connections between MySQL clients and the server using the Secure Sockets Layer (SSL) protocol. This user guide discusses how to create and use SSL connections.
The standard configuration of MySQL is intended to be as fast as possible, so encrypted connections are not used by default. Doing so would make the client/server protocol much slower. Encrypting data is a CPU-intensive operation that requires the computer to do additional work and can delay other MySQL tasks. For applications that require the security provided by encrypted connections, the extra computation is warranted.
Encrypting MySQL commands is great for administering remote MySQL databases, or setting up secure connections between separate application and database servers.
To enable SSL for MySQL, carefully follow these steps. Make sure you are running at least MySQL verison 5.0.27.
First, login to your MySQL server using SSH. For more information on SSH, see our user guide at http://support.eapps.com/hsp/ssh
We will want to login to MySQL and check the status of SSL.
[root@server ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.27-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> \s
The output should look like this:
...
SSL: Not in use
...
Now, exit MySQL and change directories to /home.
mysql> exit
Bye
[root@server ~]# cd /home/
[root@server home]#
Next we will want to use a script to generate all the necessary SSL certificates for us automatically. With the text editor of your choice, create an empty text file and paste in the text below.
#-------------------------------------------------------------
#------------------START SCRIPT-------------------
#-------------------------------------------------------------
DIR=`pwd`/openssl
PRIV=$DIR/private
mkdir $DIR $PRIV $DIR/newcerts
#check if centos4 or centos5
VER=$(awk '{printf "%d", $3}' /etc/redhat-release);
if [ $VER -ge 5 ]; then
cp /etc/pki/tls/openssl.cnf $DIR
replace ../../CA $DIR -- $DIR/openssl.cnf
else
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf
fi
# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)
touch $DIR/index.txt
echo "01" > $DIR/serial
echo ""
echo "Generation of Certificate Authority(CA):"
echo ""
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 []:
echo ""
echo "Create server request and key"
echo ""
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
echo ""
echo "Sign server cert"
echo ""
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
echo ""
echo "Create client request and key"
echo ""
echo "Remember to use a different commonName (CN) than from above"
echo ""
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
echo ""
echo "Sign client cert"
echo ""
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
echo ""
echo "Creating a my.cnf file that you can use to test the certificates"
echo ""
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
echo "DONE!"
#------------------------------------------------------------
#-------------------END SCRIPT--------------------
#------------------------------------------------------------
After saving the file in your /home directory, you will want to change its permissions to make the script executable. Then you can execute it.
[root@server home]# chmod 755 script
[root@server home]# ./script
You can look at the example values shown above in the script to figure out what your own values should look like.
After the script has executed, the necessary SSL certificates will be created, as well as a my.cnf file, which is a configuration file for MySQL. MySQL will read from /etc/my.cnf on startup, so that it can load any custom settings.
If you do not have a file on your system called /etc/my.cnf, then you are not using a custom MySQL setup and you can copy the my.cnf created by the script to /etc like so:
[root@server home]# cp my.cnf /etc/my.cnf
Otherwise, if you are already using a custom MySQL configuration, you can append the info from the newly created my.cnf to your /etc/my.cnf file:
[root@server home]# cat my.cnf >> /etc/my.cnf
Next you will want to grant privileges to the remote user. From the MySQL documentation:
The secure way to require use of an SSL connection is to create an account on the server that includes a REQUIRE SSL clause in the GRANT statement. Then use that account to connect to the server, where both the server and the client have SSL support enabled.
[root@server ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.27-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> grant SELECT, INSERT on *.* to root@client_domain_or_IP_address IDENTIFIED BY “client-password” REQUIRE SSL;
Grant Statement Clarification:
SELECT, INSERT are two actions that are allowed, but could also include others such as UPDATE, DELETE, INDEX, ALTER, CREATE, DROP or just ALL PRIVILEGES.
*.* = grant permission to any table on any database
customer.* = grant permission to any table on the database “customer”
customer.address = grant permission only to the table “address” found in the database “customer”
Here, we used “root” as the remote user, but it could be any user on the client system.
Now, exit MySQL and restart it.
mysql> exit
Bye
[root@server ]# service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL SUCCESS!
[root@server ]#
Now when you enter MySQL and run the /s command as above, it should reveal something like this:
mysql> \s
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
...
That's it for the server setup, now onto the client...
You will want to move the following files to your client machine:
/home/openssl/cacert.pem
/home/openssl/client-cert.pem
/home/openssl/client-key.pem
On the remote client, place these files in any directory you choose, then create a /etc/my.cnf or add the following lines to an existing one:
[client]
ssl-ca=/$DIR/cacert.pem
ssl-cert=/$DIR/client-cert.pem
ssl-key=/$DIR/client-key.pem
Note that you will need to replace $DIR with the full path of the directory you put the files in.
Then after a restart of the client, you should be able to enter your password, connect to the database server, and begin to make SSL encrypted queries.
[root@client ]# service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL SUCCESS!
[root@client ]# mysql -h hostname_or_IP_address -P 3306 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.27-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Remember to replace “hostname_or_IP_address” with the domain or IP of the MySQL server. Also, make sure to use the password you created in the grant statement above.
If you have any problems getting the above configuration to work, send an email to support@eapps.com.