So after battling this for a while, I think I finally have it fixed, and would like to share the solution.
I did break mysql from cpanel to see if that would fix it. (did a dump and a copy of the /var/lib/mysql folder first)
http://blog.cpanel.net/replacing-mysql-with-percona-in-11-36/
It made it 2 glorious minutes before crashing. No long think it's a mysql problem directly.
Tried dumping each database one by one, dropping, deleting the directory, create, import - same problem.
At this point I've almost ruled out hardware just based on the fact it's the same error over and over (the SMART alerts I get are for age but nothing bad, also did the best I could testing the memory without rebooting the box).
This is what seemed to have done the trick.
added to my.cnf
innodb_force_recovery= 4
restarted mysql in read-only, then dumped the database
mysqldump --all-databases --add-drop-table > dumpfile.sql
(I also made another copy of /var/lib/mysql just in case)
got a list of all of the databases into a file
mysql -e "show databases" > /tmp/dbnames
Edited out information_schema, performance_schema and mysql from /tmp/dbnames
dumped just the mysql table (for safety concerns)
mysqldump mysql > mysql.sql
Now, if I was going to do this over again (and I hope I don't have to), I would have excluded the mysql table from the -all-db dump (ran into innodb_table_status tablespace exists, but didn't exist, but wouldn't let me create because it exists, but wouldn't let me alter it because it didn't loop).
Quick loop through the list and dropped the databases (in Bash):
for F in `cat /tmp/dbnames`; do echo Dropping $F; mysql -e "drop database $F"; echo $F Dropped; done
Found a bunch of databases that had errant table names, errors like these:
ERROR 1051 (42S02) at line 1: Unknown table
So after it got done I saw that there were still more databases to be had, I removed the directories for the orphaned tables. I stopped mysql, moved the ib* files to a different directory and restarted mysql. Came up nice and clean, recreated the files by itself, imported the dump file and it got stuck on the mysql tables tablespace. Deleted all of the lines for that table from my dumpfile and was able to get everything back in.
Kept having issues with the mysql.innodb* tables, so I was able to delete the ibd files (I couldn't alter, discard, import, delete, create, show create or anything with them) and recreate them using these queries I found:
CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
Has been running without issue for 9+ hours now, which is the longest it's gone without failure in a few weeks.
Hope this helps someone.
Chris