mysql database details

[vadi]

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!"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20071225/c603b459/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