Tuesday, February 19, 2013

MySQL keeps crashing!! What to do?

MySQL crashes due to various reasons like hardware problem, unexpected system shutdown, power failure and many more. Before things get out from the hand, you should try to find out the reason of MySQL crash and fix it on time. You can try below steps when MySQL crashes.

  1. First check whether mysqld server is running or dies. If it dies then try to find out the reason by checking the server error log.
  2. It may happen that MySQL is crashing only on your system so try to find out the problems from your end.
  3. When crash happens, immediately stop the mysqld server using mysqladmin shutdown command and run myisamchk – silent -- force */* .MYI from the data directory. This command will check all myisam tables.
  4. Almost 95 % MySQL crash happens due to some specific queries. You can check which queries are killing server using mysqld query log.
  5. Test MySQL by running the test in mysql – test directory.
  6. Run fork_big.pl script.
  7. Use - - skip- external- locking to prevent external locking in mysqld server.
  8. When mysqld does not respond then run mysqladmin -u root processlist command. This command tells you about the status of all the current connections.
  9. Run mysqladmin -i 5 status to know the statistics of queries.
  10. Create a bug report and send it to MySQL official site.
  11. Check whether you are using latest patches for your OS or not.
  12. The crash could also happen due to defective hardware so try to troubleshoot it.

MySQL crash also happens due to corrupted tables & indexes. Following are the reasons for table corruption:If MySQL server is killed in the middle of update operation.

  • A bug in mysqld killed MySQL server.
  • Crashed data file.
  • A bug in data storage engine.
  • Data or index files have been accessed at the same time by some external program and mysqld at the same time.

You can try above things when MySQL crashes. If the reason behind crash is corrupted tables then you can repair the corrupt tables using MySQL repair commands or a 3rd party repair tool for MySQL.

No comments:

Post a Comment