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)