Table of Contents

MySQL Replication

Vorbereiten des Masters

Damit der Master Bin Logs schreibt müssen in der Config ein paar Sachen gemacht werden.

Änderungen in der my.cnf (bei Mariadb kann das etwas sein) - Service muss danach neugestartet werden. 

server_id = 1
log_bin = /var/lib/mysql/mysql-bin.log
relay-log=/var/lib/mysql/mysql-relay-bin
sync_binlog = 1
expire_logs_days=7
max_binlog_size=500M
max_relay_log_size=500M
relay_log_space_limit=5G
relay_log_purge=1
replicate-ignore-db=performance_schema,information_schema
log-error = /var/log/mysql/error.log
bind-address = 192.168.0.1

Manche Einstellungen sollten natürlich angepasst werden.

Danach muss ein User angelegt werden am Master.
Als IP wird die Source IP vom Slave genommen. 

mysql -u root

CREATE USER 'repl'@'192.168.10.28' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.10.28';
FLUSH PRIVILEGES;

Vorbereiten des Slaves

Ähnlich wie beim Master braucht man hier ein paar Config Einstellungen.

Änderungen in der my.cnf (bei Mariadb kann das etwas sein) - Service muss danach neugestartet werden. 

server_id = 2
log_bin = /var/lib/mysql/mysql-bin.log
relay-log=/var/lib/mysql/mysql-relay-bin
sync_binlog = 1
expire_logs_days=7
max_binlog_size=500M
max_relay_log_size=500M
relay_log_space_limit=5G
relay_log_purge = 1
replicate-ignore-db=performance_schema,information_schema
log-error = /var/log/mysql/error.log
bind-address = 192.168.0.1
read_only = 1
super_read_only = 1


Überprüfen ob die RO Flags auch ziehen: 

show global variables like '%read_only%';


Übernehmen des Datenstand

Die Daten vom Master müssen auf den Slave. Dies geht am besten mit einem Backup. Zur not kann man auch den ganzen Server Clonen. Achtung UUID der MySQL Intanz muss angepasst werden.


Backup auf dem Master

Das Backup muss ein offline Backup sein (bei MyISAM). Damit die DB online bleiben kann gibt es auch für MyISAM einen Parameter. Es kann jedoch trotzdem zu Problemen der Application in der Zeit kommen. 

mysqldump --all-databases --allow-keywords --single-transaction --quote-names --flush-logs --source-data=2 -r /root/mysqldump.sql

Bei ältern DBs muss der Parameter --source-data gegen --master-data ersetztn werden
mysqldump --all-databases --allow-keywords --single-transaction --quote-names --flush-logs --master-data=2 -r /root/mysqldump.sql


Backup auf dem Master und scp

Hier ein Beispiel wie man den Dump gleich auf den Slave weiter schieben kann via SCP.
Ich verwende hier zusätzlich noch das Tool pv um mir einen Status anzeigen zu lassen.

Ohne gzip 

mysqldump --all-databases --allow-keywords --single-transaction --quote-names --flush-logs --source-data=2 | pv | ssh root@<IP> 'cat > <path>/masterdump.sql'


Mit gzip 

mysqldump --all-databases --allow-keywords --single-transaction --quote-names --flush-logs --source-data=2 | pv | gzip -9 -c | ssh root@<IP> 'cat > <path>/masterdump.sql.gz'


Restore auf dem Slave

Sync Bin Log beim Import deaktivieren 

mysql -u root
SET GLOBAL sync_binlog=0;
SHOW VARIABLES like '%sync%';



Daten importieren einfach ohne gzip 

cat /root/mysqldump.sql | mysql -u root



Import von gzip Daten 

zcat /path/to/file.sql.gz | mysql -u 'root'



Import von gzip Daten mittels pv 

pv masterdump.sql.gz | gunzip | mysql -u root


Progress der Datenübernahme 

watch -n 5 'echo "show processlist;" | mysql -uroot' ;


Sync Bin wieder aktivieren und rechte aktualisieren 

mysql -u root
SET GLOBAL sync_binlog=1;
SHOW VARIABLES like '%sync%';

FLUSH PRIVILEGES;


Starten der Replikation

Auf dem Slave 

root@db01:~$ head -n 50 db_dump_from_master.sql | grep -i "master"
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000011', MASTER_LOG_POS=386;

mysql -u root
stop slave;
reset slave;
change master to master_host='<Master-IP>', master_user='repl', master_password='<PW>', master_log_file='<Master Log File>', master_log_pos=<Master Log Pos>;

Beispiel:
change master to master_host='192.168.0.1', master_user='repl', master_password='password', master_log_file='mariadb-bin.000011', master_log_pos=386;

start slave;


Status des Slave

show slave status\G

Wichtige Parameter:


Master Log Position

So kann man sich die Master Log Position ansehen. 

mysql -u root
show master status;

Dies sollte jedoch für den Aufbau des Slaves nicht nötigen sein da wir dies in den Dump gleich rein schreiben.

Man kann sich die Log Positon auch offline ansehen 

mysqlbinlog <letztes file> | tail
end_log_pos ->


Server ID anzeigen

SHOW VARIABLES LIKE 'server_id';


Server UUID anzeigen

show variables like '%uuid%';


Server UUID ändern

Oft liegt unter /etc/mysql oder /var/lib/mysql ein File mit dem Namen “auto.cnf”.
Dieses muss gelöscht werden und der Service neu gestartet. 

rm /var/lib/mysql/auto.cnf
systemctl restart mysql.service


Slave Wiederaufbau

Funktioniert die Replikation warum auch immer nicht mehr kann man den Slave wenn die Logfiles am Master noch verfügbar sind so wieder aufbauen 

show slave status\G

stop slave;
reset slave;
start slave;


Sollte das nicht funktionieren und die DBs nicht mehr zusammen finden kann man den Startpunkt auch manuell angeben. 

show slave status\G
stop slave;
reset slave;

change master to master_host='<Master-IP>', master_user='repl', master_password='<PW>', master_log_file='<Relay_Master_Log_File>', master_log_pos=<Exec_Master_Log_Pos>;

start slave


Manueler Logswitch

Mit diesem Befehl wird das aktuell Binlog geswitch auf das nächste.
Befehl muss auf der Master DB abgesetzt werden. 

FLUSH BINARY LOGS;


Problem beim Sync

Falls es beim Sync zu Problemen kommt.

Duplicate entry

Wenn man beim Sync die Meldung bekommt das es Duplicate entry's gibt kann man die auf der Standby entweder manuell raus löschen oder Skipen 

mysql -u root

stop slave;
set global sql_slave_skip_counter=1
start slave;
show slave status\G


Kommt dies mehrfach vor kann man dies mit einer Schleife abhandeln.
Dies passiert wenn die Dbs nicht richtig zusammen finden und in der Slave schon die gleichen Daten wie in der Master Db stehen. Damit überspringt man dann diese Datensätze. 

while [ 1 ]; 
do
dup=`mysql -u root -e "show slave status \G;" | grep "Duplicate entry" | wc -l`
if [ $dup -eq 2 ] ; 
then mysql -u root -e "stop slave; set global sql_slave_skip_counter=1; start slave;"; 
fi; sleep 1; 
mysql -u root -e "show slave status\G"; 
done

Wenn es sehr viele davon sind dann kann man sleep 1 auch auf sleep 0 setzten und go…
Wobei man sich hier schon überlegen sollte ob man die Slave DB nicht komplett neu aufbaut…


Drop aller Datenbanken

Muss zB für einen neuen Slave am Slave selbst alle DBs gelöscht werden kann diese Schleife dazu verwendet werden 

for i in `mysql -uroot -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema`;
do mysql -uroot -e "drop database $i;";
echo $i - deleted;
done


MySQL-Bin Logs Löschen

Wenn viele alte Logs rum liegen auf einem Master können diese so gelöscht werden

Löschen der Logs bis zum Log xxx

PURGE BINARY LOGS TO 'mysql-bin.000223';


Löschen der Logs älter als drei Tage

PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;


Löschen der Logs bis zu einem Daten

PURGE BINARY LOGS BEFORE '20021-11-01 00:00:00';


Setzen von automatischen löschen

In der my.cnf - Neustart von MySQL erforderlich 

expire-logs-days=3


Online in der DB setzten 

SET GLOBAL expire_logs_days = 3;


Anzeigen ob dieser Parameter schon gesetzt ist 

SHOW VARIABLES like 'expire%';