Safely Delete Old MySQL Database

[mjalki1]

Zabbix 6.2.1
Ubuntu 18.04.6 LTS

Inherited Zabbix from my predecessor and now running into the issue of /var: Disk space is critically low (used > 90%) on the Zabbix server. My knowledge of Zabbix and Linux is limited (I know enough to be dangerous) so bear with me.

Disk usage of the 1.2TB partition is now at about 100%, I modified the Housekeeping values from 365d to 90d and it barely made a dent. I'm aware from reading the manuals and other forum posts that it could take many housekeeping cycles before it cleans up to the parameters that have been set. Now here's the kicker: when I started poking around in /var to see what was taking up so much space I saw that /var/zabbix/ has both /mysql and /mysql-old directories. /var/zabbix/mysql/zabbix is only taking up about 15GB and is actively being written to but /var/zabbix/mysql-old/zabbix is taking up about 120GB and the entries were last modified one year ago.

My question is: is it safe to remove that old database, how do I safely go about doing that and will it free up that storage afterwards? If I can provide any additional information let me know and I'll do my best. Thank you in advance.

[cyber]

Looks like someone did some mysql related works there once and created a copy of DB... if it is not used, then you can just delete it...
IIRC mysql will not release space even, if you run your housekeeper and remove data... those files will still be large. I think there was something about need to copy data to new tables and switch them and then drop old ones etc... That's why there is partitioning etc. automatically dropping old data by deleting files...

[tim.mooney]

cyber is correct, when you delete lots of rows from a mysql database, it doesn't automatically shrink the corresponding on-disk file. Instead, it marks that section of the file as "available for re-use", so if you add some new rows, it doesn't immediately need to grow the file, it can use the space it already allocated on disk.

When tables have grown very large on disk but lots of data has been removed from them, you can use the "OPTIMIZE TABLE <tablename>" command to force mysql/MariaDB to reclaim free space within a table. This takes a while and temporarily requires even more disk space (it optimizes it to a new on-disk file, only once its complete does it switch the new table into place and remove the old file), so be sure you have free space in that filesystem equal to about 120% of the size of the table you're trying to shrink.

Note that the OPTIMIZE TABLE clean-up procedure only works when your mysql install is using innodb_file_per_table, but that is the recommended (required) setting for Zabbix, so that should be the case. If innodb_file_per_table was not set when your install was created, you don't get separate files for each database table, everything is stored in one massive file.

Regarding the old data files that likely aren't being used, I would make sure the 'lsof' command is installed, and while mysqld/mariadb is running, use lsof (as root) on the /var/zabbix/mysql-old/zabbix directory and the files within. If nothing has them open, then your guess is likely correct: it's an old copy of the data that your predecessor made and didn't clean up.

Example:

[root@monitor mysql]# lsof /var/lib/mysql/zabbix/*.ibd
COMMAND  PID  USER   FD   TYPE DEVICE   SIZE/OFF    NODE NAME
mysqld  2195 mysql   47u   REG  253,0     114688 4456662 /var/lib/mysql/zabbix/sessions.ibd
mysqld  2195 mysql   49u   REG  253,0     114688 4456452 /var/lib/mysql/zabbix/users.ibd
mysqld  2195 mysql   50u   REG  253,0     163840 4456454 /var/lib/mysql/zabbix/maintenances.ibd
mysqld  2195 mysql   51u   REG  253,0     114688 4456458 /var/lib/mysql/zabbix/hstgrp.ibd
mysqld  2195 mysql   52u   REG  253,0     114688 4456492 /var/lib/mysql/zabbix/valuemaps.ibd

etc. That example shows that mysqld process ID 2195 has all of those files (and more, which I've not show) open.

If you run that in your orphaned directory, you should not get any output.