[linux] MySQL zalohy

[Lubomir Host]

Zapis tabuliek na disk vyriesis v mysql cez FLUSH TABLES WITH READ LOCK.
Nasledne spravis snapshot.

Ja mam na to tiez skript, ktory to ale nezalohuje ako subory, ale
vydumpuje kazdu schemu zvlast. Vyhodu to ma taku, ze mozes tento backup
spristupnit vlastnikovi schemy, naimportovat inde standardnou cestou
atd. ...

Skript, ktory mam to vsetko spravi automaticky.

rajo

On Sun, May 17, 2009 at 08:13:26PM +0200, Ing. Ján ONDREJ wrote:
> Je to lepsie ako nic, ale v uplne konzistentnom stave nemusi byt.
> Existuju totis rozne cache, napr. samotnej databazy alebo diskove, takze by
> si v skripte mal aspon na cas vytvorenia snapshotu (zvycajne <1s) stopnut
> mysqld, napr.:
>   service mysqld stop
>   lvcreate -s ...
>   service mysqld start
> 
> Sice budes mat minimalny vypadok, ale konzistentnost udajov stoji za to.
> 
> Ak by si to robit nechcel, tak este mas moznost urobit aspon "sync" tesne
> pred samotnym lvcreate -s, ten bude vhodny aj ked mysqld stopnes.
> 
> Ja som skor uvazoval nad 100% konzistentnou DB s uplne nulovym vypadkom a to
> tym, ze urobim nie len snapshot diskovej particie, ale aj pamate virtualneho
> pocitaca. Musis mat ale virtualne nie len diskove oddiely, ale aj samotny
> pocitac.
> 
> 		SAL
> 
> On Sun, May 17, 2009 at 06:47:35PM +0100, Martin Simovic wrote:
> > 
> > 
> > On Sun, 2009-05-17 at 03:46 +0200, warezman wrote:
> > > Cafte,
> > > 
> > > na MySQL zalohy pouzivam mysqldump nasledovne:
> > > 
> > > mysqldump -R -q –single-transaction --user=root --password=pass
> > > database db | gzip >  file.gz
> > > 
> > > Akurat, ze na serveroch s novym jadrom mi po spusteni príkazu apache
> > > ide velmi pomaly [nacitanie fora 20s], na starsom debiane to fungovalo
> > > v poriadku. Problem by vyriesilo nastavenie priority mysqldumpu, ale
> > > neviem ako na to. Dalsie riesenie je kopirovat mysql priamo z
> > > /var/lib/mysql ale neni to najlepsie riesenie.
> > > 
> > 
> > mozes aj priamo kopirovat /var/lib/mysql ale len ak si schopny najprv
> > spravit snapshot filesystemu. ja mam napr. vsetko na lvm a riesim to
> > skriptom
> > 
> > #!/bin/bash
> > /bin/echo -n "Generating a snapshot of runnig database at " ; date +%H:%
> > M:%S
> > /sbin/lvcreate -s -n snap -L 1G system/var || exit 1 
> > /bin/echo -n "Mounting frozen database partition on /backup " ; date +%
> > H:%M:%S
> > /bin/mount -v -t ext3 /dev/mapper/system-snap /backup
> > /bin/echo -n "Cleaning up destination directory /database " ; date +%H:%
> > M:%S
> > /bin/rm -rf /database/*
> > /bin/echo -n "Copying new database files to /database " ; date +%H:%M:%S
> > /bin/cp -a /backup/lib/mysql /database
> > /bin/echo -n "Unmounting a database snapshot " ; date +%H:%M:%S
> > /bin/umount -v /backup
> > /bin/echo -n "Deleting database snapshot " ; date +%H:%M:%S
> > /sbin/lvremove -f system/snap
> > /bin/echo -n "Done " ; date +%H:%M:%S
> > 
> > toto mi bezi z cronu kazdu noc. nasledne je robeny backup celeho
> > filesystemu, keby som potreboval restornut databazu tak ju najdem
> > konzistentnu v /database directory.
> > 
> > 
> > > S pozdravom
> > > _______________________________________________
> > > https://lists.linux.sk/mailman/listinfo/linux
> > > Meta FAQ: http://www.sklug.sk/lists/linux/metafaq.html
> > 
> > 
> > 
> > This e-mail message may contain confidential and/or privileged information. If you are not an addressee or otherwise authorized to receive this message, you should not use, copy, disclose or take any action based on this e-mail or any information contained in the message.
> > If you have received this material in error, please advise the sender immediately by reply e-mail and delete this message. Thank you.
> > Allinea Software and Streamline Computing are trading divisions of Concurrent Thinking Limited: Registered in England and Wales No: 03913912
> > Registered Address: The Innovation Centre, Warwick Technology Park, Gallows Hill, Warwick, CV34 6UW, United Kingdom
> > _______________________________________________
> > https://lists.linux.sk/mailman/listinfo/linux
> > Meta FAQ: http://www.sklug.sk/lists/linux/metafaq.html
> _______________________________________________
> https://lists.linux.sk/mailman/listinfo/linux
> Meta FAQ: http://www.sklug.sk/lists/linux/metafaq.html
-- 
  ,''`.  Lubomir Host 'rajo' <rajo AT platon.sk>    ICQ #:  257322664
 : :' :  Jabber: rajo AT jabber.platon.sk      VoIP: callto://rajo207
 `. `'   WWW: http://rajo.platon.sk/  Platon Group: http://platon.sk/
   `-    GnuPG key: DC0C C7EA 55C8 B089 C41D 944A F251 A93A 2361 A82F