{{tag>[mysql replication security powerdns]}}

=====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:<code>mysql> SHOW VARIABLES LIKE 'have_ssl';</code>

====Master configuration====
===Replication user===
  * Create replication user on **master** server:<code>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)
</code>
  * Test connection with the above username and password, from the slave to the master.
===SSL keys===
  * Create directory for key and certificates:<code>mkdir -p /etc/pki/tls/certs/mariadb
cd /etc/pki/tls/certs/mariadb</code>
  * Create private key and public certificate:<code>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</code>
  * Copy public certificate to (as) 'CA certificate':<code>cp master-public.pem ca.pem</code>

===MariaDB configuration===
  * Edit /etc/my.cnf, add:<code>[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</code>
  * Restart MariaDB:<code>systemctl restart mariadb</code>

===Require SSL====
  * Modify replicator user to require SSL:<code>MariaDB [(none)]> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR "replicator";</code>
  * Test connection again from slave, this should no **fail**!
===Slave configuration===
  * Create directory and copy master CA certificate to **slave**:<code>mkdir -p /etc/pki/tls/certs/mariadb
cd /etc/pki/tls/certs/mariadb</code>
  * Test connection from slave to master using SSL:<code>mysql -u replicator -p'yourpassword' -hns1.polaire.nl --ssl-ca /etc/pki/tls/certs/mariadb/ca.pem --ssl-verify-server</code>
  * Show cipher in use:<code><MariaDB [(none)]> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | DHE-RSA-AES256-GCM-SHA384 |
+---------------+---------------------------+
1 row in set (0.05 sec)</code>

====Client certificate verification====
  * On the **master** server enable client key verification:<code>MariaDB [(none)]> GRANT USAGE ON *.* TO 'replicator'@'%' REQUIRE SUBJECT '/CN=ns2.polaire.nl';

MariaDB [(none)]> SHOW GRANTS FOR "replicator";</code>
  * Client connection test should **fail** again!
  * Now create a key and certificate for the **slave**:<code>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</code>
  * Append public key to CA on both **master and slave**:<code>cat slave-public.pem >> ca.pem</code>
  * Restart master server:<code>systemctl restart mariadb</code>
  * Test connection, this should work!<code>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</code>
====Backup master and import to slave====
  * Create backup on **master**:<code>mysqldump -u root -p --single-transaction --all-databases --master-data=1 > /tmp/master_backup.sql</code>
  * Copy the dump to the ** slave**.

====Slave configuration====
  * Edit /etc/my.cnf on the **slave**:<code>[mysqld]
log_bin = mysql-bin
server_id = 20
log_slave_updates = 1
relay_log = mysql-relay-bin
read_only = 1</code>

====Enable replication====
  * Import the backup on the **slave**:<code>MariaDB [(none)]> source /tmp/master_backup.sql</code>
  * Configure replication:<code>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)</code>
  * Start slave:<code>MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

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