在CentOS中默认安装有MariaDB,这个是MySQL的分支,但为了需要,还是要在系统中安装MySQL,而且安装完成之后可以直接覆盖掉MariaDB。
(1)下载并安装MySQL官方的 Yum Repository
[root@localhost mysql]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
使用上面的命令就直接下载了安装用的Yum Repository,大概25KB的样子,然后就可以直接yum安装了。
[root@localhost mysql]# yum -y install mysql57-community-release-el7-10.noarch.rpm
之后就开始安装MySQL服务器
[root@localhost mysql]# yum -y install mysql-community-server
这步可能会花些时间,安装完成后就会覆盖掉之前的mariadb。
至此MySQL就安装完成了,然后是对MySQL的一些设置。
(2)MySQL数据库设置首先启动MySQL
[root@localhost mysql]# systemctl start mysqld.service
查看MySQL运行状态,运行状态如图:
此时MySQL已经开始正常运行,不过要想进入MySQL还得先找出此时root用户的密码,通过如下命令可以在日志文件中找出密码:
[root@localhost mysql]# grep "password" /var/log/mysqld.log
2020-01-11T19:19:11.869507Z 1 [Note] A temporary password is generated for root@localhost: 6x-tiUE!IvQw
[root@localhost mysql]#
如下命令进入数据库:
[root@localhost mysql]# mysql -uroot -p
Enter password:
mysql>
输入初始密码,此时不能做任何事情,因为MySQL默认必须修改密码之后才能操作数据库:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
这里有个问题,新密码设置的时候如果设置的过于简单会报错:
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
原因是因为MySQL有密码设置的规范,具体是与validate_password_policy的值有关:
默认是1,即MEDIUM,所以刚开始设置的密码必须符合长度,且必须含有数字,小写或大写字母,特殊字符。
有时候,只是为了自己测试,不想密码设置得那么复杂,譬如说,我只想设置root的密码为123456,必须修改两个全局参数:
解决方案:
(1)首先,修改validate_password_policy参数的值
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.01 sec)
(2)validate_password_length(密码长度)参数默认为8,我们修改为1
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
(3)完成之后再次执行修改密码语句即可成功
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
退出后,重新登陆即可
mysql> exit;
Bye
[root@localhost mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 9
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
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 userG;
如图所示:
(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)
(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
我们就可以做进行连接: