MySQL Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'

mysql percona
Paul Draper picture Paul Draper · Feb 15, 2015 · Viewed 29.1k times · Source

MySQL (Percona 5.6) will not start.

This error has happened to me several times. Each time, I have had to remove MySQL data directories and reinstall MySQL.

Is there another way to fix MySQL? (Specifically one in which the data not blown away?)

/var/log/mysql/error.log

150214 16:36:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-02-14 16:36:40 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2015-02-14 16:36:40 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2015-02-14 16:36:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-02-14 16:36:40 1018 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2015-02-14 16:36:40 1018 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Incorrect file format 'plugin'
2015-02-14 16:36:40 1018 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-02-14 16:36:40 1018 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-02-14 16:36:40 1018 [Note] InnoDB: The InnoDB memory heap is disabled
2015-02-14 16:36:40 1018 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-02-14 16:36:40 1018 [Note] InnoDB: Memory barrier is not used
2015-02-14 16:36:40 1018 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-02-14 16:36:40 1018 [Note] InnoDB: Using Linux native AIO
2015-02-14 16:36:40 1018 [Note] InnoDB: Using CPU crc32 instructions
2015-02-14 16:36:40 1018 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2015-02-14 16:36:40 1018 [Note] InnoDB: Completed initialization of buffer pool
2015-02-14 16:36:40 1018 [Note] InnoDB: Highest supported file format is Barracuda.
2015-02-14 16:36:40 1018 [Note] InnoDB: The log sequence numbers 714340126 and 714340126 in ibdata files do not match the log sequence number 716513090 in the ib_logfiles!
2015-02-14 16:36:40 1018 [Note] InnoDB: Database was not shutdown normally!
2015-02-14 16:36:40 1018 [Note] InnoDB: Starting crash recovery.
2015-02-14 16:36:40 1018 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-02-14 16:36:40 1018 [Note] InnoDB: Restoring possible half-written data pages 
2015-02-14 16:36:40 1018 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 292596, file name binlog.000056
2015-02-14 16:36:40 1018 [Note] InnoDB: 128 rollback segment(s) are active.
2015-02-14 16:36:40 1018 [Note] InnoDB: Waiting for purge to start
2015-02-14 16:36:40 1018 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.21-70.1 started; log sequence number 716513090
2015-02-14 16:36:40 1018 [Note] Recovering after a crash using binlog
2015-02-14 16:36:40 1018 [Note] Starting crash recovery...
2015-02-14 16:36:40 1018 [Note] Crash recovery finished.
2015-02-14 16:36:40 1018 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-02-14 16:36:40 1018 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-02-14 16:36:40 1018 [Note] Server hostname (bind-address): '*'; port: 3306
2015-02-14 16:36:40 1018 [Note] IPv6 is available.
2015-02-14 16:36:40 1018 [Note]   - '::' resolves to '::';
2015-02-14 16:36:40 1018 [Note] Server socket created on IP: '::'.
2015-02-14 16:36:40 1018 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'
150214 16:36:40 mysqld_safe mysqld from pid file /tmp/mysqld.pid ended

Answer

John Linhart picture John Linhart · Jul 29, 2015

I managed so solve this problem with answer from ruby.b

You'll have to repair your host table. To do this, issue the following command to start your server bypassing the privilege system

In one terminal, run

$ sudo mysqld --skip-grant-tables

Open another terminal and execute these commands

$ mysql
mysql> use mysql
mysql> repair table host use_frm;
mysql> exit

And restart the mysql service

$ sudo service mysql restart

Related questions

mysql process cannot be stopped

I have recently installed 5.5.28-29.2 Percona Server (GPL), Release 29.2 in a Ubuntu 12.04 OS Desktop. I have tried to stop the server using different methods: - sudo /etc/init.d/mysql stop - sudo kill -9 pid - mysqladmin -u root …

Read More
COMMIT OR conn.setAutoCommit(true)

I have noticed some programmer using COMMIT other using conn.setAutoCommit(true); to end the transaction or roll back so what are the benefits of using one instead of the other? Where is the main difference? conn.setAutoCommit(true); over …

Read More
Converting iso-8859-1 data to UTF-8 in UTF8 and Latin1 tables

Problem Summary: While trying to convert a site with mysql database from latin1 to utf8, some special characters are not displaying correctly despite ensuring charsets are all utf8 system wide. Problem Details: This is a common problem. But I seem …

Read More