A MySQL 5.7 Cluster Based on Ubuntu 16.04 LTS – Part 2

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.

Using Hostnames

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	localhost	node1	node2	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.

Adapting Node1

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
# Management process options:
hostname= node1  # Hostname of the manager
datadir=/opt/mysql/server-5.7/mysql-cluster/cluster-data  # Directory for the log files

hostname= node2    # Hostname of the first data node
datadir=/usr/local/mysql/data   # Remote directory for the data files

hostname= node3
datadir=/usr/local/mysql/data   # Remote directory for the data files

# SQL node options:

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 
socket          = /tmp/mysql.sock
nice            = 0

# * 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

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


ndbcluster # run NDB storage engine

# Options for MySQL Cluster processes:

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 :

# Options for mysqld process:


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	@  (mysql-5.7.16 ndb-7.5.4, Nodegroup: 0, *)
id=3	@  (mysql-5.7.16 ndb-7.5.4, Nodegroup: 0)

[ndb_mgmd(MGM)]	1 node(s)
id=1	@  (mysql-5.7.16 ndb-7.5.4)

[mysqld(API)]	1 node(s)
id=4	@  (mysql-5.7.16 ndb-7.5.4)


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 []:test@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
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
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

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.

Let’s create a new test user, allow remote access from and enforce SSL connections:

mysql> CREATE USER 'ssl-test'@'' IDENTIFIED BY 'obscure' REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

Switch to the console of the machine and try to connect. You can then display some variables about the SSL configuration.

stefan@$ mysql -p -h -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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


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:		ssl-test@
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: 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.




Continue reading

Encrypt a USB Drive (or any other partition) Using LUKS

Did you ever want to feel like secret agent or do you really need to transport and exchange sensitive data? Encrypting your data is not much effort and can be used to protect a pen drive or any partition and the data on it from unauthorized access. In the following example you see how to create an encrypted partition on a disk. Note two things: If you accidentally encrypt the wrong partition, the data is lost. For ever. So be careful when entering the commands below. Secondly, the method shown below only protects the data at rest. As soon as you decrypt and mount the device, the data can be read from everyone else if you do not use correct permissions.


Prepare a mount point for your data and change ownership.

# Create a mount point
sudo mkdir /media/cryptoUSB
# Set permissions for the owner
sudo chown stefan:stefan /media/cryptoUSB

Create an Encrypted Device

Encrypt the device with LUKS. Note that all data on the partition will be overwritten during this process.

# Create encrypted device 
sudo cryptsetup --verify-passphrase luksFormat /dev/sdX -c aes -s 256 -h sha256

# From the man page:
       --cipher, -c 
              Set the cipher specification string.
       --key-size, -s 
              Sets  key  size in bits. The argument has to be a multiple of 8.
              The possible key-sizes are limited by the cipher and mode used.
       --verify-passphrase, -y
              When interactively asking for a passphrase, ask for it twice and
              complain  if  both  inputs do not match.
       --hash, -h 
              Specifies the passphrase hash for open (for  plain  and  loopaes
              device types).

# Open the Device
sudo cryptsetup luksOpen /dev/sdX cryptoUSB
# Create a file system (ext3)
sudo mkfs -t ext3 -m 1 -O dir_index,filetype,sparse_super /dev/mapper/cryptoUSB
# Add a label
sudo tune2fs -L Crypto-USB /dev/mapper/cryptoUSB
# Close the devicesudo cryptsetup luksClose cryptoUSB


The usage is pretty simple. With a GUI you will be prompted for decrypting the device. At the command line, use the following commads to open and decrypt the device.

# Open the Device
sudo cryptsetup luksOpen /dev/sdcX cryptoUSB
# Mount it
sudo mount /dev/mapper/cryptoUSB /media/cryptoUSB

When you are finished with your secret work, unmount and close the device properly.

sudo umount /media/cryptoUSB 
sudo cryptsetup luksClose cryptoUSB

Continue reading