Child pages
  • How to configure MySQL SSL Connections
Skip to end of metadata
Go to start of metadata

Overview

This document describes how to configure your server to use SSL for MySQL database connections. When you secure your MySQL connections, malicious users cannot intercept your MySQL traffic in transit.

Important:

You must possess root access to the client and host server in order to complete this tutorial.

Create the SSL key storage directory


 

Create the directory to store the SSL keys.

Perform the following steps:

  1. Log in to your server via SSH. For more information, read our SSH Access documentation.
  2. Create a SSL key storage directory that MySQL has access to. For example, use the mkdir /mysql_keys command to create a mysql_keys directory.

Create the required SSL keys


 

Create the SSL keys.

Perform the following steps:

Note:

In the following examples, /mysql_keys represents the key storage directory.

  1. Run the following commands to create the CA keys:

    openssl genrsa 2048 > /mysql_keys/ca-key.pem
    openssl req -sha1 -new -x509 -nodes -days 3650 -key /mysql_keys/ca-key.pem > /mysql/ca-cert.pem
  2. Run the following commands to create the server SSL key and certificate.

    openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/server-key.pem > /mysql_keys/server-req.pem
    openssl x509 -sha1 -req -in /mysql_keys/server-req.pem -days 3650 -CA /mysql/ca-cert.pem -CAkey /mysql_keys/ca-key.pem -set_serial 01 > /mysql_keys/server-cert.pem
    openssl rsa -in /mysql_keys/server-key.pem -out /mysql_keys/server-key.pem
  3. Run the following commands to create the client SSL key and certificate:

    openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /mysql_keys/client-key.pem > /mysql_keys/client-req.pem
    openssl x509 -sha1 -req -in /mysql_keys/client-req.pem -days 3650 -CA /mysql_keys/ca-cert.pem -CAkey /mysql_keys/ca-key.pem -set_serial 01 > /mysql_keys/client-cert.pem
    openssl rsa -in /mysql_keys/client-key.pem -out /mysql_keys/client-key.pem

Configure MySQL to use the SSL keys


 

Edit the MySQL configuration.

Perform the following steps:

  1. Open the /etc/my.cnf file with your preferred text editor.
  2. Insert the following lines in the [mysql] section of the my.cnf file:

    sslssl-cipher=DHE-RSA-AES256-SHA
    ssl-ca=/mysql_keys/ca-cert.pem
    ssl-cert=/mysql_keys/server-cert.pem
    ssl-key=/mysql_keys/server-key.pem
  3. Insert the following lines in the [client] section of the my.cnf file:

    Note:

    If the [client] section does not exist, you must add the section.

    sslssl-cert=/mysql_keys/client-cert.pem
    ssl-key=/mysql_keys/client-key.pem

    The following is an example of the updated my.cnf file:

    [mysqld]
    max_connections=500
    log-slow-queries
    
    max_allowed_packet=268435456
    open_files_limit=10000
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    performance-schema=0
    
    
    sslssl-cipher=DHE-RSA-AES256-SHA
    ssl-ca=/mysql_keys/ca-cert.pem
    ssl-cert=/mysql_keys/server-cert.pem
    ssl-key=/mysql_keys/server-key.pem
    
    [client]
    sslssl-cert=/mysql_keys/client-cert.pem
    ssl-key=/mysql_keys/client-key.pem
  4. Save your changes to the /etc/my.cnf file and exit your text editor.
  5. Run the following command to update the file permissions of the /mysql_keys directory and its files:

    chown -Rf mysql. /mysql_keys
  6. Run the /scripts/restartsrv_mysql script to restart MySQL.

Verify your changes


Test the SSL configuration.

Perform the following steps:

  1. To view MySQL's active SSL configuration, run the following command:

    mysql -e "show variables like '%ssl%';"

    The system will return the following example:

    +---------------+------------------------+
    | Variable_name | Value                  |
    +---------------+------------------------+
    | have_openssl  | YES                    |
    | have_ssl      | YES                    |
    | ssl_ca        | /mysql_keys/ca-cert.pem |
    | ssl_capath    |                        |
    | ssl_cert      | /mysql_keys/server-cert.pem |
    | ssl_cipher    | DHE-RSA-AES256-SHA     |
    | ssl_key       | /mysql_keys/server-key.pem  |
    +---------------+------------------------+
  2. To check a local connection to MySQL, run the following command:

    Note:

    The following command tests the cPanel example user.

    mysql -u example -p

    The command will prompt you to enter the MySQL user account password. Once connected, enter the status command. The command will return the following example:

    mysql> status
    --------------
    mysql  Ver 14.14 Distrib 5.5.42, for Linux (x86_64) using readline 5.1
      
    Connection id:        19
    Current database:   
    Current user:        example@localhost
    SSL:            Cipher in use is DHE-RSA-AES256-SHA
    Current pager:        stdout
    Using outfile:        ''
    Using delimiter:    ;
    Server version:        5.5.42-cll MySQL Community Server (GPL)
    Protocol version:    10
    Connection:        Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:        /var/lib/mysql/mysql.sock
    Uptime:            4 min 36 sec
    Threads: 1  Questions: 67  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.242
    --------------

Set up a remote MySQL user with an SSL connection


 

Create the user and allow remote access.

Note:

This step is optional.

Perform the following steps:

  1. Use cPanel's MySQL Databases interface (Home >> Databases >> MySQL Databases) to create your database user.
  2. Add the remote server's IP address to WHM's Remote MySQL interface (Home >> Databases >> Remote MySQL).
  3. On the remote MySQL server, create a SSL key storage directory that MySQL has access to. For example, use mkdir /mysql_keys to create a mysql_keys directory.

    Note:

    You can skip this step if you have created the /mysql_keys directory on the remote MySQL server.

  4. Move a copy of the client SSL certificate to the remote MySQL server's /mysql directory.
  5. On the remote MySQL server, open the /etc/my.cnf file with your preferred text editor.
  6. Insert the following lines in the [client] section of the my.cnf file:

    Note:

    If the [client] section does not exist, you must add the section.

    sslssl-cert=/mysql_keys/client-cert.pem
    ssl-key=/mysql_keys/client-key.pem

    The following is an example of the updated /etc/my.cnf file:

    [mysqld]
    max_connections=500
    log-slow-queries
    
    max_allowed_packet=268435456
    open_files_limit=10000
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    performance-schema=0
    
    
    [client]
    sslssl-cert=/mysql_keys/client-cert.pem
    ssl-key=/mysql_keys/client-key.pem
  7. Save your changes to the /etc/my.cnf file and exit your text editor.
  8. Run the following command to update the file permissions of the /mysql_keys directory and its files:

    chown -Rf mysql. /mysql_keys
  9. Restart the MySQL server.

    Note:

    If the remote MySQL server runs cPanel & WHM, use the /scripts/restartsrv_mysql script to restart MySQL.

Test the remote MySQL connection.

Perform the following steps:

  1. Log in to the remote MySQL server via SSH. For more information, read our SSH Access documentation.

  2. Use the mysql command to remotely connect to the MySQL server. The following example uses the IP address 192.168.0.1 for the destination MySQL server and example for the MySQL user:

    mysql -u example -h 192.168.0.1 -p
  3. The mysql command will connect you to the remote MySQL server and request the MySQL user password. Once connected, enter the status command. This command will return the following results:

    mysql> status
    --------------
    mysql  Ver 14.14 Distrib 5.5.42, for Linux (x86_64) using readline 5.1
      
    Connection id:        19
    Current database:   
    Current user:        example@localhost
    SSL:            Cipher in use is DHE-RSA-AES256-SHA
    Current pager:        stdout
    Using outfile:        ''
    Using delimiter:    ;
    Server version:        5.5.42-cll MySQL Community Server (GPL)
    Protocol version:    10
    Connection:        Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:        /var/lib/mysql/mysql.sock
    Uptime:            4 min 36 sec
    Threads: 1  Questions: 67  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.242
    --------------

Additional Documentation

There is no content with the specified labels

There is no content with the specified labels

There is no content with the specified labels