On my remote Ubuntu 20.04 server I have an instance of mariadb running.
I can access this server through SSH with user myuser and open run the CLI-client on the server to open a prompt for the database.
$ 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)]>
But not on the localhost:
$ mysql -u root -h 127.0.0.1
ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)
I also cannot access this database remotely over SSH.
Server output
Status is active.
sudo systemctl status mariadb
mariadb.service - MariaDB 10.3.37 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active : active (running) since Fri 2023-01-06 10:25:46 UTC; 2 days ago
This command, return nothing.
sudo lsof -i -P -n | grep sql
This command, returns only a socket:
sudo netstat -anpe | grep sql
unix 2 [ ACC ] STREAM LISTENING 269765 39044/mysqld/run/mysqld/mysqld.sock
Firewall settings:
$ 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)
And these are the settings for the file: /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
These directories are included in the configuration.
$ 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 contents:
[client-server]
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
Client (Sequel Pro) connection settings:
- MySQL Host:
127.0.0.1 - Username:
root - Password: (empty)
- Port:
3306 - SSH Host:
123.123.7.7 - SSH User:
myuser - SSH Key:
~/.ssh/id_rsa
Client error message:
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
Expectation
I believe that because there is no local instance of mariadb binded to localhost on port 3306, I also cannot get the port forwarding to work over SSH. I must be missing a configuration that binds the address to the localhost.
I expect to be able to see this:
$ 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
skip-networkingby any chance anywhere within the reach of mariadb server? Check all included files, including the drop directory, usually it is/etc/my.cnf.d/, or whatever it is on your system. If it isn't, I'd file this as a bug. // BTW, you can forward TCP-to-socket or soket-to-socket with SSH just like TCP-to-TCP using -L command line option. So you don't need to listen at localhost just to be able to forward a database connection via SSH./etc/mysql/mariadb.conf.d/50-server.cnffor its config thus picking upbind-address?