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ć.
Zawartość artykułu
METODA #1 – mamy dostęp do konsoli Linux
Zrzut bazy do pliku SQL
Tutaj sprawa jest prosta. Po prostu wykonujemy zrzut bazy danych:
|
1 |
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 WordPresswp_
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_
|
1 |
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:
|
1 |
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:
|
1 |
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:
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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:
|
1 |
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:
|
1 |
ALTER TABLE |
Przykład:
Czyli od miejsca:
|
1 |
ALTER TABLE `ps_access` |
– do końca pliku.
Uwaga! Musimy pominąć komendę
|
1 |
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
