起因

自己在服务器上搭了一个mysql的服务，想从本地访问服务器上的mysql服务做一些测试。发现连不上，本地测试机和服务器都在内网环境，这就奇了怪了，于是开始了debug之路

测试端口

mysql服务器

$ nmap localhost

Starting Nmap 7.40 ( https://nmap.org ) at 2021-03-30 06:50 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.0000020s latency).
Other addresses for localhost (not scanned): ::1
Not shown: 997 closed ports
PORT     STATE SERVICE
4/tcp    open  unknown
80/tcp   open  http
3306/tcp open  mysql

本地机器

$ ping 10.90.17.34
正在 Ping 10.90.17.34 具有 32 字节的数据:
来自 10.90.17.34 的回复: 字节=32 时间=26ms TTL=52

$ telnet 10.90.17.34 3306
Trying 10.90.17.34...
telnet: Unable to connect to remote host: Connection refused

结论：服务器的3306端口开启，本地能够ping通mysql服务器，但服务器拒绝服务

修改mysql配置文件

查看mysql监听的端口和地址发现，监听地址为127.0.0.1，mysql的配置文件的bind-address = 127.0.0.1`代表只允许本机访问。所以要修改配置文件监听地址

$ netstat -napt
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      45354/mysqld

找配置文件网上的教程五花八门，说法不一，下面亲自试了一个比较通用的方法

$ which mysqld
/usr/sbin/mysqld

$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
2021-03-30  7:05:31 139722083077504 [Note] Plugin 'FEEDBACK' is disabled.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

可以看到，mysql的配置文件时按照上面的顺序加载的，只需要挨个查看即可，这时候又可能会出现别的幺蛾子，挨个查看发现，/etc/my.cnf和~/.my.cnf为空。/etc/mysql/my.cnf的内容如下

$ cat /etc/mysql/my.cnf
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

可以看到，/etc/mysql/my.cnfinclude了其他文件，并不是真正的配置文件，接下来继续深入查看它所include的文件即可。最后在/etc/mysql/mariadb.conf.d找到了50-server.cnf

/etc/mysql/mariadb.conf.d# ls
50-client.cnf  50-mysql-clients.cnf  50-mysqld_safe.cnf  50-server.cnf

把bind-address直接注释，再重启即可【尝试改为0.0.0.0，但是还不能访问，127.0.0.1和0.0.0.0的区别见这篇文章】

$ service mysql restart
$ netstat -napt
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp6       0      0 :::3306                 :::*                    LISTEN      182625/mysqld

mysql权限控制

经过上面一系列操作，本地倒是能访问3306端口了，但是会报错

\Host 'xxxxxxxxxxx' is not allowed to connect to this MariaDB serverConnection closed by foreign host.

原因是mysql自身有权限控制

$ mysql -hlocalhost -uroot

MariaDB [(none)]> SELECT host FROM mysql.user WHERE User = 'root';
+-----------+
| host      |
+-----------+
| localhost |
+-----------+
1 row in set (0.00 sec)

如果想将需要添加要授予访问权限的IP地址，执行

CREATE USER 'root'@'ip_address' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip_address';
FLUSH PRIVILEGES;

如果您确实希望任何/所有系统都通过root连接，请使用%通配符授予访问权限

CREATE USER 'root'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

然后，终于成功了～～～