Security Configuration For MySQL NDB Cluster Replication

In this blog, we will discuss about how to setup MySQL NDB Cluster replication in a more secure way with the help of binary log and relay log encryption and a secure connection. These measures protect binary log dat in transit and at rest.

Let’s create two MySQL NDB Clusters with the following environment, Here, one will be termed as ‘source’ cluster and the other one will be termed as ‘replica’ cluster.

• MySQL NDB Cluster version (Latest GA version)
• 1 Management node
• 4 Data nodes
• 1 MySQLDs
• Configuration slots for up to 4 additional API nodes

Step 1: Start both of the Clusters

Let’s start both the source cluster and replica cluster but do not start the MySQLD servers from both the clusters as we want to modify their configuration first.

Start the second cluster (replica):

Now both the clusters are up and running.

Step 2: Configure for secure connections

MySQL supports encrypted connections between clients and the MySQL server using the TLS (Transport Layer Security) protocol. TLS is also referred as SSL. To have a secure connection, we must configure both servers and clients. With MySQL replication, a MySQL server acting as a Replica is a client to a MySQL server acting as a Source.

TLS related files for both Server and Client roles (ca.pem, server-cert.pem, server-key.pem, client-cert.pem, and client-key.pem) can be created by MySQL Server during installation and kept under the mysql data directory. The server needs the ca.pem, server-cert.pem, and server-key.pem files, while clients use the client-cert.pem and client-key.pem files to connect to the server.

For details about these files can be found from the MySQL manual, link here.

A) Configuration for Servers:

In the Source cluster MySQL server configuration file (my.cnf) add the following configuration and file paths.

ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
require_secure_transport=ON

By enabling require_secure_transport, all MySQL clients including replica MySQL servers are required to use encrypted connections otherwise their connection will not succeed.

B) Configuration for Clients (including the replica cluster MySQL Server):

From the previous steps we saw that example clients TLS files also get created during mysql installation. Let’s scp (secure copy) these client ssl files (ca.pem, client-cert.pem, client-key.pem) from the source MySQL server to client locations either into mysql data directory or to any other location.

I have created a separate directory for storing the ssl files on the client side i.e. /export/home/saroj/mysql-tree/8.0.25/ndbd/mysqld_data/ssl_data/

C) Add the client ssl files in the MySQL configuration file (my.cnf):

ssl_ca=/export/home/saroj/mysql-tree/8.0.25/ndbd/mysqld_data/ssl_data/ca.pem
ssl_cert=/export/home/saroj/mysql-tree/8.0.25/ndbd/mysqld_data/ssl_data/client-cert.pem
ssl_key=/export/home/saroj/mysql-tree/8.0.25/ndbd/mysqld_data/ssl_data/client-key.pem

Step 3: Configure for binary log and relay log encryption

In this step, we will encrypt both binary log and relay log so that data is encrypted when it is at rest. We need to configure both the source and replica MySQL server my.cnf files.

[mysqld]
binlog_encryption = ON
early-plugin-load=keyring_file.so

The binlog_encryption option, enables encryption for binary log files and relay log files on this host. To use log file encryption, a keyring plugin must be installed and configured to supply MySQL Server's keyring service. MySQL provides many keyring plugin, here we have chosen ‘keyring_file’. This plugin is available in MySQL Community Edition and MySQL Enterprise Edition distributions. Users do not have to install the plugin specifically but rather pass the file name to system variable early-plugin-load. The ‘keyring_file.so’ file can normally be found at the mysql installation path i.e. mysql/lib/plugin/* though of course this might vary between MySQL installation types (DEB, RPM, Source Code build, ...).

Step 4: Start the MySQLD servers on both source and replica clusters:

Start the source cluster’s MySQL server:

Start the replica cluster’s MySQL server:

Step 5: Create a user ‘user1’ with limited privileges

By default the ‘root’ user, has almost all privileges which makes it more susceptible to misuse. To avoid this, we will create a user called ‘user1’ and will assign only the ‘replication slave’ privilege to it. With this privilege, ‘the user1’ account can be used to connect securely to MySQL server (source cluster) and fetch data.

Sub-Steps:
a) Login as ‘root’ user in the MySQL server of source cluster.
b) Create a user ‘user1’ and update the ‘user1’ account to allow permitted clients to connect
c) Grant the ‘replication slave’ privilege to the user ‘user1’.

On the Source Cluster MySQl Server:

mysql> bin/mysql -u root -P"13010";
mysql> create user 'user1'@'%' identified by 'user123#';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'user1'@'%';
mysql> flush privileges;

Step 6: Start the replication:

On the replica cluster, MySQL server:

mysql> bin/mysql -u root -P"13010";
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='100.103.21.66', SOURCE_USER='user1', SOURCE_PASSWORD='user123#', SOURCE_PORT=13010, SOURCE_SSL=1;
mysql> start replica;

Here we set, SOURCE_SSL=1, to use and require SSL (TLS) for the connection made to the source, and SOURCE_USER=’user1’ to use our restricted-privilege account on the source MySQL Server.

Let’s check the replication state:

From the Replica’s MySQL server:

mysql> show replica status\G

From the above image, we can see that replication has been establish between source cluster and replica cluster.

Step 7: Let’s do some check:

1) Show that that, the binary logs and relay logs are encrypted:

From the source’s cluster MySQL server:

mysql> SHOW BINARY LOGS;

From the above image, we can see that binary log is encrypted.

2) To verify the keyring_file plugin status:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';

From the below image, we can see that, plugin is active.

3) Let’s see if we can access directly binary log files and read its content:

shell> bin/mysqlbinlog --verbose /export/home/saroj/mysql-tree/8.0.25/ndbd/mysqld_data/binlog/test01/test01-binlog.000001

We can see the error message from the above image i.e. “ERROR: Reading encrypted log files directly is not supported.” while trying to access it through mysqlbinlog tool.

4) Let’s see if our replication connection is really secured:

mysql> bin/mysql -u root -P"13010"
mysql> stop replica;
mysql> reset replica all;
mysql> quit;

Let’s start a new replication connection:

mysql> bin/mysql -u root -P"13010";
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='100.103.21.66', SOURCE_USER='user1', SOURCE_PASSWORD='user123#', SOURCE_PORT=13010;
mysql> start replica;

Note that from the above ‘change replication source’ command, we have not passed ‘SOURCE_SSL=1’. Let’s check the replication status:

Last_IO_Error: error connecting to master 'user1@100.103.21.66:13010' - retry-time: 60 retries: 1 message: Connections using insecure transport are prohibited while --require_secure_transport=ON.

The Source has Require_secure_transport=ON, so it does not let any client connect without TLS. Having SOURCE_SSL=1 in the CHANGE REPLICATION SOURCE command controls whether the Replica tries to connect to the Source with or without TLS. When SOURCE_SSL=0, the Replica tries to connect to the Source without TLS, and the Source rejects it.

Step 8: Let’s create database, table and some operations on it

Now let’s create a database ‘test1’, table ‘t1’ and will insert rows into table ‘t1’ continuously from the source cluster. I am using a tool to insert rows, can also be done through any script.

Let’s check the rows count from replica’s MySQL server:

So from the above image, we can see that replica’s have the same number of rows count than the source.

Let’s check the state of replication:

mysql> show replica status\G

From the above image, we can see that replica is waiting for more events from the source and replication is intact between source and replica clusters.

In this blog, we have created a new replication user with reduced privileges, setup secure connections over TLS and arranged for our binlog and relay log files to be encrypted.

There is much more to all of these features, and please refer to the manual. This blog is a good introduction and works well with NDB Cluster. Users should really understand more about Client/Server certificates, keys, user accounts etc before trying out above.

This concludes our discussion on security configuration for MySQL NDB Cluster Replication.