话不多说,直接上问题: Host 'x.x.x.x' is not allowed to this MYSQL server
原因是:未给root用户赋予远程登录权限
后来网上找了资料,用如下指令
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
解释一下该命令,该命令是对用户的赋权操作
- GRANT:赋权命令
- ALL PRIVILEGES:当前用户的所有权限
- ON:介词
- *.*:当前用户对所有数据库和表的相应操作权限
- TO:介词
- ‘root’@’%’:权限赋给root用户,所有ip都能连接
- 连接时输入密码,密码为123456
- WITH GRANT OPTION:允许级联赋权
但是此时又报了一个错误
提示的错误信息如下:You are not allowed to create a user with GRANT;
产生用户不能授权的原因是mysql 数据库中user 表中的特定用户(root) 的host 的属性值为localhost.
解决办法如下:
# 使用mysql 数据库
mysql > use mysql;
# 特定用户的host 修改
mysql > update user set host='%' where user='root';
# 指定用户的授权
mysql > grant all privileges on test.* to root@'%'
最后执行
FLUSH PRIVILEGES
重新连接,成功!
IDEA连接数据库问题:
“Unable to load authentication plugin ‘caching_sha2_password”
网上查了资料说是
主要原因8.x版本的验证模块和之前版本不同:
5.x版本是:default_authentication_plugin=mysql_native_password
8.x版本就是:default_authentication_plugin=caching_sha2_password
解决办法是将Maven中的MySQL换成8.x版本
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
但是还是不行,于是尝试了其他的解决方法,将加密规则重新改一下,打开MySQL改密码
在修改密码规则之前我随便修改了一下密码,如下
结果却跟我说出现了
“ERROR 1819 (HY000): Your password does not satisfy the current policy requirements”
于是要先修改密码规则
1、查看 mysql 初始的密码策略
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
2、设置密码的长度为6
mysql> select @@validate_password.length;
+----------------------------+
| @@validate_password.length |
+----------------------------+
| 6 |
+----------------------------+
3、 设置密码的验证强度等级
mysql> select @@validate_password.policy;
+----------------------------+
| @@validate_password.policy |
+----------------------------+
| LOW |
+----------------------------+
4、再次查看
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 6 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | LOW |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------+
修改完毕,于是满心欢喜回来重新修改密码结果却又出现了这种情况
mysql> alter user 'root'@'localhost' identified by '123456' password expire never;
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'
我去,这也太坑了,后来网上看到一篇博客,说是
要看自己的root用户对应的地址,默认是localhost,但是我前面改成了%(可以重新往上翻),所以要将“localhost”改成“%”
查看方式如下:
mysql> use `mysql`;
Database changed
mysql> select Host, User from user;
+-----------+------------------+
| Host | User |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+------------------+
4 rows in set (0.00 sec)
再运行一开始的
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234556';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
再次连接数据库,成功!