Replikace databáze MySQL (diskuse)

[razor]

IMHO chybí zmínka o tom, že výše popsaná replikace je tzv. asynchroní a že od verzi 5.5 je k dispozici i tzv. semisynchroní replikace.

[David Watzke]

Díky za připomínku, upravil jsem podle toho nadpis "Konfigurace".

[Pavel Francírek]

Dále jsem nenašel zmínku o statement vs. row replikaci (ta je třeba u clusteru default). A jejich chování se v některých případech radikálně liší (operace nad celými tabulkami atp.).

[Luboš Doležel (Doli)]

Vždyť to tam je...

[lh]

rýchle nakonfigurovanie replikácie

[Kvakor]

K toimu load balancingu - u většiny webů je poměr čtení/zápis velké číslo, některé CMS klidně udělají okolo sta selectů jen na zobrazení jedíné stránky, ale zápis se vyskytuje jen pří změně dat (úprava obsahu, diskuze a spol), tedy pokud si web nedělá svojí interní statistiku.

Z toho důvodu je výhodné směrovat veškerá čtení z databáze přes load balancer na slave servery, zatímco na master server půjdou pouze zápisy. Pokud se tato funkcionalita vloží do nějaké databázové mezivrstvy, tak může všecho probíhat z pohledu programátora naprosto transparentně (tedy pokud nejsou požadavky na sobě vzájemně závislé).

Navíc může master server fungovat jako failover během komunikaci s databází - mezivstrva nemusí čekan na to, než balancer vybere jiný server, protože ví, že master běží (a pokud neběží, tak je nejspíš jednoduší ukázat uživatel nejakého veselého obluďáka s přetrženým kabelem a hlášením o poruše - uživatel se na pár sekund potěší veselým obrázkem a než dá reload, je zvolen nový master a vše zas funguje normálně).

[frEon]

Tohle imho funguje hezky tak dlouho, dokud nemas ty servery hodne vytizeny. Pak se ti muze stat, ze neco do master db ulozis, pocitas s tim, ze to tam je, sahnes si na nektery slave a ouha, ono to tam jeste neni.

[diverman]

některé CMS klidně udělají okolo sta selectů jen na zobrazení jedíné stránky

Tak to je hodne spatne, pokud se provadi nasledujici pseudokod:

for row in (SELECT foo FROM Foo):
   SELECT bar FROM Bar WHERE foo = row[0]

Jinymi slovy, kdyz pocet dotazu na stranku je primo umerny poctu vypisovanych polozek. Spatny framework/CMS/programator.

[trekker.dk]

některé CMS klidně udělají okolo sta selectů jen na zobrazení jedíné stránky

Dokonce jsem viděl i několikrát zopakovaný stejný select (při vyřizování jednoho požadavku)

[martin]

V clanku nikde neni zminka o zabezpeceni. Musim komunikaci master<--->slave tunelovat (ssh, stunnel), nebo uz je sama o sobe sifrovana a bez starosti ji muzu pustit do Internetu pres pul sveta?

[David Watzke]

Šifrované to není, dokud šifrování nepovolíš (či nepřikážeš), stejně jako všechna ostatní komunikace s MySQL. Více v tomto článku.

[lacik]

Budu to muset řešit výhledově, v blízké budoucnosti dostanu na starost webový projekt, ale protože nejsem programátor, neodpustím si dotaz: je možné řešit replikaci k předem stanovenému okamžiku třeba jednou za den nebo týden, nebo příkazem? A pokud k tomu replikace není vhodná, jakým jiným způsobem řešit můj problém?

Oč mi jde: mám databázi, která obsahuje odkaz na XML soubor s textem, někdy dost rozsáhlým. Program v php pak skládá web stránku ze záznamů v databázi a z onoho XML souboru přes XSLT. Pokud se přidává záznam do projektu, pak se musí vytvořit záznam v databázi, zeditovat k němu příslušný nově vzniklý soubor XML a upravit či doplnit v daších XML souborech odkazy. Teprve poté, po delší práci, jsou synchronní jak položky v databázi, tak i soubory XML, a chtěl bych to dávkově zpracovat (čili během jednoho okamžiku skriptem odeslat nové a změněné XML soubory na server a replikovat databázi). Právo na insert/update/delete a na editaci XML mají pouze admini v admin verzi db, návštěvníci webu mají pouze select do serverové verze db schovaný v php.

Předem díky za navedení na použitelné a funkční řešení.

[Jakub Lucký]

To nehledáte replikaci, ale nějaký deployment systém... Ale líp vám neporadím, s tím nepracuju...

[RapMan]

Diky za clanek, videl jsem hodne master - slave replikaci, i master-master.

Co vsak neni nikde dobre zdokumentovane je to, kdyz se master rozbije, provoz presunu na slave, jak pak nejlepe spravit master a opet na nej presunout provoz, aby doslo k co nejmensimu vypadku.

[http://blog.hostname.sk]

Mozno hladate mysql-proxy. Aplikacia sa potom nepripaja priamo do DB, ale na proxy. Presmerovanie sa potom vykona zmenou v nastaveniach mysql-proxy

[Ondra]

Z asi 2leté zkušenosti doporučím naučit se 2 věci. Většinou se replikace zasekne na nějaké chybě. Jedna možnost je tu zmíněnou operaci přeskočit: STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; Další možností je v konfiguraci nastavit, aby tyto události nevyvolávaly chybové stavy (teď z hlavy přesně nevím jak se ten parametr jmenuje).

Druhá možnost je použít mysqldbcompare, které vyhodí rozdíly mezi master a slave databází a vypíše i SQL příkazy, které se mají spustit, aby bylo vše stejné. Funguje to OK.

[RapMan]

skip counter je jasny, ale automaticky bych to neriskoval. Prenos dat z logu bezi, i kdyz SQL thread vykazuje chybu, ale chapu, kdyz nekdo ze slave potrebuje ihned ziskavat aktualni data, ze si to lajzne....

[Pev]

Možná by se Vám na to hodil flipper.

[Program]

Nelíbí se mi rsync živé db. Pokud proběhne nejdřív překopírování datových souborů a až pak logů, vyleze z toho více či méně nekonzistetní databáze. FLUSH TABLES WITH READ LOCK to nezachrání, protože nezamnkne interní procesy v DB.

Opravte mě, jestli se mýlím, ale tohle opravdu NEDOPORUČUJI UDĚLAT. Když už tak využít LVM snapshot, pak je to OK.

[lh]

Ake interne procesy v MySQL? Kde by som nasiel viacej info?

[Program]

Chce to znát trochu detaily MySQL, viz http://dev.mysql.com/doc/refman/5.5/en/. Nic méně nemyslím, že je dnes perspektivní nějak hodně zkoumat mysql, pokud člověk není nucený ho spravovat.

Nic méně, abych líp odpověděl:

1) Ukládání upravených stránek na disk -> Stránky jsou uloženy v trans. logách, takže když MySQL najednou celé spadne, tak je to ok, ale když db kopírujete za živa, tak se Vám data mění pod rukama. (Viz dirty pages v doc.)

2) Change buffer (odložený update sekundárních indexů) -> Dle dokumentace update probíhá při načtení dané stránky do buffer poolu, nikde ale není psáno, že musí čekat libovolně dlouho a nemůže si ji načíst sám (viz change buffering).

3) Undo buffer -> Pokud rdbm nestíhala odstraňovat data z undo bufferu, může to provést zrovna teď. To se týká celé správy MVCC architektury, která se dělá na pozadí.

Ničemu z toho read lock nezabrání, navíc pokud db má pár desítek GB, tak se bude kopírovat docela dlouho a celou dobu bude nepoužitelná. To už je lepší ji rovnou vypnout a kopírovat "za studena".

[rajo]

V dokumentacii sa pise:

FLUSH TABLES WITH READ LOCK

Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

Cize podla mna globalny read lock zarucuje, ze vsetky zmeny su zapisane v tabulkovych suboroch na disku a nie niekde v pamati alebo transakcnom logu. Ak by to tak nebolo, tak by takyto command bol uplne zbytocny a neexistovala by cesta, ako MySQL odbackupovat bez dlhej odstavky (ok, slo by to replikovanim na slave, ktory by sa mohol zastavit na potrebnu dobu zalohovania).

Ze sa v dokumentaci spomina snapshot FS je fajn, ale nie je nutne ho pouzit. Ak mam dostatok casu, kludne mozem data skopirovat rsyncom alebo aj cez F5 v mc. Kym nevytukam "UNLOCK TABLES", tak ziaden zapis na disk neprebehne a vsetky vlakna mysql co chcu zapisovat na disk cakaju na uvolnenie global read lock.

A par ludi adminovat MySQL servery musi, inac by firmy nemohli predavat sluzby webhostingu.

[Program]

To jste bohužel pochopil špatně. Ten postup je: FLUSH TABLES WITH READ LOCK; udělat FS/LVM snapshot; UNLOCK TABLES;

Možná by ten flush ani nebylo nutné dělat, ale byla by větší pravděpodobnost poškození DB.

O tom, že nemáte pravdu se přesvědšíte snadno. Stačí se podívat na SHOW ENGINE INNODB STATUS\G. V sekci BUFFER POOL AND MEMORY. Ideálně si to pustit v linuxovém watchi. Uvidíte, že "dirty pages" se budou postupně házet na disk.

[Jan33]

Diky za článek. Jako lehký úvod to je dobré. Jen mi hlavou vrta proč je nutné nejdřív zastavit master. Nešlo by jej donutit k vytvoření kompletního redo logu? Tedy aby se slave synchronizoval s prázdnou (myšleno s prázdnými tabulkami) databází? Přece když mi slave zdechne nebo potrebuji připojit další nebudu zastavovat mastra? Nebo to vážně nejde udělat bez zastavení a dumpu?

[David Watzke]

No, v praxi se často používá LVM snapshotů. Uděláš na masterovi FLUSH TABLES WITH READ LOCK, až proběhne (často jen pár sekund), tak vytvoříš snapshot (to je téměř okamžitě) a pak to můžeš zase odemknout. Pak připojíš snapshot, odkopíruješ data, odpojíš ho a zrušíš.

Kompletní redo log vytvořit podle mě nemůžeš z principu fungování replikace - jak on má zpětně vědět, jaké příkazy byly provedeny? A k získání příkazů pro vytvoření dané databáze (včetně obsahu, volitelně) slouží právě dump.

[Jan33]

Asi si to predtavuju jako Hurvínek válku. Nevím ale proč by do toho "inicializačního" redo logu nemohla db vysypat aktuální obsah tabulek. Kouzlo je v tom, že by se tak stalo automaticky, bez nutnosti zamykat celou databázi a zjišťovat nějaká čísla příkazů.

LVM to pravda zrychluje, když ho tedy máš, ale stejně mi prijde , že je v tom příliš (zbytečné) lidské práce a ryziko selhání je velké. Stačí se uklepnou v čísle a při jdeš o část dat a ani na to nemusíš (hned) přijít.

[Program]

Teoretických monžostí rozumného inicializačního syncu je víc. Problém je v tom, že to nikdo u InnoDB neudělal a vzhledem ke stavu celého projektu pravděpodobně v tomto století neudělá. Je hodně "jednoduchých" věcí, které prostě nikdo zatím neudělal.

[David Watzke]

Když si na to napíšeš skript, tak v tom lidská práce není žádná (Vyzkoušeno.)

[Pev]

Docela hezké video na youtube k tomuto tématu (začíná to replikacemi a ukazuje dalí nástroje): MySQL High Availability Solutions with Lenz Grimmer.