From: Baron Schwartz (baronxaprb.com)
Date: Mon Jan 14 2008 - 10:14:54 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hi Dom,

On Jan 14, 2008 10:49 AM, Dominic Baines <dombainesyahoo.com> wrote:
> After a pretty long time I have returned to being a mysql DBA again after spending a lot of time with Oracle and MSSQL.
>
> I have several databases that I need to to work on, all various source builds from 4.1.16 to 4.0.20 and the average database contains some 200 or more tables. Some are clustered some are stand alone.
>
> There is an overall project to upgrade and consolidate some of these and to add in belt and braces redundancy and to add database backups.
>
> Most server installation are for just a single version, some installations have just 2 or 3 databases some have 20 or more.
>
> The first task I need to run is to determine the database table storage engines (all make use of MyISAM, InnoDB and Archive), then update frequency and row numbers.
>
> Now if I use:
>
> show table status from <database name>;
>
> It will list all the table information I need however, what I am trying to figure out is how to get access to the data this produces directly.
>
> The data I want is name, engine, rows, avg_row_length, max_data_length, create_time and update_time.
>
> I can do it manually but that is a bit mind numbing and leads to inaccuracy if this were Oracle I could query one of the V$ views and get this immediately. If this were version 5 I might use INFORMATION_SCHEMA.
>
> Obviously it has been far too long and I have forgotten completely.
>
> I do not want to go down the perl if I can help it. Is there a way to do this internally using sql I seem to remember there wasn't?

There's no way to do it from SQL, but try this, using a tool from Maatkit:

mk-find --printf '%D.%N %E %S %A %M %C %U\n'

Welcome back to MySQL :-) You can get Maatkit from
http://sourceforge.net/projects/maatkit/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]