Database backup and partitioning

[nahueld]

Hi, i'm having some trouble backing up the database of my zabbix server, I'm trying to make an upgrade and before doing it i wanted to have everything backed up in case something goes wrong.

I'm currently running zabbix 5.0 and MySQL8.0, on a RockyLinux, the size of the database is almost 37GB, and i've been trying to make the bakcup using the "mysqldump" command to a remote server and after it finishes the result is a 12.5GB sql file, i do not find any error in mysql log (its empty).
I also tried making the backup from the workbench from my pc and it did the same, the result is a 12.5GB file, again without errors or anything.

command used -> "sudo mysqldump -u root -p --single-transaction zabbix > respaldo_mysql_zabbix.sql"
Here's the size of each column (dont know if could be important)


Another "problem" i saw, is that the database is growing too much, and i see the housekeeper process sometimes takes too much time, and after it seems that it doesnt realy delete information, so i read about partitioning the database.
Has anyone done it, or is there any "tutorial" because i couldnt find any

Thanks
Nahuel

[Markku]

My understanding is that the MySQL tables don't shrink when rows are deleted, so I think it is natural that the backup can be smaller than the database.

I believe one of the currently best articles about Zabbix database partitioning is in the Zabbix blog: https://blog.zabbix.com/partitioning...cedures/13531/

I recommend practicing the partitioning with a copy of the database first. Partitioning is quite simple at the end, just be careful and think ahead why something is done. Feel free to ask here if you encounter specific problems.

Markku

[nahueld]

My understanding is that the MySQL tables don't shrink when rows are deleted, so I think it is natural that the backup can be smaller than the database.

Ok, i'm going to look into this.

just a question, if MySQL doesnt shrink when deleting rows, is there a way to reduce the space used by the database ?

I believe one of the currently best articles about Zabbix database partitioning is in the Zabbix blog: https://blog.zabbix.com/partitioning...cedures/13531/

Thank you, this is exactly what i was looking for and couldnt find.

I recommend practicing the partitioning with a copy of the database first. Partitioning is quite simple at the end, just be careful and think ahead why something is done. Feel free to ask here if you encounter specific problems.

My first idea is to make the partitioning in a copy of the server and see if everything continues running, and then doing it in the real server.

Thanks

[Markku]

just a question, if MySQL doesnt shrink when deleting rows, is there a way to reduce the space used by the database ?

Yes there is, for example by exporting and importing the database. There may also be easier ways, search for compacting a database.

Markku