Over a million developers have joined DZone.

Secure Binlog Server: Encrypted Binary Logs and SSL Communication

DZone's Guide to

Secure Binlog Server: Encrypted Binary Logs and SSL Communication

Let's look at the 2.1.3 GA release of MariaDB MaxScale and see how it can help ensure that MaxScale Binlog Server is set up securely.

· Database Zone ·
Free Resource

Download "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.

The 2.1.3 GA release of MariaDB MaxScale, introduces the following key features for the secure setup of MaxScale Binlog Server:

  • The binlog cache files in the MaxScale host can now be encrypted.
  • MaxScale binlog server also uses SSL in communication with the master and the slave servers.

The MaxScale binlog server can optionally encrypt the events received from the master server: the setup requires a MariaDB (from 10.1.7) master server with encryption active and the mariadb10-compatibility=On option set in maxscale.cnf. This way both master and MaxScale will have encrypted events stored in the binlog files. How does the Binary log encryption work in MariaDB Server and in MaxScale?

Let’s look at MariaDB Server 10.1 or 10.2 implementation first.

  • The encryption is related to stored events and not to their transmission over the network: SSL must be enabled in order to secure the network layer.
  • Each binlog file holds a new special Binlog Event, type 0xa4 (164), called START_ENCRIPTION event: it's written to disk but never sent to connected slave servers.
  • Each binlog event is encrypted/decrypted using an AES Key and an Initialization Vector (IV) built from the "nonce" data in START_ENCRIPTION event and the current event position in the file.
  • The encrypted event has the same size as that of a nonencrypted event.

Let’s start with MariaDB Server 10.1.7 configuration: my.cnf.

file_key_management_filename = /some_path/keys.txt

Binlog files can be optionally encrypted by specifying:


The encryption key facility has to be added as well. The easiest one to setup is the Key File:


With its key file:

file_key_management_filename = /some_path/keys.txt

The keys listed in the key file can be specified per table but the system XtraDB/InnoDB tablespace and binary log files always use the key number 1, so it must always exist.

The last parameter is the AES encryption algorithm. AES_CBC (default) or AES_CTR.

file_key_management_encryption_algorithm=aes_cbc | aes_ctr

There is another key management solution (Eperi Gateway for Databases), which allows storing keys in a key server, preventing an attacker with file system access from unauthorized database file reading.

For additional information, please read more about MariaDB data-at-rest encryption.

MariaDB MaxScale Implementation

The implementation follows the same MariaDB Server implementation above.

  • MaxScale adds its own START_ENCRIPTION event (which has same size but different content from the master one).
  • The encryption algorithm can be selected: AES_CBC or AES_CTR.
  • The event size on disk is the same as the “clear” event.
  • Only key file management is currently supported: no key rotation.

A closer look at the new event might be interesting for most readers:

START_ENCRIPTION size is 36 or 40 bytes in size depending on CRC32 being used or not:

  • Replication header 19 bytes +
  • 1 byte encryption schema           // 1 is for system files
  • 4 bytes Encryption Key Version  // It allows key rotation
  • 12 bytes NONCE random bytes // first part of IV

As the saved event on disk has the same size of the clear event, the event size is in “clear” in the replication header.

Moving some bytes in the replication event header is required in order to encrypt/decrypt the event header and content.

MariaDB MaxScale configuration

Encrypt_binlog = On|Off 
Encryption_algorithm = aes_ctr OR aes_cbc 
Encryption_key_file =/maxscale_path/enc_key.txt

The specified key file must have this format: a line with 1;HEX(KEY).

Id is the scheme identifier, which must have the value 1 for binlog encryption, the ; is a separator and HEX(KEY) contains the hex representation of the KEY. The KEY must have exact 16, 24 or 32 bytes size and the selected algorithm (aes_ctr or aes_cbc) with 128, 192, or 256 ciphers will be used.

Note: The key file has the same format as MariaDB Server 10.1 so it's possible to use an existing key file (not encrypted) which could contain several scheme; keys: only key id with value 1 will be parsed, and if not found, an error will be reported.


#This is the Encryption Key File 
# key id 1 is for binlog files encryption: it's mandatory 
# The keys come from a 32bytes value, 64 bytes with HEX format

See all the encryption options in “router options”


Enable SSL Communication to Master and from Connecting Slaves

We have seen how to secure data at rest and we would like to show how to secure all the components in the Binlog Server setup:

  • Master.
  • MaxScale.
  • Slaves.
  • Network connections.

SSL between the Master and MaxScale is required, along with SSL communications with the slave servers.

How to Use SSL in the Master Connection

We have to add some new options to CHANGE MASTER TO command issued to MySQL Listener of Binlog Server, the options are used in the following examples:

All options.


Or just use some of them:


Some constraints:

  • In order to enable/re-enable Master SSL communication, the MASTER_SSL=1 option is required and all certificate options must be explicitly set in the same CHANGE MASTER TO command.
  • New certificate options changes take effect after MaxScale restart or after MASTER_SSL=1 with the new options.
Master_SSL_Allowed: Yes 
  		Master_SSL_CA_File: /home/mpinto/packages/certificates/client/ca.pem 
			Master_SSL_Cert: /home/mpinto/packages/certificates/client/client-cert.pem 
			Master_SSL_Key: /home/mpinto/packages/certificates/client/client-key.pem 

Setting Up Binlog Server Listener for Slave Server

[Binlog_server_listener] type=listener service=BinlogServer protocol=MySQLClient address= port=8808 authenticator=MySQL ssl=required ssl_cert=/usr/local/mariadb/maxscale/ssl/crt.maxscale.pem ssl_key=/usr/local/mariadb/maxscale/ssl/key.csr.maxscale.pem ssl_ca_cert=/usr/local/mariadb/maxscale/ssl/crt.ca.maxscale.pem ssl_version=TLSv12

The final step is for each slave that connects to Binlog Server:

The slave itself should connect to MaxScale using SSL options in the CHANGE MASTER TO … SQL command

The setup is done! Encryption for data-at-rest and for data-in-transit is now complete!

Read "Developing Apps Using Active-Active Redis Enterprise" and discover the advantages over other active-actve databases.

database ,tutorial ,binlog ,encryption ,ssl

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}