[linux] MySQL zalohy

[Martin Simovic]

Potom moze pre teba byt riesenie master-slave replikacia. a na slave
mozes zbehnut mysqldump ako casto budes chciet bez toho aby si ovplyvnil
vykon alebo dostupnost databazy na master.
M.

On Mon, 2009-05-18 at 15:35 +0200, warezman wrote:
> Neviem, ja by som to chcel zalohovat bez akehokolvek vypadku a kazdu
> db zvlast. Preto pouzitie lvm neni pre mna moc priatelne.
> 
> Dňa 18. máj 2009 9:25, Marek Podmaka <marki na marki-online.net> napísal/a:
> > Cau,
> >
> > Chcelo by to hlavne konzistentnu databazu. To sa najlepsie urobi
> > pomocou SQL prikazu "FLUSH TABLES WITH READ LOCK". Potom vytvorit
> > snapshot filesystemu, potom "UNLOCK TABLES" a databaza je opat
> > pristupna aj na zapis.
> >
> > Inac ja zatial nemam problem ani so samotnym mysqldump (celkova
> > velkost /var/lib/mysql asi 4 GB), ale nepouzivam transakcie a pustam
> > osobitny mysqldump pre kazdu databazu (aby som mohol lahsie
> > restorovat).
> >
> > Viac napr. na:
> > http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/
> >
> > Sunday, May 17, 2009, 19:47:35, 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.
> >
> >
> >
> > --
> >  bYE, Marki
> >
> > _______________________________________________
> > 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



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