Good God, MySQL Data File is 75GB, how to fix?

[arretx]

I initiated a backup of my /appdata folder on my Unraid server, which is where all of the files associated with ZoneMinder reside. The only files that are not in this location are the actual video files which are stored on a share.

I noticed that the ZoneMinder backup folder grew to 107GB, which didn't seem right, and I suspect it's been slowly growing over time as I haven't noticed it until now.

In my /mnt/user/appdata/Zoneminder/mysql folder, I see three offending files:

1. ib_logfile0 = 5GB+
2. ib_logfile1 = 5GB+
3. ibdata1 = 75GB

Given the size of the database, I don't know how to begin to tinker with this so I don't break it. ZM is running based upon this: https://github.com/dlandon/zoneminder.unraid

Does anyone know what steps I need to take to resolve this issue?

I currently have 4 cameras all passing through with no jpg's being saved.

[Magic919]

It’s just a general DB thing and not ZM specific. A quick Google will present some options. Dumping the DB, deleting the files and then restoring will be one way.

[arretx]

This sounds nuclear. What is making it so large?

[Magic919]

Database usage and database configuration.

[mikb]

This sounds nuclear. What is making it so large?

Nuclear is "delete the database entirely"

Dumping out the db and remaking it would re-pack all the wanted data sensibly, and free up any trapped bloat in there from millions of insert/remove operations the db has gone through.

When my database unexpectedly grew and filled /var without warning. I did this (from command line):-

Code: Select all

mysql -uroot -p yourdatabasepasswordhere
use zm
optimize table Logs;  # Or, if that doesn't work
truncate table Logs;
quit

This got it back to sensible sizes -- optimize seems to free up space that has got trapped (a bit like a defrag). truncate is harsh, and drops all lines in the table, leaving it empty. In the case of logs, I didn't mind losing that data to get control back ... however it might be a bit drastic on other tables!