MySql Master-Slave Replikation mit SSL

Sinnvoll ist diese Replikation dann, wenn man Last-Verteilung benötigt, und es ok ist, wenn der Slave auch mal ein paar Sekunden hinterherhinken kann. Der Slave wird sinnvollerweise read-only gesetzt, die einzigen Schreibzugriffe sind die, die durch den Master kommen.

Technisch passiert bei der Replikation ganz grob gesagt folgendes: Der Master schreibt alle Schreibzugriffe in ein Binärlog. Dafür gibt es je nach MySql-Version und Default-Einstellung zwei Varianten: Entweder wird direkt der SQL-Befehl protokolliert oder die tatsächliche Änderung der jeweiligen Zeile(n). (Statement-Based versus Row-based). Dann gibt es noch die Kombination aus beiden als das Mixed-Format. Der Slave verbindet sich mit dem Master und liest dieses Binärlog permanent aus, schreibt sie in ein Relay-Log auf dem Slave-Server und über dieses Log wird dann der Slave aktualisiert.

Voraussetzungen

Zwei fertig eingerichtete Debian/Ubuntu Server mit jeweils MySql installiert (MariaDB, Percona). Ich mache das auf Debian 8. Ob bei Ubuntu irgendwelche Pfade anders sind, weiß ich leider nicht.

Die meisten Shell-Befehle , die ich im folgenden notiere, sind notwendigeweise als root oder mit sudo auszuführen.

MySql Einstellungen anpassen

Zuerst ist die /etc/mysql/my.cnf des Masters zu editieren:

die Mysql-Instanz benötigt eine eindeutige Id. Der Eintrag ist schon auskommentiert in der my.cnf vorhanden. suche:

 #server-id = 1

und de-kommentiere das:

 server-id = 1

Desweiteren ist dort irgendwo auskommentiert:

#ssl-ca=/etc/mysql/cacert.pem
#ssl-cert=/etc/mysql/server-cert.pem
#ssl-key=/etc/mysql/server-key.pem

Nimm den Kommentar raus und schreibe ssl davor, um ssl zu aktivieren (ssl=1 geht auch):

ssl
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Für die Konsistenz in der Namensgebung habe ich bei cacert noch einen Bindestrich ergänzt: ca-cert.pem

Normalerweise ist es sinnvoll mysql abzusichern, indem der mysql-server nur noch auf localhost lauscht. Das macht man mit bind-adress 127.0.0.1. Bei der Replikation muss sich aber auch der Server des Slaves verbinden können. Man kann MySql aber nur entweder von einer IP oder allen lauschen lassen. Das bind-adress muss also auskommentiert werden.

#bind-adress 127.0.0.1 

Alternativ geht auch ein bind-adress 0.0.0.0

Replikation-User erzeugen

Sinnvoll ist ein user, der ausschließlich das Recht hat, zu replizieren. Diesen entweder bequem mit phpMyAdmin anlegen oder über den mysql-client:

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

Siehe auch die offzielle Dokumentation dazu:https://dev.mysql.com/doc/refman/5.7/en/replication-howto-repuser.html

Zertifikate anlegen

Die benötigten Zertifikate müssen natürlich auch angelegt werden. Wir können selbst zertifizierte Zertifikate benutzen, drum legen wir zuerst das Zertifizierungs-Zertifikat an, ein Schlüsselpaar:

Zuerst den privaten Schlüssel:

>openssl genrsa 2048 > ca-key.pem

Dann das Zertifikat dazu, das ist der öffentliche Teil, der ein Ablaufdatum hat, hier 3 Jahre:

>openssl req -new -x509 -nodes -days 1095 -key ca-key.pem > ca-cert.pem

Ähnlich Server Key und Zertifikat erstellen:

>openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server-key-pkcs8.pem > server-req.pem >openssl rsa -in server-key-pkcs8.pem -out server-key.pem >openssl x509 -req -in server-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Client Key und Zertifikat erstellen:
>openssl req -newkey rsa:2048 -days 1095 -nodes -keyout client-key-pkcs8.pem > client-req.pem >openssl rsa -in client-key-pkcs8.pem -out client-key.pem >openssl x509 -req -in client-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Gib dem ganzen vernünftige Zugriffsrechte und Besitzverhältnisse:
>chmod 400 /etc/mysql/*.pem
>chown mysql /etc/mysql/*.pem

Zertifikate sollten nur vom Besitzer gelesen werden können. Und der Besitzer sollte mysql sein.

Jetzt muss man sich nur noch irgendwie im Kalender notieren, dass man vor Ablauf der 3 Jahre die Zertifikate aktualisiert. Nicht dass das Replizieren dann irgendwann nicht mehr läuft und man bekommt es ewig nicht mit…

Dump des Masters anlegen:

Um eine Datenbank zu dumpen, geht folgendes:

>mysqldump --databases meinedb --allow-keywords --single-transaction --flush-logs --master-data=2 -r /root/mysqldump.sql

mehr als eine DB gibt man kommasepariert an. Wenn man alle Datenbanken replizieren will, schreibt man –all-databases statt –databases. Mir erscheint es nicht sinnvoll, alle zu replizieren, weil dann doch auch die user mitsamt Rechten repliziert werden. Die müssen aber ggf. doch unterschiedlich sein.

Am besten gzippt man den Dump gleich mal, falls er groß ist:

>gzip mysqldump.sql

Dann die Datei auf den Server, auf dem der slave laufen soll übertragen. Am besten mit scp.

> scp -P portnummer datenbankdatei.sql.gz username@domain.tld:~

Dump in den Slave importieren

Der slave sollte die zu replizierende Datenbank(en) noch nicht angelegt haben. Nötigenfalls löschen (drop databasename). gunzip mysqldump.sql.gz nicht vergessen

Import des Dumps:

>mysql -uroot -p <mysqldump.sql

Dann das mysql-root-Passwort eingeben. Man kann das Passwort auch direkt hinter -p schreiben (-ppasswort, ja ohne leerzeichen) aber wenn man Sonderzeichen im Passwort hat (die man normalerweise haben sollte!) muss man die erst escapen, das macht es umständlich und überhaupt unsicherer.

Slave einrichten

Kopiere die nötigen Schlüssel, die du im Master-Server angelegt hast. ca-cert und die client-keys brauchst du auf dem Slave-Server, kopiere sie nach /etc/mysql

Editiere nun die my.cnf. Am Anfang in der Sektion [client] ergänze bzw. de-kommentiere:

ssl
ssl-ca = /etc/mysql/ca-cert.pem
ssl-cert = /etc/mysql/client-cert.pem
ssl-key = /etc/mysql/client-key.pem

Das ermöglicht einer client-Verbindung SSL zu verwenden.

Dann musst du die server-id setzen, jetzt natürlich unbedingt eine andere id nehmen, z.B. die 2. Es braucht außerdem die Angabe eines Relay-Logs und wir setzen den Slave auf readonly:

server-id=2
relay-log = /var/log/mysql/mysql-relay-bin
read-only = 1

Wenn man nur bestimmte Datenbanken replizieren möchte, ergänzt man folgendes, db01 und db02 stehen für die datenbanken, die man replizieren möchte:

replicate-do-db=db01
replicate-do-db=db02 

Nun den Mysql-Server neu starten

>service mysql restart

In der mysqldump.sql musst du die Position des binlogs ermitteln. Da steht relativ am Anfang der Datei sowas o.ä.:

--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=365;

Der Server muss ja wissen, an welcher Stelle genau er mit dem replizieren beginnen soll. Zwischenzeitlich können im Master schon neue Daten reingekommen sein, das ist kein Problem.

Gehe in den mysql-client mit:

>mysql- uroot -p    

Jetzt kommen die magischen Kommandos zum Slave werden:

change master to master_host='ip des masters', master_user='repl', 
master_password='<pw des users repl>', master_log_file='mysql-bin.000006', 
master_log_pos=312,

Weitere Parameter für die SSL-Verbindung:

 change master to master_ssl=1,MASTER_SSL_CA='/etc/mysql/ca-cert.pem', 
 MASTER_SSL_CERT='/etc/mysql/client-cert.pem', MASTER_SSL_KEY='/etc/mysql/
 client-key.pem';

Damit ist der Slave aber noch nicht gestartet. Zum Anschauen, ob die Eingaben angenommen worden sind, geht.

 show slave status\G

Das \G formatiert die Ausgabe. Um den Slave tatsächlich zu starten, benutze diese Anweisung:

start slave

Ein weiteres „show slave status\G“ würde dann ungefähr so aussehen:

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: <ip adresse>
              Master_User: repl
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000021
      Read_Master_Log_Pos: 5583
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 5870
    Relay_Master_Log_File: mysql-bin.000021
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 5583
          Relay_Log_Space: 6454
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: Yes
       Master_SSL_CA_File: /etc/mysql/ca-cert.pem
       Master_SSL_CA_Path:
          Master_SSL_Cert: /etc/mysql/client-cert.pem
        Master_SSL_Cipher:
           Master_SSL_Key: /etc/mysql/client-key.pem
    Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
           Master_SSL_Crl: /etc/mysql/ca-cert.pem
       Master_SSL_Crlpath:
               Using_Gtid: No
              Gtid_IO_Pos:
1 row in set (0.00 sec)

Wenn was nicht geht

Was sagt die /var/log/syslog ?

Kann die Connection denn generell aufgebaut werden? Teste mit:

mysql -urepl -p -h <ip des masters> 

Wenn das alles auf Anhieb nicht klappt, nimm erst mal alles SSL raus und schau ob es ohne SSL hinhaut.

Um es nochmal zu probieren hilft ein:

stop slave;
reset slave;

MySql-Server mit iptables absichern

Zum Abschluss lassen wir nur den Slave-Server und localhost an unseren Mysql-Master ran: In dieser Reihenfolge eingeben:

iptables -I INPUT -p tcp --dport 3306 -j DROP
iptables -I INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
iptables -I INPUT -p tcp -s <ip des slaves>--dport 3306 -j ACCEPT

Das setzt die IP-Tables chain so, dass zuerst die beiden ACCEPTs geprüft werden und der Rest geht auf DROP, d.h. wird nicht durchgelassen.

mit „iptables -L“ anschauen ob es richtig herum ist (erst kommen die beiden ACCEPTS, dann DROP): mit „/sbin/iptables-save“ wendet man die Regeln dann an.

Meine Doku stützt sich auf: https://www.thomas-krenn.com/de/wiki/MySQL_Replikation und anderen.