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
