MySQL – szybkie klonowanie bardzo dużej bazy danych na przykładzie bazy danych skryptu PrestaShop (2 metody)

Czasem zachodzi potrzeba szybkiego sklonowania ogromnej bazy danych MySQL np. dla potrzeb testów albo uruchomienia klona strony www.

Mówimy tu o bazie wielkości kilka GB.

Założenie jest też takie, że nie przesyłamy żadnych plików pomiędzy serwerem a naszym komputerem. Wszystkie operacje są wykonywane zdalnie i nie obciążają naszego łącza.

Jak to sprawnie wykonać? Oto 2 metody jak to zrobić.

METODA #1 – mamy dostęp do konsoli Linux

Zrzut bazy do pliku SQL

Tutaj sprawa jest prosta. Po prostu wykonujemy zrzut bazy danych:

mysqldump -uuzytkownik_bazy -p'tajne-haslo' -hhost_bazydanych nazwabazydanych > plik_zrzutu_bazy.sql

Zamiana prefixu

Następnie w takim zrzucie musimy zamienić prefix w bazie danych.

PREFIX są to znaki alfanumeryczne przed nazwą tabel, które identyfikują tabele w obrębie danej grupy tabel. Dla przykładu standardowym prefixem w PrestaShop jest przedrostek `ps_` przed nazwami tabel, a dla WordPress `wp_`

Tutaj posłużymy się komendą sed pokazaną w artykule:

znajdź i zamień frazę w pliku tekstowym poprzez komendę w konsoli Linux

Czyli jeśli prefix to `ps_` zastosujemy komendę:

sed -ie 's/`ps_/`psklon_/g' plik_zrzutu_bazy.sql

Wgranie zmienionego pliku SQL

Plik zrzutu bazy zostanie zmodyfikowany i możemy go wgrać do naszej bazy danych:

mysql -uuzytkownik_bazy -p'tajne-haslo' -hhost_bazydanych nazwabazydanych < plik_zrzutu_bazy.sql

Tabele z nowym prefixem zostaną dopisane  do bazy danych i to wszystko. Założenie tutaj jest takie, że klona wgrywamy do tej samej bazy danych.

Jeśli ma być to wgrane do innej bazy zastosujemy:

mysql -uinnyuzytkownik_bazy -p'inne-tajne-haslo' -hodpowiedni_host_bazydanych innabazydanych < plik_zrzutu_bazy.sql

W klonie aplikacji WWW podłączamy się pod odpowiednią bazę danych i stosujemy odp. hasła i nazwy użytkownika.

Oczywiście założenie jest takie, że musimy posiadać na serwerze odpowiednią ilość pamięci dyskowej aby wykonać taki zrzut. Nalezy pamiętać, że klonowanie bazy też wykorzysuje przestrzeń dyskową. Więc w tej metodzie potrzebujemy de facto 2x więcej miejsca na wykonanie tej operacji.

METODA #2 – nie mamy dostępu do konsoli Linux

Sytuacja jest inna jeśli provider hostingowy nie udostepnia konsoli Linux a skrypty powłoki w PHP też są niedostępne. Np. aby móc wywołać komendy poprzez odpowiednie skrypty PHP do emulowania terminala Linux.

Jednak i na to jest sposób ;-) Ważne, aby mieć dostęp do PhpMyAdmin. Ale ten skrypt jest dostępny w większości serwerów.

Możemy też dograć wersję portable tego skryptu w ostateczności. Wiele takich wersji PMA jest dostępna w sieci.

Skrypt SQL tworzący nowe tabele-klony

W PMA w zakładce SQL wywołujemy komendę, która pokaże nam wszystkie tabele:

SHOW TABLES;

Exportujemy to (przycisk pod tabelką) do pliku SQL albo Excel, aby mozna było wyświetlić go w formie „słupka” w pliku tekstowym:

ps_accessory
ps_address
ps_address_format
ps_alias
ps_attachment
ps_attachment_lang
ps_attribute
ps_attribute_group
ps_attribute_group_lang
ps_attribute_group_shop
ps_attribute_impact
ps_attribute_lang
ps_attribute_shop
ps_blocklink
ps_blocklink_lang
ps_blocklink_shop
ps_carrier
ps_carrier_group
ps_carrier_lang
...

W następnym kroku skorzystamy ze specyficznej i użytecznej komendy MySQL:

CREATE TABLE nowabaza.ps_access SELECT * FROM starabaza.ps_access

Tworzy ona w równoległej bazie MySQL identyczne tabele jak we wzorcowej. Niestety nie da się jej zastosować od razu do wszystkich tabel (bez np. pętli for itp.) więc dlatego posłuży nam do tego wyżej wyeksportowana lista tabel.

Otwórz plik Excel i utwórz komórki wg. schematu:

Nie zapomnij o średniku w ostatniej kolumnie:

Następnie przeciągnij procedury CREATE i SELECT (oraz średnik) w dół aby powielić te komórki:

Efektem będzie elegancko wypełniona tabelka:

Pamiętaj aby usunąć przedzielenia w dużej ilosci tabel typu:

Zaburzą tylko one stworzenie odp. zapytania. Po poprostu je usuń:

Tak wykonane komórki skopuj do pliku tekstowego:

Usuń wystąpienia tabulacji (przez znajdź i zamień). Znaki tabulacji zaznaczone zostały tutaj przykładowo na zielono:

Skrypt zapisz np. jako tabele.sql zaimportuj do bazy danych przez PhpMyAdmin:

Efekt:

W ten sposób w równoległej bazie będziemy mieć listę nowych tabel – klonów z bazy wzorcowej. Niezależnie od wielkości bazy danych. Z racji, że kopiowanie odbywa się po stronie MySQL (z praktycznie całkowitym pominięciem interpretora PHP) – są one bardzo szybkie w wykonaniu i poradzą sobie z dużą ilością danych. Możemy też je rozbić na partie. Np. w kilku podejściach aż do przekopiowania wszystkich. W naszym przypadku (baza danych > 4GB) udało się to za 1 podejściem 😉 Na dowód – nasz plik zrzutu:

Uzupełnienie zaimportowanych danych

Jednak nasze tabele-klony nie są kompletne. Użyta wyżej komenda istotnie importuje dane jednak pozbawione tzw. indexów. Określają one np. automatyczne przyznawanie następnej wartości dla kluczowej tabeli (AUTO_INCREMENT) itp. Przez to jeśli wejdziemy w przykładową tabelę – brakuje ID tabeli. Takie dane nie nadają się do podłaczenia do aplikacji WWW, bo po pierszym przeładowaniu baza danych zostanie uszkodzona, a dane najprawdopodobniej będą już bezużyteczne:

Aby to poprawić użyj we wzorcowej bazie danych opcji Export i dla wszyskich tabel zaznacz jedynie strukturę:

Wyeksportuj to w formie pliku tekstowego.  Mimo, że baza danych może mieć kilka GB – jak wyeksportujemy samą strukturę plik tekstowy będzie miał rozmiar zaledwie kilkaset kb. Bez problemu możemy go edytować w dowolnym edytorze.

Otwieramy taki plik i kopiujemy dyrektywy które znajdują się na końcu pliku. Zaraz od miejca, gdzie jest koniec tworzenia ostatniej tabeli.

W tym przypadku ostatnią tabelą jest ps_zone_shop kopiujemy zatem dyrektywy w dół od pierwszego wystąpienia:

ALTER TABLE

Przykład:

Czyli od miejsca:

ALTER TABLE `ps_access`

– do końca pliku.

Uwaga! Musimy pominąć komendę

COMMIT;

Znajdującą się na końcu pliku. Domyka ona analogiczną komendę rozpoczynającą zrzut MySQL w tym pliku. A nie bedzie ona nam tutaj potrzebna:

Tak gotowy plik z komendami „ALTER TABLE” zapisujemy jako np. modyfikacja.sql i wgrywamy do nowej bazy danych aby określić odpowiednie indeksy.

W efekcie działań – tabele będą już poprawione. Wyżej prezentowana tabela też będzie juz kompletna:

Baza danych zostanie w ten sposób sklonowana i może być użyta do dalszych celów.

Jak pokazaliśmy w #2 metodzie – kopiowanie po powłoce MySQL jest bardzo efektywne. Może być wręcz wykonane bezpośrednio w kosoli Linux przez bezpośrednie połaczenie z silnikiem bazy danych. W ten sposób sklonujesz nawet bardzo rozrośnięte bazy danych MySQL. A co najważniejsze – jest to możliwe zdalnie bez przesyłania np. 10GB pliku SQL do wgrania do MySQL..

Powodzenia !

grafika:hipwallpaper.com