mysql database details

vadi vadi.ksdba at gmail.com
Tue Jan 1 15:10:04 CET 2008


Hi All,

I want to attach queries output file to the nagios, so that I can download
it whenever I need it( It's difficult to display high output on the nagios
screen).

Can any one help me please?

Thanks,
Vadiraj


On Dec 25, 2007 10:02 AM, vadi <vadi.ksdba at gmail.com> wrote:

> Hello Gary / Nagios team,
>
> Thank you Gary Sir,
>
> I got the answer and it's correct:
>
> SELECT s.schema_name,CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(
> t.index_length))/1024/1024,2),0.00),"Mb")
> total_size,CONCAT(IFNULL(ROUND(((SUM( t.data_length)+SUM(t.index_length
> ))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")
> data_used,CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb")
> data_free,IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length ))-SUM(
> t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0)
> pct_used,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s
> LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schemaWHERE
> s.schema_name = "nagios_objects" GROUP BY s.schema_name ORDER BY pct_used
> DESC\G
>
> Thanks,
> Vadiraj
>
>
>
>
>
> On Dec 25, 2007 3:07 AM, Gary Every < gevery at gmail.com> wrote:
>
> > check out mysqlshow --status -k <db_name> from the command line, it''l
> > give you a bunch of info - the "rows" and "data_length" columns are what
> > you're looking for as far as sizes go.
> >
> > G.~
> >
> >
> >
> > On Dec 24, 2007 2:15 PM, vadi <vadi.ksdba at gmail.com> wrote:
> >
> > > Hi Gary / Nagios Team,
> > >
> > > Thanks for all your supports.
> > >
> > > Irrespective of the engine (InnoDB, MyISAM), I am not able to
> > > calculate table size and DB size
> > >
> > > Do you have any perl/bash script to do or please help me how to find
> > > this information?
> > >
> > > Regards,
> > > Vadiraj
> > >
> > >
> > > On Dec 23, 2007 9:00 PM, Gary Every <gevery at gmail.com> wrote:
> > >
> > > > You should be able to get all the information you need thusly:
> > > >
> > > > In your script grab all the databases:
> > > > $sql = "SHOW DATABASES";
> > > > // or
> > > >
> > > > // Count the return rows
> > > > // Iterate through them with info from the information_schema.tables
> > > > table:
> > > >
> > > > SELECT * from information_schema WHERE TABLE_SCHEMA = 'db_name';
> > > >
> > > > There are columns in there that will get you where you want to be :
> > > > Specifically the ENGINE column, which tells you what type engine
> > > > (innodb,myisam, etc) that table is
> > > >
> > > > information_schema is a great place to get info about all your
> > > > tables  - use it
> > > >
> > > >
> > > > You could probably get all the info you need using the following
> > > > SINGLE query:
> > > >
> > > > $sql = "select count(1), table_schema as Database_name, engine as
> > > > Engine_Type  from information_schema.tables where engine != 'MEMORY' group
> > > > by table_schema,engine";
> > > >
> > > > You'll get a return like this: (as you'll see the myisam and innodb
> > > > table types are split out for you already, as in the ocsweb db)
> > > >
> > > >
> > > > +----------+--------------------+-------------+
> > > > | count(1) | DB_Name            | Engine_Type |
> > > > +----------+--------------------+-------------+
> > > > |       58 | cacti              | MyISAM      |
> > > > |        2 | events             | InnoDB      |
> > > > |        4 | information_schema | MyISAM      |
> > > > |       15 | mysql              | MyISAM      |
> > > > |       24 | ocsweb             | InnoDB      |
> > > > |       12 | ocsweb             | MyISAM      |
> > > > +----------+--------------------+-------------+
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > On Dec 23, 2007 7:10 AM, vadi < vadi.ksdba at gmail.com> wrote:
> > > >
> > > > > Dear All,
> > > > >
> > > > > Can any one help me for writing one bash script to find below
> > > > > content.
> > > > > 1>    To find number of MySql database and there size.
> > > > > 2>    To find number of tables in there corresponding databases.
> > > > > 3>    To find number of InnoDB and MyISAM tables in each
> > > > > databases.
> > > > >
> > > > > I want to display this in my nagios tool.
> > > > >
> > > > > Thanks in advance.
> > > > >
> > > > > Regards,
> > > > > Vadiraj
> > > > >
> > > > >
> > > > > -------------------------------------------------------------------------
> > > > > This SF.net email is sponsored by: Microsoft
> > > > > Defy all challenges. Microsoft(R) Visual Studio 2005.
> > > > > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > > > > _______________________________________________
> > > > > Nagios-users mailing list
> > > > > Nagios-users at lists.sourceforge.net
> > > > > https://lists.sourceforge.net/lists/listinfo/nagios-users
> > > > > ::: Please include Nagios version, plugin version (-v) and OS when
> > > > > reporting any issue.
> > > > > ::: Messages without supporting info will risk being sent to
> > > > > /dev/null
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > Gary Every
> > > > "Pay it Forward!"
> > > >
> > >
> > >
> >
> >
> > --
> > Gary Every
> > "Pay it Forward!"
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20080101/a856fa1a/attachment.html>
-------------- next part --------------
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
-------------- next part --------------
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null


More information about the Users mailing list