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:
>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.