SSH隧道- MySQL服务器已经消失。

Question

我正试图通过以下php 7.4.1测试脚本连接到我的远程数据库：

局部

我已经在我的xampp机器上安装了ubuntu 16，并通过ssh隧道运行了下面的脚本来测试连接：

<?php
$mysqli = new mysqli("127.0.0.1", "root", "", "test_db", 13306);

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

/* check if server is alive */
if ($mysqli->ping()) {
    printf ("Our connection is ok!\n");
} else {
    printf ("Error: %s\n", $mysqli->error);
}

/* close connection */
$mysqli->close();
?>

在执行上述文件时，我的ssh-tunnel如下所示：

admin@admin-VirtualBox:~$ ssh -v -v -v -i /home/admin/.ssh/id_rsa -N -L 13306:127.0.0.1:3306 root@xx.xx.xxx.xxx
OpenSSH_7.6p1 Ubuntu-4ubuntu0.3, OpenSSL 1.0.2n  7 Dec 2017
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 19: Applying options for *
debug2: resolving "xx.xx.xxx.xxx" port 22
debug2: ssh_connect_direct: needpriv 0
debug1: Connecting to xx.xx.xxx.xxx [xx.xx.xxx.xxx] port 22.
debug1: Connection established.
debug1: identity file /home/admin/.ssh/id_rsa type 0
debug1: key_load_public: No such file or directory
debug1: identity file /home/admin/.ssh/id_rsa-cert type -1
debug1: Local version string SSH-2.0-OpenSSH_7.6p1 Ubuntu-4ubuntu0.3
debug1: Remote protocol version 2.0, remote software version OpenSSH_8.2p1 Ubuntu-4ubuntu0.1
debug1: match: OpenSSH_8.2p1 Ubuntu-4ubuntu0.1 pat OpenSSH* compat 0x04000000
debug2: fd 3 setting O_NONBLOCK
debug1: Authenticating to xx.xx.xxx.xxx:22 as 'root'
debug3: hostkeys_foreach: reading file "/home/admin/.ssh/known_hosts"
debug3: record_hostkey: found key type ECDSA in file /home/admin/.ssh/known_hosts:1
debug3: load_hostkeys: loaded 1 keys from xx.xx.xxx.xxx
debug3: order_hostkeyalgs: prefer hostkeyalgs: ecdsa-sha2-nistp256-cert-v01@openssh.com,ecdsa-sha2-nistp384-cert-v01@openssh.com,ecdsa-sha2-nistp521-cert-v01@openssh.com,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521
debug3: send packet: type 20
debug1: SSH2_MSG_KEXINIT sent
debug3: receive packet: type 20
debug1: SSH2_MSG_KEXINIT received
debug2: local client KEXINIT proposal
debug2: KEX algorithms: curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha256,diffie-hellman-group14-sha1,ext-info-c
debug2: host key algorithms: ecdsa-sha2-nistp256-cert-v01@openssh.com,ecdsa-sha2-nistp384-cert-v01@openssh.com,ecdsa-sha2-nistp521-cert-v01@openssh.com,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,ssh-ed25519-cert-v01@openssh.com,ssh-rsa-cert-v01@openssh.com,ssh-ed25519,rsa-sha2-512,rsa-sha2-256,ssh-rsa
debug2: ciphers ctos: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: ciphers stoc: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: MACs ctos: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: none,zlib@openssh.com,zlib
debug2: compression stoc: none,zlib@openssh.com,zlib
debug2: languages ctos: 
debug2: languages stoc: 
debug2: first_kex_follows 0 
debug2: reserved 0 
debug2: peer server KEXINIT proposal
debug2: KEX algorithms: curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,diffie-hellman-group14-sha256
debug2: host key algorithms: rsa-sha2-512,rsa-sha2-256,ssh-rsa,ecdsa-sha2-nistp256,ssh-ed25519
debug2: ciphers ctos: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: ciphers stoc: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
debug2: MACs ctos: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: none,zlib@openssh.com
debug2: compression stoc: none,zlib@openssh.com
debug2: languages ctos: 
debug2: languages stoc: 
debug2: first_kex_follows 0 
debug2: reserved 0 
debug1: kex: algorithm: curve25519-sha256
debug1: kex: host key algorithm: ecdsa-sha2-nistp256
debug1: kex: server->client cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug1: kex: client->server cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug3: send packet: type 30
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug3: receive packet: type 31
debug1: Server host key: ecdsa-sha2-nistp256 SHA256:aaaaaaaaaaaaaaaaaaaaa/aaaaaaaaaaaaaaaaaaaaa
debug3: hostkeys_foreach: reading file "/home/admin/.ssh/known_hosts"
debug3: record_hostkey: found key type ECDSA in file /home/admin/.ssh/known_hosts:1
debug3: load_hostkeys: loaded 1 keys from xx.xx.xxx.xxx
debug1: Host 'xx.xx.xxx.xxx' is known and matches the ECDSA host key.
debug1: Found key in /home/admin/.ssh/known_hosts:1
debug3: send packet: type 21
debug2: set_newkeys: mode 1
debug1: rekey after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug3: receive packet: type 21
debug1: SSH2_MSG_NEWKEYS received
debug2: set_newkeys: mode 0
debug1: rekey after 134217728 blocks
debug2: key: /home/admin/.ssh/id_rsa (0x55d7cd2a7400), explicit, agent
debug3: send packet: type 5
debug3: receive packet: type 7
debug1: SSH2_MSG_EXT_INFO received
debug1: kex_input_ext_info: server-sig-algs=<ssh-ed25519,sk-ssh-ed25519@openssh.com,ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,sk-ecdsa-sha2-nistp256@openssh.com>
debug3: receive packet: type 6
debug2: service_accept: ssh-userauth
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug3: send packet: type 50
debug3: receive packet: type 51
debug1: Authentications that can continue: publickey,password
debug3: start over, passed a different list publickey,password
debug3: preferred gssapi-keyex,gssapi-with-mic,publickey,keyboard-interactive,password
debug3: authmethod_lookup publickey
debug3: remaining preferred: keyboard-interactive,password
debug3: authmethod_is_enabled publickey
debug1: Next authentication method: publickey
debug1: Offering public key: RSA SHA256:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa /home/admin/.ssh/id_rsa
debug3: send_pubkey_test
debug3: send packet: type 50
debug2: we sent a publickey packet, wait for reply
debug3: receive packet: type 60
debug1: Server accepts key: pkalg rsa-sha2-512 blen 279
debug2: input_userauth_pk_ok: fp SHA256:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
debug3: sign_and_send_pubkey: RSA SHA256:aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
debug3: send packet: type 50
debug3: receive packet: type 52
debug1: Authentication succeeded (publickey).
Authenticated to xx.xx.xxx.xxx ([xx.xx.xxx.xxx]:22).
debug1: Local connections to LOCALHOST:13306 forwarded to remote address 127.0.0.1:3306
debug3: channel_setup_fwd_listener_tcpip: type 2 wildcard 0 addr NULL
debug3: sock_set_v6only: set socket 5 IPV6_V6ONLY
debug1: Local forwarding listening on ::1 port 13306.
debug2: fd 5 setting O_NONBLOCK
debug3: fd 5 is O_NONBLOCK
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 13306.
debug2: fd 6 setting O_NONBLOCK
debug3: fd 6 is O_NONBLOCK
debug1: channel 1: new [port listener]
debug2: fd 3 setting TCP_NODELAY
debug3: ssh_packet_set_tos: set IP_TOS 0x10
debug1: Requesting no-more-sessions@openssh.com
debug3: send packet: type 80
debug1: Entering interactive session.
debug1: pledge: network
debug3: receive packet: type 80
debug1: client_input_global_request: rtype hostkeys-00@openssh.com want_reply 0
debug3: receive packet: type 4
debug1: Remote: /root/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding
debug3: receive packet: type 4
debug1: Remote: /root/.ssh/authorized_keys:1: key options: agent-forwarding port-forwarding pty user-rc x11-forwarding
debug1: Connection to port 13306 forwarding to 127.0.0.1 port 3306 requested.
debug2: fd 7 setting TCP_NODELAY
debug2: fd 7 setting O_NONBLOCK
debug3: fd 7 is O_NONBLOCK
debug1: channel 2: new [direct-tcpip]
debug3: send packet: type 90
debug3: receive packet: type 91
debug2: channel 2: open confirm rwindow 2097152 rmax 32768
debug3: receive packet: type 96
debug2: channel 2: rcvd eof
debug2: channel 2: output open -> drain
debug2: channel 2: obuf empty
debug2: channel 2: close_write
debug2: channel 2: output drain -> closed
debug2: channel 2: read<=0 rfd 7 len 0
debug2: channel 2: read failed
debug2: channel 2: close_read
debug2: channel 2: input open -> drain
debug2: channel 2: ibuf empty
debug2: channel 2: send eof
debug3: send packet: type 96
debug2: channel 2: input drain -> closed
debug2: channel 2: send close
debug3: send packet: type 97
debug3: channel 2: will not send data after close
debug3: receive packet: type 97
debug2: channel 2: rcvd close
debug3: channel 2: will not send data after close
debug2: channel 2: is dead
debug2: channel 2: garbage collecting
debug1: channel 2: free: direct-tcpip: listening port 13306 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 36828 to 127.0.0.1 port 13306, nchannels 3
debug3: channel 2: status: The following connections are open:
  #2 direct-tcpip: listening port 13306 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 36828 to 127.0.0.1 port 13306 (t4 r0 i3/0 o3/0 fd 7/7 cc -1)

远程

mysql服务器显然正在运行：

root@ubuntu-2gb-nbg1-1:~# netstat -tlpn | grep mysql
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      457519/mysqld       
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      457519/mysqld       

对于变量我有以下配置，在运行SHOW VARIABLES LIKE '%time%';时我得到：

binlog_max_flush_queue_time 0
connect_timeout 90
default_password_lifetime   0
delayed_insert_timeout  300
explicit_defaults_for_timestamp ON
flush_time  0
have_statement_timeout  YES
innodb_flush_log_at_timeout 1
innodb_lock_wait_timeout    50
innodb_old_blocks_time  1000
innodb_rollback_on_timeout  OFF
interactive_timeout 300
lc_time_names   en_US
lock_wait_timeout   31536000
log_timestamps  UTC
long_query_time 10.000000
max_execution_time  0
mysqlx_connect_timeout  30
mysqlx_idle_worker_thread_timeout   60
mysqlx_interactive_timeout  28800
mysqlx_port_open_timeout    0
mysqlx_read_timeout 30
mysqlx_wait_timeout 28800
mysqlx_write_timeout    60
net_read_timeout    90
net_write_timeout   90
original_commit_timestamp   36028797018963968
regexp_time_limit   32
rpl_stop_slave_timeout  31536000
slave_net_timeout   60
slow_launch_time    2
system_time_zone    CET
time_zone   SYSTEM
timestamp   1614522618.931326
wait_timeout    300

我的防火墙规则如下所示：

root@ubuntu-2gb-nbg1-1:~# sudo ufw status verbose
Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip

To                         Action      From
--                         ------      ----
80/tcp (Nginx HTTP)        ALLOW IN    Anywhere                  
22/tcp                     ALLOW IN    Anywhere                  
22/tcp (OpenSSH)           ALLOW IN    Anywhere                  
127.0.0.1 3306/tcp         ALLOW IN    127.0.0.1                 
80/tcp (Nginx HTTP (v6))   ALLOW IN    Anywhere (v6)             
22/tcp (v6)                ALLOW IN    Anywhere (v6)             
22/tcp (OpenSSH (v6))      ALLOW IN    Anywhere (v6) 

但是，在执行本地php脚本时，我得到：

​

​

有什么建议我做错了吗？

谢谢你的回复！

更新

我的my.cnf如下所示：

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

文件夹mysqld.cnf中的/etc/mysql/mysql.conf.d如下所示：

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[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
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 1024M
# thread_stack      = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log        = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id     = 1
# log_bin           = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db      = include_database_name
# binlog_ignore_db  = include_database_name

# my settings
net_read_timeout=90
net_write_timeout=90
interactive_timeout=300
connect_timeout=90

innodb_log_file_size = 128M
log_error_verbosity = 3

文件夹mysqldump.cnf中的/etc/mysql/conf.d如下所示：

[mysqldump]

quick

quote-names

max_allowed_packet  = 16M

Answer 1

看起来ssh隧道不是指向REMOTE mysql服务器，而是指向LOCAL mysql服务器，在本地机器上监听端口3306。

所有关于mysql配置、授权和可能不方便调试下一个故障点(如果有的话)的建议都是这样的，但是首先，客户端到服务器的套接字连接应该指向正确的服务器。

一旦隧道实际指向远程服务器，检查连接失败根源的一个有用工具就是性能模式host_cache表。

见手册：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-host-cache-table.html

Answer 2

debug1: Remote protocol version 2.0, remote software version OpenSSH_8.2p1 Ubuntu-4ubuntu0.1

这个OpenSSH版本在Ubuntu20.04上使用。对于根用户，Ubuntu20.04/ MySQL 8服务器默认使用auth_socket身份验证插件。你把它改成mysql_native_password了吗？

mysql> ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '';
mysql> FLUSH PRIVILEGES;

Answer 3

上面给出的建议是有效的，用户权限需要更改，但我认为您需要检查这一点，以获得更清晰的远程访问：MySQL: How to allow remote connection to mysql。

在这里分配的配置绑定地址是127.0.0.1，您需要将其更改为：

mysql服务器ip

完全评论

或允许所有的ips

配置mysql服务器时，需要更改/添加权限和绑定地址。

更新：

您可以检查您作为要连接的用户是否具有特权。

select * from information_schema.USER_PRIVILEGES

对于所有这些，我使用以下方法修改了数据

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

对于特定IP

GRANT ALL PRIVILEGES ON *.* TO 'root'@'mysql_server_ip' IDENTIFIED BY 'password';

更新IP

update  information_schema.USER_PRIVILEGES set GRANTEE = `'root'@'new_mysqlserver_ip'` where GRANTEE = `'root'@'old_mysql_server_ip'` ;

别忘了刷新特权

flush privileges;

对于绑定地址:您可以检查它是否在/etc/mysql/mysql.conf.d/mysqld.cnf中被注释掉

然后，需要重新启动mysql服务器。