mysql好好的突然出现了 ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server 错误
折腾了我一晚上，竟然是因为它……
如果你mysql服务器所在本机能够进入，那么恭喜你，网上一搜，第一页的99%答案，就能解决你的问题
原因是mysql服务器 默认只允许 localhost 也就是 127.0.0.1 连接
# 使用 mysql 数据库
use mysql;
# 允许任何ip访问
update user set host = '%' where user ='root';
# 刷新权限
flush privileges;出现了 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配置路径
- 进入
计算机管理-服务和应用程序-服务，查找mysql，在属性中查看defaults-file文件路径 - 进入注册表，
Win+R，输入regedit，路径中输入HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\mysql查看defaults-file文件路径 - 通过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命令窗口
net stop mysql # mysql：注册的服务名
# 确认是否停止成功
Get-Service mysql # 出现以下信息
Status Name DisplayName
------ ---- -----------
Stopped MySQL mysql
# or
net stop mysql # 提示信息，没有启动 MySQL 服务。然后执行
# 查看是否有报错信息
mysqld --console重点排查出现连续[ERROR]行的第一行出现的英文
-
出现
The innodb_system data file 'ibdata1' must be writable如果mysql服务正在运行，那么该提示是正常的，因为服务正在运行，是不被允许修改的。
如果服务没有启动的话，在mysql存储数据的data目录下找到ibdata1文件，修改其权限
-
出现
Plugin mysqlx reported: 'All I/O interfaces are disabled, X Protocol won't be accessible'是因为my.ini配置文件中使用了
skip-grant-tables，这里我们先去掉，没有的话忽略 -
出现
TCP/IP, --shared-memory, or --named-pipe should be configured on NT OS确认my.ini配置文件中没有设置
shared-memory，这里我们先去掉，没有的话忽略 -
出现
Column count of mysql.user is wrong. Expected 51, found 8. The table is probably corrupted大概率是mysql的user表出了问题
-
出现其他问题，请出门左转，解决之后再回来
如果出现 MySQL 服务无法启动。 ，先确认my.ini配置文件中不包含 skip-grant-tables 和其他在出现问题后添加的多余的选项，然后确认执行 mysqld --console 之后，有按 Ctrl + C终止进程，double两次哦！
重新启动mysql服务，现在就可以正常启动了
解决方案
当执行 mysqld --console 没有出现其他错误的时候，比如
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.现在我们需要的是允许无密码登录
在my.ini配置文件中加上 skip-grant-tables 和 shared-memory ，mysql8需要添加这两行
=== line-highlight
data-line: 6,7
===
[mysqld]
datadir=C:/scoop_soft/persist/mysql/data
skip-grant-tables
shared-memory
[client]
port=3306然后我们再切回到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如何恢复呢？
通过上面的mysqldump备份命令，从其他相同mysql版本拷贝一份mysql的user表过来
# 进入mysql
mysql
use mysql;
source <user表文件路径>;
# 然后查看表结构，是否有变化
describe user;在 skip-grant-tables 模式下是不允许执行创建用户等操作的
mysql官方提供了一种当忘记密码时重置密码的方式，我们可以在这里执行其他的操作
在根目录或者哪个路径创建一个mysql-init.txt，路径主要是为了方便填写，在mysql-init.txt文件中输入以下内容
CREATE USER 'pianxiangjishu'@'%' IDENTIFIED BY 'pianxiangjishu';
grant all privileges on *.* to 'pianxiangjishu'@'%' with grant option;
flush privileges;作用说明
# 创建账户(% 代表所有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;接着在powershell窗口执行
mysqld --defaults-file="C:\\scoop_soft\\apps\\mysql\\current\\my.ini" --init-file="D:\\powershell\\mysql-init.txt" --console如果没有类似 Operation CREATE USER failed for 'root'@'localhost'. 类似报错信息的话，到这一步说明已经完成99%了
将my.ini配置文件中的skip-grant-tables和shared-memory去掉或者注释掉，重启mysql服务
赶紧使用新创建的用户登录吧！
# 进入mysql
mysql -upianxiangjishu -ppianxiangjishushow databases;
# 出现以下信息
+--------------------+
| Database |
+--------------------+
| blog |
| blog_back |
| graduation |
| information_schema |
| mysql |
| performance_schema |
| scrapy |
| sys |
+--------------------+
8 rows in set (0.03 sec)看到这的时候，我觉得我又可以了，哈哈哈哈哈哈
查看mysql的用户表
use mysql;
select host,user from user;你会发现只有pianxiangjishu一个用户，这时使用本机navicat连接，发现[ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist报错
# 如果存在，删除
DROP USER 'mysql.infoschema'@'localhost';
# 新建，password修改为自己定义的
CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'password';
# 授权
GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;接着发现navicat可以连接了，远程也可以了，嗯，我也好了
总结
是在navicat中使用的时候出现的，那么问题也是出现在navicat中，因为是user表的问题，此前一直没有问题，然而前两天将几年前的一个项目的数据库导了进来，其中就有一个user表，先是怀疑是这里问题，后来又在skip-grant-tables模式下查看了mysql的user表结构，再次确认是mysql中的user表被覆盖掉了，再次体现了数据库表前缀的重要性
解决思路总结
# 查看错误信息，并解决
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
评论 (0)