Over a million developers have joined DZone.

Percona XtraDB Cluster for MySQL and encrypted Galera replication

· Performance Zone

Discover 50 of the latest mobile performance statistics with the Ultimate Guide to Digital Experience Monitoring, brought to you in partnership with Catchpoint.

This post comes from  at the MySQL Performance Blog.


Few people realize that Galera/Percona XtraDB (PXC) replication can be encrypted via SSL for secure transfer of your replicated data.  Setting this up is actually quite easy to do and probably will look familiar to a lot of people.

Setting up SSL and Galera

Create and propagate a single key/cert pair

First, we create a private key/cert pair:

[root@node1 ssl]# openssl req -new -x509 -days 365000 -nodes -keyout key.pem -out cert.pem
Generating a 2048 bit RSA private key
writing new private key to 'key.pem'
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
[root@node1 ssl]# ls -lah
total 16K
drwxr-xr-x. 2 root root 4.0K Apr  1 12:08 .
dr-xr-x---. 4 root root 4.0K Apr  1 12:03 ..
-rw-r--r--. 1 root root 1.2K Apr  1 12:08 cert.pem
-rw-r--r--. 1 root root 1.7K Apr  1 12:08 key.pem

Note that we are creating a certificate with a very long expiration time. If you use the default expiration of 1 year, your cluster will fail on the first state change after the expiration date has past.

Note also that we currently need to use the same cert and key on every node, so our next step is to copy these files to all our other nodes.  Technically you should probably do this over a secure channel between the nodes via ssh or similar:

[root@node1 ssl]# scp  *.pem root@node2:.

Once we have the files on all nodes, let’s put them into /etc/mysql so they are in a common place with correct permissions:

[root@node1 ssl]# mkdir /etc/mysql
[root@node1 ssl]# mv *.pem /etc/mysql
[root@node1 ssl]# cd /etc/mysql
[root@node1 mysql]# chown -R mysql.mysql /etc/mysql/
[root@node1 mysql]# chmod -R o-rwx /etc/mysql/
[root@node1 mysql]# ls -lah
total 16K
drwxr-x---.  2 mysql mysql 4.0K Apr  1 12:12 .
drwxr-xr-x. 60 root  root  4.0K Apr  1 12:12 ..
-rw-r-----.  1 mysql mysql 1.2K Apr  1 12:08 cert.pem
-rw-r-----.  1 mysql mysql 1.7K Apr  1 12:08 key.pem

These are just examples of how you might do it.  Just take care to not expose your private key and keep it secure as possible while still getting it copied amongst your nodes.

Configuring Galera

The configuration here is quite easy:

wsrep_provider_options          = "socket.ssl_cert=/etc/mysql/cert.pem; socket.ssl_key=/etc/mysql/key.pem"

In my case, I have an existing non-SSL cluster, so to re-bootstrap, I simply:

[root@node3 mysql]# service mysql stop
[root@node2 mysql]# service mysql stop
[root@node1 mysql]# service mysql stop
[root@node1 mysql]# service mysql start --wsrep_cluster_address=gcomm://
[root@node2 mysql]# service mysql start
[root@node3 mysql]# service mysql start

There should be no need for SST in this case: each node was shutdown cleanly, and brought back up cleanly. As soon as the first node is restarted with SSL enabled, all future nodes must also have it enabled.

Other SSL options

It is also possible to set the following options (though they seem to have sane defaults to me):

  • socket.ssl_cipher = AES128-SHA by default
  • socket.ssl_compression = yes by default

Other questions

Will IST be encrypted if I set the above settings?

Yes, according to Codership, IST transfers use the same socket settings as regular group communication (gcomm).

Will SST be encrypted?

No, none of the default methods currently support SSL.  However, SST is scriptable, so this technically shouldn’t be that difficult to add.  We’d welcome some open source contribution in this space for encrypted versions of (or just enhancements to) the current SST scripts.

Is your APM strategy broken? This ebook explores the latest in Gartner research to help you learn how to close the end-user experience gap in APM, brought to you in partnership with Catchpoint.


Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}