本文在redhat8系统(192.168.100.100)中安装mysql8,并在优麒麟中通过navicat远程访问成功。
安装命令:yum install mysql-server
一、mysql文件的路径
redhat8通过yum安装mysql8后的配置文件是通过/etc/my.cnf
调用的/etc/my.cnf.d/
目录,其中的mysql-server.cnf
为主配置文件,mysql-default-authentication-plugin.cnf
为身份验证文件
mysql-server.cnf
初始参数:
[root@hollowman ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql #数据目录
socket=/var/lib/mysql/mysql.sock #socket
log-error=/var/log/mysql/mysqld.log #日志文件
pid-file=/run/mysqld/mysqld.pid #PID文件
二、mysql初始化
初始安装的mysql8并没有密码:
[root@hollowman ~]# cat /var/log/mysql/mysqld.log | grep password
2021-01-24T15:42:44.955519Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
安全初始化,设置Root密码和进行安全设置
[root@hollowman ~]#mysql_secure_installation
Enter current password for root (enter for none): #输入当前 root 用户密码,安装完后默认为空,可直接回车,除非已经设置过密码。
Set root password? [Y/n] y > > #是否设置root密码?
Remove anonymous users? [Y/n] y #是否要移除掉匿名用户?
Disallow root login remotely? [Y/n] y #是否禁止root远程登陆?
Remove test database and access to it? [Y/n] y #是否删除test数据库?
Reload privilege tables now? [Y/n] y #是否重新加载权限?
也可以空密码进入后,直接进行密码修改:
alter user 'root'@'localhost' identified with mysql_native_password by '密码`;
[root@hollowman mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, 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> alter user 'root'@'localhost' identified with mysql_native_password by 'redhat';
Query OK, 0 rows affected (0.10 sec)
三、添加可以供navicat访问的用户
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create user 'admin'@'%' identified by 'redhat';
Query OK, 0 rows affected (0.10 sec)
mysql> grant all on *.* to 'admin'@'%';
Query OK, 0 rows affected (0.04 sec)
mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | admin | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
| localhost | root | mysql_native_password |
+-----------+------------------+-----------------------+
5 rows in set (0.01 sec)
四、关于navicat远程连接错误提示的解决办法
navicat连接数据库提示如下错误信息:
2003-can’t connect to mysql_server on ‘192.168.100.100’(113 “没有找到主机的路由”)
问题判断:
redhat防火墙在捣鬼,redhat防火墙有两个,iptables(redhat8以前版本),firewalld(redhat7以后版本)
iptables关闭防火墙规则(redhat8默认未安装iptables):
iptables -F #因为redhat8未装iptables,此处可成功,但无意义
service iptables save #因为redhat8未安装iptables,所以此处失败,只是体验一下关闭防火墙的过程,无关紧要
firewalld配置方法:
第一种方法:直接关闭firewall防火墙
[root@hollowman ~]# systemctl stop firewalld
[root@hollowman ~]# systemctl disabled firewalld
第二种方法:判断firewall应该是禁止mysql服务了,添加mysql服务即可
[root@hollowman ~]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens160
sources:
services: cockpit dhcpv6-client ssh
ports:
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
[root@hollowman ~]# firewall-cmd --add-service=mysql --permanent --zone=public
success
[root@hollowman ~]# firewall-cmd --reload
success
[root@hollowman ~]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens160
sources:
services: cockpit dhcpv6-client mysql ssh
ports:
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
navicat连接测试截图: