Backing up Zabbix MySQL DB

[runner]

Hello Welkin,

maybe an idea for those having very big databases.

I using LVM Snapshot for backing up my database. Taking a snapshot of my db (about 10GB) takes me about 15 sec to get an consistant shot.

http://tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html

This perl script is fine getting consistant snapshots of mysql.

http://lenz.homelinux.org/mylvmbackup/

When snapshot is done u can use any backupprogramm u prefer or simply copy the whole database.

Regards
Karsten

P.S. I using Innodb with the option
innodb_file_per_table
otherwise your db will never become smaller when you cleanup history. Onlyway to shrink db (is not using this option) is to dump db and reload it.

[welkin]

thanks for the idea but for using a lvm snapshot i'll have to use a lvm volume at least for the db partition right? right now i'm not using lvm but i haven't found a pleasent way to backup our database so i think i'll try this although a multiple master setup is still the preferred way. Does anyone know if this is possible with postgre sql?

regards welkin

[bee]

gap on graph

Hi,
When backup running, (using automysqlbackup) there is gap on graph.


is it normal? ZABBIX seem not processing data during backup running.

Thanks,
BEE

[ngarnier]

Use the "--single-transaction" of mysqldump, this avoids to lock the table for writing data.