MySQL 致命错误：无法打开和锁定权限表：文件格式“用户”不正确

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

提问by Paul Draper

MySQL (Percona 5.6) will not start.

MySQL (Percona 5.6) 不会启动。

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

这个错误在我身上发生过几次。每次，我都必须删除 MySQL 数据目录并重新安装 MySQL。

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

有没有其他方法可以修复 MySQL？（特别是其中的数据没有被吹走？）

/var/log/mysql/error.log

/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

采纳答案by Tim Richards

Thanks to John for putting me on the right track, I had a few other hoops to jump through on my system. Hope this helps someone.

感谢约翰让我走上正轨，我的系统还有其他一些障碍要跳过。希望这可以帮助某人。

This is a corrupt privilege table. Maybe caused by an upgrade or power failure. My system OpenSUSE 13.2, MySQL 5.6. Simple reinstall does not fix, must delete all traces of MySQL before reinstall OR…

这是一个损坏的权限表。可能是由于升级或电源故障引起的。我的系统 OpenSUSE 13.2，MySQL 5.6。简单的重新安装并不能解决问题，必须在重新安装之前删除 MySQL 的所有痕迹或...

Close down all instances of MySQL

关闭所有 MySQL 实例

$ systemctl stop mysql.service
$ pkill -9 mysqld

Start server bypassing privilege system

启动服务器绕过特权系统

$ sudo mysqld_safe --user=root --skip-grant-tables

Start MySQL command line tool

启动 MySQL 命令行工具

$ mysql

If you receive

如果您收到

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

mysqld_safe is running with its socket somewhere else. Find it with.

mysqld_safe 正在其他地方使用它的套接字运行。找到它。

$ sudo find / -type s

Mine was in /var/run/mysql/ Edit the socket line in my.cnf, making a note of your existing socket setting. My line became

我的在 /var/run/mysql/ 编辑 my.cnf 中的 socket 行，记下您现有的 socket 设置。我的线路变成了

socket=/var/run/mysql/mysql.sock

Return to 'Close down all instances of MySQL' (the top of these instructions). Follow them through to 'Start MySQL command line tool'. Hopefully you can open mysql successfully. From mysql command line.

返回“关闭所有 MySQL 实例”（这些说明的顶部）。跟随他们到“启动 MySQL 命令行工具”。希望你能成功打开mysql。从 mysql 命令行。

mysql> use mysql
mysql> repair table user use_frm;
mysql> exit

Close down all instances of MySQL

关闭所有 MySQL 实例

$ systemctl stop mysql.service
$ pkill -9 mysqld

Re-edit my.cnf, returning the socket line to its original setting.

重新编辑 my.cnf，将套接字行恢复到其原始设置。

I had to reset permissions on 2 files in my mysql data directory.

我不得不在我的 mysql 数据目录中重置 2 个文件的权限。

$ chown mysql:mysql server2.err
$ chown mysql:mysql server2.pid

Start the MySQL server

启动 MySQL 服务器

$ systemctl start mysql.service

I then got the same original error with another table (db)

然后我在另一个表（db）上得到了相同的原始错误

[ERROR] Fatal error: Can't open and lock privilege  tables: Incorrect file format 'db'

And had to repeat the above procedure multiple times altering the 'repair' command until all privilege tables were fixed.

并且必须多次重复上述过程，更改“修复”命令，直到修复所有权限表。

mysql> repair table db use_frm;

回答by John Linhart

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

我设法用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

并重启mysql服务

$ sudo service mysql restart

回答by Lukas Knuth

Here is the setup for @John Linhart's answer, if you're running Docker:

这是@John Linhart的答案的设置，如果您正在运行Docker：

First, start a new docker container with the appropriate tag from the mysql-container (the same you used to write the DB with).

首先，使用 -container 中的适当标签启动一个新的 docker 容器mysql（与您用来编写 DB 的标签相同）。

$ docker run --rm -it -v <named_volume>:/var/lib/mysql mysql:<tag> /bin/bash

This will launch a new container with the correct named volume (or mounted volume) mounted in the container and drop you into a shell as root. The mysqld-Daemon will refuse to launch as root though, so we'll run it as the mysql-user:

这将启动一个新容器，其中安装了正确命名的卷（或已安装的卷），并将您以 root 身份放入 shell。该mysqld-daemon将拒绝作为启动根了，所以我们将运行它作为mysql-user：

$ whoami
root
$ which mysqld
/usr/sbin/mysqld
$ su mysql
$ whoami
mysql
$ /usr/sbin/mysqld --skip-grant-tables
....

Now to run the SQL commands, we'll connect to the running container from a new terminal:

现在要运行 SQL 命令，我们将从新终端连接到正在运行的容器：

$ docker ps 
CONTAINER ID [...]
abc123 [...]
$ docker exec -it abc123 /bin/bash
# We're on the container now!
$ whoami
root
$ mysql
...

And continue from there. When you're done, leave the container on the second terminal via exit. The terminal running mysqldwill not respond to CMD+C, so we'll stop the container via Docker:

并从那里继续。完成后，通过 将容器留在第二个终端上exit。终端运行mysqld不会响应 CMD+C，所以我们将通过 Docker 停止容器：

$ docker ps
CONTAINER ID [...]
abc123 [...]
$ docker stop abc123