Replikace databáze MySQL

7. 11. 2012 | David Watzke | Návody | 10367×

V tomto článku si popíšeme master-slave replikaci databáze MySQL – co to vlastně je, jak to probíhá, jaké jsou výhody a nevýhody a jak něco takového nakonfigurovat.

Obsah

• Co to je master-slave replikace?
• K čemu je to dobré?
• Konfigurace asynchronní master-slave replikace
• Další možnosti

Co to je master-slave replikace?

Jde o způsob jakým udržovat stejný obsah databáze na více DB serverech naráz, přestože se SQL příkazy provádějí pouze na jednom ze serverů.

Replikace probíhá tak, že hlavní DB server (master) si ukládá tzv. transakční logy (informace o tom, jaké příkazy provádí), které pak (zpravidla po síti) pomocí replikačního protokolu postupně zasílá vedlejšímu DB serveru (slave – těchto může být i více) a tento pak vykonává stejné příkazy jako master.

Slave může být provozován na pomalejším hardware než master a potom je možné (pravděpodobné), že bude minimálně ve špičkách docházet ke zpoždění – replikace nebude probíhat v reálném čase a slave dožene mastera až v klidnějším období, kdy se databáze nemění tak rychle. To je jeden z důvodů proč jsou potřeba transakční logy – jinak by master tyto informace mohl posílat slavům ihned. Další důvod je ten, že replikace nemusí probíhat pořád a lze ji přerušit a navázat kdykoliv, pokud máme transakční logy z časového období od přerušení replikace až po současnost a známe aktuální pozici v logu, od které musíme replikaci navázat.

K čemu je to dobré?

Replikační slave lze využít pro účely zálohování databáze, a to zejména v případě, kdy je hlavní DB server příliš vytížený nebo je na něm velká databáze, kterou si nemůžeme dovolit zálohovat pomocí mysqldump – zamklo by to tabulky na nepřijatelně dlouhou dobu. V takovém případě lze dump spustit na některém ze slave serverů a jediné k čemu může dojít je, že se zpozdí replikace.

Dále, pokud vypadne master DB server, může jej slave okamžitě zastoupit. Ovšem může dojít k tomu, že ve slave DB budou chybět některé poslední změny.

Pokud nám nezáleží na replikačním zpoždění, tedy smíříme-li se s tím, že změny se na slave serverech projeví se zpožděním, tak lze replikaci využít i pro load-balancing, ale je potřeba mít na paměti, že slave DB servery lze v takové konfiguraci používat pouze pro čtení, protože zápis (do replikované tabulky) by způsobil nekonzistenci s masterem a replikace by se dříve či později rozpadla. Je-li toto omezení nepřijatelné, pak je potřeba využít multi-master replikace, kterou se v tomto článku zabývat nebudu.

Konfigurace asynchronní master-slave replikace

Master server je potřeba nastavit tak, aby ukládal transakční (binární) logy. Toto jsou soubory na souborovém systému, ve kterých jsou uloženy informace o změnách v DB, které byly provedeny. Samozřejmě se zaznamenávají pouze příkazy, které mění data (INSERT, UPDATE, ...). Do hlavního konfiguračního souboru my.cnf přidáme:

server-id        = 1
log_bin          = /var/log/mysql/mysql-bin.log
max_binlog_size  = 100M
expire_logs_days = 10
binlog_format    = MIXED

Volba server-id je ID serveru – slave servery budou pokračovat v číslování od 2 dále. Pomocí log_bin určíme, kam se mají transakční logy ukládat, dále max_binlog_size stanoví jak velké soubory mají vznikat (kdyby se měl soubor zvětšit nad tuto hodnotu, vytvoří se další), pak nastavíme kolik dnů zpátky chceme mít logy k dispozici (expire_logs_days) a nakonec binlog_format určuje formát binárních logů*. Je potřeba zkontrolovat volbu bind-address – pokud DB server naslouchá pouze na lokálním síťovém rozhraní, nedostane se k ní vzdálený slave.

O formátech binárních logů a jejich výhodách a nevýhodách by se dal napsat samostatný článek. Zde pouze zmíním, že máte tři základní možnosti: 1) STATEMENT, kdy se do logů budou ukládat přímo SQL příkazy, 2) ROW, kdy se budou ukládat přímo změněné řádky anebo jako v ukázce 3) MIXED – to nejlepší z obou světů, kdy se používá standardně STATEMENT a kde to není bezpečné, tam ROW.

Dále je (na master serveru) potřeba vytvořit uživatele s právem replikace, tedy pod správcovským účtem v MySQL spustit:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replslave'@'adresa_slave' IDENTIFIED BY 'slozite_heslo';

Slave server je potřeba nastavit tak, aby replikoval.

server-id             = 2
relay-log             = relay-bin
relay-log-index       = relay-bin.index
relay-log-space-limit = 5G

Volby relay-log* určují nastavení logů obsahujících data z transakčních logů od master serveru (názvy souborů, jejichž umístění je relativní k datadiru a maximální velikost relay logu).

Když máme toto připravené, je potřeba zkopírovat adresář s daty MySQL (datadir – obvykle /var/lib/mysql), ale musíme si předem uložit pozici v binárním logu, abychom replikaci mohli od tohoto bodu na slave serveru navázat. Na master DB serveru tedy spustíme:

mysql> FLUSH TABLES WITH READ LOCK;   -- zamkne celou databázi
mysql> SHOW MASTER STATUS;            -- tento výstup je potřeba si uložit
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.045325 | 47975493 |              |                  |
+------------------+----------+--------------+------------------+

A klienta MySQL nebudeme zavírat (jinak by se zámek zrušil) – zejména pokud toto provádíme vzdáleně přes SSH, je vhodné použít nástroj screen. Nyní zkopírujeme celý datadir na slave server (vypneme slave DB a můžeme kopírovat rovnou na správné místo) a ihned poté můžeme klienta na master serveru ukončit, čímž se databáze opět odemkne.

master # rsync -av --progress --delete /var/lib/mysql/ slave:/var/lib/mysql/

Alternativou tohoto kopírování za běhu je vytvoření dumpu pomocí mysqldump, ale opět nesmíme zapomenout na to, že budeme potřebovat znát pozici v binárním logu v době dumpu.

Nyní, poté co jsme překopírovali datadir na slave, je vše připraveno pro to, abychom replikaci spustili na slave serveru. Spustíme zde tedy DB server a v MySQL spustíme pod správcovským účtem následující:

mysql> CHANGE MASTER TO MASTER_HOST='adresa_master',
        MASTER_PORT=3306, MASTER_USER='replslave',MASTER_PASSWORD='slozite_heslo',
        MASTER_LOG_FILE='mysql-bin.045325', MASTER_LOG_POS=47975493; -- všimněte si, že jsem použil údaje z výše vypsaného MASTER STATUS
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G

Ve výpisu posledního příkazu musí být toto:

Slave_IO_Running:  Yes
Slave_SQL_Running: Yes

Pokud I/O či SQL vlákno neběží, tak došlo k chybě, která bude vysvětlená ve sloupci Last_Error.

Proces (od kopírování datadiru z master serveru) můžeme opakovat kdykoliv dojde k nezotavitelné chybě na slave serveru nebo když chceme přidat další slave server.

Další možnosti

Předvedl jsem nejjednodušší variantu, kdy se replikuje celá databáze, ale lze docílit i toho, aby se replikovaly pouze vybrané databáze či tabulky a dokonce se i každá z nich může replikovat na jiný slave server. Toho lze docílit tak, že na slave serveru v my.cnf nastavíme:

replicate-wild-do-table = nazev_databaze.%

Použité „žolíkové“ (wildcard) znaky fungují stejně jako v SQL výrazu LIKE a například abc%.def% by vybralo ze všech databází začínajících na „abc“ tabulky s názvy začínajícími na „def“.

Když dojde při replikaci na slave k chybě, která se vám nezdá kritická a chcete ji přeskočit, lze toho docílit pomocí:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Proměnná SQL_SLAVE_SKIP_COUNTER určuje počet chyb, které se mají jednorázově přeskočit.

Je ovšem možné nastavit přeskakování určitých chyb pokaždé. Například chyba „duplicate entry“ (při vkládání stejného řádku podruhé) má kód 1062 (pro všechny kódy chyb viz dokumentaci) a přeskakování tohoto typu chyb lze nastavit tak, že na slave přidáme do my.cnf následující:

slave-skip-errors = 1062

Přejeme-li si přeskakovat více druhů chyb, pak kódy oddělujeme čárkou.

Přeskakování chyb ale nenastavujte, pokud k tomu nemáte dobrý důvod anebo pokud dobře nerozumíte tomu, proč k chybám dochází. Snadno tak může dojít k nekonzistenci dat, kdy na slave serveru budete mít něco jiného, než má master.

       

Komentáře

[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.