MySQL service fails to start: Can't open and lock privilege tables: Incorrect file format 'host'

Created:

2016-11-16 12:48:58 UTC

Modified:

2017-04-24 11:14:33 UTC

1

Was this article helpful?


Have more questions?

Submit a request

MySQL service fails to start: Can't open and lock privilege tables: Incorrect file format 'host'

Applicable to:

  • Plesk 12.5 for Linux
  • Plesk 11.5 for Linux
  • Plesk 12.0 for Windows
  • Plesk 12.0 for Linux

Symptoms

MySQL service is failing to start, the following errors can be observed in MySQL log /var/log/mysqld.log while trying to start the service:

mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
/usr/libexec/mysqld: Incorrect file format 'plugin'
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
InnoDB: Initializing buffer pool, size = 8.0M
InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Started; log sequence number 0 2248162432
[ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'host'

Cause

Database mysql is corrupted.

Resolution

To resolve the issue follow the steps:

  1. Backup folder with databases /var/lib/mysql .

  2. Start the MySQL daemon in order to be able to run MySQL queries:

    # /usr/libexec/mysqld --skip-grant &
  3. Run mysqlcheck utility to check mysql database corruption:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -uadmin mysql
    mysql.columns_priv
    Error : Incorrect file format 'columns_priv'
    error : Corrupt
    mysql.db
    Error : Incorrect file format 'db'
    error : Corrupt
    mysql.event
    Error : Incorrect file format 'event'
    ..............................
  4. Make sure that you have a dump of mysql database, it is supposed to be located in /var/lib/psa/dumps directory

    # ll /var/lib/psa/dumps/mysql.daily.dump.0.gz
    -rw------- 1 root root 212336 Feb 20 00:21 /var/lib/psa/dumps/mysql.daily.dump.0.gz

Also try to backup databases with command if it is possible:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin -A > all_dbs
  1. Remove mysql database:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin
    mysql> drop database mysql;
    Query OK, 23 rows affected, 2 warnings (0.00 sec)
  2. Recreate mysql database:

    # mysql_install_db
  3. Restore the last mysql database dump from /var/lib/psa/dumps :

    # cp /var/lib/psa/dumps/mysql.daily.dump.0.gz mysql.daily.dump.0.gz
    # gunzip mysql.daily.dump.0.gz
    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin --one-database mysql < mysql.daily.dump.0
  4. Kill /usr/libexec/mysqld --skip-grant & process which was started at the 1st step:

    # ps aux | grep mysqld | awk '{print$2}' | while read l; do kill -9 $l; done
  5. Start mysqld service:

    # /etc/init.d/mysqld start
Have more questions? Submit a request
Please sign in to leave a comment.