致命错误:无法打开和锁定特权表:表“mysql”。主机不存在

时间:2021-11-13 06:46:37

I am on a server that has afresh install on RHEL 5. I was able to install Apache and PHP just fine., but I am having serious trouble with my MySQL installation. I tried the following:

我是在一个重新安装在RHEL 5上的服务器上。我可以很好地安装Apache和PHP。但是我的MySQL安装遇到了很大的麻烦。我试着以下:

yum install mysql-server mysql 

And didn't get any errors or conflicts. Then I tried to start mysql with the following commands:

没有任何错误或冲突。然后我尝试用以下命令启动mysql:

chkconfig --levels 235 mysqld on
service mysqld start

And get Timeout error occurred trying to start MySQL Daemon.

在尝试启动MySQL守护进程时发生超时错误。

I checked my logs and see this error:

我检查了日志,看到了这个错误:

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

I'm not sure where to go from here.

我不知道从这里到哪里去。

For reference I am using RHEL 5 and installed the latest versions of PHP 5 and Apache.

为了便于参考,我使用RHEL 5并安装了最新版本的PHP 5和Apache。

17 个解决方案

#1


58  

  1. Uninstall mysql using yum remove mysql*

    使用yum删除mysql*。

  2. Recursively delete /usr/bin/mysql and /var/lib/mysql

    递归删除/usr/bin/mysql和/var/lib/mysql。

  3. Delete the file /etc/my.cnf.rmp

    删除文件/etc/my.cnf.rmp

  4. Use ps -e to check the processes to make sure mysql isn't still running.

    使用ps -e检查进程以确保mysql不运行。

  5. Reboot server with reboot

    重启服务器重启

  6. Run yum install mysql-server. This also seems to install the mysql client as a dependency.

    运行yum安装mysql服务器。这似乎也将mysql客户端安装为依赖项。

  7. Give mysql ownership and group priveleges with:

    给予mysql所有权,并对其进行分组:

    chown -R mysql /var/lib/mysql

    乔恩- r mysql /var/lib/mysql

    chgrp -R mysql /var/lib/mysql

    chgrp - r mysql /var/lib/mysql

  8. Use service mysqld start to start MySQL Daemon.

    使用服务mysqld开始启动MySQL守护进程。

#2


52  

After chown and chgrp'ing /var/lib/mysql per the answer by @Bad Programmer, you may also have to execute the following command:

在chown和chgrp'ing /var/lib/mysql每一个答案由@Bad程序员完成后,您可能还需要执行以下命令:

sudo mysql_install_db --user=mysql --ldata=/var/lib/mysql

Then restart your mysqld.

然后重新启动mysqld。

#3


34  

I had this issue on arch linux as well. The issue was pacman installed the package in a different location than MySQL was expecting. I was able to fix the issue with this:

我在arch linux上也有这个问题。问题是pacman在一个不同的位置安装了这个包,而不是MySQL所期望的。我能够解决这个问题:

sudo mysql_install_db --user=mysql --basedir=/usr/ --ldata=/var/lib/mysql/

Hope this helps someone!

希望这可以帮助别人!

#4


11  

mysql_install_db –-user=mysql –ldata=/var/lib/mysql

Worked for me in Centos 7

在Centos 7工作。

#5


10  

The root of my problem seemed to be selinux, which was turned on (enforcing) automatically on OS install.

我的问题的根源似乎是selinux,它在OS安装上自动打开(强制执行)。

I wanted my mysql in /data.

我想要我的mysql数据。

After verifying that my.cnf had:

确认my.cnf有:

datadir=/data/mysql

(and leaving the socket at /var/lib/mysql) I executed the command to turn off selinux for mysqld (alternative is to turn it off completely):

(并在/var/lib/mysql中离开套接字),我执行了关闭selinux的命令(另一种方法是完全关闭):

setsebool -P mysqld_disable_trans=1

I ran the following commands:

我运行以下命令:

> chown -R mysql .
> chgrp -R mysql .
> mysql_install_db --user=mysql

I started the mysql daemon and everything worked fine after that.

我启动了mysql守护进程,之后一切正常。

#6


10  

initialize mysql before start on windows.

在windows开始之前初始化mysql。

mysqld --initialize

#7


6  

When download mysql zip version, if run mysqld directly, you'll get this error: 2016-02-18T07:23:48.318481Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 2016-02-18T07:23:48.319482Z 0 [ERROR] Aborting

当下载mysql zip版本时,如果直接运行mysqld,您会得到这个错误:2016-02-18T07:23:48.318481Z 0[错误]致命错误:无法打开和锁定特权表:表“mysql”。用户不存在2016-02-18t07:23:48.319482z0[错误]中止。

You have to run below command first: mysqld --initialize

您必须先运行以下命令:mysqld——初始化。

Make sure your data folder is empty before this command.

在此命令之前,确保您的数据文件夹是空的。

#8


3  

If you move your datadir, you not only need to give the new datadir permissions, but you need to ensure all parent directories have permission.

如果您移动您的datadir,您不仅需要提供新的datadir权限,还需要确保所有父目录都有权限。

I moved my datadir to a hard drive, mounted in Ubuntu as:

我把我的datadir移动到一个硬盘上,安装在Ubuntu中:

/media/*user*/Data/

and my datadir was Databases.

我的datadir是数据库。

I had to set permissions to 771 to each of the media, user and Data directories:

我必须将权限设置为771到每个媒体、用户和数据目录:

sudo chmod 771 *DIR*

If this does not work, another way you can get mysql to work is to change user in /etc/mysql/my.cnf to root; though there are no doubt some issues with doing that from a security perspective.

如果这不起作用,另一种方法可以让mysql工作,那就是将/etc/mysql/my.cnf中的用户更改为root;尽管从安全的角度来看,这样做无疑有些问题。

#9


3  

I just met the same problem with mysql 5.7 on OSX:

我在OSX上遇到了同样的问题,mysql 5.7

rm -rf {datadir}
mysqld --initialize --datadir {datadir}
mysqld --datadir {datadir}

#10


2  

For myself, I had to do:

对于我自己,我不得不这样做:

yum remove mysql*

rm -rf /var/lib/mysql/
cp /etc/my.cnf ~/my.cnf.bkup

yum install -y mysql-server mysql-client

mysql_install_db

chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /var/log/mysql

service mysql start

Then I was able to get back into my databases and configure them again after I nuked them the first go around.

然后我又回到了我的数据库,在我第一次使用它们之后再重新配置它们。

#11


2  

Just this command is enough to do the magic on centos 6.6

仅仅这个命令就足以在centos 6.6上执行魔法了。

mysql_install_db

mysql_install_db

#12


0  

In my case the path of MySQL data folder had a special character "ç" and it make me get...

在我的例子中,MySQL数据文件夹的路径有一个特殊的字符“c”,它使我得到…

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

致命错误:无法打开和锁定特权表:表“mysql”。主机不存在。

I'm have removed all special characters and everything works.

我已经删除了所有的特殊字符,一切正常。

#13


0  

On CentOS EL 6 and perhaps on earlier versions there is one way to get into this same mess.

在CentOS EL 6和早期版本中,有一种方法可以让你陷入同样的困境。

Install CentOS EL6 with a minimal installation. For example I used kickstart to install the following:

安装CentOS EL6和最小安装。例如,我使用kickstart来安装以下内容:

%packages
@core
acpid
bison
cmake
dhcp-common
flex
gcc
gcc-c++
git
libaio-devel
make
man
ncurses-devel
perl
ntp
ntpdate
pciutils
tar
tcpdump
wget
%end

You will find that one of the dependencies of the above list is mysql-libs. I found that my system has a default my.cnf in /etc and this contains:

您将发现上面列表的一个依赖项是mysql-libs。我发现我的系统有一个默认的my.cnf /etc,其中包含:

[mysqld]
dataddir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

When you build from the Generic Linux (Architecture Independent), Compressed TAR Archive your default data directory is /usr/local/mysql/data which conflicts with the /etc/my.cnf already present which defines datadir=/var/lib/mysql. Also the pid-file defined in the same file does not have permissions for the mysql user/group to write to it in /var/run/mysqld.

当您从通用的Linux(体系结构独立)构建时,压缩TAR压缩文件的默认数据目录是/usr/local/mysql/data,它与/etc/my.cnf发生冲突,它定义了datadir=/var/lib/mysql。同样,在同一个文件中定义的pid文件也没有mysql用户/组在/var/run/mysqld中写入的权限。

A quick remedy is to mv /etc/my.cnf /etc/my.cnf.old which should get your generic source procedure working.

一个快速的补救方法是,mv /etc/my.cnf /etc/my.cnf.old,它应该能让你的通用程序运行。

Of course the experience is different of you use the source RPMs.

当然,经验是不同的你使用源rpm。

#14


0  

I had the same issue in trying to start the server and followed the "checked" solution. But still had the problem. The issue was the my /etc/my.cnf file was not pointing to my designated datadir as defined when I executed the mysql_install_db with --datadir defined. Once I updated this, the server started correctly.

我在尝试启动服务器和遵循“检查”解决方案时遇到了相同的问题。但问题仍然存在。问题是我的/etc/my.cnf文件没有指向我的指定的datadir,这是在我使用datadir定义的mysql_install_db时定义的。一旦我更新了这个,服务器就正确启动了。

#15


0  

If you have a server which used to happily run MySQL, but now gives this error, then an uninstall and re-install of MySQL is overkill.

如果您有一个服务器,它曾经很愉快地运行MySQL,但是现在给出了这个错误,那么卸载和重新安装MySQL就太过了。

In my case, the server died and took a few disk blocks with it. This affected a few files, including /var/lib/mysql/mysql/host.frm and /var/lib/mysql/mysql/proc.frm

在我的例子中,服务器死了,并带走了几个磁盘块。这影响了一些文件,包括/var/lib/mysql/mysql/host.frm和/var/lib/mysql/mysql/proc.frm。

Luckily, I could copy these from another server, and this got me past that table error.

幸运的是,我可以从另一个服务器复制这些,这让我忘记了那个表错误。

#16


0  

I got similar error on overlayfs (overlay2) that is the default on Docker for Mac. The error happens when starting mysql on the image, after creating a image with mysql.

我在overlayfs上也有类似的错误(overlay2),这是Mac上的默认设置。在使用mysql创建一个图像后,在图像上启动mysql时会发生错误。

2017-11-15T06:44:22.141481Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table storage engine for 'user' doesn't have this option

Switching to "aufs" solved the issue. (On Docker for Mac, the "daemon.json" can be edited by choosing "Preferences..." menu, and selecting "Daemon" tab, and selecting "Advanced" tab.)

切换到“aufs”解决了这个问题。(关于Mac的Docker,“守护进程”。可以通过选择“Preferences…”菜单和选择“Daemon”选项卡,选择“Advanced”选项卡来编辑json。

/etc/docker/daemon.json :

/etc/docker/daemon.json:

{
  "storage-driver" : "aufs",
  "debug" : true,
  "experimental" : true
}

Ref:

裁判:

https://github.com/moby/moby/issues/35503

https://github.com/moby/moby/issues/35503

https://qiita.com/Hige-Moja/items/7b1208f16997e2aa9028

https://qiita.com/Hige-Moja/items/7b1208f16997e2aa9028

#17


-3  

My case on Ubuntu 14.04.2 LTS was similar to others with my.cnf, but for me the cause was a ~/.my.cnf that was leftover from a previous installation. After deleting that file and purging/re-installing mysql-server, it worked fine.

我在Ubuntu 14.04.2 LTS上的例子与my.cnf相似,但对我来说,原因是一个~/.my.cnf,这是之前安装的遗留问题。删除该文件并清除/重新安装mysql-server后,它运行良好。

#1


58  

  1. Uninstall mysql using yum remove mysql*

    使用yum删除mysql*。

  2. Recursively delete /usr/bin/mysql and /var/lib/mysql

    递归删除/usr/bin/mysql和/var/lib/mysql。

  3. Delete the file /etc/my.cnf.rmp

    删除文件/etc/my.cnf.rmp

  4. Use ps -e to check the processes to make sure mysql isn't still running.

    使用ps -e检查进程以确保mysql不运行。

  5. Reboot server with reboot

    重启服务器重启

  6. Run yum install mysql-server. This also seems to install the mysql client as a dependency.

    运行yum安装mysql服务器。这似乎也将mysql客户端安装为依赖项。

  7. Give mysql ownership and group priveleges with:

    给予mysql所有权,并对其进行分组:

    chown -R mysql /var/lib/mysql

    乔恩- r mysql /var/lib/mysql

    chgrp -R mysql /var/lib/mysql

    chgrp - r mysql /var/lib/mysql

  8. Use service mysqld start to start MySQL Daemon.

    使用服务mysqld开始启动MySQL守护进程。

#2


52  

After chown and chgrp'ing /var/lib/mysql per the answer by @Bad Programmer, you may also have to execute the following command:

在chown和chgrp'ing /var/lib/mysql每一个答案由@Bad程序员完成后,您可能还需要执行以下命令:

sudo mysql_install_db --user=mysql --ldata=/var/lib/mysql

Then restart your mysqld.

然后重新启动mysqld。

#3


34  

I had this issue on arch linux as well. The issue was pacman installed the package in a different location than MySQL was expecting. I was able to fix the issue with this:

我在arch linux上也有这个问题。问题是pacman在一个不同的位置安装了这个包,而不是MySQL所期望的。我能够解决这个问题:

sudo mysql_install_db --user=mysql --basedir=/usr/ --ldata=/var/lib/mysql/

Hope this helps someone!

希望这可以帮助别人!

#4


11  

mysql_install_db –-user=mysql –ldata=/var/lib/mysql

Worked for me in Centos 7

在Centos 7工作。

#5


10  

The root of my problem seemed to be selinux, which was turned on (enforcing) automatically on OS install.

我的问题的根源似乎是selinux,它在OS安装上自动打开(强制执行)。

I wanted my mysql in /data.

我想要我的mysql数据。

After verifying that my.cnf had:

确认my.cnf有:

datadir=/data/mysql

(and leaving the socket at /var/lib/mysql) I executed the command to turn off selinux for mysqld (alternative is to turn it off completely):

(并在/var/lib/mysql中离开套接字),我执行了关闭selinux的命令(另一种方法是完全关闭):

setsebool -P mysqld_disable_trans=1

I ran the following commands:

我运行以下命令:

> chown -R mysql .
> chgrp -R mysql .
> mysql_install_db --user=mysql

I started the mysql daemon and everything worked fine after that.

我启动了mysql守护进程,之后一切正常。

#6


10  

initialize mysql before start on windows.

在windows开始之前初始化mysql。

mysqld --initialize

#7


6  

When download mysql zip version, if run mysqld directly, you'll get this error: 2016-02-18T07:23:48.318481Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 2016-02-18T07:23:48.319482Z 0 [ERROR] Aborting

当下载mysql zip版本时,如果直接运行mysqld,您会得到这个错误:2016-02-18T07:23:48.318481Z 0[错误]致命错误:无法打开和锁定特权表:表“mysql”。用户不存在2016-02-18t07:23:48.319482z0[错误]中止。

You have to run below command first: mysqld --initialize

您必须先运行以下命令:mysqld——初始化。

Make sure your data folder is empty before this command.

在此命令之前,确保您的数据文件夹是空的。

#8


3  

If you move your datadir, you not only need to give the new datadir permissions, but you need to ensure all parent directories have permission.

如果您移动您的datadir,您不仅需要提供新的datadir权限,还需要确保所有父目录都有权限。

I moved my datadir to a hard drive, mounted in Ubuntu as:

我把我的datadir移动到一个硬盘上,安装在Ubuntu中:

/media/*user*/Data/

and my datadir was Databases.

我的datadir是数据库。

I had to set permissions to 771 to each of the media, user and Data directories:

我必须将权限设置为771到每个媒体、用户和数据目录:

sudo chmod 771 *DIR*

If this does not work, another way you can get mysql to work is to change user in /etc/mysql/my.cnf to root; though there are no doubt some issues with doing that from a security perspective.

如果这不起作用,另一种方法可以让mysql工作,那就是将/etc/mysql/my.cnf中的用户更改为root;尽管从安全的角度来看,这样做无疑有些问题。

#9


3  

I just met the same problem with mysql 5.7 on OSX:

我在OSX上遇到了同样的问题,mysql 5.7

rm -rf {datadir}
mysqld --initialize --datadir {datadir}
mysqld --datadir {datadir}

#10


2  

For myself, I had to do:

对于我自己,我不得不这样做:

yum remove mysql*

rm -rf /var/lib/mysql/
cp /etc/my.cnf ~/my.cnf.bkup

yum install -y mysql-server mysql-client

mysql_install_db

chown -R mysql:mysql /var/lib/mysql
chown -R mysql:mysql /var/log/mysql

service mysql start

Then I was able to get back into my databases and configure them again after I nuked them the first go around.

然后我又回到了我的数据库,在我第一次使用它们之后再重新配置它们。

#11


2  

Just this command is enough to do the magic on centos 6.6

仅仅这个命令就足以在centos 6.6上执行魔法了。

mysql_install_db

mysql_install_db

#12


0  

In my case the path of MySQL data folder had a special character "ç" and it make me get...

在我的例子中,MySQL数据文件夹的路径有一个特殊的字符“c”,它使我得到…

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

致命错误:无法打开和锁定特权表:表“mysql”。主机不存在。

I'm have removed all special characters and everything works.

我已经删除了所有的特殊字符,一切正常。

#13


0  

On CentOS EL 6 and perhaps on earlier versions there is one way to get into this same mess.

在CentOS EL 6和早期版本中,有一种方法可以让你陷入同样的困境。

Install CentOS EL6 with a minimal installation. For example I used kickstart to install the following:

安装CentOS EL6和最小安装。例如,我使用kickstart来安装以下内容:

%packages
@core
acpid
bison
cmake
dhcp-common
flex
gcc
gcc-c++
git
libaio-devel
make
man
ncurses-devel
perl
ntp
ntpdate
pciutils
tar
tcpdump
wget
%end

You will find that one of the dependencies of the above list is mysql-libs. I found that my system has a default my.cnf in /etc and this contains:

您将发现上面列表的一个依赖项是mysql-libs。我发现我的系统有一个默认的my.cnf /etc,其中包含:

[mysqld]
dataddir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

When you build from the Generic Linux (Architecture Independent), Compressed TAR Archive your default data directory is /usr/local/mysql/data which conflicts with the /etc/my.cnf already present which defines datadir=/var/lib/mysql. Also the pid-file defined in the same file does not have permissions for the mysql user/group to write to it in /var/run/mysqld.

当您从通用的Linux(体系结构独立)构建时,压缩TAR压缩文件的默认数据目录是/usr/local/mysql/data,它与/etc/my.cnf发生冲突,它定义了datadir=/var/lib/mysql。同样,在同一个文件中定义的pid文件也没有mysql用户/组在/var/run/mysqld中写入的权限。

A quick remedy is to mv /etc/my.cnf /etc/my.cnf.old which should get your generic source procedure working.

一个快速的补救方法是,mv /etc/my.cnf /etc/my.cnf.old,它应该能让你的通用程序运行。

Of course the experience is different of you use the source RPMs.

当然,经验是不同的你使用源rpm。

#14


0  

I had the same issue in trying to start the server and followed the "checked" solution. But still had the problem. The issue was the my /etc/my.cnf file was not pointing to my designated datadir as defined when I executed the mysql_install_db with --datadir defined. Once I updated this, the server started correctly.

我在尝试启动服务器和遵循“检查”解决方案时遇到了相同的问题。但问题仍然存在。问题是我的/etc/my.cnf文件没有指向我的指定的datadir,这是在我使用datadir定义的mysql_install_db时定义的。一旦我更新了这个,服务器就正确启动了。

#15


0  

If you have a server which used to happily run MySQL, but now gives this error, then an uninstall and re-install of MySQL is overkill.

如果您有一个服务器,它曾经很愉快地运行MySQL,但是现在给出了这个错误,那么卸载和重新安装MySQL就太过了。

In my case, the server died and took a few disk blocks with it. This affected a few files, including /var/lib/mysql/mysql/host.frm and /var/lib/mysql/mysql/proc.frm

在我的例子中,服务器死了,并带走了几个磁盘块。这影响了一些文件,包括/var/lib/mysql/mysql/host.frm和/var/lib/mysql/mysql/proc.frm。

Luckily, I could copy these from another server, and this got me past that table error.

幸运的是,我可以从另一个服务器复制这些,这让我忘记了那个表错误。

#16


0  

I got similar error on overlayfs (overlay2) that is the default on Docker for Mac. The error happens when starting mysql on the image, after creating a image with mysql.

我在overlayfs上也有类似的错误(overlay2),这是Mac上的默认设置。在使用mysql创建一个图像后,在图像上启动mysql时会发生错误。

2017-11-15T06:44:22.141481Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table storage engine for 'user' doesn't have this option

Switching to "aufs" solved the issue. (On Docker for Mac, the "daemon.json" can be edited by choosing "Preferences..." menu, and selecting "Daemon" tab, and selecting "Advanced" tab.)

切换到“aufs”解决了这个问题。(关于Mac的Docker,“守护进程”。可以通过选择“Preferences…”菜单和选择“Daemon”选项卡,选择“Advanced”选项卡来编辑json。

/etc/docker/daemon.json :

/etc/docker/daemon.json:

{
  "storage-driver" : "aufs",
  "debug" : true,
  "experimental" : true
}

Ref:

裁判:

https://github.com/moby/moby/issues/35503

https://github.com/moby/moby/issues/35503

https://qiita.com/Hige-Moja/items/7b1208f16997e2aa9028

https://qiita.com/Hige-Moja/items/7b1208f16997e2aa9028

#17


-3  

My case on Ubuntu 14.04.2 LTS was similar to others with my.cnf, but for me the cause was a ~/.my.cnf that was leftover from a previous installation. After deleting that file and purging/re-installing mysql-server, it worked fine.

我在Ubuntu 14.04.2 LTS上的例子与my.cnf相似,但对我来说,原因是一个~/.my.cnf,这是之前安装的遗留问题。删除该文件并清除/重新安装mysql-server后,它运行良好。