Dominic Baines 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?
>
> Can anyone advise?
>
> Dom
On Linux you could try these two options:
$ mysql -u root -p -e "SHOW TABLE STATUS FROM <dbname>" | awk '{print
$1, $2, $5, $6, $8, $12, $13}' | column -t
$ mysql -u root -p -e "SHOW TABLE STATUS FROM <dbname>\G" | egrep
"Name:|Engine:|Rows:|Avg_row_length:|Max_data_length:|Create_time:|Update_time:"
The first option will create a nice formatted table. Here is an example
showing a few columns:
$ mysql -u root -p -e "SHOW TABLE STATUS FROM world" | awk '{print $1,
$2, $5, $6, $12}' | column -t
Name Engine Rows Avg_row_length Create_time
City MyISAM 4079 67 2008-01-14
Country MyISAM 239 261 2008-01-14
CountryLanguage MyISAM 984 39 2008-01-14
Werner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
