Secured MySQL InnoDB Cluster with Certificate creation using OpenSSL

This is a demo tutorial to show how we can create InnoDB Cluster with newly installed Certificate and having X509 certificate verification via MySQL Router connection.   

Recorded Video

The full process is recorded on Youtube - showing creating InnoDB Cluster with newly installed CA, Server Certificates.  The Router creation is configured with SSL Server certificate from the same CA certificate across Server nodes.   The creation of User (create user my509user identified by '....' require X509) using X509 certificate PASSTHROUGH verification via Router connection.

https://www.youtube.com/watch?v=w1xgpjw0VTw

Environment

The following environment was tested 

Oracle Linux Server release 8.6

MySQL Server 8.0.31

MySQL Shell 8.0.31

MySQL Router 8.0.31

Github Script

The github script provides the steps to guide thru Installation of 3 nodes 

https://github.com/ivanxma/mylab/tree/main/13-InnoDBCluster/99-SSL-cert-IC

To configure the node1/node2/node3 hostname under ./comm.sh   (Change the hostname based on your environment)

```

export HOST1=workshop20

export HOST2=workshop22

export HOST3=workshop23

```

Background

The creation of InnoDB Cluster creates Internal User (mysql_innodb_cluster_<server_id>@'%') 

Here is an example :

mysql> select user,host from mysql.user;

+--------------------------+-------------+

| user                     | host        |

+--------------------------+-------------+

| gradmin                  | %           |

| mysql_innodb_cluster_101 | %           |

| mysql_innodb_cluster_201 | %           |

| mysql_innodb_cluster_301 | %           |

| mysql.infoschema         | localhost   |

| mysql.session            | localhost   |

| mysql.sys                | localhost   |

| root                     | localhost   |

+--------------------------+-------------+

8 rows in set (0.00 sec)

Note : the RENAME USER does not work.

 (e.g.  mysql > rename user mysql_innodb_cluster_101@'%' to mysql_innodb_cluster_101@'10.0.%' )  

"%" as host may can be restricted to specific IP subnet via replicationAllowedHost option with MySQL Shell Admin API.   

https://dev.mysql.com/doc/mysql-shell/8.0/en/create-cluster.html#mysql-innodb-cluster-replicationallowedhost

As an example to restrict user creation with Host from subnet 192.0.2.0/24:

mysql-js> dba.createCluster('testCluster', {replicationAllowedHost:'192.0.2.0/24'})

Group Replication with communication stack "MYSQL" goes through the standard MySQL port (for example 3306.  The encrypted communication / recovery channel is established with SSL certificate.    

https://dev.mysql.com/doc/refman/8.0/en/group-replication-secure-socket-layer-support-ssl.html

group_replication_ssl_mode  is configured as REQUIRED to ensure secured SSL between nodes.

REQUIRED

Establish a secure connection if the server supports secure connections.

group_replication_ssl_mode can also be configured as VERIFY_CA to ensure certificate being used and verified.

VERIFY_CA

Like REQUIRED, but additionally verify the server TLS certificate against the configured Certificate Authority (CA) certificates.

The following sections describe the steps to setup the VERIFY_CA option and creation of certificates using OpenSSL command.

1. On each node to initialize MySQL Data Directory [ Script ] 

2. Create CA certificate on Node1 and using the same CA certificate to create server-cert.pem for each server 

Refer to CA creation [ Script ]

Refer to Server certificate creation [ Script ] 

3. Startup the server with unique $SERVER_ID on each HOST [ Script ] 

4.On each host, configure Group Replication Admin User [ Script ] 

5. Create 3 nodes Innodb Cluster with MySQL Shell with specific options to secure the SSL connection

 [ Create Cluster Script ] [ Add Instances Script ]

6. Bootstrap MySQL Router [ Script ]

7. Create Router Certificate with same CA certificate and Configure the mysqlrouter.conf  

[ Create Router Script ] [ Config PASSTHROUGH ]

8. Start Router [ Script ] 

9 Finally, creating a user with X509 and Login via MySQL Router

    Login to Primary Server with 'root'

mysql > create user my509user@'%' identified by 'my509pass' REQUIRE X509;

     Alternatively, user creation can follow more constraint with 

REQUIRE SUBJECT "/...."

 REQUIRE ISSUER "/..."

    For example (Check with the client certificate for the ISSUER or SUBJECT content  [ openssl x509 -text -in <certificate file> ] )

    example 1 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE ISSUER "/O=MySQL/CN=MySQL" ;

    example 2 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE SUBJECT "/CN=hostname";

    example 3 : mysql > create user my509user@'%' identified by 'my509pass' REQUIRE SUBJECT "/CN=hostname" AND ISSUER "/O=MySQL/CN=MySQL;

 

    Login using certificate via the Router  For example : (Given that the 'client-key.pem' 'client-cert.pem' is created on each host)

mysql -umy509user -pmy509pass -h127.0.0.1 -P6446 --ssl-mode=VERIFY_CA --ssl-ca=~/data/3310/ca.pem --ssl-cert=~/data/3310/client-cert.pem --ssl-key=~/data/3310/client-key.pem 

References

https://github.com/ivanxma/mylab/tree/main/13-InnoDBCluster/99-SSL-cert-IC

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster-setting-options.html

https://dev.mysql.com/doc/mysql-shell/8.0/en/create-cluster.html#mysql-innodb-cluster-replicationallowedhost