The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Monitor MySQL Database Sizes

Discussion in 'General Discussion' started by darren.nolan, Sep 16, 2008.

  1. darren.nolan

    darren.nolan Well-Known Member

    Joined:
    Oct 4, 2007
    Messages:
    259
    Likes Received:
    0
    Trophy Points:
    16
    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 :)

    Code:
    #!/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.
     
  2. Webou

    Webou Member

    Joined:
    Jun 6, 2007
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Hello darren.nolan,

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

    It would be very useful!

    Thanks in advance,

    Michael
     
  3. ljesh

    ljesh Well-Known Member

    Joined:
    Aug 2, 2008
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    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 :)
     
  4. darren.nolan

    darren.nolan Well-Known Member

    Joined:
    Oct 4, 2007
    Messages:
    259
    Likes Received:
    0
    Trophy Points:
    16
    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.
     
  5. Webou

    Webou Member

    Joined:
    Jun 6, 2007
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Nice, I got it working, thanks :)
     
  6. darren.nolan

    darren.nolan Well-Known Member

    Joined:
    Oct 4, 2007
    Messages:
    259
    Likes Received:
    0
    Trophy Points:
    16
    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!
     
  7. xisn

    xisn Well-Known Member

    Joined:
    Dec 4, 2004
    Messages:
    128
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator

    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
     
  8. sneader

    sneader Well-Known Member

    Joined:
    Aug 21, 2003
    Messages:
    1,126
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    La Crosse, WI
    cPanel Access Level:
    Root Administrator
    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
     
  9. darren.nolan

    darren.nolan Well-Known Member

    Joined:
    Oct 4, 2007
    Messages:
    259
    Likes Received:
    0
    Trophy Points:
    16
    That's fine - so long as you don't like charge for it or anything.
    :D
     
Loading...

Share This Page