[linux] MySQL zalohy

[warezman]

Nebude to ale sposobovat vacssiu zataz?

2009/5/18 Martin Simovic <msimovic na concurrent-thinking.com>:
> 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
> _______________________________________________
> https://lists.linux.sk/mailman/listinfo/linux
> Meta FAQ: http://www.sklug.sk/lists/linux/metafaq.html
>