mysql错误ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

mysql错误ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

偏向技术
/ 0 评论 / 1,694 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2021年04月12日,已超过1170天没有更新,若内容或图片失效,请留言反馈。

mysql好好的突然出现了 ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server 错误

折腾了我一晚上,竟然是因为它……

如果你mysql服务器所在本机能够进入,那么恭喜你,网上一搜,第一页的99%答案,就能解决你的问题

原因是mysql服务器 默认只允许 localhost 也就是 127.0.0.1 连接

sql
# 使用 mysql 数据库
use mysql;
# 允许任何ip访问
update user set host = '%' where user ='root';
# 刷新权限
flush privileges;
123456

出现了 ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061),是因为mysql服务未启动

出现了 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES),是因为密码错误

不同的错误信息对应了太多的解决方案,而其中一个解决方案又可能是导致其他问题出现的原因




看到这里的话,说明很不幸,出现这个问题的原因有点“难以启齿”,因为出现的时候我也不知道,很懵逼,哈哈哈,就很突然的样子……

但是仔细一想,是不是对数据库有过什么操作,比如当使用可视化界面时,出现了某个警示弹框,没有仔细确认,直接按下了 Enter 键,亦或者在命令行中,也是一个 Enter 键好手。

定位MySQL存储位置

假设你的mysql服务名称就是 mysql

获取MySQL配置路径

  1. 进入 计算机管理 - 服务和应用程序 - 服务,查找mysql,在属性中查看 defaults-file 文件路径
  2. 进入注册表,Win + R ,输入 regedit ,路径中输入 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\mysql 查看 defaults-file 文件路径
  3. 通过powershell,输入 Get-Item "HKLM:\SYSTEM\CurrentControlSet\Services\MySQL\" 查看 defaults-file 文件路径

问题排查

不论如何输入 mysql -uroot -p 和密码,总是出现 ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server ,即使按照网上方案所说,增加host参数,mysql -u root -h 192.168.X.X -p,也无济于事

从结论来推理写文章的思路,真是莫名的心疼自己。只要数据还在,出现的问题就不是问题

暂时不用重装mysql

先停掉mysql服务

powershell
# 以管理员模式打开powershell命令窗口
net stop mysql # mysql:注册的服务名

# 确认是否停止成功
Get-Service mysql # 出现以下信息
Status   Name               DisplayName
------   ----               -----------
Stopped  MySQL              mysql
# or
net stop mysql # 提示信息,没有启动 MySQL 服务。
12345678910

然后执行

powershell
# 查看是否有报错信息
mysqld --console
12

重点排查出现连续[ERROR]行的第一行出现的英文

  1. 出现 The innodb_system data file 'ibdata1' must be writable

    如果mysql服务正在运行,那么该提示是正常的,因为服务正在运行,是不被允许修改的。

    如果服务没有启动的话,在mysql存储数据的data目录下找到ibdata1文件,修改其权限

  2. 出现 Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'

    是因为my.ini配置文件中使用了skip-grant-tables,这里我们先去掉,没有的话忽略

  3. 出现 TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS

    确认my.ini配置文件中没有设置 shared-memory,这里我们先去掉,没有的话忽略

  4. 出现 Column count of mysql.user is wrong. Expected 51, found 8. The table is probably corrupted

    大概率是mysql的user表出了问题

  5. 出现其他问题,请出门左转,解决之后再回来

如果出现 MySQL 服务无法启动。 ,先确认my.ini配置文件中不包含 skip-grant-tables 和其他在出现问题后添加的多余的选项,然后确认执行 mysqld --console 之后,有按 Ctrl + C终止进程,double两次哦!

重新启动mysql服务,现在就可以正常启动了

解决方案

当执行 mysqld --console 没有出现其他错误的时候,比如

powershell
mysqld --console
# 出现以下信息
2021-04-11T09:45:03.994427Z 0 [System] [MY-010116] [Server] C:\scoop_soft\apps\mysql\current\bin\mysqld.exe (mysqld 8.0.23) starting as process 28084
2021-04-11T09:45:04.040213Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-11T09:45:04.043538Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2021-04-11T09:45:04.043884Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2021-04-11T09:45:04.044183Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2021-04-11T09:45:04.044743Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-04-11T09:45:04.045098Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-04-11T09:45:04.046027Z 0 [System] [MY-010910] [Server] C:\scoop_soft\apps\mysql\current\bin\mysqld.exe: Shutdown complete (mysqld 8.0.23)  MySQL Community Server - GPL.
12345678910

现在我们需要的是允许无密码登录

在my.ini配置文件中加上 skip-grant-tablesshared-memory ,mysql8需要添加这两行

ini
=== line-highlight
data-line: 6,7
===
[mysqld]
datadir=C:/scoop_soft/persist/mysql/data
skip-grant-tables
shared-memory
[client]
port=3306
123456789

然后我们再切回到powershell窗口

powershell
# 重启mysql服务
net stop mysql
net start mysql

# 进入mysql
mysql -u -p

# 进入之后
use mysql;
# 查看user表结构
describe user;
select host,user from user;
# 如果出现empty或者对它有些熟悉的话,说明mysql的user表被干掉了
# 此时可退出mysql环境,通过mysqldump命令进行其他表备份
exit
# 单数据库备份
mysqldump -uroot -p --databases <数据库名> > db.sql # db.sql是要保存文件的路径
# 多数据库备份
mysqldump -uroot -p --databases <数据库名1> <数据库名2> > db1_2.sql
# 多表备份
mysqldump -uroot -p --databases <数据库名> --tables <表名1> <表名2> > db_tb1_tb2.sql
# 所有数据库备份
mysqldump -uroot -p --all-databases > all.sql
1234567891011121314151617181920212223

如何恢复呢?

通过上面的mysqldump备份命令,从其他相同mysql版本拷贝一份mysql的user表过来

sql
# 进入mysql
mysql
use mysql;
source <user表文件路径>;
# 然后查看表结构,是否有变化
describe user;
123456

skip-grant-tables 模式下是不允许执行创建用户等操作的

mysql官方提供了一种当忘记密码时重置密码的方式,我们可以在这里执行其他的操作

在根目录或者哪个路径创建一个mysql-init.txt,路径主要是为了方便填写,在mysql-init.txt文件中输入以下内容

sql
CREATE USER 'pianxiangjishu'@'%' IDENTIFIED BY 'pianxiangjishu';
grant all privileges on *.* to 'pianxiangjishu'@'%' with grant option;
flush privileges;
123

作用说明

sql
# 创建账户(% 代表所有IP)
CREATE USER 'pianxiangjishu'@'%' IDENTIFIED BY 'pianxiangjishu';
# 赋予权限,with grant option这个选项表示该用户可以将自己拥有的权限授权给别人
grant all privileges on *.* to 'pianxiangjishu'@'%' with grant option;
# 改密码&授权超用户,flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里
flush privileges;
123456

接着在powershell窗口执行

powershell
mysqld --defaults-file="C:\\scoop_soft\\apps\\mysql\\current\\my.ini" --init-file="D:\\powershell\\mysql-init.txt" --console
1

如果没有类似 Operation CREATE USER failed for 'root'@'localhost'. 类似报错信息的话,到这一步说明已经完成99%了

将my.ini配置文件中的skip-grant-tablesshared-memory去掉或者注释掉,重启mysql服务

赶紧使用新创建的用户登录吧!

powershell
# 进入mysql
mysql -upianxiangjishu -ppianxiangjishu
12
sql
show databases;
# 出现以下信息
+--------------------+
| Database           |
+--------------------+
| blog               |
| blog_back          |
| graduation         |
| information_schema |
| mysql              |
| performance_schema |
| scrapy             |
| sys                |
+--------------------+
8 rows in set (0.03 sec)
123456789101112131415

看到这的时候,我觉得我又可以了,哈哈哈哈哈哈

查看mysql的用户表

powershell
use mysql;
select host,user from user;
12

你会发现只有pianxiangjishu一个用户,这时使用本机navicat连接,发现[ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist报错

sql
# 如果存在,删除
DROP USER 'mysql.infoschema'@'localhost';
# 新建,password修改为自己定义的
CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'password';
# 授权
GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;
123456

接着发现navicat可以连接了,远程也可以了,嗯,我也好了

总结

是在navicat中使用的时候出现的,那么问题也是出现在navicat中,因为是user表的问题,此前一直没有问题,然而前两天将几年前的一个项目的数据库导了进来,其中就有一个user表,先是怀疑是这里问题,后来又在skip-grant-tables模式下查看了mysql的user表结构,再次确认是mysql中的user表被覆盖掉了,再次体现了数据库表前缀的重要性

解决思路总结

powershell
# 查看错误信息,并解决
mysqld --console
# 通过免密登录mysql,进一步确认问题所在
# 在my.ini配置文件中写入skip-grant-tables和shared-memory
# 重启服务
# 进入mysql
mysql
# 如果无法进入mysql的话,使用该方式可以执行在skip-grant-tables模式下无法执行的sql语句
mysqld --defaults-file="<my.ini文件所在位置>" --init-file="<mysql-init.txt文件所在路径>" --console
123456789

写作最后


1

评论 (0)

取消