Navicat MySQL连接Linux下MySQL的问题解决方案

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

我们就可以做进行连接:
在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值