generate-mysql-ssl.md

Getting Started with MySQL Over SSL

See references here:

1. https://lowendbox.com/blog/getting-started-with-mysql-over-ssl/
2. https://stackoverflow.com/questions/29989010/setting-up-mysql-ssl-connections

export CommonName="www.example.com"
export LocalityName="database"
export StateOrProvinceName="Rio de Janeiro"
export OrganizationName="ByJG"
export CountryName="BR"

Server

Generate Server SSL

openssl genrsa 2048 > ca-key.pem

openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem \
    -subj "/C=$CountryName/ST=$StateOrProvinceName/L=$LocalityName/O=$OrganizationName/CN=$CommonName" \
    > ca-cert.pem

openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem \
    -subj "/C=$CountryName/ST=$StateOrProvinceName/L=$LocalityName/O=$OrganizationName/CN=$CommonName" \
    > server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -sha1 -req -in server-req.pem -days 730  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Open /etc/mysql/conf.d/ssl.cnf

[mysqld]
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
require-secure-transport = ON

Connect to MySQL

Create a user with password

GRANT ALL PRIVILEGES ON *.* TO 'iamsecure'@'%' IDENTIFIED BY 'password' REQUIRE SSL;

Create a user without password

SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
GRANT ALL PRIVILEGES ON *.* TO 'iamsecure'@'%' REQUIRE SSL;

Client

openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem \
    -subj "/C=$CountryName/ST=$StateOrProvinceName/L=$LocalityName/O=$OrganizationName/CN=$CommonName" \
    > client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

Running Server (Docker):

chmod 999:999 *.pem
docker run --name some-mysql --rm \
    -v $PWD/custom:/etc/mysql/conf.d \
    -v $PWD/ssl:/etc/mysql/ssl \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=password -d mysql:5.7

Connect to Client

mysql --ssl-ca=ca-cert.pem --ssl-key=client-key.pem --ssl-cert=client-cert.pem -u iamsecure -p -h 127.0.0.1