Ben's notes

Encrypt MariaDB replication traffic

CentOS 7
MariaDB

Check for SSL support

Check SSL variable on both master and slave. This value should be 'DISABLED' or 'OK'. If value is 'NO' then reinstall or recompile with SSL support:
```
mysql> SHOW VARIABLES LIKE 'have_ssl';
```

Master configuration

Replication user

Create replication user on master server:

mysql>MariaDB [powerdns]> CREATE USER 'replicator'@'%' IDENTIFIED BY 'chooseyourownpassword!';
Query OK, 0 rows affected (0.00 sec)

MariaDB [powerdns]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [powerdns]> GRANT SELECT ON powerdns.* TO 'replicator'@'%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [powerdns]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Test connection with the above username and password, from the slave to the master.

SSL keys

Create directory for key and certificates:

mkdir -p /etc/pki/tls/certs/mariadb
cd /etc/pki/tls/certs/mariadb

Create private key and public certificate:

openssl req -x509 -sha256 -newkey rsa:4096 \
-keyout master-private.pem -out master-public.pem \
-subj '/CN=ns1.polaire.nl' -nodes -days 3650

chmod 400 master-private.pem
chown mysql master-private.pem

Copy public certificate to (as) 'CA certificate':
```
cp master-public.pem ca.pem
```

MariaDB configuration

Edit /etc/my.cnf, add:

[mysqld]
log_bin = mysql-bin
server_id = 10
ssl-ca=/etc/pki/tls/certs/mariadb/ca.pem
ssl-cert=/etc/pki/tls/certs/mariadb/master-public.pem
ssl-key=/etc/pki/tls/certs/mariadb/master-private.pem

Restart MariaDB:
```
systemctl restart mariadb
```

Require SSL

Modify replicator user to require SSL:

MariaDB [(none)]> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR "replicator";

Test connection again from slave, this should no fail!

Slave configuration

Create directory and copy master CA certificate to slave:

mkdir -p /etc/pki/tls/certs/mariadb
cd /etc/pki/tls/certs/mariadb

Test connection from slave to master using SSL:

mysql -u replicator -p'yourpassword' -hns1.polaire.nl --ssl-ca /etc/pki/tls/certs/mariadb/ca.pem --ssl-verify-server

Show cipher in use:

<MariaDB [(none)]> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | DHE-RSA-AES256-GCM-SHA384 |
+---------------+---------------------------+
1 row in set (0.05 sec)

Client certificate verification

On the master server enable client key verification:

MariaDB [(none)]> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SUBJECT '/CN=ns2.polaire.nl';

MariaDB [(none)]> SHOW GRANTS FOR "replicator";

Client connection test should fail again!

Now create a key and certificate for the slave:

cd /etc/pki/tls/certs/mariadb
openssl req -x509 -sha256 -newkey rsa:4096 \
-keyout slave-private.pem -out slave-public.pem \
-subj '/CN=ns2.polaire.nl' -nodes -days 3650

chmod 400 slave-private.pem
chown mysql slave-private.pem

Append public key to CA on both master and slave:
```
cat slave-public.pem >> ca.pem
```
Restart master server:
```
systemctl restart mariadb
```

Test connection, this should work!

mysql -u replicator -p'yourpass' -hns1.polaire.nl \
    --ssl-ca /etc/pki/tls/certs/mariadb/ca.pem \
    --ssl-cert /etc/pki/tls/certs/mariadb/slave-public.pem \
    --ssl-key /etc/pki/tls/certs/mariadb/slave-private.pem

Backup master and import to slave

Create backup on master:

mysqldump -u root -p --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql

Copy the dump to the slave.

Slave configuration

Edit /etc/my.cnf on the slave:

[mysqld]
log_bin = mysql-bin
server_id = 20
log_slave_updates = 1
relay_log = mysql-relay-bin
read_only = 1

Enable replication

Import the backup on the slave:

MariaDB [(none)]> source /tmp/master_backup.sql

Configure replication:

CHANGE MASTER TO
Master_Host='ns1.polaire.nl',
Master_User='replicator',
Master_Password='yourpassword',
Master_SSL=1,
Master_SSL_CA = '/etc/pki/tls/certs/mariadb/ca.pem',
Master_SSL_CERT = '/etc/pki/tls/certs/mariadb/slave-public.pem',
Master_SSL_KEY = '/etc/pki/tls/certs/mariadb/slave-private.pem',
Master_SSL_Verify_Server_Cert = 1;
Query OK, 0 rows affected (0.08 sec)

Start slave:

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status \G

Table of Contents

Encrypt MariaDB replication traffic

Check for SSL support

Master configuration

Replication user

SSL keys

MariaDB configuration

Require SSL

Slave configuration

Client certificate verification

Backup master and import to slave

Slave configuration

Enable replication