I think I have tied the issue down to an innodb problem. We should be using innodb but has automatically disabled for some reason. We had db corruption issues previously.
One thing we did do at this stage was delete files from the innodb log folder.
From the sql error logs coming up to the outage and corrupt DB I could see the following error’s in the SQL error log;
090323 18:52:52090323 18:52:52 [ERROR] Cannot find table FRESH_Fronter81_Demo/LP_FrameTbl from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
See
http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
how you can resolve the problem.
After the outage when we rolled back to the mornings db backup we saw;
100212 10:56:01 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './changelog_db/searchindex_queue_tbl.frm'
Although I’m not 100% sure this makes me believe we may have some kind of corruption of the innodb files, or the .frm files within the database.
I have noticed after the outage the innodb engine has been automatically disabled. I assume that this is causing the massive server utilisation we are seeing at sporadic times as we have no engine running.
I think we need to re-establish the .frm – innodb file relationship to re-enable the innodb engine, but I’m not sure how we can do this. I have checked the my.cnf file and it looks as though the Innodb directories are all pointing to the right location.
However I am no MySQL expert by any means and really would need some help to resolve this issue.
One other thing I did notice which may also be helpful is within the innodb_data_home_dir referenced in my.cnf (/var/lib/mysql/innodb) the last time any of the innodb files have been updated or created was on the 15th January, on the day of the database failure.
-rw-rw---- 1 mysql mysql 0 Jan 15 11:07 innodb_status.9998