MySQL致命错误:无法打开和锁定特权表:不正确的文件格式“用户" [英] MySQL Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'
问题描述
MySQL(Percona 5.6)无法启动.
MySQL (Percona 5.6) will not start.
这个错误发生在我身上好几次了.每次，我都必须删除MySQL数据目录并重新安装MySQL.
This error has happened to me several times. Each time, I have had to remove MySQL data directories and reinstall MySQL.
还有另一种修复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
推荐答案
感谢John将我带入正确的轨道，我在系统上还有其他一些难关.希望这对某人有帮助.
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.
这是一个损坏的特权表.可能是由于升级或电源故障引起的.我的系统OpenSUSE 13.2，MySQL 5.6.简单的重新安装无法解决，必须在重新安装之前删除MySQL的所有痕迹，否则...
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…
关闭所有MySQL实例
Close down all instances of MySQL
$ systemctl stop mysql.service
$ pkill -9 mysqld
启动服务器绕过特权系统
Start server bypassing privilege system
$ sudo mysqld_safe --user=root --skip-grant-tables
启动MySQL命令行工具
Start MySQL command line tool
$ mysql
如果收到
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
mysqld_safe正在其套接字在其他地方运行.找到它.
mysqld_safe is running with its socket somewhere else. Find it with.
$ sudo find / -type s
我的位于/var/run/mysql/ 编辑my.cnf中的套接字行，记下您现有的套接字设置. 我的行变成了
Mine was in /var/run/mysql/ Edit the socket line in my.cnf, making a note of your existing socket setting. My line became
socket=/var/run/mysql/mysql.sock
返回关闭所有MySQL实例"(这些说明的顶部).按照它们进行操作，以启动MySQL命令行工具".希望您可以成功打开mysql.从mysql命令行开始.
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> use mysql
mysql> repair table user use_frm;
mysql> exit
关闭所有MySQL实例
Close down all instances of MySQL
$ systemctl stop mysql.service
$ pkill -9 mysqld
重新编辑my.cnf，将套接字行恢复为其原始设置.
Re-edit my.cnf, returning the socket line to its original setting.
我必须重置mysql数据目录中2个文件的权限.
I had to reset permissions on 2 files in my mysql data directory.
$ chown mysql:mysql server2.err
$ chown mysql:mysql server2.pid
启动MySQL服务器
$ systemctl start mysql.service
然后我在另一个表(db)中遇到了相同的原始错误
I then got the same original error with another table (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;
这篇关于MySQL致命错误:无法打开和锁定特权表:不正确的文件格式“用户"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
