How to install mysql and configure SSL?

Resolution

1. Download and install the required mysql related packages

   yum install mysql-server perl-DBD-MySQL perl-DBI
   

2. Start mysql.

   service mysqld start
   

3. Optionally set mysqld to start at boot

   chckconfig mysqld on
   

4. Change mysql root password

   /usr/bin/mysqladmin -u root password 'mysql'
   

5. Configure SSL for mysql server and the clients that will access the server

   mkdir -p /etc/mysql/newcerts  
   chown -R mysql:mysql /etc/mysql/newcerts
   

6. Create a certificate authority

   cd /etc/mysql/newcerts  
   openssl genrsa 2048 > ca-key.pem
   

   • NOTE This command will ask details of your certificate provider, provide a unique Common Name when asked

   openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem
   

7. Create a certificate for the server using the CA certificate generated above

   • NOTE Do not provide a password if asked in the next step
     • The Common Name used here must differ from the one used for the Certificate Authority above.

   openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
   
   openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
   

8. Create a certificate for the clients using the same CA certificate

   • NOTE You must provide the details for the client that will connect to the server.
     • The Common Name used here must differ from the one used for the Certificate Authority and the Server certificate above.

   openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem 
   
   openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
   

9. Make sure following entries are present in /etc/my.cnf under the [mysqld] section

   ssl
   ssl-ca=/etc/mysql/newcerts/ca-cert.pem
   ssl-cert=/etc/mysql/newcerts/server-cert.pem
   ssl-key=/etc/mysql/newcerts/server-key.pem
   

10. Restart mysqld

    service mysqld restart
    

11. Ensure that mysql root is authenticated with SSL and has correct permissions

    • NOTE use your mysql root password here.

    mysql -u root -p  
    mysql> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql' REQUIRE SSL;
    mysql> quit
    

Client side configuration

• We can add global configuration in /etc/my.conf file for all users to use SSL to connect to MySQL server.

# cat /etc/my.cnf
[client]
ssl-ca=/tmp/ca-cert.pem
ssl-cert=/tmp/client-cert.pem
ssl-key=/tmp/client-key.pem

• We can have client configuration for a specific user in its $HOME/.my.cnf

# cat ~/.my.cnf
[client]
ssl-ca=/tmp/ca-cert.pem
ssl-cert=/tmp/client-cert.pem
ssl-key=/tmp/client-key.pem

1. Test that SSL is working

   • login to the database from MySQL server

   cd /etc/mysql/newcerts  
   mysql --ssl-cert=ca-cert.pem --ssl-key=client-key.pem --ssl-cert=client-cert.pem -u root -p -v -v -v  
   Enter password: <password>
   

• Login to database from client machine

  # mysql -h <Server IP/FQDN> -u root -p
  

  • Check for the ciphers

  mysql> SHOW STATUS LIKE 'Ssl_cipher';  
  --------------  
  SHOW STATUS LIKE 'Ssl_cipher'  
  --------------  
  
  +---------------+--------------------+  
  | Variable_name | Value              |  
  +---------------+--------------------+  
  | Ssl_cipher    | DHE-RSA-AES256-SHA |
  +---------------+--------------------+  
  1 row in set (0.00 sec)  
  
  mysql> show variables like '%%ssl%%';  
  --------------  
  show variables like '%%ssl%%'  
  --------------  
  
  +---------------+-------------------------------------+  
  | Variable_name | Value                               |  
  +---------------+-------------------------------------+  
  | have_openssl  | YES                                 |  
  | have_ssl      | YES                                 | 
  | ssl_ca        | /etc/mysql/newcerts/ca-cert.pem     |  
  | ssl_capath    |                                     |  
  | ssl_cert      | /etc/mysql/newcerts/server-cert.pem |  
  | ssl_cipher    |                                     |  
  | ssl_key       | /etc/mysql/newcerts/server-key.pem  |  
  +---------------+-------------------------------------+  
  7 rows in set (0.01 sec)  
  
  mysql> quit