Useful DBA Unix for analyze (check) tables
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]
Re: Check tables?
Myisamchk returns an exit code. if myisamchk myfile.MYI ; then do something else do something else fi Mark wrote: >Hello, > >Is there a way I can do a "CHECK TABLES" which will return an exit code of >some sort, to use in a shell-script? Looking at the output of a myisamchk is >fun, but I rather have a utility too that will report an error-status that I >can use to decide whether to make a backup/restore, etc. > >Thanks > >- Mark > >P.S. Keeping the bot happy: mysql > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Check tables?
Hello, Is there a way I can do a "CHECK TABLES" which will return an exit code of some sort, to use in a shell-script? Looking at the output of a myisamchk is fun, but I rather have a utility too that will report an error-status that I can use to decide whether to make a backup/restore, etc. Thanks - Mark P.S. Keeping the bot happy: mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Commands out of sync error when check tables
hi, I execute two check command using C, it report "Command out of sync error". the original statement is as below: sprintf(ls_sql, "check table aa"); mysql_real_query(dbfd, ls_sql, strlen(ls_sql)); num = mysql_affected_rows(dbfd); sprintf(ls_sql, "check table bb"); mysql_real_query(dbfd, ls_sql, strlen(ls_sql)); num = mysql_affected_rows(dbfd); When it execute the second check sql, it report "out of sync error", What wrong with it? Thanks in advance. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Check tables, BUG or JUST OPTION?
I'm running mysql 3.23.32, I did the test: With a running server I forced a kind of problem, I delete the MYI file. #mv contaComum.MYI contaComum.MYI.bck Then I run CHECK TABLE contaComum; It returns OK. If I restard server or execute a flush tables it'll show the problem. Should CHECK TABLE execute an automatic FLUSH TABLES? --- Tenha uma conta de email Grátis no ACBusca! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB & Check tables
Hi! > "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter> Hello Michael, Peter> Friday, January 26, 2001, 12:20:43 AM, you wrote: MW> Hi! >>> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter> Hello monty, Peter> As I remember according to documentation MYSQL should support check Peter> table on BDB tables: Peter> `CHECK TABLE' only works on `MyISAM' and `BDB' tables. On `MyISAM' Peter> tables it's the same thing as running `myisamchk -m table_name' on the Peter> table. MW> I tried to implement this; I had got everything coded when I MW> noticed the on can only check BDB tables when there is no active MW> transaction and one is the only one that is using the table. This MW> is impossible to guarantee with the current MySQL code; To do this we MW> have to add a new layer on top on transactions, which isn't something MW> we plan to do until in 4.x MW> I have now removed the notes about BDB tables in the CHECK command. Peter> OK. But just note to really use BDB tables in production people need Peter> to have a possibility to recover data if BDB table somehow got Peter> dammaged. I know there is a chance to recover data by alter table or Peter> if this does not help to do an mysqldump and restore (well of couse Peter> this is worse then with MYISAM there repair table sometimes helps even Peter> then mysqldump does not work). To do the repair you of couse need to Peter> find out something is going wrong and it's better to find this before Peter> the errors will block normal execution. Peter> As far as I know there is no tools now to fix BDB tables (the one Peter> provided with BDB does not work with MYSQL tables) so it was nice Peter> option anyway - to start other mysqld and check the table :) And as Peter> far as I know this is safe anyway as check table does not change Peter> anything in BDB ? I agree that we need to look at this soon. It would be easy to do checking just when BDB is started, but it's a bit hard to do when BDB is in use. Peter> Also - does not lock tables write enshures there are no other active Peter> transactions ? Currently the LOCK TABLE only ensures that no one will be able to execute another query that updates the table. The problem is that we need to also implement a list of tables that has an active transaction on the table and not allow the lock table to succeed until these transactions are taken care of. It will take a couple of weeks until Tim will have time to look at this. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: BDB & Check tables
Hello Michael, Friday, January 26, 2001, 12:20:43 AM, you wrote: MW> Hi! >> "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter>> Hello monty, Peter>> As I remember according to documentation MYSQL should support check Peter>> table on BDB tables: Peter>> `CHECK TABLE' only works on `MyISAM' and `BDB' tables. On `MyISAM' Peter>> tables it's the same thing as running `myisamchk -m table_name' on the Peter>> table. MW> I tried to implement this; I had got everything coded when I MW> noticed the on can only check BDB tables when there is no active MW> transaction and one is the only one that is using the table. This MW> is impossible to guarantee with the current MySQL code; To do this we MW> have to add a new layer on top on transactions, which isn't something MW> we plan to do until in 4.x MW> I have now removed the notes about BDB tables in the CHECK command. OK. But just note to really use BDB tables in production people need to have a possibility to recover data if BDB table somehow got dammaged. I know there is a chance to recover data by alter table or if this does not help to do an mysqldump and restore (well of couse this is worse then with MYISAM there repair table sometimes helps even then mysqldump does not work). To do the repair you of couse need to find out something is going wrong and it's better to find this before the errors will block normal execution. As far as I know there is no tools now to fix BDB tables (the one provided with BDB does not work with MYSQL tables) so it was nice option anyway - to start other mysqld and check the table :) And as far as I know this is safe anyway as check table does not change anything in BDB ? Also - does not lock tables write enshures there are no other active transactions ? -- Best regards, Petermailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BDB & Check tables
Hi! > "Peter" == Peter Zaitsev <[EMAIL PROTECTED]> writes: Peter> Hello monty, Peter> As I remember according to documentation MYSQL should support check Peter> table on BDB tables: Peter> `CHECK TABLE' only works on `MyISAM' and `BDB' tables. On `MyISAM' Peter> tables it's the same thing as running `myisamchk -m table_name' on the Peter> table. I tried to implement this; I had got everything coded when I noticed the on can only check BDB tables when there is no active transaction and one is the only one that is using the table. This is impossible to guarantee with the current MySQL code; To do this we have to add a new layer on top on transactions, which isn't something we plan to do until in 4.x I have now removed the notes about BDB tables in the CHECK command. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BDB & Check tables
Hello monty, As I remember according to documentation MYSQL should support check table on BDB tables: `CHECK TABLE' only works on `MyISAM' and `BDB' tables. On `MyISAM' tables it's the same thing as running `myisamchk -m table_name' on the table. Therefore it does not: mysql> check table layers_la01.g00layers_stat_hits; +-+---+--++ | Table | Op| Msg_type | Msg_text | | +-+---+--++ | layers_la01.g00layers_stat_hits | check | error| The handler for the table |doesn't support check/repair | +-+---+--++ 1 row in set (0.00 sec) and this is also thrue for any table mysql> create table t (t int) type=BDB; Query OK, 0 rows affected (0.30 sec) mysql> check table t; ++---+--++ | Table | Op| Msg_type | Msg_text | ++---+--++ | test.t | check | error| The handler for the table doesn't support check/repair | ++---+--++ 1 row in set (0.01 sec) -- Best regards, Peter mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
