The steps outlined below explain how to enable security within MySQL (If it is not already enabled by default in the release your are using). There are different approaches depending on the version/distribution of MySQL you are using. If in any doubt, you should consult the appropriate documentation pages for the MySQL release you are using.
The first step is to create the new database certificates using one of the methods shown below. Once the database certs are generated, follow the common steps to deploy them on all of the database nodes.
Available as of Version 7.1.0, the tpm cert gen mysqlcerts command can perform the database certificate generation steps for you, along with handling directory creation, ownership and permissions.
The tungsten_mysql_ssl_setup command must be available (shipped with Tungsten v7.1.0 onwards).
shell>tpm cert gen my --datadir /etc/mysql/certsAbout to execute Write Action `gen mysqlcerts`, Ready to proceed (y/N)?y=========================================================================== >>> doGen processing typeSpec: mysqlcerts =========================================================================== gen::genMysqlCerts: Using datadir /etc/mysql/certs from the command line gen::genMysqlCerts: datadir /etc/mysql/certs does not exist - attempting to create...SUCCESS EXECUTING /usr/bin/sudo /opt/continuent/tungsten/tools/tungsten_mysql_ssl_setup --nols --datadir /etc/mysql/certs --extra ... --------------------------------------------------------------------------- -rw-r----- 1 mysql mysql 1765 Jul 23 13:02 /etc/mysql/certs/ca-key.pem -rw-r--r-- 1 mysql mysql 1387 Jul 23 13:02 /etc/mysql/certs/ca.pem -rw-r--r-- 1 mysql mysql 1371 Jul 23 13:02 /etc/mysql/certs/client-cert.pem -rw-r----- 1 mysql mysql 1679 Jul 23 13:02 /etc/mysql/certs/client-key.pem -rw-r----- 1 mysql mysql 1890 Jul 23 13:02 /etc/mysql/certs/private_key.pem -rw-r--r-- 1 mysql mysql 451 Jul 23 13:02 /etc/mysql/certs/public_key.pem -rw-r--r-- 1 mysql mysql 1371 Jul 23 13:02 /etc/mysql/certs/server-cert.pem -rw-r----- 1 mysql mysql 1679 Jul 23 13:02 /etc/mysql/certs/server-key.pem
The mysql_ssl_rsa_setup command must be available.
This tool is shipped with MySQL 5.7 onwards and makes the creation of all of the certificates much easier. If you have this tool available, then you can follow these steps:
Invoke mysql_ssl_rsa_setup on one of the hosts.
This will generate the SSL certificates and RSA keys by default in
/var/lib/mysql. These files should be copied to
the other hosts.
The mysql_ssl_rsa_setup supports the
--datadir=/my/custom/path/ option if the you want
to use a different location. Continuent recommends using
/etc/mysql/certs as the location.
The generated pem files should be readable by the
tungsten and mysql OS users.
The "Common Name" field for the Server and Client certificates MUST be different than the "Common Name" specified for the CA Cert.
Generate CA Cert
shell>openssl genrsa 2048 > $MYSQL_CERTS_PATH/ca-key.pemshell>openssl req -sha256 -new -x509 -nodes -days 3650 \ -key $MYSQL_CERTS_PATH/ca-key.pem \ -out $MYSQL_CERTS_PATH/ca.pem
Generate Server Cert
shell>openssl req -sha256 -newkey rsa:2048 -nodes -days 3650 \ -keyout $MYSQL_CERTS_PATH/server-key.pem \ -out $MYSQL_CERTS_PATH/server-req.pemshell>openssl rsa -in $MYSQL_CERTS_PATH/server-key.pem -out $MYSQL_CERTS_PATH/server-key.pemshell>openssl x509 -sha256 -req -in $MYSQL_CERTS_PATH/server-req.pem -days 3650 \ -CA $MYSQL_CERTS_PATH/ca.pem \ -CAkey $MYSQL_CERTS_PATH/ca-key.pem \ -set_serial 01 \ -out $MYSQL_CERTS_PATH/server-cert.pem
Generate Client Cert
shell>openssl req -sha256 -newkey rsa:2048 -days 3600 -nodes \ -keyout $MYSQL_CERTS_PATH/client-key.pem \ -out $MYSQL_CERTS_PATH/client-req.pemshell>openssl rsa -in $MYSQL_CERTS_PATH/client-key.pem -out $MYSQL_CERTS_PATH/client-key.pemshell>openssl x509 -sha256 -req -in $MYSQL_CERTS_PATH/client-req.pem -days 3650 \ -CA $MYSQL_CERTS_PATH/ca.pem \ -CAkey $MYSQL_CERTS_PATH/ca-key.pem \ -set_serial 01 \ -out $MYSQL_CERTS_PATH/client-cert.pem
Verify All Certificates
shell> openssl verify -CAfile $MYSQL_CERTS_PATH/ca.pem \
$MYSQL_CERTS_PATH/server-cert.pem $MYSQL_CERTS_PATH/client-cert.pemThese are the common steps needed to deploy database SSL for all three of the above cert generation methods.
Update the configuration files on all
Database nodes, and repeat as needed
so that every Database node has the
same database cert configuration
(my.cnf and optionally
tungsten.ini).
Add the SSL certificates you just created to the
[mysqld] stanza in your
my.cnf
[mysqld] ssl_ca=/etc/mysql/certs/ca.pem ssl_cert=/etc/mysql/certs/server-cert.pem ssl_key=/etc/mysql/certs/server-key.pem require_secure_transport=ON
Add the following to the [client] stanza in your
my.cnf
[client] ssl_ca=/etc/mysql/certs/ca.pem ssl_cert=/etc/mysql/certs/client-cert.pem ssl_key=/etc/mysql/certs/client-key.pem ssl_mode=REQUIRED
This will enable client SSL, and also require the mysql client to
connect through SSL to the server. You may adjust the client ssl_mode
option to suit your needs (DISABLED,
PREFERRED (default), REQUIRED,
VERIFY_CA, VERIFY_IDENTITY).
The tpm command will parse the
my.cnf file and retrieve the certificates paths.
It is still possible to specify different paths via the following
tungsten.ini settings:
datasource-mysql-ssl-ca=/etc/mysql/certs/ca.pem datasource-mysql-ssl-cert=/etc/mysql/certs/client-cert.pem datasource-mysql-ssl-key=/etc/mysql/certs/client-key.pem
tpm install will add these client certificates to the tungsten truststore, keystore, connector truststore and keystore.
Copy certs to all Database nodes (repeat as needed so that every Database node has the same certificates)
shell> rsync -av $MYSQL_CERTS_PATH/ yourDBhost:$MYSQL_CERTS_PATH/Set proper ownership and permissions on ALL DB nodes
shell>sudo chown -R mysql: $MYSQL_CERTS_PATH/shell>sudo chmod -R g+w $MYSQL_CERTS_PATH/
Restart MySQL on all nodes using the standard rolling maintenance procedure - see Section 6.15.3, “Performing Maintenance on an Entire Dataservice” for more information.
cctrl>lscctrl>datasource db3 shundb3#service mysql restartcctrl>recovercctrl>datasource db2 shundb2#service mysql restartcctrl>recovercctrl>switch to db2cctrl>datasource db1 shundb1#service mysql restartcctrl>recovercctrl>switch to db1cctrl>ls
Add a new user to MySQL that requires SSL to connect. Do this just once on the current Primary and let it propagate to the Replicas.
shell>tpm mysqlmysql>DROP USER ssl_user;mysql>CREATE USER ssl_user@'%' IDENTIFIED BY 'secret';mysql>GRANT ALL ON *.* TO ssl_user@'%' REQUIRE SSL WITH GRANT OPTION;mysql>flush privileges;
Verify that MySQL is working with SSL
Expect this to fail, because the ssl_user is only allowed to connect to the database using SSL:
shell> mysql -u ssl_user -psecret -h 127.0.0.1 -P 13306Expect this to pass, because we have supplied the proper SSL credentials:
shell> mysql -u ssl_user -psecret -h 127.0.0.1 -P 13306 --ssl-ca=/etc/mysql/certs/ca.pemVerify SSL:
mysql> status
...
SSL: Cipher in use is DHE-RSA-AES256-SHA
...If you are able to login to MySQL and see that the status is SSL: Cipher in use, then you have successfully configured MySQL to use SSL.