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)