Managing MySQL Replication over SSL

WebPlatform server Infrastructure architecture menu

See also

Deploying code changes
Replacing a VM
Maintaining Fastly configuration
Managing MySQL replication
Create new database credentials and configure a web application to use it
Maintaining ElasticSearch cluster
Maintaining email services

Summary

How to manage communication and replication between MySQL servers across data-centers.

Conventions

This document will follow those facts as a convention.

Master MySQL server is known as db1 and the replication is db2

In a few bullet points

In /etc/mysql/my.cnf, make sure that each node has ‘ssl’ alone, close to the SSL certificates
Create CA and self-signed certificates, see Salt TLS module (salt.modules.tls)
Remove db2 MySQL entries from MediaWiki configuration files, sync them to app* servers
On db2, Lock databases
On db2, Dump database tables
On db2, Unlock databases

Memory dump, using Salt stack

TLS salt module

   salt 'db1*' tls.create_ca 'mysql' days=730 CN='db1-masterdb.production.wpdn' O='W3C' OU='WebPlatform Docs' emailAddress='EMAIL'

Checks

Checking if SSL is supported

Connect to the MySQL server in question and check if have_ssl is to YES

   mysql> SHOW VARIABLES LIKE 'have_ssl';
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | have_ssl      | YES   |
   +---------------+-------+

Procedures

Changing MariaDB replication master

Quoting what I wrote on a blog post I wrote on January 2015: Create a MariaDB cluster with replication over SSL:

From the masterdb MySQL server, in a command line session
Lock writes on masterdb databases FLUSH TABLES WITH READ LOCK; (make sure this session stays open, the lock only lasts with the session you used to write it. See MariaDB flush tables pages)
From a secondary MySQL server; Wait replication to catch up SHOW SLAVE STATUS\G
Remove replication configuration
Tell all web apps to use new database master (from the salt master, change the infra:hosts_entries:masterdb key in /srv/pillar/infra/production.sls (or /srv/pillar/infra/staging.sls for staging).
From the masterdb; remove database lock by closing the session opened earlier
Setup new replication configuration to use new master

@@TODO, Make a more precise procedure with new setup and how to manage.