Automated Backups Prolem

[jbennett]

Recently I noticed some warnings about the size of some of my backups.

I have had my thresholds set to warn at 21500 kbytes and to go critical at 21000 kbytes for the /store/backups/mysql/daily/nagios/ and /store/backups/mysql/weekly/nagios/ backups.

I started to get some notices that the backups were less than 0kb recently. When I manually check the size of the backups, I see that they are around 420kb.

When I check previous weeks, I see that the backups are anywhere from 50M to 200M+ depending on the week (been adding and removing checks to try and help the load on the server).

Last week, I came into a machine that had been updated with patching and had crashed. I ended up having to follow the 'Repairing the Database' document found here.

In the process, I found that I needed to truncate the logentries and the notifications tables. I've had to do this in the past as these tables grow absolutely HUGE considering the number of checks we are running.

My question is in regards to why our MySQL backups are no longer even remotely close in size to previous backups? Can this be attributed directly to the clearing of those two tables?

Everything seems to be running fine otherwise.

[abrist]

My question is in regards to why our MySQL backups are no longer even remotely close in size to previous backups? Can this be attributed directly to the clearing of those two tables?

Maybe , as those tables are the likely culprits of large backups. How big were the tables before you truncated them? (you may want to decompress a previous backup to check)

[jbennett]

I realize this may be a simple question, but how to I decompress it just to check the size without restoring it?

[lmiltchev]

If you just need to check the size of an archive, you can run something like:

Code: Select all

tar -tvf xxx.tar.gz

or

Code: Select all

gzip -lrv xxx.sql.gz

[jbennett]

I can tell the size of the archive file from previous months just by looking at the size of the .gz file on the disk. I need to know the size of a specific set of tables without restoring it.

[abrist]

You can use "lz" from the "mtools" package to list the contents of a tar.gz.

Code: Select all

yum install mtools -y
lz <archive>

[jbennett]

That doesn't work for a .sql.gz package, unfortunately. See below:

Code: Select all

# lz nagios-week.42.2013-10-19_07h00m.sql.gz

Reading directory of "nagios-week.42.2013-10-19_07h00m.sql.gz".
tar: This does not look like a tar archive
tar: Skipping to next header
tar: Exiting with failure status due to previous errors

[abrist]

Apologies, I thought you were trying to peek into a tar.gz. For just gz:

Code: Select all

zcat -lv <archive>

[jbennett]

But that still doesn't give me specific table sizes?

I'm trying to compare two tables from this backup to the most recent backup to determine if that's the reason for my backups being substantially smaller now.

[abrist]

I guess the real issue here is that the d/w/m backups are just dumps of the necessary sql queries to rebuild the db. You would have to restore the sql to a db to see their actual sizes. If you had a backup of the MYI/MYDs we could check those sizes, but as of now, I am unaware of a utility that could approximate the table usage from the .sql file. My apologies.