Mysql suddenly crashing, advice on how to recover

MySQL

I have a Mysql database running on Ubuntu that is suddenly falling over. I have tried cleaning the installation by removing:-

apt-get remove mysql-server 
apt-get remove mysql-client 
apt-get remove mysql-common

from the system and then reinstalling them with aptitude.

I have also reinstated all the files in /var/lib/mysql from a backup that I know was working for the database.

I can get onto the database and switch to the database where I suspect there is a corrupt file, but as soon as I do 'show tables', I get a crash:-

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    5
Current database: DBNAME

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR: 
Can't connect to the server

The last lines of the error log are displaying:-

120311 17:12:29 [ERROR] Column count of mysql.db is wrong. Expected 22, found 20. The table is probably corrupted
120311 17:12:29 [ERROR] mysql.user has no `Event_priv` column at position 29
120311 17:12:29 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
120311 17:12:29 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

I was hoping that a fresh install would eradicate the 'Column count of mysql.db is wrong' error, but it doesn't seem to have done so.

Is anyone able to suggest a way to recover my DB?!

Best Answer

Run mysql_upgrade manually - chances are your MySQL database doesn't meet the schema requirements for the updated version of MySQL you're using.

Do you have the privileges backed up for the server? If so it might be quicker to back up your mysql data dir (just in case) - shut down MySQL, remove the 'mysql' directory/db from the data dir and run 'mysql_install_db' to have it re-generate the database, start MySQL and reload your privileges.

Related Topic