Replikacja bazy danych pozwala na synchronizację danych między serwerami, co zwiększa wydajność, odporność na awarie i umożliwia równoważenie obciążenia. W tym artykule szczegółowo omówimy, jak skonfigurować replikację MySQL w trybie Master-Slave oraz Master-Master, a także jak sprawdzić jej stan i rozwiązać typowe problemy.

Replikacja Master-Slave a Master-Master

Replikacje bazy danych możemy uruchomić w modelu Master-Slave lub Master-Master:

Master-Slave

W modelu Master-Slave mamy jeden główny serwer (Master), który przyjmuje operacje zapisów (INSERT, UPDATE, DELETE), a drugi serwer (Slave) służy wyłącznie do odczytu danych.

Schemat działania:

  • Każda zmiana na Masterze jest zapisywana w binarnym logu
  • Slave pobiera ten log i wykonuje te same operacje u siebie.

Master-Slave jest często wykorzystywany jako backup wykonywany w czasie rzeczywistym, który również daje możliwość przełączenia bazy danych w przypadku awarii Mastera. Nie jest on jednak zbyt często wykorzystywany do balansowania ruchu ze względu na brak możliwości modyfikacji danych (chyba, że wystarczy nam tylko odczyt danych, np. w aplikacjach webowych, gdzie większość operacji to operacje SELECT).

Master-Master

W modelu Master-Master mamy dwa serwery Master, które mogą przyjmować zarówno odczyty, jak i zapisy.

Schemat działania:

  • Każdy serwer Master jest jednocześnie Slave dla drugiego serwera.
  • Operacje zapisu wykonywane na jednym serwerze są replikowane na drugi.
  • Oba serwery są w pełni synchronizowane.

Model Master-Master wykorzystywany jest głównie do balansowania ruchu. Jest bardziej podatny na błędy ze względu na ryzyko konfliktów - jeśli ten sam rekord zostanie zmodyfikowany na obu serwerach jednocześnie, może dojść do konfliktów, które trzeba ręcznie rozwiązywać.

Konfiguracja baz danych przeznaczonych do replikacji

Zmiany w pliku konfiguracyjnym

Zakładam, że posiadasz zainstalowaną bazę danych na serwerach które mają być objęte replikację. Zalecane jest, aby użyta była ta sama wersja MySQL, zapewni to największą zgodność.

Na obu serwerach otwórz pliki konfiguracyjne bazy danych (zazwyczaj jest to plik /etc/mysql/my.cnf) i odszukaj fragment:

bind-address = 127.0.0.1

Domyślnie serwer bazy danych jest dostępny tylko dla lokalnych połączeń. Ponieważ będziemy z nim łączyć się z poziomu innego serwera, musimy wyłączyć to ograniczenie zakomonetując tę linjkę:

# bind-address = 127.0.0.1

W tym momemcie warto zaznaczyć, że jeżeli korzystasz z zapory firewall na swoim serwerze, powinieneś otworzyć port 3306, np. poleceniem:

ufw allow 3306

Następnie w tym samym pliku wprowadź konfiguracje dot. zapisywania logów binarnych serwera zawierających operacje o wszystkich wprowadzonych zmianach w bazie danych. Na ich podstawie replikowana jest inna baza danych

server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
binlog_do_db    = nazwa_bazy
  • server-id to unikalny identyfikator serwera w ramach danej puli serwerów objętych replikacją. Dla pierwszego serwera ustaw 1, a dla drugiego 2
  • log_bin to ścieżka w którym zapisywanie będą logi binarne
  • binlog_do_db to nazwa bazy objętej replikacją. Wprowadź tutaj nazwę swojej bazy danych

Utworzenie użytkownika replikacji

Na serwerze typu master otwórz konsolę MySQL jako użytkownik root i wprowadź poniższe polecenia. Jeżeli konfigurujesz replikację typu master-slave wykonaj to tylko dla jednego, głównego serwera master. Jeżeli natomiast Twoim celem jest replikacja master-master, wykonaj poniższe kroki dla obu serwerów.

mysql -u root -p

Utwórz nowego użytkownika. Zamień password na hasło, które użyjesz:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';

Nadaj odpowiednie uprawnienia utworzonemu użytkownikowi:

GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

Następnie zrestartuj bazę danych, aby załadować nową konfigurację:

systemctl restart mysql

Wykonanie zrzutu bazy danych

Zanim skonfigurujemy replikację, wykonamy zrzut bazy danych głównego serwera, a następnie na drugim serwerze utworzymy bazę danych na podstawiego wykonane zrzutu. Da to nam gwarancje, że od samego początku replikacji, obie bazy danych są takie same.

Zablokowanie bazy danych na czas zrzutu

Aby zapewnić spójność danych podczas zrzutu, należy zablokować tabele do odczytu:

FLUSH TABLES WITH READ LOCK;

Polecenie to blokuje możliwość zapisu do bazy, ale nadal pozwala na odczyt danych. Oznacza to, że podczas trwania blokady inne sesje mogą wykonywać zapytania SELECT, ale nie mogą modyfikować danych (np. za pomocą INSERT, UPDATE, DELETE). Pamiętaj o tym, jeżeli wykonujesz je na produkcyjnej wersji bazy danych.

Następnie wykonaj polecenie:

SHOW MASTER STATUS\G

Zwróci ono wartości File i Position, które będą potrzebne do konfiguracji drugiego serwera. Są to odpowiednio nazwa pliku dziennika binarnego i pozycja w której wykonaliśmy zrzut bazy, a więc miejsce od którego powinna rozpocząć się replikacja. Zapisz obie wartości, będą potrzebne podczas konfiguracji replikacji na drugim serwerze.

Wykonanie zrzutu bazy do pliku

Następnie wykonaj zrzut bazy danych do pliku, który zostanie przeniesiony na drugi serwer:

mysqldump -u root -p nazwa_bazy > dump.sql

Odblokowanie bazy danych

Po zakończeniu zrzutu możemy odblokować tabele:

UNLOCK TABLES;

Utworzenie kopii bazy danych na drugim serwerze

Na drugim serwerze - niezależnie czy konfigurujesz replikację Master-Master czy Master-Slave - utwórz bazę danych z utworzonego wcześniej zrzutu. Zrzut prześlesz między serwerami przez scp lub rsync, ew. pobierając najpierw zrzut na swój komputer, a następnie wysyłając go na drugi serwer przy pomocy klienta sftp, np. WinSCP.

mysql -u root -p nazwa_bazy < dump.sql

Uruchomienie replikacji

Synchronizacja danych w replikacji polega na tym, że serwer Slave na podstawie logów binarnych wykonuje na swojej bazie danych dokładnie te same zapytania modyfikujące strukturę bazy lub jej zawartość co na serwerze Master. W przypadku replikacji w której mamy dwa serwery Master wówczas oba serwery wzajemnie wykonują u siebie te same zapytania.

Uruchomienie replikacji Master-Slave

Na serwerze Slave uruchom zapytanie:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='ip-mastera',
  SOURCE_USER='replicator',
  SOURCE_PASSWORD='password',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=12345;

Gdzie:

  • SOURCE_HOST to publiczny adres IP serwera Master
  • SOURCE_USER i SOURCE_PASSWORD to dane użytkownika na serwerze Master z uprawnieniami do replikacji
  • SOURCE_LOG_FILE i SOURCE_LOG_POS to plik i pozycja w nim od której powinna rozpocząć się replikacja. Powinieneś zapisać te wartości przed wykonaniem zrzutu bazy danych

Następnie możesz uruchomić replikację:

START REPLICA;

W kolejnym rozdziale dowiesz się w jaki sposób sprawdzić czy replikacja działa poprawnie i jak rozwiązać najpopularniejsze problemy.

Uruchomienie replikacji Master-Master

Uruchomienie replikacji Master-Master jest bardzo podobne jak Master-Slave z tą różnicą, że polecenie CHANGE REPLICATION SOURCE TO powinieneś wykonać na obu serwerach. Na pierwszym serwerze jako źródło replikacji ustaw drugi serwer, a drugim serwerze - pierwszy serwer. To samo dotyczy wartości SOURCE_LOG_FILE i SOURCE_LOG_POS, one również powinny być ustawione w analogiczny sposób.

Sprawdzenie stanu replikacji i rozwiązywanie problemów

Sprawdzenie statusu replikacji

Na serwerze z replikowaną bazą danych uruchom polecenie:

SHOW REPLICA STATUS\G

Zwróć uwagę na wartość Replica_SQL_Running_State. Powinna ona mieć wartość podobną do: Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates co oznacza, że skopiowane zostały wszystkie wpisy z logów binarnych i serwer oczekuje na kolejne zapytania.

W przypadku gdy z jakiegoś powodu replikacja nie będzie działać prawidłowo, w odpowiedzi na wykonane polecenie zobaczyć treść błędu. Po jego rozwiązaniu zrestartuj replikację poleceniami:

STOP REPLICA;
START REPLICA;