在我的远程 Ubuntu 20.04 服务器上,我有一个正在运行的 mariadb 实例。我可以通过 SSH 使用用户访问此服务器myuser
,并在服务器上打开 CLI 客户端以打开数据库提示。
$ mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 69
Server version: 10.3.37-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
但不在本地主机上:
$ mysql -u root -h 127.0.0.1
ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)
我也无法通过 SSH 远程访问该数据库。
服务器输出
状态为活跃。
sudo systemctl status mariadb
mariadb.service - MariaDB 10.3.37 数据库服务器
已加载:已加载(/lib/systemd/system/mariadb.service;已启用;供应商预设:已启用)
活跃:自 2023-01-06 星期五 10:25:46 UTC 以来处于活跃状态(正在运行);2 天前
此命令不返回任何内容。
sudo lsof -i -P -n | grep sql
此命令仅返回一个套接字:
sudo netstat -anpe | grep sql
unix 2 [ ACC ] STREAM LISTENING 269765 39044/mysqld/run/mysqld/mysqld.sock
防火墙设置:
$ sudo ufw status
Status: active
To Action From
-- ------ ----
22/tcp ALLOW Anywhere
80 ALLOW Anywhere
443 ALLOW Anywhere
3306 ALLOW Anywhere
22/tcp (v6) ALLOW Anywhere (v6)
80 (v6) ALLOW Anywhere (v6)
443 (v6) ALLOW Anywhere (v6)
3306 (v6) ALLOW Anywhere (v6)
这些是该文件的设置:/etc/mysql/mariadb.conf.d/50-server.cnf
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /run/mysqld/mysqld.pid
socket = /run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# 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
这些目录包含在配置中。
$ which mysqld
/usr/sbin/mysqld
$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
/usr/sbin/mysqld: One can only use the --user switch if running as root
2023-01-11 9:03:53 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-01-11 9:03:53 0 [Warning] Could not open mysql.plugin table. Some options may be missing from the help text
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
/etc/mysql/my.cnf
内容:
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
客户端(Sequel Pro)连接设置:
- MySQL 主机:
127.0.0.1
- 用户名:
root
- 密码: (空)
- 港口:
3306
- SSH 主机:
123.123.7.7
- SSH 用户:
myuser
- SSH 密钥:
~/.ssh/id_rsa
客户端错误信息:
Used command: /usr/bin/ssh -v -N -S none -o ControlMaster=no -o ExitOnForwardFailure=yes -o ConnectTimeout=10 -o NumberOfPasswordPrompts=3 -i /Users/myself/.ssh/id_rsa -o TCPKeepAlive=no -o ServerAliveInterval=60 -o ServerAliveCountMax=1 [email protected] -L 51130:127.0.0.1:3306
OpenSSH_8.6p1, LibreSSL 3.3.6
debug1: Reading configuration data /Users/myself/.ssh/config
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 21: include /etc/ssh/ssh_config.d/* matched no files
debug1: /etc/ssh/ssh_config line 54: Applying options for *
debug1: Authenticator provider $SSH_SK_PROVIDER did not resolve; disabling
debug1: Control socket " none" does not exist
debug1: Connecting to 123.123.7.7 [123.123.7.7] port 22.
debug1: fd 3 clearing O_NONBLOCK
debug1: Connection established.
debug1: identity file /Users/myself/.ssh/id_rsa type 0
debug1: identity file /Users/myself/.ssh/id_rsa-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_8.6
debug1: Remote protocol version 2.0, remote software version OpenSSH_8.2p1 Ubuntu-4ubuntu0.5
debug1: compat_banner: match: OpenSSH_8.2p1 Ubuntu-4ubuntu0.5 pat OpenSSH* compat 0x04000000
debug1: Authenticating to 123.123.7.7:22 as 'myuser'
debug1: load_hostkeys: fopen /Users/myself/.ssh/known_hosts2: No such file or directory
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts: No such file or directory
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts2: No such file or directory
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: algorithm: curve25519-sha256
debug1: kex: host key algorithm: ssh-ed25519
debug1: kex: server->client cipher: [email protected] MAC: <implicit> compression: none
debug1: kex: client->server cipher: [email protected] MAC: <implicit> compression: none
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: SSH2_MSG_KEX_ECDH_REPLY received
debug1: Server host key: ssh-ed25519 SHA256:asdf/LAU/Y
debug1: load_hostkeys: fopen /Users/myself/.ssh/known_hosts2: No such file or directory
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts: No such file or directory
debug1: load_hostkeys: fopen /etc/ssh/ssh_known_hosts2: No such file or directory
debug1: Host '123.123.7.7' is known and matches the ED25519 host key.
debug1: Found key in /Users/myself/.ssh/known_hosts:17
debug1: rekey out after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: rekey in after 134217728 blocks
debug1: Will attempt key: /Users/myself/.ssh/id_rsa RSA SHA256:AO6tKEjnczkQI2lrYFWbLhYpwPFTboZt3+bTd0Tg1JM explicit
debug1: SSH2_MSG_EXT_INFO received
debug1: kex_input_ext_info: server-sig-algs=<ssh-ed25519,[email protected],ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,[email protected]>
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey
debug1: Next authentication method: publickey
debug1: Offering public key: /Users/myself/.ssh/id_rsa RSA SHA256:xx+xx explicit
debug1: Server accepts key: /Users/myself/.ssh/id_rsa RSA SHA256:xx+xx explicit
debug1: read_passphrase: can't open /dev/tty: Device not configured
debug1: Authentication succeeded (publickey).
Authenticated to 123.123.7.7 ([123.123.7.7]:22).
debug1: Local connections to LOCALHOST:51130 forwarded to remote address 127.0.0.1:3306
debug1: Local forwarding listening on ::1 port 51130.
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 51130.
debug1: channel 1: new [port listener]
debug1: Requesting [email protected]
debug1: Entering interactive session.
debug1: pledge: filesystem full
debug1: Connection to port 51130 forwarding to 127.0.0.1 port 3306 requested.
debug1: channel 2: new [direct-tcpip]
debug1: client_input_global_request: rtype [email protected] want_reply 0
debug1: client_input_hostkeys: searching /Users/myself/.ssh/known_hosts for 123.123.7.7 / (none)
debug1: client_input_hostkeys: searching /Users/myself/.ssh/known_hosts2 for 123.123.7.7 / (none)
debug1: client_input_hostkeys: hostkeys file /Users/myself/.ssh/known_hosts2 does not exist
debug1: client_input_hostkeys: no new or deprecated keys from server
debug1: Remote: /home/myuser/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding
debug1: Remote: /home/myuser/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding
channel 2: open failed: connect failed: Connection refused
debug1: channel 2: free: direct-tcpip: listening port 51130 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 51133 to 127.0.0.1 port 51130, nchannels 3
期待
我认为,由于没有将 mariadb 的本地实例绑定到端口 3306 上的 localhost,因此我也无法通过 SSH 进行端口转发。我肯定缺少将地址绑定到 localhost 的配置。
我希望能够看到这一点:
$ sudo lsof -i -P -n | grep sql
mysqld 2172 mysql 21u IPv4 19184 0t0 TCP 127.0.0.1:3306 (LISTEN)
sudo netstat -anpe | grep sql
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 112 19184 2172/mysqld
unix 2 [ ACC ] STREAM LISTENING 19185 2172/mysqld /var/run/mysqld/mysqld.sock