[linux] MySQL zalohy

[Marek Podmaka]

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