Page tree
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. If you do not enable encryption, the MySQL server transmits all remote traffic, which includes user credentials, in plain text. When you secure your MySQL connections, malicious users cannot intercept and read your MySQL traffic in transit.

Important:

To perform the steps in this guide, you must possess root access to the client and host server.

Create the SSL key storage directory


 

Create the directory to store the SSL keys

  1. Log in to your server via SSH. For more information, read our SSH Access documentation.
  2. Create a directory, that MySQL can access, to store the SSL keys. For example, run the following command to create the /mysql_keys directory:
mkdir /mysql_keys

Create the required SSL keys


 

Create the SSL keys

Note:

In the following code 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

To configure MySQL to use the SSL keys you created, perform the following steps:

  1. Open the /etc/my.cnf file with a text editor.
  2. In the [mysql] section of the configuration file add the following lines:

    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. In the [client] section of the configuration file, add the following lines:

    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 example contains an example of the updated /etc/my.cnf file:

     Click to view...
    [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 the files within:

    chown -Rf mysql. /mysql_keys
  6. Run the following script to restart MySQL:

    /scripts/restartsrv_mysql

Verify your changes


Test the SSL configuration

To test MySQL's 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 output should resemble the following example:

     Click to view...
    +---------------+------------------------+
    | 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 test the cPanel user example.

    mysql -u example -p

    The command will prompt you to enter the account's password. Once connected, enter the status command. The command should return the following similar results:

     Click to view...
    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 (optional)


 

Create the user and allow remote access

Note:

This step is optional if you do not require remote MySQL user access.

Perform the following steps to set up a remote MySQL user:

  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 directory, that MySQL can access, to store the SSL keys. For example, run the following command to create the /mysql directory:

    Note:

    If you have already created the /mysql_keys directory on the remote MySQL server, you can skip this step.

    mkdir /mysql_keys
  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 a text editor.
  6. In the [client] section of the configuration file, add the following lines:

    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 example contains an example of the remote MySQL server's updated /etc/my.cnf file:

     Click to view...
    [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 the files within:

    chown -Rf mysql. /mysql_keys
  9. Restart the MySQL server. If the remote MySQL server runs cPanel & WHM, use the following script to restart MySQL:

    /scripts/restartsrv_mysql

Test the remote MySQL connection

To 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 destination MySQL server. The following example uses 192.168.0.1 as the IP address for the destination MySQL server and example as the MySQL user:

    mysql -u example -h 192.168.0.1 -p

    The command will connect to the remote MySQL server and prompt you to enter the account's password. Once connected, enter the status command. The command should return the following similar results:

     Click to view...
    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