mysql8指定远程连接及权限修改（windows）

root权限丢失，重新修改root权限

mysql8免密登录mysql

mysqld --console --skip-grant-tables --shared-memory

查看所有权限

select * from mysql.user where User=root and Host=localhostG;

修改权限

update mysql.user set Insert_priv=Y,Update_priv=Y,Delete_priv=Y where User=root and Host=localhost;

刷新权限并退出

flush privileges;

exit；

重启mysql

指定远程连接，并对其分配权限

# 查询用户表
select user,host,plugin from user;

# 对指定ip创建连接用户名和密码
create user root126@192.168.0.126 identified by root126;

# 分配权限(只读)
grant select on *.* to root126@192.168.0.126 with grant option;
# 分配权限(增删改查)
grant select,insert,update,delete on *.* to root126@192.168.0.126 with grant option;

# navicat连接报错，修改密码连接方式为mysql_native_password
update mysql.user set plugin=mysql_native_password where User=root119 and Host=192.168.0.119;

UPDATE user SET authentication_string="" WHERE user=root119;

ALTER USER root119@192.168.0.119 IDENTIFIED BY root119; 

flush privileges;