In a recent article, I described how to setup a basic MySQL Cluster with two data nodes and a combined SQL and management node. In this article, I am going to highlight a hew more things and we are going to adapt the cluster a little bit.
For making our lives easier, we can use hostnames which are easier to remember than IP addresses. Hostnames can be specified for each VM in the file /etc/hosts . For each request to the hostname, the operating system will lookup the corresponding IP address. We need to change this file on all three nodes to the following example:
root@node1:~# cat /etc/hosts 127.0.0.1 localhost 192.168.0.81 node1 192.168.0.82 node2 192.168.0.83 node3 # The following lines are desirable for IPv6 capable hosts ::1 localhost ip6-localhost ip6-loopback ff02::1 ip6-allnodes ff02::2 ip6-allrouters
Now we can lookup IP addresses by the associated hostname.
The management and the SQL node are both running in the same VM. Now that we introduced the hostnames for all machines, we need to reconfigure the cluster manager and the MySQL Server. The cluster configuration is stored in the file /opt/mysql/server-5.7/mysql-cluster/config.ini . Replace all the IP addresses with the corresponding host name.
root@node1:~# cat /opt/mysql/server-5.7/mysql-cluster/config.ini [ndb_mgmd] NodeId=1 # Management process options: hostname= node1 # Hostname of the manager datadir=/opt/mysql/server-5.7/mysql-cluster/cluster-data # Directory for the log files [ndbd] NodeId=2 hostname= node2 # Hostname of the first data node datadir=/usr/local/mysql/data # Remote directory for the data files [ndbd] NodeId=3 hostname= node3 datadir=/usr/local/mysql/data # Remote directory for the data files [mysqld] # SQL node options: NodeId=4 hostname=node1
For reconfiguring the SQL node, we need to adapt the file /etc/my.cnf . We need to replace all IP addresses in this file too.
root@node1:~# cat /etc/my.cnf [mysqld_safe] socket = /tmp/mysql.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /tmp/mysql.pid socket = /tmp/mysql.sock port = 3306 basedir = /opt/mysql/server-5.7 datadir = /opt/mysql/server-5.7/data tmpdir = /tmp skip-external-locking bind-address=node1 key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error.log expire_logs_days = 10 max_binlog_size = 100M # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ndbcluster # run NDB storage engine ndb-connectstring=node1 [mysql_cluster] # Options for MySQL Cluster processes: ndb-connectstring=node1
If done, stop the cluster and the SQL node like this:
root@node1:~# /opt/mysql/server-5.7/bin/ndb_mgm -e shutdown Connected to Management Server at: node1:1186 3 NDB Cluster node(s) have shutdown. Disconnecting to allow management server to shutdown. root@node1:~# service mysql stop
Adapt the Data Nodes
Replacing the IP adresses with hostnames is pretty straightforward. Change the IP address on each node with the host name in the file /etc/my.cnf :
[mysqld] # Options for mysqld process: ndbcluster [mysql_cluster] ndb-connectstring=node1
Startup the Cluster
Now that we have exchanged all IP addresses for the hostname, we can restart the cluster as follows:
# Login into node1 root@node1:~# /opt/mysql/server-5.7/bin/ndb_mgmd --reload -f /opt/mysql/server-5.7/mysql-cluster/config.ini MySQL Cluster Management Server mysql-5.7.16 ndb-7.5.4 # Login into each data node and restart the ndbd process stefan@node2:~$ sudo /opt/mysql/server-5.7/bin/ndbd stefan@node3:~$ sudo /opt/mysql/server-5.7/bin/ndbd # Start the MySQL instance root@node1:~# service mysql start
Now the cluster should be up again. Verify it like this:
root@node1:~# /opt/mysql/server-5.7/bin/ndb_mgm -- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: node1:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @192.168.0.82 (mysql-5.7.16 ndb-7.5.4, Nodegroup: 0, *) id=3 @192.168.0.83 (mysql-5.7.16 ndb-7.5.4, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=1 @192.168.0.81 (mysql-5.7.16 ndb-7.5.4) [mysqld(API)] 1 node(s) id=4 @192.168.0.81 (mysql-5.7.16 ndb-7.5.4) ndb_mgm>
Encrypt Data in Transit
For improving the security of the cluster, we can encrypt the traffic which is exchanged between the MySQL node and the MySQL client. Please note that this encryption method is not specific for the cluster, but rather encrypts the traffic between the MySQL client and the MySQL server. You can use this method also for standard MySQL server setups. It does not cover encryption between the data nodes. By design, data nodes are intended to be run in a private network, not via the open Interwebs.
Create a CA Authority Key and Certificate
To do so, we work on node 1. In the first step we create a CA authority and a CA certificate.
# Change into the installation directory root@node1: cd /opt/mysql/server-5.7 # Create a folder called certs root@node1:/opt/mysql/server-5.7# mkdir certs # Enter the folder root@node1:/opt/mysql/server-5.7# cd certs/ # Create the CA key root@node1:/opt/mysql/server-5.7/certs# openssl genrsa 2048 > ca-key.pem # Using that key, create a new CA certificate valid for 365 days. Leave the password empty # Example data to provide in the next step: ## Country Name (2 letter code) [AU]:AT ## State or Province Name (full name) [Some-State]:Tirol ## Locality Name (eg, city) :Innsbruck ## Organization Name (eg, company) [Internet Widgits Pty Ltd]:Acme ## Organizational Unit Name (eg, section) :Infra ## Common Name (e.g. server FQDN or YOUR name) :ca-node ## Email Address :firstname.lastname@example.org openssl req -new -x509 -nodes -days 365 -key ca-key.pem -out ca-cert.pem
Create a Server Certificate
In the second step, we create a server certificate and sign it with the CA from the previous step.
# Create a new key root@node1:/opt/mysql/server-5.7/certs# openssl req -newkey rsa:2048 -days 365 -nodes -keyout server-key.pem -out server-req.pem # Convert the key root@node1:/opt/mysql/server-5.7/certs# openssl rsa -in server-key.pem -out server-key.pem # Sign it with the CA openssl x509 -req -in server-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
The server key is ready. We now need to create a client certificate.
Create a Client Certificate
In a rather similar fashion, we create a client certificate.
# Create the certificate openssl req -newkey rsa:2048 -days 365 -nodes -keyout client-key.pem -out client-req.pem # Create the key, again passwordless openssl req -newkey rsa:2048 -days 365 -nodes -keyout client-key.pem -out client-req.pem # Sign it openssl x509 -req -in client-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem # Convert it openssl rsa -in client-key.pem -out client-key.pem
Now we have created the keys and can configure MySQL for using SSL encryption.
Configure MySQL to use Encryption
Add the following configuration parameters to the /etc/my.cnf file in order to define server and client certificates and keys.
# MySQL Server [mysqld] ssl-ca = /opt/mysql/server-5.7/certs/ca-cert.pem ssl-key = /opt/mysql/server-5.7/certs/server-key.pem ssl-cert = /opt/mysql/server-5.7/certs/server-cert.pem # MySQL Client Configuration [mysql] ssl-ca = /opt/mysql/server-5.7/certs/ca-cert.pem ssl-key = /opt/mysql/server-5.7/certs/client-key.pem ssl-cert = /opt/mysql/server-5.7/certs/client-cert.pem
Restart the MySQL server and test the SSL encryption. You can immediately see that the client uses SSL, although it does not really make a lot of sense for the local user.
root@node1:/opt/mysql/server-5.7/certs# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.16-ndb-7.5.4-cluster-gpl MySQL Cluster Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. You are enforcing ssl conection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. mysql>
Let’s create a new test user, allow remote access from 192.168.0.12 and enforce SSL connections:
mysql> CREATE USER 'ssl-test'@'192.168.0.12' IDENTIFIED BY 'obscure' REQUIRE SSL; Query OK, 0 rows affected (0.01 sec)
Switch to the console of the machine 192.168.0.12 and try to connect. You can then display some variables about the SSL configuration.
email@example.com:~$ mysql -p -h 192.168.0.81 -u ssl-test -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.16-ndb-7.5.4-cluster-gpl MySQL Cluster Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show variables like '%ssl%'; mysql> show variables like '%ssl%'; +---------------+---------------------------------------------+ | Variable_name | Value | +---------------+---------------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /opt/mysql/server-5.7/certs/ca-cert.pem | | ssl_capath | | | ssl_cert | /opt/mysql/server-5.7/certs/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /opt/mysql/server-5.7/certs/server-key.pem | +---------------+---------------------------------------------+ 9 rows in set (0,01 sec)
To verify that you are actually using SSL encryption, the following command helps, it gives SSL: Cipher in use is DHE-RSA-AES256-SHA :
mysql> status -------------- mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper Connection id: 11 Current database: Current user: firstname.lastname@example.org SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.16-ndb-7.5.4-cluster-gpl MySQL Cluster Community Server (GPL) Protocol version: 10 Connection: 192.168.0.81 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 32 min 36 sec Threads: 4 Questions: 14 Slow queries: 0 Opens: 114 Flush tables: 2 Open tables: 9 Queries per second avg: 0.007 --------------
What about the Data Nodes?
So far we encrypted only the traffic between the MySQL client and the MySQL server. This configuration does not differ from a single server or replication setup and does not include the traffic between the data nodes, which is not encrypted at all. MySQL cluster has been designed to be run in a controlled environment within high speed network locally. The MySQL Cluster FAQ states:
It is very unlikely that a cluster would perform reliably under such conditions, as NDB Cluster was designed and implemented with the assumption that it would be run under conditions guaranteeing dedicated high-speed connectivity such as that found in a LAN setting using 100 Mbps or gigabit Ethernet—preferably the latter. We neither test nor warrant its performance using anything slower than this.
Also, it is extremely important to keep in mind that communications between the nodes in an NDB Cluster are not secure; they are neither encrypted nor safeguarded by any other protective mechanism. The most secure configuration for a cluster is in a private network behind a firewall, with no direct access to any Cluster data or management nodes from outside.