Re: How to quickly detect if there are any crashed tables
On Thu, May 10, 2012 at 10:26 PM, Steven Staples sstap...@mnsi.net wrote: I think you can scan the syslog for the mysql daemon, and it will show you any crashed, or problematic tables? If this is in fact the case, you could try that, and then run though the tables to check them later? Indeed, I was thinking about scanning the log file as a last resort, but I'm thinking how can the daemon report that some tables are crashed and need repairing at startup so fast? Shouldn't it be possible to apply the same method after the daemon has started via an external command or a query? -- Fita Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to quickly detect if there are any crashed tables
Much faster: SHOW TABLE STATUS -- It will have NULLs for the tables that really need REPAIR. (Those that were not properly closed don't have to be REPAIRed.) If you system is new enough, you can find the list of databases (TABLE_SCHEMA) from `information_schema`. In the long run, consider switching to InnoDB; it is self-repairing. -Original Message- From: Adrian Fita [mailto:adrian.f...@gmail.com] Sent: Thursday, May 10, 2012 11:40 AM To: mysql@lists.mysql.com Subject: How to quickly detect if there are any crashed tables Hi. I have several hundreds of databases with MyISAM tables in a server and after a power outage, I have to manually repair them with mysqlcheck. Sometimes I forget and it's not nice. So I am thinking of making a Nagios plugin to check if there are any crashed tables that need repairing. I tried using mysqlcheck --all-databases --quick --check --check-only- changed --fast, but it still took around 2h to run. I also tried using myisamchk with find /var/lib/mysql -name '*.MYI' -exec myisamchk --silent --fast '{}' \;, but still, after 15 minutes, it hasn't finished. I am looking for a solution that will give me an answer in at least 3-4 minutes. I apreciate any sugestions you might have. Thanks, -- Fita Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: How to quickly detect if there are any crashed tables
You can enable check/recovery automatically by using myisam_recover. Look it up in the documentation. There is no way to repair them faster, though. On May 10, 2012, at 2:40 PM, Adrian Fita wrote: Hi. I have several hundreds of databases with MyISAM tables in a server and after a power outage, I have to manually repair them with mysqlcheck. Sometimes I forget and it's not nice. So I am thinking of making a Nagios plugin to check if there are any crashed tables that need repairing. I tried using mysqlcheck --all-databases --quick --check --check-only-changed --fast, but it still took around 2h to run. I also tried using myisamchk with find /var/lib/mysql -name '*.MYI' -exec myisamchk --silent --fast '{}' \;, but still, after 15 minutes, it hasn't finished. I am looking for a solution that will give me an answer in at least 3-4 minutes. I apreciate any sugestions you might have. Thanks, -- Fita Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to quickly detect if there are any crashed tables
On 10/05/12 21:51, Mihail Manolov wrote: You can enable check/recovery automatically by using myisam_recover. Look it up in the documentation. There is no way to repair them faster, though. Thanks for the quick response. This definetly looks like a useable solution. Do you know if during the auto-repair at startup, the server with all the tables will be available and answering to queries? Or will it make the tables available as it progresses with the repair? -- Fita Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to quickly detect if there are any crashed tables
AFAIK the tables will be locked one by one until checked/repaired. On May 10, 2012, at 3:07 PM, Adrian Fita wrote: On 10/05/12 21:51, Mihail Manolov wrote: You can enable check/recovery automatically by using myisam_recover. Look it up in the documentation. There is no way to repair them faster, though. Thanks for the quick response. This definetly looks like a useable solution. Do you know if during the auto-repair at startup, the server with all the tables will be available and answering to queries? Or will it make the tables available as it progresses with the repair? -- Fita Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to quickly detect if there are any crashed tables
AFAIK the tables will be locked one by one until checked/repaired. On May 10, 2012, at 3:07 PM, Adrian Fita wrote: On 10/05/12 21:51, Mihail Manolov wrote: You can enable check/recovery automatically by using myisam_recover. Look it up in the documentation. There is no way to repair them faster, though. Thanks for the quick response. This definetly looks like a useable solution. Do you know if during the auto-repair at startup, the server with all the tables will be available and answering to queries? Or will it make the tables available as it progresses with the repair? -- Fita Adrian I think you can scan the syslog for the mysql daemon, and it will show you any crashed, or problematic tables? If this is in fact the case, you could try that, and then run though the tables to check them later? Just pushing out a thought... Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql