Co to jest replikacja MySQL?

Serwer MySQL jest bardzo chętnie wybierany jako baza danych dla wielu serwisów internetowych. Z biegiem czasu baza potrafi się rozrastać do kolosalnych rozmiarów. Poza przechowywaniem treści strony (artykuły, komentarze, lista użytkowników), zawiera również ustawienia samej strony czy aplikacji. W takim wypadku regularne robienie kopii zapasowej staje się wręcz koniecznością. Duża baza danych to również większe obciążenie dla serwera, który musi uporać się z zarządzaniem milionami rekordów. Remedium na wiele tego typu problemów może być replikacja.

Serwer bazodanowy MySQL udostępnia bardzo ciekawy mechanizm replikacji. Czym on jest? W skrócie, dzięki replikacji każda zmiana na serwerze głównym (master), prowadzi do identycznej zmiany na serwerze zapasowym (slave). Replikacja pozwala nam na:

• Skalowalność – możliwe jest rozłożenie obciążenia pomiędzy wieloma serwerami. Operacje zapisu i aktualizacji rekordów odbywają się na jednym serwerze, a pobieranie i przeszukiwanie danych z drugiego.
• Bezpieczeństwo – dzięki replikacji tworzymy klona istniejącej bazy produkcyjnej. O ile nie uchroni nas to przed operacjami typu DROP TABLE, to może pomóc w przypadku awarii sprzętowej głównego serwera. Zreplikowana baza świetnie nadaje się do wykonywania tradycyjnego backupu, bez konieczności wstrzymywania pracy bazy głównej.
• Analiza – skomplikowane operacje analityczne, różnego rodzaju przeliczenia i analizy statystyczne mogą być wykonywane na osobnym serwerze bez konieczności obciążania głównej bazy.
• Separacja – możemy udostępnić klon bazy produkcyjnej dla developerów lub testerów, aby wykonali swoje prace na kopii bazy.

Mechanizm replikacji MySQL

Replikacja danych MySQL opiera się o bardzo prostą zasadę. Serwer główny (master), prowadzi swego rodzaju dziennik, w którym zapisuje każdą czynność, jaką wykonał. Wykorzystuje do tego bin-logi. Są to pliki binarne zawierające instrukcje, jakie wykonał master. Serwer zapasowy (slave) odczytuje te dane i kolejno wykonuje zapytania, zapełniając bazę kolejnymi rekordami. Efektem tej pracy są dwie identyczne bazy danych.

Skonfigurowanie mechanizmu replikacji powoduje uruchomienie po stronie serwerów dodatkowych wątków. Na serwerze master pojawi się dodatkowy wątek (na każdy serwer slave), który odpowiada za wysyłanie bin-logów do serwerów slave.

Natomiast serwer zapasowy tworzy dwa wątki. Pierwszy z nich zwany I/O Thread odpowiada za odbieranie dziennika od serwera głównego. Zapisuje je lokalnie na dysku w plikach tymczasowych (relay-log). Drugi wątek, zwany SQL Thread, zajmuje się parsowaniem tych plików i wykonywaniem zapytań do bazy.

Rodzaje replikacji

Dostępne są trzy różne metody replikacji, co przekłada się na format danych zapisywanych do bin-logów. Odpowiada za to zmienna binlog_format, która może przyjąć wartość: ROW, STATEMENT, MIXED. Metody te to:

• SBR (statement-based replication) – w tym trybie, serwer do pliku zapisuje zapytania jakie wykonał. Był to jedyny dostępny tryb przed wersją MySQL 5.1.4.
• RBR (row-based replication) – do bin-logów zapisywane są wyniki działań zapytań na serwerze master. Zapisywana jest informacja jaki rekord został w jaki sposób zmieniony.
• MFL (mixed-format logging) – jest to połączenie dwóch powyższych typów replikacji.

Każdy z powyższych metod ma swoje wady i zalety. Najszybszą metodą replikacji jest wykorzystanie techniki SBR. Serwer główny zapisuje do pliku zapytanie jakie wykonał, następnie serwer zapasowy je odczytuje i wykonuje. Przykładem takiego zapytania może być:

DELETE FROM customers WHERE customerNumber = 495;

Metoda ta jest bardzo szybka i wydajna. Do pliku logów zapisywane są tylko zapytania SQL. Powoduje to niestety problemy w przypadku bardziej skomplikowanych zapytań czy procedur składowanych. Wyobraźmy sobie zapytanie, które korzysta z funkcji losowych (RAND()). Replikacja takiego zapytania będzie miała zupełnie inny wynik po stronie każdego z serwerów zapasowych.

Rozwiązaniem tego problemu było wprowadzenie replikacji metodą RBR. W tym wypadku do bin-logów zapisywane są zmiany, jakie zaszły po wykonaniu polecenia. Logowane są informacje na temat sposobu modyfikacji konkretnych rekordów. Niestety ta metoda jest znacznie powolniejsza od replikacji zapytań. Powoduje również znaczne zwiększenie ilości wysyłanych danych pomiędzy replikującymi się serwerami. Dlatego stworzono metodę mixed-format logging.

W tej metodzie, w większości przypadków, logowane są zapytania SQL tak jak w przypadku SBR, natomiast dla zapytań, których wynik nie jest przewidywalny, włączana jest replikacja RBR.

Konfiguracja

Na samym początku zajmiemy się konfiguracją serwera master. W tym celu musimy poddać edycji plik konfiguracyjny serwera MySQL. Najczęściej jest to plik /etc/my.cnf. Powinny znaleźć się w nim takie opcje jak:

[mysqld]
log-bin = /tmp/mysql-bin
binlog_format = mixed
max_binlog_size = 50M
server-id = 1

Każda opcja odpowiada ze konkretny parametr dotyczący replikacji:

• log-bin = /tmp/mysql-bin – uruchamia mechanizm logowania zmian w bazie danych. Zmiany te zapisywane są do pliku.
• binlog_format = mixed – ustawia format zapisu danych do bin-logów. W tym wypadku jest to format mieszany.
• max_binlog_size = 50M – maksymalny rozmiar dziennika logów.
• server-id – liczba ta określa unikatowy numer serwera w obrębie mechanizmu replikacji.

Dostępne są również opcje, które pozwalają nam na replikowanie wybranej bazy danych lub tabeli. Domyślnie klonowane będą wszystkie bazy i tabele w obrębie serwera. Opcja binlog-do-db=database wymusza replikację tylko wybranych baz danych.

Po restarcie serwera MySQL, logujemy się do niego (mysql -u root -p) i wydajemy następujące zapytanie SQL. Powinno ono pokazać nam status serwera master.

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 106
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

mysql>

Teraz należy stworzyć użytkownika, który będzie odpowiadał za autoryzację serwerów zapasowych. W tym celu wydajemy zapytanie SQL:

CREATE USER 'repuser'@'%' IDENTIFIED BY 'haslo';
GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'%' IDENTIFIED BY 'haslo';

Teraz przyjdzie nam pora na skonfigurowanie serwera slave. Najczęściej jest to plik /etc/my.cnf. Powinny znaleźć się w nim takie opcje jak poniżej. Nie dotyczą one serwera MySQL 5.5 i nowszych. Dla nich replikację ustawiamy za pomocą polecenia SQL.

server-id=2
master-host=192.168.0.164
master-user=repuser
master-password=haslo
master-connect-retry=30

Dla MySQL 5.5 i nowszych:

CHANGE MASTER TO MASTER_HOST='192.168.0.164',
MASTER_USER='repuser',
MASTER_PASSWORD='haslo';

Podłączamy się klientem do serwera i wydajemy polecenie, które załaduje pierwszą porcję danych z serwera głównego a następnie zatrzyma replikację.

mysql> LOAD DATA FROM MASTER;
Query OK, 0 rows affected, 1 warning (0.34 sec)
mysql> SLAVE STOP;
Query OK, 0 rows affected (0.00 sec)

Kolejnym krokiem jest ustawienie parametrów replikacji. Wydajemy zapytanie SQL:

CHANGE MASTER TO
MASTER_HOST='192.168.0.164',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1274;

Wartości MASTER_LOG_FILE oraz MASTER_LOG_POS pobieramy z polecenia SHOW MASTER STATUS wydanego po stronie serwera głównego. Teraz pozostaje nam włączyć replikację oraz sprawdzić jej status.

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.164
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1274
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             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: 1274
              Relay_Log_Space: 407
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

Replikacja powinna działać bezproblemowo.

Replikowanie wybranej tabeli

Mechanizm MySQLa udostępnia możliwość replikowanie wybranej tabeli, bazy lub wyłączenia jej z replikacji. Odpowiadają za to parametry:

replicate-do-db
replicate-ignore-db
replicate-do-table
replicate-ignore-table
replicate-wild-do-table
replicate-wild-ignore-table
binlog-do-db
binlog-ignore-db

W teorii umożliwiają one filtrowanie tego, co jest replikowane. Możemy replikować lub nie tylko wybraną tabelę, bazę oraz kilka tabel za pomocą wildcardów. Filtrowanie możemy ustawić już na poziomie binlogów (binlog-do-db, binlog-ignore-db). Definiujemy wtedy jakie dane trafiają w ogóle do plików.

W praktyce nie jest już tak kolorowo. Wyobraźmy sobie, że ustawiliśmy opcję: replicate-ignore-db=sales. Oznacza to, że nie chcemy replikować bazy sales.

USE prices;
UPDATE sales.january SET amount=amount+1000;

W takim wypadku zapytanie się zreplikuje. Dlaczego? Otóż filtrowanie w MySQLu bierze pod uwagę tylko ustawienie aktywnej bazy za pomocą polecenia USE; Zatem jeżeli odwołamy się do bazy korzystając ze składni baza_danych.tabela, filtr MySQLa nie wyłapie takiego odwołania i zreplikuje zapytanie.

Dlatego nie jest zalecanie korzystanie z tego typu funkcjonalności, jeżeli nie jesteśmy pewni jakie zapytania idą do bazy danych. Może się okazać, że ładnie skonfigurowana replikacja nagle się rozjedzie przez tego typu problem.

Typowe problemy replikacji

Wielu administratorów uważa, że replikacja zastąpi konieczność wykonywania backupu danych. Możemy ją wykorzystać do wykonywania kopii zapasowych. Dzięki temu backup jest pobierany z jednego z serwerów zapasowych, nie obciążając pracującego produkcyjnie serwera głównego. Serwer slave można nawet na jakiś czas wyłączyć, wykonać kopię plików a następnie uruchomić ponownie.

Niestety zreplikowana baza danych nie uchroni nas przed różnego rodzaju zapytaniami, które kasują rekordy, tabele czy bazy danych. Zapytania typu TRUNCATE TABLE lub DROP TABLE, zostaną bardzo szybko przeniesione na serwer zapasowy i skasują nasz backup. Aby uniknąć takich sytuacji, możemy skorzystać z polecenia CHANGE MASTER TO MASTER_DELAY = N;. Opóźni to replikowanie danych o N sekund. Może to nas uchronić przed skasowaniem danych po stronie serwera zapasowego.

Replikacja chroni nas przed fizycznym uszkodzeniem serwera głównego. W takim wypadku możemy przełączyć naszą aplikację na bazę zapasową.

Jedną z ważniejszych cech dla replikacji MySQL jest fakt, że jest ona asynchroniczna. Nie daje to nam żadnej gwarancji, że wykonaniu dowolnej operacji na głównej bazie danych, zostały one przesłane i zapisane na zapasowych bazach. Może się tak zdarzyć, że mamy ogromnego pecha i tuż po zakończeniu transakcji, serwer główny się wyłącza. Nasza aplikacja przełącza się na zapasową bazę. Pech chciał, że druga baza nie zdążyła z replikacją.

Rozwiązaniem takich problemów jest wprowadzony mechanizm półsynchroniczny (semisynchronous replication). Został on dodany dość niedawno, bo w wersji MySQL 5.5. Po wykonaniu polecenia COMMIT, następuje wysłanie danych do serwera zapasowego. Zapisuje on je do relay-logów. W momencie, kiedy zapisze wszystkie otrzymane dane, wysyła potwierdzenie do serwera master. W momencie otrzymania przez serwer główny informacji od co najmniej jednego serwera slave transakcja jest uznawana za zakończoną. Rozwiązanie takie oczywiście wypływa na wydajność głównej bazy danych, która musi czekać na odpowiedzi z serwera zapasowego.

Jak naprawić replikację?

Nie raz zdarzy się nam, że serwer slave przestanie replikować dane. Przyczyn może być wiele, ale najczęstszym powodem wstrzymania replikacji jest błędne zapytanie SQL, które nie może się wykonać. Sprawdzając status całego mechanizmu zobaczymy:

Slave_IO_Running: Yes
Slave_SQL_Running: No

Oznacza to, że wątek pobierający dane z serwera master działa, lecz wątek, który powinien je ładować do bazy danych z jakiś przyczyn się wyłączył. Poniżej zobaczymy również powód zatrzymania się tego wątku.

Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`cms`.`comment_replies`, CONSTRAINT `comment_replies_12` FOREIGN KEY (`comment_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE)' on query. Default database: 'cms'. Query: 'INSERT INTO comment_replies (`ID`,`FILE_ID`,`RECIPIENT_ID`,`COMMENT_ID`,`PROFILE_ID`,`CREATED`,`NUMBER`,`AUTHOR`,`TEXT`) VALUES (NULL,5236,2466656,'1133',454,1303150999,1,'Lorem Ipsum','Proin vel nulla vel nisi eleifend rhoncus dignissim vitae nulla. Quisque odio nibh.')

Replikacja powinna sama się zatrzymać, lecz dla pewności wykonujemy polecenie:STOP SLAVE;. Teraz możemy ręcznie poprawić bazę danych np. poprzez dodanie brakującego elementu w innej tablicy, stworzenie tabeli i tym podobne. Możemy zawsze wykonać polecenie, które pozwoli na ominięcie błędnego zapytania:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Teraz pozostaje już tylko włączyć ponownie replikowanie za pomocą: START SLAVE;.

Mechanizm replikacji bazy danych to bardzo ciekawe rozwiązanie. Przed jego wdrożeniem do warunków produkcyjnych należy zwrócić uwagę na kilka jego mankamentów. Warto przetestować wcześniej implementację tego mechanizmu w różnych sytuacjach, aby uniknąć przykrych niespodzianek.