1、遇到无法连接,如图所示:
(1)我们尝试查看数据库,执行命令:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| metastore |
| mysql |
| oozie |
| performance_schema |
| staff |
| sys |
+--------------------+
8 rows in set (0.01 sec)
(2)查看数据库的表显示出来:
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| tb_emp1 |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
32 rows in set (0.02 sec)
(3)系统数据库下的Mysql数据表下,若查询表user,如果,执行命令:
select * from user;
会出现乱码,如图所示:
为了直观清楚观看,我们可以这样执行:
mysql> select * from user\G;
如图所示:
(4)这时,我们需要插入一条语句,可能会出错
mysql> select host,user,password from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
排查这个问题,我们可以从mysql中表user的结构看,直接执行:
mysql> describe user;
我们需要注意的是,从图中可以发现:
password替换成authentication_string,所以直接执行:select host,user,password from user;
就会出现错误:
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
正确的执行:
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
3 rows in set (0.00 sec)
(5)接下来,我们还需要对执行权限命令:
mysql> grant all privileges on *.* to root@'%' identified by '123456';
发生另一个错误:
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 | OFF |
| 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 |
+--------------------------------------+--------+
7 rows in set (0.14 sec)
由此可见: validate_password_length | 8 |:与自己自定义的密码不符合它的要求规范,我们现在需要把它默认的规范改为6位!
(2)首先需要设置密码的验证强度等级,设置 validate_password_policy 的全局参数为 LOW 即可
mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)
设置为6位数的密码
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
(3)再次查看 mysql 初始的密码策略
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| 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 |
+--------------------------------------+-------+
7 rows in set (0.00 sec)
(5)现在可以为 mysql 设置简单密码了,只要满足六位的长度即可!
mysql> grant all privileges on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
(5)我们还需要做,再次执行权限命令:
mysql> grant all privileges on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
(6)最后,再次查看权限信息,直接执行:
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+---------------+-------------------------------------------+
4 rows in set (0.00 sec)
多了一个%号
注意授权后必须FLUSH PRIVILEGES;
否则无法立即生效。
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(7)工作准备完毕之后,我们就可以连接window的客服端工具navicat,提示要注意的是:
关闭防火墙:systemctl stop firewalld.service
[root@hadoop105 conf]# systemctl stop firewalld.service
我们就可以做进行连接: