W poprzednim poście porównywałem odporność obu silników na awarie. W skrócie chodziło o to, co się stanie, jeśli wyciągniemy wtyczkę z kontaktu? Jakie będą skutki? Jak szybko serwer zacznie działać i serwować dane? W tym poście pokrótce opiszę, co się stanie, jeśli wtyczka została wyciągnięta i po ponownym starcie serwera okazuje się, że trzeba dane przywrócić z backupu.

Dane z bazy MySQL można backupować na kilka różnych sposobów. Jednym z najbardziej popularnych jest wykonanie zrzutu danych do formatu SQL przy pomocy programu mysqldump. Odzyskiwanie tych danych sprowadza się do wgrania zrzutu ponownie do bazy. Tego typu operacja wygląda tak samo w przypadku obu silników. Różnicą pomiędzy MyISAM i InnoDB będzie jedynie to, że (jak opisywałem to w jednym z poprzednich postów) oryginalne InnoDB nie potrafi zbudować indeksu przy pomocy sortowania. Nawet stosując najnowsze wersje InnoDB Plugin czy XtraDB, przy pomocy sortowania mogą być odbudowywane tylko tak zwane „secondary indexes”. Przy odbudowie indeksu głównego (czyli zazwyczaj PRIMARY KEY), ze względu na to, że jest klastrowany, nie można zastosować tego algorytmu. W efekcie odzyskiwanie danych backupowanych do formatu SQL szybsze będzie dla tabel działających na silniku MyISAM.

Kolejną popularną metodą wykonywania backupu w MySQL jest przegranie binarne plików z danymi. W przypadku MyISAM dane z tabeli przechowywane są w plikach typu: tabela.*. Zazwyczaj są to pliki o rozszerzeniach: frm – zawiera definicję tabeli, MYD – zawiera dane, MYI – zawiera strukturę indeksów dla danej tabeli. W przypadku InnoDB wygląda to różnie, w zależności od ustawień serwera. W domyślnej konfiguracji w katalogu danej bazy trzymane są tylko pliki frm, tak jak w przypadku MyISAM zawierają one definicję tabeli. Dane umieszczane są w jednym lub większej ilości plików tworzących tzw. tablespace. W przypadku dużych baz danych są to duże pliki, które zawierają dane z wszystkich tabel InnoDB.
Istnieje możliwość zmiany tej konfiguracji. Ustawiając wartość zmiennej ‚innodb_file_per_table’ na 1 wymuszamy na MySQL tworzenie osobnego tablespace dla każdej tabeli. W takiej sytuacji w katalogu bazy danych, oprócz pliku tabela.frm tworzony jest także plik tabela.ibd, zawierający dane i indeksy.

W przypadku MyISAM wykonanie binarnego backupu sprowadza się do zablokowania możliwości zapisywania danych do tabel poprzez wyłączenie serwera MySQL albo po prostu wydanie polecenia:

FLUSH TABLES WITH READ LOCK;

Po zablokowaniu zapisu wykonujemy kopię pożądanych plików – czy to przez zwykłe cp, czy też tworząc snapshot z poziomu LVM czy SANa. Odzyskanie danych sprowadza się do wyłączenia serwera bazodanowego i zastąpienie odpowiednich plików plikami z kopii zapasowej. Tego typu mechanizm tworzenia i odzyskiwania kopii zapasowej jest szybki, miły i przyjemny. Możemy w prosty sposób odtworzyć dane z dokładnością do tabeli.

W przypadku InnoDB sprawa wygląda trochę gorzej. Skopiować odpowiednie dane jest prosto. Niestety, aby mieć pewność, że kopia zapasowa w ogóle do czegokolwiek będzie się nadawała, należałoby przed jej wykonaniem wyłączyć serwer MySQL. Wyłączenie serwera skutkować będzie poprawnym zamknięciem się InnoDB – wszystkie dane z z bufora zapisywane są do logu transakcji, a wszystkie dane z logu zapisywane są do tablespace na dysku. Dopiero teraz można skopiować na bok pliki InnoDB i być pewnym, że podczas startu nie pojawią się problemy. Oczywiście, jest możliwość zablokowania MySQL tylko do odczytu, polecenie takie jak pisałem w przypadku MyISAM, a następnie przegranie wszystkich plików InnoDB (także plików logu transakcji), ale nie ma niestety 100% pewności, że taki backup nadaje się do czegokolwiek. Można wykonać snapshot LVM, ale tu także nie ma pewności czy uda się z takiego backupu odzyskać dane. W praktyce wygląda to tak, że kilkukrotnie tego typu backup wykorzystywałem z powodzeniem. Jest to szybsze rozwiązanie, niż odtwarzanie dumpu SQL. Internet jest jednak pełny wypowiedzi ludzi, którym to się nie udało i dane zostały utracone.

Na szczęście istnieją programy, które potrafią wykonać kopię zapasową tabel InnoDB. Jednym z nich jest Hot Backup firmy Innobase, dostępny za 390 Euro na rok. Drugim jest XtraBackup – program stworzony przez Perconę i wydany na GPL2.

Problem z pierwszym jest taki, że jest to aplikacja komercyjna i swoje kosztuje. Problem z drugim jest taki, że przygotowanie środowiska w którym XtraBackup będzie poprawnie działać, zajmuje chwilę czasu i wymaga od Administratora MySQL pewnych umiejętności, jeśli chodzi o kompilację MySQL, patchowanie kodu źródłowego i innych tego typu przyjemności. Przed chwilą próbowałem uruchomić XtraBackup zbudowany ze źródeł udostępnionych na stronie Percony, kompilowany i budowany jako paczka deb przy pomocy skryptów Percony. Zbudować się zbudował, ale działanie kończy się segfaultem. Jestem pewny, że po poświęceniu chwili uda się go przygotować tak, aby działał jak należy, ale prosto z pudełka działać nie chce. Przynajmniej u mnie.

Nie da się ukryć, że jeśli chodzi o backup, znacznie wygodniejszym rozwiązaniem jest MyISAM. Tworzenie kopii zapasowej jest szybkie, dzięki binarnemu backupowi. Przy pomocy binarnego backupu można szybko odzyskać pojedyncze tabele. Backup można przenosić między serwerami – pliki z danymi skopiowane z serwera A zadziałają po wgraniu na serwer B. W przypadku InnoDB kopię tabeli można wykonać tylko wtedy, jeśli została ona utworzona już po ustawieniu zmiennej ‚innodb_file_per_table’ na 1. Jeśli zmienna ta jest w wartości domyślnej, przegrać możemy tylko całość tablespace – duże pliki z danymi z wszystkich baz. Nie ma możliwości wydzielenia z tych plików danych dla tylko jednej tabeli. Co więcej, trzeba pamiętać o tym, że InnoDB jest bardzo wrażliwe na różnice w konfiguracji dotyczącej tablespace. Jeśli na jednym serwerze, w konfiguracji ustawione jest tworzenie trzech plików tablespace, dwa po 200MB a trzeci także 200MB z możliwością powiększenia się, to takie pliki nie uda się wgrać na serwer, w którym konfiguracja ustawiona jest inaczej. Potencjalnie może się też okazać, że po przeniesieniu plików InnoDB pomiędzy różnymi wersjami czy architekturami MySQL, nie będzie się dało uruchomić serwera. W przypadku InnoDB najlepszym rozwiązaniem byłoby skorzystanie z XtraBackup, jedyny problem, jak pisałem wcześniej, to zmuszenie go do działania. Jak już zacznie działać, to jest to bardzo przydatna aplikacja rozwiązująca sporo problemów przy wykonywaniu kopii zapasowej bazy z InnoDB.