cPanel MySQL 5.5 Server keeps crashing (lots of errors + warnings)

Question

I have a dedicated server (not a VPS/VM) running x64 Centos 6.5 Final + cPanel. The machine has 16 GB memory. I run a game server on this server (which uses MySQL as the back-end for data store). I also run my website and forums on this machine.

Recently, I started to get lots of error regarding not enough resources to run the game server, so I used the following https://tools.percona.com/ to generate a new my.cnf (optimized for my server).

Since then, the resource related warnings have gone, but the MySQL Server keeps crashing quite frequently now.

This is my /etc/my.cnf so far:

[mysqld]
innodb_file_per_table=1
open_files_limit=3576

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 100

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 8G

Here is my mysql-error.log : http://pastebin.com/Qp8hMywg

And my server.domain.com.err : http://pastebin.com/1KzaRMRV

---

Update

I ran MySQL Tuner perl script and this is the output: http://pastebin.com/7zxbTKa7

---

Any idea what could be causing it to crash frequently?

Thanks in advance for any help / tips.

Answer 1

I don't know much about the MySQL issue but it seems that Percona is aware of this issue.

The reasons for the current incompatibilities are:

• Percona Server with XtraDB contains features that have been backported from MyQL 5.5. Some of the MySQL 5.5 features added new
  error codes.
• Some Percona Server with XtraDB features have added new error codes.

Answer 2

Your db seems corrupted; I'd recommend following the steps in the link in the error message; http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html, but first things first, back up the db files!

Start by setting

[mysqld]
innodb_force_recovery = 1

try restarting the db. You should be able to dump out the tables to a new clean db. If that doesnt work set it to

[mysqld]
innodb_force_recovery = 2

And restart again. MySQL doesnt reccomend going higher than 3 on production db, due to potential additional corruption.

The other thing to look at is if you have recently moved the DB, did you make sure to move the innodb log files too?