All:
I created this script to analyze the tables on a periodic basis. It
accounts for changes in the schema by using a database table called SysTable
in each db to be analyzed.
Create table SysTable
TableName varchar(255) not null
);
We run this once per week now using a CRON job.
A quick overview:
1. Empty SysTable if it was previously used.
2. Get a list of tables in a file
3. Load those into SysTable
4. Create the analyze statements
5. Run them and place the result in a log file.
if [ ! -d /dba] ; then
mkdir /dba
fi
mysql db1name -e 'truncate SysTable'
mysql db1name -s -e 'show tables' > /dba/tables_db1name.txt
mysql db1name -e 'load data infile "/dba/tables_db1name.txt" into table
SysTable'
mysql db1name -s -e 'select concat("analyze table db1name.", TableName,";")
from SysTable' > /dba/analyze_db1name.sql
mysql db2name -e 'truncate SysTable'
mysql db2name -s -e 'show tables' > /dba/tables_db2name.txt
mysql db2name -e 'load data infile "/dba/tables_db2name.txt" into table
SysTable'
mysql db2name -s -e 'select concat("analyze table db2name.", TableName,";")
from SysTable' > /dba/analyze_db2name.sql
cat analyze_*.sql > analyze.sql
mysql db1name < analyze.sql > analyze.log
rm /dba/tables_*.txt
rm /dba/analyze*.sql
Of course, this can be done with filenames in unix as well using ls, cut and
cat on the .frms in the data directory. However, the side-effect of having
a table of table names in each data base can be quite desirable. Consider
that you can add the check table command to this script and
periodically/automatically check the tables too.
Hope some of you out there find this useful.
Best Regards,
Boyd E. Hemphill
MySQL Certified Professional
[EMAIL PROTECTED]
Triand, Inc.
www.triand.com
O: (512) 248-2287
M: (713) 252-4688
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
