Cleanup zabbix database.

[Bambix]

Hello,

Our company runs a zabbix 1.8.6 server. We are looking to upgrade our zabbix version but first we want to shrink the database. The database is 340 GB and this is way to large...
I know our database is not setup with best practices like 1 innodb file per table and mysql table partitioning,...
But we first want to shrink the database.


witch querys can i run to cleanup our database?
Any help would be appreciated

[acropia]

Been there, done that

I've been in this situation recently and after hours/days of Googling I found out that:

1. You can not shrink with all data in ibdata files
2. You can not 'change' too innodb_file_per_table option

When you store all data in ibdata files there is now way to shrink a live database. My preferred method is:

1. Drop all history and trend data. You can backup this data too, but it would take me days too write all the data to a backup. And I can't have that much downtime on my monitoring platform.

2. Backup the remaining database. Because I first dropped all history and trend data, this backup is only a fraction of the original size (100 MB)

3. Then configure the database server with innodb_file_per_table. And recreate the database with the backup file.

[Bambix]

Is there a way to keep a part of the history?
Like last 30 days.

[Colttt]

take a loom at this, there are some scripts to delete old data..

https://github.com/burner1024/zabbix-sql

[Awolfie]

wrong thread