Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

时间:2022-09-20 08:39:43

环境:centos6.8
Mysql-5.7.13
本人代码中的path即为本人mysql目录=/application/mysql

本人是初学者,参照书上安装方式,二进制安装,即解压以后初始化mysql即可以完成安装

groupadd mysql
useradd mysql -s /sbin/nologin -g mysql -M
tar -xf mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
cd mysql-5.7.13-linux-glibc2.5-x86_64
mv mysql-5.7.13-linux-glibc2.5-x86_64.tar /application/mysql-5.7.13
ln -s /application/mysql-5.7.13 /application/mysql
/bin/cp /application/mysql/support-files/my-default.cnf /etc/my.cnf
mkdir /application/mysql/data
/application/mysql/bin/mysql_install_db --datadir=/application/mysql/data --basedir=/application/mysql --user=mysql

报错一

[WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
[ERROR]   The data directory '/application/mysql/data' already exist and is not empty.

按照warning提示,删除了data文件夹并重新创建,执行以下命令
/application/mysql/bin/mysqld –basedir=/application/mysqld –datadir=/application/mysql/data –user=mysql
又出现报错二

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
[ERROR] Aborting

并且在第二次运行此命令的时候出现不同报错
报错三

[ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
[Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

循环出现几十次后自动停止,于是我重新覆盖了/etc/my.cnf文件后,又出现了不同的错误
报错四

InnoDB: Operating system error number 13 in a file operation.
[ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
Cannot open datafile './ibdata1'

大意是没有权限,我重新查看了下mysql目录下的文件权限,发现创建的data是root用户,删除后重新创建再执行

chown -R mysql.mysql data

后再次执行后初始化命令,出现最早的报错二:’mysql.user’ doesn’t exist,我意识到应该重点去查找这个最早的错误的解决方法

搜索网上的一些方法
方案一:/path/mysqld initialize
出现如下报错五

[ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
[Warning] Can't create test file /usr/local/mysql/data/xhoo2.lower-test
[Warning] Can't create test file /usr/local/mysql/data/xhoo2.lower-test
[ERROR] failed to set datadir to /usr/local/mysql/data/
[ERROR] Aborting

一个错误,继续谷歌,但是发现网上的方法都是安装好以后,某次启动出现的偶然情况,并无用处
方案二

/path/mysqld --basedir=/application/mysql --datadir=/application/mysql/data --user=mysql --ldata=/var/lib/mysql 

此种方法网上都是yum安装时候出现的错误,与本人二进制安装不太符合,别无他法,姑且司马当活马医
我本人/var下并没有这个目录,从网上得知道/var/lib/mysql是安装目录,修改后代码

path/mysqld --basedir=/application/mysql --datadir=/application/mysql/data --user=mysql --ldata=/path/mysql

出现如下报错六

mysqld: Table 'mysql.plugin' doesn't exist
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
[ERROR] unknown variable 'ldata=/application/mysql'

虽然还是有报错,而且error提示无法识别这个参数,但报错内容不同,姑且认为离答案更近一步,继续搜寻这个错误
然而心烦意躁中我又运行了最初的代码

/application/mysql/bin/mysqld --basedir=/application/mysqld --datadir=/application/mysql/data --user=mysql

无意中发现这次出现了报错二和报错六

[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it
[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

考虑到报错六先于报错二,方案二也亟需解决这个报错,继续搜寻报错六的答案
针对报错六网上有些答案
方案一
检查my.cnf 看到 read_only= on ,注释掉,重新启动mysql,ok
我是初次安装初始化步骤,my.cnf里面无此选项,不符合
方案二
/path/mysql_install_db –user=mysql –datadir=/usr/local/mysql/var
这不正是我最早初始化的操作么,不符合
方案三

http://forums.mysql.com/read.php?25,516360,516404#msg-516404

我初次安装,不符合
方案四
有个帖子提到了官网,于是我就去官网转了一圈。堪堪过四级的水平,找到了如下内容

http://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html

/path/mysqld initialize –basedir=/application/mysql –datadir=/application/mysql/data –user=mysql
还多了一条报错

[ERROR] Too many arguments (first extra is 'initialize')

其余的也都大抵类似,至此,陷入了困境

某天,跟朋友又提到这个问题,我又扒开了这个埋没的问题,看到之前朋友给我发的一个链接,在此要感谢帮我的朋友,以及这篇文章的原主人
【CentOS6.7 64位环境下安装部署MySQL-5.7.13】http://www.linuxidc.com/Linux/2016-07/133688.htm?hmsr=toutiao.io

/application/mysql/bin/mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

发现奇迹般的没有在出现error,仔细看了下之前的排错,出现在initialize前面没有–符号
突然想起上面的方案四,是如此类似,又进了一遍官网,果然是自己粗心少打–
因此最终的解决方案就是

/application/mysql/bin/mysqld --initialize --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

还是官网比较权威,学好英语很重要,细心更重要
——————————————————————-Over—————————————————————————————-