无法让 mariadb 绑定到本地主机

无法让 mariadb 绑定到本地主机

在我的远程 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

相关内容