Jeżeli znane są ci tematy związane z optymalizacją MySQL to wiesz doskonale, że w pewnym momencie zasoby serwera MySQL się kończą i trzeba coś z tym zrobić nawet jeżeli wyczerpiemy wszystkie dostępne opcje a przypomnijmy wliczamy do tego: uporządkowanie struktury tabel, optymalizacja kodu aplikacji, optymalizacja zapytań a nawet denormalizację baz danych.
Wzrost danych portalu czy aplikacji internetowej potrzebnych do obsłużenia funkcjonalności jest w pewnym momencie na tyle duży, że trzeba zarezerwować więcej zasobów na obsługę bazy danych. Z artykułu o skalowaniu pionowym i poziomym platformy hostingowej jasno wynika, że w dłuższej perspektywie bardziej opłacalne jest zwiększanie ilości serwerów baz danych niż rozbudowę jednej dużej maszyny, która będzie obsługiwać wszystkie zapytania. Aby baza zachowała integralność mimo rozłożenia jej funkcji na kilka osobnych maszyn potrzebny jest mechanizm replikacji baz danych.
Replikacja baz danych to bardzo popularne rozwiązanie skalowania baz danych wykorzystujące fakt, że z bazy danych dużo częściej odczytujemy dane niż je do niej zapisujemy. Ktoś teraz zapyta: przecież wtedy wykorzystujemy pamięć podręczną typu memcached lub Redis. Oczywiście, jeżeli jednak Cache-Hit-Ratio nie przekracza powiedzmy 50% to baza w dalszym ciągu jest obciążona i warto byłoby temu zaradzić.
Zasada działania replikacji baz danych Master-Slave
Baza danych, która zawiera wszystkie dane aplikacji, obsługuje zapisy, odczyty, aktualizacje i co jakiś czas usuwanie w pewnych stadiach rozwoju aplikacji zaczyna być wąskim gardłem. Mówiąc kolokwialnie: nie wyrabia z tym wszystkim. Rozwiązaniem problemu jest replikacja bazy danych czyli utrzymywanie danych w więcej niż jednej bazie danych a w praktyce na całkiem osobnych serwerach MySQL.
Replikacja polega na zdywersyfikowaniu serwerów docelowych dla zapytań SQL. Nasz dotychczasowy serwer staje się Masterem a ten drugi będzie jego poddanym – Slave. Sługa robi to co nakazuje mu mistrz. Replikacja nie polega na wykonywaniu ciągłych kopii zapasowych i wczytywania ich do kolejnego serwera. Jest to asynchroniczne powielenie wszystkich operacji jakie znajdują się w dzienniku zdarzeń jednego serwera (w tym wypadku Mastera) na drugim serwerze (dla naszego przypadku Slave’a).
Co w praktyce daje replikacja?
Jeżeli mamy dwie identyczne bazy danych, która jedna odzwierciedla stan drugiej, możemy kierować zapisy tylko na serwera nadrzędnego a odczyty do serwera podrzędnego.
Dlaczego tak? Zapisy niemal we wszystkich systemach są bardziej „zasobożerne” i powodują blokady. O tym jak działają blokady w składowaniu InnoDB i MyISAM można przeczytać w porównaniu wewnętrznych trybów składowania MySQL. Bardziej angażuje procesor i nie cierpi obciążeń.
Duża ilość użytkowników chce szybkich odczytów – rzecz jasna dla zachowania dobrego User Experience portalu czy aplikacji. Jeżeli potrzebujemy jeszcze więcej serwerów podrzędnych możemy je specjalizować i skalować horyzontalnie:
Oczywiście to nie jedyna konfiguracja. Można tworzyć drzewa zawierające wiele serwerów nadrzędnych i podrzędnych ale to temat może na kolejny, bardziej zaawansowany artykuł.
Przewodnik wykonania replikacji na serwerze dedykowanym
Do uruchomienia replikacji typu Master – Slave potrzebujemy dwóch serwerów dedykowanych lub VPSów (lub jakąkolwiek inną metodę hostingu, która nie ogranicza dostępu do systemu operacyjnego).
Działania wykonuję na Debianie 8 (Jesse), ale to nie ma jakiegokolwiek znaczenia, bo procedura jest wszędzie taka sama jeżeli wykorzystujemy MySQL.
Krok 1: Instalujemy MySQL na obydwu serwerach
Tutaj nie będę instruował bo to nie jest kurs z podstaw administracji serwerami. Jeżeli chcecie szybko mieć stos z MySQL można użyć gotowych instalatorów, które oferują instalację LAMPa a nawet WordPressa czy PrestaShop.
Krok 2: Konfiguracja my.cnf
Na masterze trzeba „odkomentować” linijkę server-id (i nadać mu numer – w tym przypadku 1) oraz log bin:
server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
Zaś na serwerze podległym odkomentowujemy tylko server id:
server-id = 2
Krok 3: Utworzenie użytkownika replikacji na Masterze
Tworzymy użytkownika – dla celów tutorialu niech przyjmie nazwę „replikacja” a jego hasło to: „haslo”. Następnie udzielamy mu uprawnień REPLICATION SLAVE.
Krok 4: Utworzenie kopii z bazy Master
Za pomocą dowolnej metody może to być mysqldump. Lub jeżeli jest to stack LAMP, za pomocą eksportu w phpMyAdmin.
Krok 5: Wczytanie kopii bazy do Slave
Albo za pomocą scp w SSH lub za pomocą importu w phpMyAdmin.
Krok 6: Przypisanie Mastera do Slave’a
Uruchamiamy SQL i wykonujemy zapytanie:
CHANGE MASTER TO MASTER HOST='10.10.10.10', MASTER USER='replikacja', MASTER PASSWORD='haslo';
Jeżeli wszystko przebiegło pomyślnie wykonujemy polecenie:
START SLAVE
Krok 7: Sprawdzamy
Wykonując komendę:
SHOW SLAVE STATUS\G;
Powinniśmy otrzymać taki rezultat:
Najważniejsze co nas interesuje to Slave_IO_Running i Slave_SQL_Running. Jeżeli obie właściwości mają wartość „Yes” to znaczy że wszystko jest w porządku. Read_Master_Log_Pos wskazuje aktualną pozycję binloga. Pole Last Error używamy w celu debugowania.
Krok 8: Testujemy
Dodajemy rekord do tabeli Master. Jeżeli pojawia się on na serwerze podległym, można sądzić że wszystko jest OK.
Rozwiązywanie problemów z replikacją MySQL
W wyniku błędu lub desynchronizacji Slave_SQL_Running ustawia się na „No” a cała replikacja przestaje działać. Należy wówczas zatrzymać bazę Slave komendą:
STOP SLAVE
i wykonać zapytanie:
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Po wszystkim ponownie uruchomić niewolnika. Jeżeli powtarzanie czynności nie przynosi rezultatów. Należy zresetować mastera.
Restart replikacji uruchamiamy komendą:
RESET MASTER
Nawet jak celowo psujemy replikację restartujac serwery i zapisując dane rootem na serwerze slave to zawsze któraś z metod ponowne synchronizuje bazy danych.
Najczęstsze błędy w replikacji są spowodowane brakiem integralności bazy danych. Przy odtwarzaniu bazy danych z mastera do slave i odwrotnie należy blokować wszystkie tabele.
Zalety replikacji MySQL
Wszelkie efekty związane z większa wydajnością bazy danych:
- Możliwość skalowania horyzontalnego,
- Oszczędność w dalszych etapach rozwoju projektu (możliwość dokupienia najbardziej efektywnych kosztowo serwerów),
- Szybsze działanie aplikacji (więcej przestrzeni na MySQL Cache),
- Odciążenie serwera Master (mniej połączeń przypadających na jeden serwer),
- Nie trzeba przebudowywać zapytań związanych z zapisem ani odczytem.
Wady replikacji MySQL
Przy bardzo rozbudowanej infrastrukturze można napotkać problemy:
- Większe koszty w początkowych fazach rozwoju aplikacji (dodatkowy serwer kosztuje),
- Replication lag, czyli opóźnienie po jakim rekordy w Slave są odwzorowywane z bazy Master,
- Konieczność przebudowy skryptów odpowiedzialnych za zapytania SQL związane z odczytem,
- Master nadal jest pojedynczym punktem awarii – przy zwielokrotnieniu serwerów nie otrzymujemy zwiększonej dostępności,
- Slave wykonuje nawet instrukcje, które powodują błąd w wyniku czego replikacja może przestać działać.
Mity związane z replikacją MySQL
Replikacja zwalnia z konieczności wykonywania kopii zapasowych – nieprawda. W przypadku awarii głównego serwera lub włamania, szkody są propagowane także na serwerze podległym (chyba, że celowo stosujemy opóźnienie w replikacji rzędu kilku godzin).
Replikacja jest trudna do wykonania – nieprawda, czego dowodem może być ten tutorial dzięki któremu uruchomimy replikację w parę minut.
Podsumowanie
Replikacja to sprawdzone rozwiązanie skalowania baz danych. Można skorzystać z rozwiązań chmurowych typu Amazon RDS, jednak dla niektórych takie rozwiązanie może być dosyć drogie. Jeżeli nasz budżet jest ograniczony, taką replikację można śmiało zastosować na serwerach kosztujących kilkadziesiąt złotych miesięcznie. Przy okazji uczymy się administracji, która przyda nam się kiedy utrzymywanie projektu w chmurze będzie zbyt drogie lub niemożliwe.
Źródła
https://dev.mysql.com/doc/refman/8.0/en/replication.html
Henderson C. Building Scalable Web Sites. Building, Scaling, and Optimizing the Next Generation of Web Applications, Helion 2006.
Ejsmont A. Web Scalability for Startup Engineers, McGraw-Hill Education, 2015.
Krystian Magdziarz pisze: 
Gość 
kerszi 
Odpowiedz lub skomentuj