Results 1 to 10 of 10
  1. #1

    Automatically repairing crashed tables using cron

    Sometimes some sites don't work just because some table has crashed and at night times I can't get up to run "table [tbl] repair" command.

    I've a dozen databases with maybe 300 tables.

    Will it be feasible to run this command " mysqlcheck --repair --all-databases" using cron once or twice in my VPS(OpenVZ) account which has Cpanel?

  2. #2
    Join Date
    Oct 2009
    Location
    United States
    Posts
    2,612
    My recommendation would to find why MySQL tables crash in the first place and prevent them from crashing again. You can setup that cron job to run repair on all of the databases on the server for the time being. But you should find out why MySQL tables crash.

    *Just don't run the cron job for MySQL repair when backups are running in progress, as it may crash your server.

    If you have a managed server, contact your hosting provider and have them check your MySQL server. You may be running out of memory or your MySQL may not be properly configured/optimized.
    Snoork Hosting - Enterprise Servers | DDoS Protected Network
    99.9% Network Uptime | 15 Minute Ticket Response Time | 24/7 Live Chat
    Check Out Our Dedicated Server Specials For Amazing Discounts & Promotions

  3. #3
    Join Date
    May 2013
    Location
    India
    Posts
    748
    How nay tables are crashing? I have seen some websites with specific tables ( like one storing session inf ) crashing very quickly. A possible solution is to use InnoDB for such tables and that will let you to overcome the frequent crashing. Also once we have written a script for one website, to check the website contents locally and if it detects the table error, it will auto-repair the tables. We used it until our client could switch crashing table to InnoDB.

  4. #4
    Yes it's the sessions table for Mybb and Joomla too. Not sure if I simply switch the engine of sessions tables to innodb ... they will run fine.

    What command did you use to detect table error?

  5. #5
    Join Date
    Oct 2009
    Location
    United States
    Posts
    2,612
    Quote Originally Posted by rag_gupta View Post
    Yes it's the sessions table for Mybb and Joomla too. Not sure if I simply switch the engine of sessions tables to innodb ... they will run fine.

    What command did you use to detect table error?
    InnoDB is great, but keep in mind that if InnoDB database crashes it can become corrupted and you may not be able to repair it. So make sure you backup constantly.

    Converting Tables from MyISAM to InnoDB - http://dev.mysql.com/doc/refman/5.6/...to-innodb.html

    *If you are not familiar with this, I would advise hiring someone to do this for you.
    Snoork Hosting - Enterprise Servers | DDoS Protected Network
    99.9% Network Uptime | 15 Minute Ticket Response Time | 24/7 Live Chat
    Check Out Our Dedicated Server Specials For Amazing Discounts & Promotions

  6. #6
    Join Date
    May 2013
    Location
    India
    Posts
    748
    We used check_http nagios plugin along with "-s" switch to check the string in website. That helped us to identify whether the website is crashed or now. Please contact your developer to change the table to InnoDB. Though it is fairly easy to switch, I don't want to break anything in your website without knowing the actual stuff; your developer is the best person who can do it. Yes, InnoDB doesn't support manual repairing. But it is highly recommended for frequent write tables and there is very less chance for it to get corrupted comparing to MyISAM table.
    Last edited by nixtree; 01-16-2014 at 02:15 AM.

  7. #7
    I've run this simple command and it has worked:

    alter table mybb_sessions engine = InnoDB;
    So Snoork, I guess it won't crash any more?

  8. #8
    Quote Originally Posted by nixtree View Post
    ..... n who can do it. Yes, InnoDB doesn't support manual repairing. But it is highly recommended for frequent write tables and there is is very chance for it to get corrupted comparing to MyISAM table.
    Nixtree you've missed an important word near "very". Is it less or high?

  9. #9
    Join Date
    Oct 2009
    Location
    United States
    Posts
    2,612
    Quote Originally Posted by rag_gupta View Post
    I've run this simple command and it has worked:



    So Snoork, I guess it won't crash any more?
    Well, we don't know what is causing the databases to crash. You need to find the source and to do that you first need to start with MySQL logs. Review the logs, see what you can find, run MySQL Tuner on your server, optimize MySQL, monitor the server and MySQL memory consumption, and adjust the config until you get the perfect setup.

    InnoDB outperforms MyISAM under heavy load, but I haven't recommended it, so I can't tell you whether it will solve the problem or not. My first step would be to identify what is causing the MySQL database crashes before doing any work on the databases.
    Snoork Hosting - Enterprise Servers | DDoS Protected Network
    99.9% Network Uptime | 15 Minute Ticket Response Time | 24/7 Live Chat
    Check Out Our Dedicated Server Specials For Amazing Discounts & Promotions

  10. #10
    Join Date
    May 2013
    Location
    India
    Posts
    748
    Quote Originally Posted by rag_gupta View Post
    Nixtree you've missed an important word near "very". Is it less or high?
    Sorry, it's a typo..corrected it

    And I too support SnoorkHosting's suggestion. Make sure your MySQL is properly configured / tweaked. Good luck!

Similar Threads

  1. Server crashed out of memory in cron
    By psalm91 in forum Hosting Security and Technology
    Replies: 1
    Last Post: 10-01-2013, 12:25 AM
  2. How to automatically check and repair mysql databases and tables?
    By smartcard in forum Hosting Security and Technology
    Replies: 9
    Last Post: 07-30-2009, 08:09 AM
  3. How to automatically replace a crashed server
    By webdesign jr in forum Hosting Security and Technology
    Replies: 3
    Last Post: 09-12-2007, 11:13 PM
  4. cron job for optimizing, repairing ... mysql tables??
    By snakey in forum Hosting Security and Technology
    Replies: 0
    Last Post: 02-08-2005, 10:05 AM
  5. Repairing MySQL databases via cron jobs
    By dftchris in forum Programming Discussion
    Replies: 14
    Last Post: 07-05-2003, 04:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •