Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Baron Schwartz (baronxaprb.com)
Date: Mon Jan 14 2008 - 10:14:54 CST
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
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql