Monitor MySQL Database Sizes

[darren.nolan]

HOKAY!

So quotas for MySQL isn't going to happen tomorrow (/sad panda) so in the mean time, I've been checking sizes from time to time to see what people are using.

Very proud of my customers, no-one (thus far) has tried anything crazy like. But if you would like a report on your users, add this little script I wrote yesterday.

This may, or may not, be suitable for your current setup. If you have suggestions, I would love to hear them

#!/usr/bin/perl
chdir '/var/lib/mysql';
%checked;
@list = `ls -1F`;
foreach (@list) {
        next unless (/(.+)_(.+)\// && !$checked{$1});
        print "$1\t";
        $checked{$1} = 1;
        print `du -hc $1_* | tail -n 1`;
}

Add your script to a cron job so you get an email about the details, and set it for once a day, once a week, once a month - whatever you like :-)

I make no warranty for the accuracy on the generated report, if it breaks your system I will not be held responsible. If it does break your system, I suggest firstly calling your local poisons emergency centre, following by running around screaming your own name and begging for salvation.
PS. I highly doubt this could hurt your system in anyway. Just thought I'd add that for shits and giggles.

Outputs all current users and their database sizes (it groups them together for you), as I mentioned, set it up as a cron job and have cron email you the results.

[Webou]

Hello darren.nolan,

Could you explain more precisely how this script should be used ?

It would be very useful!

Thanks in advance,

Michael

[ljesh]

This is what I did
saved the script as mysqlsize
chmod 755 mysqlsize
and added in the crontab a line that runs this script every monday.
Maybe someone else knows other way

[darren.nolan]

What he said. :-)

I have that email me once a week so I can just quickly look through and see if there is any huge databases forming that I should concern myself with.

I stick my script in /root/scripts/check_mysql.sh - I don't change the chmod (I suppose that would depend on your preferences) - and then add the script to a weekly cron.

[Webou]

Nice, I got it working, thanks

[darren.nolan]

Excellent mate.

Now when I get some of that "free-time" my next goal is to take those values and combine with cPanel's cached diskspace - calculate what the total is and compare against the account's quota - if over - email with the details.

But that's for another day/week/month...

Hope you like it!

[xisn]

HOKAY!

So quotas for MySQL isn't going to happen tomorrow (/sad panda) so in the mean time, I've been checking sizes from time to time to see what people are using.

Very proud of my customers, no-one (thus far) has tried anything crazy like. But if you would like a report on your users, add this little script I wrote yesterday.

This may, or may not, be suitable for your current setup. If you have suggestions, I would love to hear them

#!/usr/bin/perl
chdir '/var/lib/mysql';
%checked;
@list = `ls -1F`;
foreach (@list) {
        next unless (/(.+)_(.+)\// && !$checked{$1});
        print "$1\t";
        $checked{$1} = 1;
        print `du -hc $1_* | tail -n 1`;
}

Add your script to a cron job so you get an email about the details, and set it for once a day, once a week, once a month - whatever you like :-)

I make no warranty for the accuracy on the generated report, if it breaks your system I will not be held responsible. If it does break your system, I suggest firstly calling your local poisons emergency centre, following by running around screaming your own name and begging for salvation.
PS. I highly doubt this could hurt your system in anyway. Just thought I'd add that for shits and giggles.

Outputs all current users and their database sizes (it groups them together for you), as I mentioned, set it up as a cron job and have cron email you the results.

This is pretty handy, so I thought I'd include it in my LogView tool

Hope you dont mind?

Link to the tool: http://forums.cpanel.net/showthread.php?t=57453

[sneader]

This is pretty handy, so I thought I'd include it in my LogView tool

Hope you dont mind?

Link to the tool: http://forums.cpanel.net/showthread.php?t=57453

Thank you for LogView, and thanks for (hopefully) adding this to it.

When do you think LogView will be available with the MySQL db size check? Or is it there now (and I need to upgrade)?

- Scott

[darren.nolan]

This is pretty handy, so I thought I'd include it in my LogView tool

Hope you dont mind?

Link to the tool: http://forums.cpanel.net/showthread.php?t=57453

That's fine - so long as you don't like charge for it or anything.