Nagy méretű MySQL napi mentése

nakivo backup

veeam

zfs snapshot

(Off: mintha Oracle esetén lenne valami 'írást felfüggeszteni, menteni akarok' funkció... BS2000-ben használtuk, a COPY parancsot a FILE LINK=DMCOPY11,SHARUPD=WEAK opcióval kombinálva ilyenkor le lehetett másolni az adatbázis DBF fájlját.

Szerk: ilyesmi

ALTER TABLESPACE name BEGIN BACKUP;
<mentés>
ALTER TABLESPACE name END BACKUP;
)

Sávszélesség függvénye:

ssh -n keresztűl menne egyből a dump a mentőszerverre.

Esetleg gzip -pel csomagolni közben ha kicsi a sávszél.

Master-Slave online replikációval mennek az adatok egy másik szerverre.

Ott akár leállíthatod a szervert majd mented fáj alapon,
vagy LOCK a táblára és mented a dump-ot. Majd UNLOCK és folytatódik a replikáció ahol abbamaradt.

Ez a megoldás nem terheli a "rendes" SQL szervert mentéskor. A master-hez nem nyulsz mentéssel közvetlenül.

Percona XtraBackup esetleg?

"és van olyan DB, ami egy bitet nem változott az elmúlt 1 napban."

rmezei gyalogmegoldas, hogy van egy teljes dumpod, es csak az azota megvaltozott tablakat mented.

tegyuk fel 25 -en volt mentes, akkor ez megmondja mi valtozott azota :

SELECT table_schema,table_name FROM information_schema.tables WHERE (ENGINE = 'MyISAM' AND update_time > '2021-03-25') OR ENGINE = 'InnoDB';

szepen vegigmesz a listan, szetbontod db / tablara es kidumpolod.

mondjuk ez az innodbre sajna nemmukodik, de azt nemmodtad, mi az engine :D

Making Incremental Backups by Enabling the Binary Log

https://dev.mysql.com/doc/refman/8.0/en/backup-methods.html

Ha nem naponta keletkezik 200GB új adat elég a különbséget menteni rendszeresen. 

Az LVM snapshotnál nincs garancia a konzisztens SQL mentésre. Értem, hogy sokaknál "eddig működött" és nekem is van ilyen plusz backupon, de nincsenek illuzióim, hogy mennyire lesz jó. Az rsync még rosszabb lehet, mert nemhogy "félig" kiírt file-okat vihet át, de még az induláskori állapothoz képest nem lesznek a táblák egységesek.

Ekkora méretnél nemcsak a backupnál, hanem az adatmennyiségen is próbálnék faragtatni. Lehet ez a mezők típusának optimalizálása, archiv adatok egyéb táblákba mozgatása, sőt esetleg teljes archiválása. Ettől mindíg felsikítanak, hogy "dehát ezt nem lehet, minden szuperfontos". Ekkor kell felvázolni, hogy semmi gond, akkor kell egy erős szerver (SSD az SQL-nek és vmi nagydiszkes dolog a tárolásnak) amire megy a replikáció és azon lehet mentést készíteni, akár ahogy fentebb javasolták, hogy leállított replikációval. Ekkora file méretnél már a backup tömörítése sem opció így a diszkgyártók is boldogok lesznek. :) Majd kell valamilyen offsite/offline megoldás egy további példánynak. Ettől a pénzügyes résznek szoktak keresztbeállni a szemei, és majd lefocizzák... :)

Az érdekes az lesz, hogy ennyi adatról hogy csináltok visszaállítást, meg tesztet róla? Legalább 1x, amikor megvan a logika mögötte, akkor azért kellene valami teszt szerintem, meg utána sem árt valszin. Már csak azért is tesztelni kéne, hogy pl. adatbázis optimalizációra rá lehessen mondani, hogy igen, N perccel kisebb lesz a downtime, hogy ha beüt a cucc, és ezzel legyenek tisztában.

A tipikus megoldás: binlog (archivelog az Oracle-nél, gondolom kb. minden hasonló tartalmaz ilyet) bekapcsolása.

Ilyenkor bátran lehet menteni akár snapshottal is.

Visszaállításkor természetesen inkonzisztens lesz, de a binlog/archivelog/stb-ből el tudja tekerni a DB-t egy konzisztens állapotig.

Ezeket a kósza emlékek alapján írom, érdemes azért utánaolvasni, de csodák nincsenek.

Igen, a másik lehetőség, hogy a replikált szervert menteni, ez olyankor szoktuk, amikor eleve terhelt a DB, és nem szeretné, ha kínoznánk a sok-sok archivelog mentéssel.

Ha kevés a delta, akkor bőven lehet megoldás a heti egy dump (ha az belefér), és hétközben a deltákat menteni.

A másik tipikus kérdés ami ilyenkor fel szokott merülni, hogy oké, de milyen gyorsan kell visszaállni mondjuk ha kidől a storage? Mert az is keresztülhúzhat pár számítást, ha kiderül, hogy van rá két perc.

innodb_flush_log_at_trx_commit

Controls the durability/speed trade-off for commits. Set to 0 (write and flush redo log to disk only once per second), 1 (flush to disk at each commit), 2 (write to log at commit but flush to disk only once per second) or 3 (flush to disk at prepare and at commit, slower and usually redundant).

1 and 3 guarantees that after a crash, committed transactions will not be lost and will be consistent with the binlog and other transactional engines. 2 can get inconsistent and lose transactions if there is a power failure or kernel crash but not if mysqld crashes. 0 has no guarantees in case of crash. 0 and 2 can be faster than 1 or 3.

És ezt beállítod mentés (bocs, snapshot:) előtt:

SET GLOBAL innodb_flush_log_at_trx_commit=1;

írták fent a lvm snapshotot, zfs snapshotot, percona xtrabackup-ot...

én a zfs snapshotot ajánlom, használjuk nagyobb db-re (700-800GB tábla is van).

szerintem a lvm meg az xtrabackup elég IO igényes...

a zfs pedig megdefragmentálódik, ez azt jelenti, hogy hdd-n egy idő után nagyon be tud lassulni.

de ssd-n megy :)

úgyhogy  ¯\_(ツ)_/¯

Eleg sokminden szoba kerult itt, megprobalok nem csak "lattam en mar karon varjut" alapon valaszolni. Felmerult az LVM snapshot, zfs snapshot, es az xtrabackup. Ahhoz, hogy lehessen tudni, hogy mi a kulonbseg, azt kell tudni, hogy hogy mukodik az iras MySQL + InnoDB eseten. Hosszu hozzaszolas kovetkezik :). Ez meg mindig eleg leegyszerusitett dolog lesz.

Beerkezik egy iras egy tablara (tegyuk fel, hogy az egyszeruseg kedveert ez egy UPDATE, ami egy int field-et allit 4-rol 6-ra), a tablan van PRIMARY KEY es egy secondary index. Azt is tegyuk fel hogy ez autocommit modban tortent, tehat rogton a COMMIT muvelet is megtortenik. Ekkor eloszor a valtoztatasok bekerulnek az innodb log bufferbe, ahonnan bekerulnek az innodb transaction logjaiba (mas neven, mas engine-ben redo log, ib_logfile innodb-nel). Ami bekerul ide az egy ugynevezett redo log record. Ebben van egy LSN (log sequence number), change type (milyen valtoza tortenik), page number (melyik page-en tortenik a valtozas), es change on page, tehat maga a valtozas. Ezt ugy kell elkepzelni, hogy pl a 123-mas page-en a 3456. byte-tol megvaltozik 4 byte erre a 4 byte-ra. Azt, hogy a log bufferbol hogy kerulnek adatok a redo logba, az innodb_flush_log_at_trx_commit hatarozza meg. MySQL esetben az 1 jelenti azt, hogy minden commitnal kiirja, es fsync-et is hiv. Ahogy banyek mondja, ezt sokan atallitjak, ilyenkor a MySQL nem teljesiti a D azaz durability kritertiumot az ACID kriteriumok kozul, de az iras sokkal gyorsabb lesz, ha nem minden COMMIT eseten, csak pl masodpercenkent kerulnek a valtozasok az ib_logfile-ba.

Ott tartunk, hogy iras utan megvan a redo log rekord. A redo log (vagy transaction log) viszont fix meretu, elobb-utobb ki kell irni belole a valtoztatasokat a vegleges helyre. Az InnoDB alapesetben 16K-s page-eken tarolja a rekordokat. A redo log irason kivul szinkron muvelet meg az, hogyha nincsenek ezek a page-ek a buffer pool-ban (ez egy in-process page cache InnoDB-nel), akkor behuzza oket (ez szinkron olvasas lesz). Ha mar ott vannak, akkor megvaltoztatja a tartalmukat a redo log rekordnak megfeleloen, es innentol kezdve memoriabol szolgalja ki a MySQL az adatokat, ezt jol irta valaki. Mivel van egy primary key es egy secondary index, ezert a valtoztatas 1 vagy 2 page-et fog erinteni attol fuggoen, hogy az adott field benne van e a secondary indexben. Az egyszeruseg kedveert valtozzon csak 1 page.

Az adat ekkor biztonsagban van: egy crash esetben megvan az eredeti page, es hozza megvan a redo log rekord, amibol visszaallithato az uj adat. Ekkor a commit visszater, es a felhasznalo szamara szinkron resz befejezodik, az adott page a buffer pool-ban dirty lesz. A page-ek header-jeben ott van, hogy melyik LSN-re nezve konzitensek, ez alapjan a kesobbi valtoztatasokat a recovery folyamat ra fogja gorgetni a redo logokbol. Ez is bonyolultabb ennel, mert mas engine-ekhez hasonloan az innodb write ahead logging-ot hasznal, tehat a roll forward utan az undo logot is hasznalni kell roll back-re, de most egyszerusitsuk le.

Innentol kezdve amit irok az teljesen a hatterben tortenik, aszinrkon a felhasznaloi irasokhoz kepest. elobb vagy utobb, ki kell irni a dirty page-et disk-re. Ennek tobb oka lehet: lehet fogy a buffer pool, es kell a hely masnak, lehet hogy annyira a vegen van a redo logoknak, hogy nemsokara kirotalodik, es ki kell irni. Arrol, hogy ez a dontes mi alapjan szuletik meg, hogy egy page-et ki kell e irni vagy nem egy masik hasonloan hosszu vagy hosszabb hozzaszolast is tudnek irni. De, tegyuk fel, hogy a dontes megszuletett, es ki kell irni a page-et a diskre. Ez az ugynevezett checkpointing process vegzi (ezt a legtobb adatbazis engine-ben ugyanigy hivjak).

Mivel az InnoDB 16k-s page-ekkel dolgozik, 16K-t irni pedig nem atomi, ezert nem lehet csak ugy a vegleges helyere irni a page-et. Ekkor elofordulhatna az, hogy pl csak 8k van belole kiirva, es jon az aramszunet, ekkor a felig megirt valtozatbol es az eredetin levo valtoztatasokbol nem lehet az adatot visszaallitani. Ezert van a shared tablespace-ben egy doublewrite buffer nevu terulet, ami fixen 128 page. Amikor a checkpointing process irja az adatbazist, eloszor a doublewrite buffert fogja irni, utana pedig a vegleges filet.

Ekkor az adat ismet biztonsagban van:
- Ha a doublewrite buffer irasa alatt lesz aramszunet, az adat visszaallithato a redo log rekordbol es az eredeti page-bol. 
- Ha a page irasa kozben van aramszunet, akkor az uj page megvan a doublewrite bufferben.

Ha az iras sikeres, akkor a page allapota a buffer pool-ban clean lesz.

Ezert aramszunet esetben mindig megvan az adat, barhanybol barhanyszor. Ez hasonloan igaz a legtobb adatbazis, engine-re, ha valaki azt mondja magarol, hogy teljesiti az ACID-bol a D-t, arra ez igaz. Ami kulonbseg lehet a MySQL-hez kepest, hogy sok engine sokkal kesobb vezette be a partial page logging-ot. Regebbi engine-eknel a redo logokba teljes page-ek kerultek, latszik az int update-es peldabol hogy az miert nem hatekony. Kis kitero, hogy a postgres partial logging implementacioja pl kifejezetten tetszik, ok azt csinaljak, hogy a doublewrite buffer helyett szamon tartjak, hogy az adott page-bol van e full verzio a redo logban, es ha van, akkor csak a valtoztatasokat irjak ki.

Volt meg par dolog ami feljott, pl a binlogok. A binlogok mysql server szintjen vannak, tehat logikai valtoztatasokat tartalmaznak. Replikaciohoz, point in time recoveryhez lehet oket hasznalni, majdnem fuggetlenek a fenti mechanizmustol. Azert majdnem, mert ha a binlogokkal egyutt szeretnenk durabilityt, akkor a sync_binlog opciora is szukseg van. Ekkor a binlog irasa resze lesz az InnoDB tranzakcional (erre a MySQL belul XA-t es 2 phase commitot hasznal, hogy szinkronizalja a server es a storage engine szintu valtoztatasokat).

Volt kerdes, hogy akkor hogy lehet megis korrupt az adatbazis. Volt is olyan valasz, hogy ha jo a hardver, akkor nem lesz az, de nem mindig jo a hardver. Lattunk pl olyat, hogy a raid controller elkezdett hulyeseget irni a diskre, es azert crashelt a MySQL, meg a redo log rekord checksumja nem volt megfelelo. Ilyenkor segithet a force recovery, ami a legmagasabb szinten kb azt csinalja, hogy figyelmen kivul hagyja a redo logokat. Ennek a hasznalata vegso eset kell legyen, mert adatvesztessel jar, lehet olyan MySQL HA megoldast epiteni, ami nem veszit adatot full cluster leallas esetben sem (semisync replikacio, xtradb cluster vagy barmi galera alapu).
Olyat is lattunk mar, hogy InnoDB page compressiont hasznaltak, es egy zlib bug miatt a compressed page lett korrupt. Ha aramszunet miatt ilyen problema lep fel, az vagy hardver hiba, vagy bug valahol a stackben, semmikeppen nem normalis. Sokszor latom sajnos, hogy crash-eket nem vizsgalnak meg, nem ertik, hogy mi tortenik, hanem ratudnak hogy ez ilyen. Ilyenkor a debugolas (illetve annak a kepessegenek) a hianya a problema.

Szinten feljott a ZFS es a ZFS snapshotokbol mentes. A ZFS nagy elonye, ha MySQL-t futtat az ember rajta, hogy az IO atomi. A ZFS iraskor mindig egy uj verziojat irja a blokknak, es az iras vegen egy pointert atir atomi modon az uj verziora. Ezert olyan "olcsok" a zfs snapshotok, mert csak nem kell torolni a regi verziot (a ZFS snapshotoknak torleskor van koltseguk). Igy ZFS-en pl ki lehet kapcsolni a doublewrite buffert nyugodtan. Sokan egyebkent is kikapcsoljak, mert nem ertik, hogy mit csinal, es azt latjak, hogy gyorsabb tole az adatbazis, majd meglepodnek, hogy korrupt lesz.

Barmilyen snapshot alapu mentesnel semmit nem kell csinalni az adatbazissal, hogy a durability kriteriumot teljesitse. Amiert megis szoktak az az, hogy a mentessel konziszens binary log pozicio is meglegyen point in time recovery-hez. Ezt el lehet erni egy FLUSH TABLES WITH READ LOCK majd SHOW MASTER STATUS parancsokkal. Itt sokan azt hiszik, hogy a FLUSH TABLES WITH READ LOCK miatt nem irodik az adatbazis, ezert crash recovery sem kell majd, ha abbol a snapshotbol indit egy uj instance-ot az ember. Ez azonban nincs igy, hiszen az csak logikai szinten allitja meg az irasokat (nem jon be az UPDATE), a checkpointing a hatterben ugyanugy zajlik. Ha crash recovery mentes snapshotot szeretnenk azt is lehet, csak akkor FLUSH TABLES WITH READ LOCK alatt arrol is meg kell gyozodni, hogy nincs dirty page.

Az xtrabackuprol igertem meg, hogy irok (ha valaki eddig elolvasta, es erdekesnek talalta, irja be egy hozzaszolasba, hogy fapapucs, hogy tudjam, hogy erdemes volt ezt megirni :) ). Az xtrabackup elinditas utan az eloterben elkezdi streamelni a redo logokat. Tehat mielott barmit csinalna, elkezdi lementeni a valtoztatasokat, es meglesz neki mind. Ezutan ugy, hogy kozben az adatbazis mukodik, elkezdi lemasolni a page-eket. Tegyuk fel, hogy az xtrabackup reggel 7-tol 8-ig dolgozik. A mentes eleje a 7 orasi allapot lesz, a vege a 8 orasi, a kozepe meg valahol kozte. Ekkor jon a --apply-log lepes, ami pont a crash recovery folyamatot fogja vegigcsinalni az igy lementett page-eken. Tehat fogja az elso page-et, megnezi az LSN-t, es megnezi a streamelt redo logokban az azon a page-en levo valtoztatasokat, majd ezt megcsinalja az osszessel. A valosagban ez annyival bonyolultabb, hogy tobbet nez egyszerre, ezert ha tobb memoriat adunk az apply-log-nak jelentosen gyorsithato a folyamar. Igy az --apply-log utan a teljes visszaallitott adatbazis a 8 orasi allapotot fogja tukrozni. Ezert szeretik az emberek uj slave instance-ok beallitasahoz hasznalni, hiaba tart orakig a mentes, mivel az uj adatbazis allapota a mentes vegi allapot lesz, nem kell sokat "behozni" ahhoz, hogy a replika up to date legyen.

Ha snapshot alapon mentunk, akkor viszont a kezdeti allapotot. Szo volt az xtrabackup IO igenyerol, ez attol fugg, hogy el kell e "vinni" a mentest a geprol. Ha el kell, akkor az xtrabackup es a zfs send fogja a legkevesebb IO-t hasznalni, hiszen ott a masolason kivul minimalis plusz IO lesz (ZFS-nel a torles, xtrabackupnal pedig  redo log masolasa). Ha LVM snapshot van, ott van a snapshot masolasanak az IO-ja plusz ami a copy on write-bol jon. Amiert azt szoktak mondani, hogy az xtrabackup IO igenyes az az, hogy "sajnos" gyors, ezert ki tudja haszalni a meglevo IO kapacitast. Erre vannak beepitett throttling mechanizmusok is (hany io muvletetet hasznalhat), illetve ha streaming modban hasznalja az ember (--stream=xbstream, a legtobben igy hasznaljak), akkor pv-vel vagy throttle-lel is remekul lehet szabalyozni, hogy milyen gyorsan mentsen.

A topicnyitonak: az xtrabackup egy az egyben megoldas a problemadra. 

Csinalsz egy primary-secondary (regebben master-slave) replikaciot, majd a replikaciobol csinalsz dumpot. :) 

Én Arcserve UPD-t használok nagyméretű MySQL mentésére. A DB egy zabbix adatbázis SSD-n, gigabiten, napi 60GB körüli növekménnyel vagyis historyval amit trenddé számol át. A DB mérete MySQL szerint kb 900GB, de a lemezen sajnos több, évek óta megy tökéletesen és stabilan és nem csak a DB van mentve, hanem az egész rendszer így könnyű diasterre is felkészülni, mert BMR restore is lehetséges azonnal.

Egyetlen dolog amire figyelni kell, az a / vagy a /tmp szabad területének mennyisége,m ert amíg fut a backup, addig az összes írási művelet egy kernel drive segítségével egy ideiglenes állományba van írva ami ha megtölti a /-ot akkor törlődik és nem történik semmi, de a mentés megszakad. Az eljárás érdekes, nagyon stabil és biztos is.

http://lenzg.net/mylvmbackup/