zabbix db backup

[n0mad44]

Hello,

I want to backup my zabbix database... so I've some questions ! I'm monitoring about 20 hosts and "tuned" update intervals of items. I'm keeping history for 365 days. My server is a dedicated zabbix server; cpu is a Xeon 3Ghz and ram is 1 Ghz


1. I want to do 2 kind of backup :
- config only (for daily dump)
- full db (config + trends/history) (for weekly dump)

Which tables need I to backup for "config only" [or which tables don't I need (-: ]?

2. Tables are using innodb (as recommended) and I want to do hot backup (without stoping mysql) : is mysqldump the "best" free tool to do it ?

3. What are the most options to use with mysqldump to quickly generate a dump. I tried a mysqldump today and it took 22minutes for an output file of 2Gb. (do you find it quick/normal/slow according to my configuration?)

4. Have you some tricks to optimize DB performance ?
I red some papers around and put theses options in my my.cnf :
innodb_buffer_pool_size=751M
innodb_log_file_size=187M
innodb_additional_mem_pool=8M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1

something bad ? something else ? maybe something to add to the crontab to reindex ?



Thanks for your help,
n0mad

[Petya]

1) For config only backup you have to backup everything except for:
- acknowledges
- events
- history*
- housekeeper
- sessions
- trends

(Have I missed something?)

2) && 3) `mysqldump --single-transcation --quick`

http://jeremy.zawodny.com/blog/archives/000690.html

Another option (which I haven't tried yet) is keeping DB files
on an LVM volume, make LVM snapshot -- here's binary online backup

If you succeed with this option -- tell me, I'm much interested.

4) mysql optimizations depend on your hardware (mostly RAM),
also database optimizations (their importance) depend on
items per second your server has to monitor .

To find how many items per second your server has to monitor
you can use this patch:

http://www.zabbix.com/forum/showthread.php?t=8714

[n0mad44]

Thanks for your reply, I will test mysqldump options soon (-: Thanks for the patch too....but how to apply it ?

In fact, I already tested LVM Snapshot and it worked fine...but I needed to stop my mysql daemon (don't remeber why).
It was a quick shutdown of the DB, but it doesn't was real hot backup !

To take the snapshot I used "lvcreate --snapshot --size 100 --name save /dev/vg/data" were "/dev/vg/data" was my db location. Then a simple mount (mount /dev/vg/save /mont/point) followed by a tar of the content of the mount point and you have a binary backup!

Before doing this, your script need to ensure /mount/point is unmounted and you have to remove older snapshot : lvremove -f /dev/vg/save

ciao

[Petya]

To apply patch:
1) put zabbix-1.4.4-items_per_second.patch file into directory with report1.php
2) run `patch -p 3 < zabbix-1.4.4-items_per_second.patch`

Do not forget to backup your php frontend files
before applying patch.

[alfiere]

Any Zabbix devel can confirm that for the 1.6 as well?


Cheers,
Alfredo

[alfiere]

This is an important aspect!

Please, give us an answer.
Furthermore I carefully suggest to insert few lines in the documentation.


Cheers,
Alfredo

[Tenzer]

Any Zabbix devel can confirm that for the 1.6 as well?

Confirm what? Which tables to leave out when doing a DB backup?

[alfiere]

Exactly.

Cheers,
Alf

[JoelG]

Tool to try

I am looking into trying http://www.maatkit.org/tools.html, specifically the parallel dump and restore.

On my system, a dump only takes about 20 minutes, but a restore now takes over 8 hours (you have tested a restore haven't you?).

I plan on dumping my 1.4.5 database for reconversion to 1.6 using these tools in the next week or so. I will update with the findings.

[bbrendon]

Google around the mysql restore performance topic.

You have to issues a few database commands for the restore to go faster.

I actually tried googling it and couldn't find it. I found it in our company wiki.
<rant>I hate how people blog about things that don't need blogging and their information is so generic and useless I want to DOS their server. </rant>
Anyway...

(
echo "SET AUTOCOMMIT=0;"
echo "SET FOREIGN_KEY_CHECKS=0;"
cat databasedump.sql
echo "SET FOREIGN_KEY_CHECKS=1;"
echo "COMMIT;"
echo "SET AUTOCOMMIT=1;"
) | mysql --user=username --password=password database