MySQL-Replication

Das Backup einer MySQL-Datenbank kann sehr viel Last/IO auf dem Server erzeugen. Daher sollten Backups nachts erfolgen. Der Nachteil hierbei ist natürlich, dass hierbei ein langer Backupinterval entsteht. Will man Backups häufiger erzeugen kann man einen Slave-Server aufsetzen, der die MySQL-Datenbanken des Master-Servers spiegelt und das Backup dann auf dem Slave erzeugt. Somit wird der Master-Server nicht der Last durch das Backup ausgesetzt.## Replikation einrichten ##

Damit überhaupt eine Replikation möglich muss auf dem Master-Server binary-logging (log-bin) aktiviert sein. Das Binärlog stellt dem Slave die Änderungen an den Datenbanken zur Verfügung. Außerdem muss jeder Server, egal ob Master oder Slave, eine eindeutige ID in Form einer Zahl erhalten (server-id). Damit das Binärlog nicht unendlich wächst sollte expire_log_days gesetzt sein, damit es nach der entsprechenden Anzahl von Tagen automatisch verfällt.

Um die Konsistenz bei Verwendung von InnoDB zu erhöhen wird außerdem noch innodb_flush_log_at_trx_commit und sync_binlog gesetzt. Über binlog-ignore-db bzw. binlog-do-db kann festgelegt werden für welche Datenbanken überhaupt Binärlogs erzeugt werden sollen. Damit der Slave mit dem Master kommunizieren kann ist außerdem skip-networking abzuschalten und die bind-address anzupassen:

Master

# /etc/mysql/my.cnf

[mysqld]
log-bin=mysql-bin
expire_logs_days=7
binlog_ignore_db=phpmyadmin
binlog_ignore_db=mysql
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
#skip-networking
bind-address = 0.0.0.0

Leider kann MySQL nur auf eine IP oder alle gebunden werden. Wenn ein Server neu aufgesetzt wird sollte hier die interne IP statt 0.0.0.0 eingetragen werden, wichig ist dann nur dass alle Scripte nicht mehr per localhost sondern über die interne IP auf MySQL zugreifen. Ist dies so nicht möglich oder zu aufwändig kann stattdessen iptables genutzt werden um die Zugriffe entsprechend einzuschränken. Außerdem sollten die Benutzerrechte geprüft werden, damit nur berechtigte Clients auf den Server zugreifen können.

Slave

Auf dem Slave muss lediglich die server-id und der Name/IP des Slave (report-host) gesetzt werden, außerdem werden die Datenbanken aufgeführt welche auf keinen Fall repliziert werden sollen:

# /etc/mysql/my.cnf

[mysqld]
server-id=2
report-host=192.168.1.124
replicate-ignore-db=mysql
replicate-ignore-db=phpmyadmin
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

Nach den Änderungen muss MySQL neu gestartet werden (zunächst nur auf dem Master):

/etc/init.d/mysql restart

Nun muss auf dem Master-Server ein Benutzer angelegt werden über den der Slave-Server die Replikation durchführen kann:

CREATE USER 'repl'@'192.168.1.124' IDENTIFIED BY '***';

GRANT SELECT ,
RELOAD ,
SUPER ,
REPLICATION SLAVE ON * . *
TO 'repl'@'192.168.1.124'
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0
MAX_USER_CONNECTIONS 0 ;

Das SELECT ist nicht zwingend notwendig, erleichtert aber das Überwachen von Inkonsistenzen zwischen Master und Slave.

Nun muss ein spezieller Datenbankdump erzeugt werden um dem Slave die korrekten Binärlog-Daten zu übergeben um die Replikation einleiten zu können.

Zunächst muss die Datenbank auf dem Master gelockt werden:

FLUSH TABLES WITH READ LOCK;

Die Client-Verbindung darf erst nach dem Backup unterbrochen werden, da der Lock sonst verloren geht. Zum erzeugen des Dumps sollte also ein neues Terminalfenster genutzt werden:

mysqldump --all-databases -p -u root > mysqldump.sql

Nachdem der Dump erzeugt wurde müssen noch ein paar Daten vom Master ausgelesen werden, danach kann der Lock wieder aufgehoben werden:

SHOW MASTER STATUS;
UNLOCK TABLES;

Die Werte File und Position die SHOW MASTER STATUS; ausgibt werden notiert.

Nun kann die Datenbank auf dem Slave importiert werden, zunächst muss natürlich MySQL gestartet werden.

WICHTIG: Wenn der Dump mit DROP TABLES erzeugt wurde müssen die Datenbanken mysql und phpmyadmin zuvor aus dem Dump entfernt werden:

/etc/init.d/mysql stop
mysqld_safe --skip-slave-start --log-warnings &

Das skip-slave-start unterbindet zunächst dass der Slave den Master kontaktiert. Alternativ kann STOP SLAVE; genutzt werden um die Slave-Verbindung abzuschalten.

Nun kann die Datenbank des Masters importiert werden:

mysql -u root -p < mysqldump.sql

Anschließend wird der Slave mit dem Master bekannt gemacht:

CHANGE MASTER TO MASTER_HOST='192.168.1.123', MASTER_USER='repl',MASTER_PASSWORD='***',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107;
START SLAVE;
SHOW SLAVE STATUS;
quit

Die Werte für MASTER_LOG_FILE und MASTER_LOG_POS müssen aus der Ausgabe von SHOW MASTER STATUS übernommen werden. SHOW SLAVE STATUS sollte für Slave_IO_Running und Slave_SQL_Running jeweils Yes zurückliefern (phpmyadmin liefert hier eine übersichtlichere Darstellung). Sollte dies nicht der Fall sein sollte mit grep mysql /var/log/syslog der Grund gefunden werden können.

Nun wird der Slave-MySQL-Server neu gestartet:

/etc/init.d/mysql restart

Im Verzeichnis /var/lib/mysql auf dem Slave sollten sich nun die Dateien master.info und relay-log.info befinden.

Außerdem kann mit dem Befehl SHOW SLAVE HOSTS (auf dem Master) und dem Befehl SHOW SLAVE STATUS (auf dem Slave) geprüft werden ob alles korrekt funktioniert. Insbesondere SHOW SLAVE STATUS ist bei der Fehlersuche sehr hilfreich.

Nachdem die Replikation korrekt eingerichtet wurde werden neue Daten, Tabellen und Datenbanken automatisch auf dem Slave hinzugefügt/entfernt. Voraussetzung ist natürlich dass der repl-Benutzer select-Zugriff auf die Datenbank hat:

GRANT SELECT ON `new_database` . * TO 'repl'@'192.168.1.124';

Mehrere MySQL-Instanzen auf einem Server

Sofern mehrere Server auf diese Weise gesichert werden sollen müssen die Datenbanknamen natürlich jeweils eindeutig sein. Ist dies nicht der Fall müssen mehrere MySQL-Instanzen auf dem Slave-Server eingerichtet werden.

Um dies zu ermöglichen sind folgende Schritte notwendig:

Es wird für jede Instanz ein eigenes Daten- und Logverzeichnis benötigt. Die Verzeichnisse für die erste Instanz existieren bereits. Die neuen Verzeichnisse werden wie folgt angelegt:

mkdir /var/lib/mysql2
chown -R mysql:mysql /var/lib/mysql2
mkdir /var/log/mysql2
chown -R mysql:mysql /var/log/mysql2

Außerdem werden separate Konfigurationsdateien benötigt, hierzu kann zunächst die Konfiguration der ersten Instanz kopiert werden:

cp -R /etc/mysql/ /etc/mysql2

Nun müssen die Werte für mysql port, pidfile und socketfile angepasst werden, außerdem natürlich die Pfade für die Daten- und Logdateien:

cd /etc/mysql2/
sed -i 's/3306/3307/g' my.cnf
sed -i 's/mysqld.sock/mysqld2.sock/g' my.cnf
sed -i 's/mysqld.pid/mysqld2.pid/g' my.cnf
sed -i 's/var\/lib\/mysql/var\/lib\/mysql2/g' my.cnf
sed -i 's/var\/log\/mysql/var\/log\/mysql2/g' my.cnf

Nun muss noch die Datenbank für die neue Instanz initialisiert werden:

mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

Verwaltet wird die neue Instanz dann mit folgenden Befehlen:

# Start der Instanz:
mysqld_safe --defaults-file=/etc/mysql2/my.cnf &

# Herunterfahren der Instanz:
mysqladmin -h 127.0.0.1 -P 3307 -p shutdown

# Verbinden mit der Instanz:
mysql -h 127.0.0.1 -P 3307

WICHTIG: Wenn man sich per TCP mit einer anderen Instanz als der Instanz auf Port 3306 verbinden will muss zwingend als Hostname 127.0.0.1 anstatt localhost verwendet werden. localhost greift immer per Socket auf den MySQL-Server zu und ignoriert daher die Angabe des Ports.

Siehe: http://bugs.mysql.com/bug.php?id=58811

Nagios-Überwachung

Ein MySQL-Slave führt neben dem eigentlich MySQL-Server noch einen Slave-Thread aus. Dieser kann von Nagios mit Hilfe der Option -S vom check_mysql-Script überwacht werden. Der Slave-Thread wird immer dann automatisch beendet wenn während der Replizierung ein Fehler auftritt. Die Überwachung ist daher sehr wichtig.

Sollte der Slave "out of sync" geraten findet sich unter http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o eine Anleitung um dies zu beheben.

Slave-Backup

Um einen mysqldump auf dem Slave auszuführen sollte zunächst die Replikation unterbrochen werden:

mysqladmin stop-slave
mysqldump --all-databases > fulldb.dump
mysqladmin start-slave

Sonstiges

http://dev.mysql.com/doc/refman/5.5/en/replication-problems.html

http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-backups.html http://dev.mysql.com/doc/refman/5.5/en/replication-semisync-installation.html

http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.5/en/replication-options-master.html

http://www.percona.com/doc/percona-toolkit/2.0/pt-table-checksum.html http://www.percona.com/doc/percona-toolkit/2.0/pt-heartbeat.html http://www.percona.com/doc/percona-toolkit/2.0/pt-table-sync.html