mysql80 遇到 1130 Host ‘localhost’ is not allowed to connect to this MySQL server
参考:Mysql 8.0.13忘记密码 重置密码
问题产生的原因:期望局域网同事能访问自己本地的mysql库表
做了如下操作:
select host, user, pligin from user;
查看了系统mysql库-user表的加密规则,默认为:update user set plugin='mysql_native_password'; FLUSH PRIVILEGES;
修改了所有的规则为mysql_native_password
,并刷新MySQL的系统权限相关表,GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
开放权限同事仍连不上我的库,显示我这边不允许连接,怀疑是我本地没开远程权限,重启了mysql。出现问题:Host 'localhost' is not allowed to connect to this MySQL server
,我自己都连不上本地的mysql。
问题解决步骤:
停止mysql服务service mysql stop
orother method
绕过密码自己访问数据库,cmd 在安装路径中运行mysqld --console --skip-grant-tables --shared-memory
,此时会停在这里新开cmd 输入登录命令:mysql -u root -p,输入密码处直接回车还原mysql-user表中的加密规则use mysqlupdate user set authentication_string=''where user='root';
清除登录密码,quit-mysql,关闭momom
use mysql;update user set plugin='caching_sha2_password';quit;
关闭以-console --skip-grant-tables --shared-memory
启动的MySQL服务密码已经置空,所以无密码状态登录MySQL,输入登录命令:mysql -u root -p,输入密码处直接回车设置新密码,quit-mysql,无密码登录后,修改密码:
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '新密码';
说什么0 row生效??what,查user发现对应的host是%
,然后这样
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY '新密码';
使用新密码登录。mysql -uroot -p****
bingo
bingo。。。
ubuntu 忘记密码-调整办法
参考:ubuntu下MySQL忘记密码重置方法
vim /etc/mysql/mysql.conf.d/f
add new lineskip-grant-tables
service mysql restart
enter mysql in terminalmysql
change to mysqluse mysql;
modify pswdupdate user set authentication_string='' where user='root';
可通过select user, host from mysql.user;
查询 user和host,便于精准修改。update user set authentication_string='' where user='root' and host='%';
quit;
vim /etc/mysql/mysql.conf.d/f
注释掉skip-grant-tables
重启mysql服务service mysql restart
mysql > use mysql
修改新密码,ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY '000000';
可通过select user, host from mysql.user;
查询 user和host,便于精准修改。ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY '000000';
quit;
bingo
bingo。。。