-
远程连接报错:
**Host '192.168.1.100' is not allowed to connect to this MySQL server**
说明所连接的用户帐号没有远程连接的权限,只能在本机(localhost)登录。
首先我以为是防火墙设置问题,在防火墙加了3306的开放端口,但是没有用
发现是数据库访问权限问题,要修改访问权限,将与root绑定的IP地址设置为**%**用于匹配所有IP地址mysql>use mysql; mysql>update user set host = '%' where user ='root'; mysql> grant all privileges on root.* to 'root'@'%' ;
我用的是mysql8.0的版本,直接运行
grant all priviliges on *.* to 'root'@'%' identified by 'root' with grant option
就会报错:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'priviliges on *.* to 'root'@'%' identified by 'root' with grant option' at line 1
因为MySQL8.0的版本已经将创建账号和赋予权限分开,只能分开执行相关语句 -
ok解决完访问权限问题,连接时又继续报错:
SQLSTATE [HY000] [2054]服务器请求的客户端未知
,发现因为在MySQL8.0+版本中,默认身份验证插件已经从’mysql_native_password’更改为’caching_sha2_password’,‘root’@'localhost’管理帐户默认使用’caching_sha2_password’身份验证插件,使用以下语句更改身份插件重置密码mysql> alter user root identified with mysql_native_password by 'yourpassword';
-
感觉可以使用了,结果。。。。。连接继续报错:
The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support. The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
新版本更新真的让人发愁啊,搜索发现是因为安装mysql的时候时区设置的不正确,
mysql默认的是美国的时区,而我们中国大陆要比他们迟8小时,采用+8:00格式,我是采取在MySQL中执行以下语句解决
mysql mysql> set global time_zone='+8:00';
还有一种办法是在MySQL配置文件是my.ini文件中[mysqld]节点在节点下加上下面这句话default-time-zone='+08:00'
更改完总算是提示连接成功了,最近碰到的都是版本更新的坑,希望跳过的坑不要再踏入了,由于操作匆忙,未能截图记录,仅以文字代码记下,以上。
4.2059 - Authentication plugin 'caching_sha2_password' cannot be loaded:
use mysql;
mysql> select user,plugin from user where user='root';
+------+-----------------------+
| user | plugin |
+------+-----------------------+
| root | caching_sha2_password |
+------+-----------------------+
1 row in set (0.01 sec)
mysql> alter user 'root'@'%' identified with mysql_native_password by 'yourpassword';
Query OK, 0 rows affected (0.01 sec)