Can´t open and lock privilege tables: Incorrect file format 'host'

[Helmut Wolken]

Hi, for those who suffer the same problem as I did - here hwo I solved it

I am running mysql 5.0.22-Debian_4-log on an XEN virtual host (Debian 4.0.3-3, kernel 2.6.16, Xen 3.0-x86_32)

After a XEN crash mysql didn´t start anymore. In /var/log/syslog I found the following error message:
Can´t open and lock privilege tables: Incorrect file format 'host'

I started mysql with:
mysqld --skip-grant
and repaired the host-table (mysql> repair table host use_frm;)

Nearly half of all tables within mysql had been corrupted after that crash, so I repeated 'repair table' for all of them.

Unfortunaly that deleted all mysql users including root.
Started with --skip-grant you can´t just add an user with ' GRANT ALL PRIVILEGES ..' so you have to add any user manually into the 'user' table by:

mysql> INSERT INTO user
-> VALUES('localhost','root','your password','Y','Y'....);

(set one 'Y' for each privilege in table user)
Don´t forget to flush these privileges:

mysql> flush privileges;

Afterwards you can restart mysql in the normal way.
Login into mysql with:
mysql -u root --password
and add all users you need by 'grant all privileges ...'

Hope that will help