mysql 安装和远程连接问题

mysql 安装和远程连接问题

最近把arm下的web环境配置好真是麻烦，遇到了很多之前没用过的软件升级后的版本问题

这里把mysql8配置记录一下

apt install mysql-server安装

之后主要分为两大步

输入mysql回车就可以进入mysql数据库

进去没有密码

先选择数据库 use mysql

第一步：主要目的是把mysql.user表改成下面这样

mysql> select host,user,plugin from user;
|-----------±-----------------±----------------------|
| host | user | plugin |
±----------±-----------------±----------------------+
| % | root | mysql_native_password |
| localhost | debian-sys-maint | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
±----------±-----------------±----------------------+
5 rows in set (0.00 sec)

1. update user set host=’%’ where user=‘root’;–修改host
2. update user set plugin=‘mysql_native_password’ where user=‘root’;–修改plugin
3. ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘你的密码’;–修改密码
4. alter user ‘root’@’%’ identified by ‘密码’ password expire never;–设置密码不过期

第二步 开启远程登录（这也是让我麻烦的地方）

网上搜索结果总结

1. alter user ‘root’@’%’ identified with mysql_native_password by ‘密码’;–开启远程登录
2. shell命令打开端口（没有ufw防火墙就不用，其他防火墙没用过，这个简单方便）：ufw allow 3306

但是连不上，找了很多地方，没找到，尝试用nmap扫描发现问题

这是mysql服务器nmap 127.0.0.1扫描结果

Host is up (0.000043s latency).
Not shown: 994 closed ports
PORT STATE SERVICE
21/tcp open ftp
22/tcp open ssh
80/tcp open http
631/tcp open ipp
3306/tcp open mysql
8080/tcp open http-proxy

这是连接主机扫描结果

Host is up (0.016s latency).
Not shown: 993 filtered ports
PORT STATE SERVICE
21/tcp open ftp
22/tcp open ssh
80/tcp open http
443/tcp closed https
631/tcp closed ipp
3306/tcp closed mysql
8080/tcp open http-proxy

显然，3306端口并没有对外开放的服务

找一下mysql配置文件

find / -name mysql.cnf&find / -name mysqld.cnf

找到了这几个地方：/etc/mysql/conf.d/mysql.cnf
/etc/mysql/mysql.conf.d/mysql.cnf
/etc/mysql/mysql.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf

查看mysql配置文件mysql.cnf/mysqld.cnf，发现只有/etc/mysql/mysql.conf.d/mysqld.cnf存在配置数据（可能某些版本不同，我的是这个）

某些地方加上注释某些地方取消，改成这样，其他地方没动

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket  = /var/run/mysqld/mysqld.sock
port            = 3306
datadir = /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
tmpdir          = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#mysqlx-bind-address    = 127.0.0.1
#

service mysql restart

使用 nmap ip 扫描

PORT STATE SERVICE
21/tcp open ftp
22/tcp open ssh
80/tcp open http
443/tcp closed https
631/tcp closed ipp
3306/tcp open mysql
8080/tcp open http-proxy

3306端口有服务！
OK！连接成功