[linux] MySQL zalohy

warezman warezman na warezman.info
Pondělí Květen 18 15:35:30 CEST 2009


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


Další informace o konferenci linux