mysql 8.0禁止远程连接_MySQL 8.0 不能被远程连接的解决办法及修改密码、创建用户、授予所有权...

MySQL能在本地能连接上，不能被远程连接的解决办法：

进入数据库，命令

# mysql -u root -p

# 输入mysql登录密码

一下命令均在mysql环境下执行。

use mysql;

SELECT user, host, plugin from mysql.user;

//查看用于远程访问的mysql用户host的权限，%表示允许所有机器访问。若host为127.0.0.1/localhost，那么这个用户就只能本机访问，则需要将host改为%

plugin:

mysql新版本(8.0以上)将root用户使用的加密方式plugin更新成caching_sha2_password

5.x是mysql_native_password

update user set host='%' where user='root';

//若输出ERROR 1130: Host '192.168.1.3' is not allowed to connect to this MySQL ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

//执行

flush privileges;

//再查看权限

SELECT user, host from mysql.user;

OK.可以远程登录啦！

---------------------------------------------------------------------------------------------------------------------------------

MySQL修改密码、创建用户、授予所有权

修改密码：

alter user 'root'@'localhost' identified by '123456';

创建用户与授权：

create user 'lgl'@'%' identified by '123456';

grant all privileges on *.* to lgl@'%';

---------------------------------------------------------------------------------------------------------------------------------

MySQL8.0的一些疑难杂症

出现

Mysql 8.0.11 出现1251- Client does not support authentication protocol 错误

OR

this authentication plugin is not supported

为了使lgl用户能通过远程登录到新版8.0的MySQL数据库

alter user 'lgl'@'%' identified with mysql_native_password by '123456';

---------------------------------------------------------------------------------------------------------------------------------

设置简单密码过程

出现

mysql> alter user 'root'@'localhost' identified by '123456';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> set global validate_password_policy=0;

ERROR 1193 (HY000): Unknown system variable 'validate_password_policy'

mysql> set global validate_password_length=1;

ERROR 1193 (HY000): Unknown system variable 'validate_password_length'

解决方法

MySQL 5.x

mysql> set global validate_password_policy=0;

mysql> set global validate_password_length=1;

MySQL 8.0

mysql> set global validate_password.policy=0;

mysql>  set global validate_password.length=1;

mysql> alter user 'root'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.05 sec)