php - 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 [e-mail]
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: [e-mail],[e-mail],[e-mail],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,[e-mail],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: [e-mail],[e-mail],[e-mail],ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,[e-mail],[e-mail],ssh-ed25519,rsa-sha2-512,rsa-sha2-256,ssh-rsa
debug2: ciphers ctos: [e-mail],aes128-ctr,aes192-ctr,aes256-ctr,[e-mail],[e-mail]
debug2: ciphers stoc: [e-mail],aes128-ctr,aes192-ctr,aes256-ctr,[e-mail],[e-mail]
debug2: MACs ctos: [e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: [e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: none,[e-mail],zlib
debug2: compression stoc: none,[e-mail],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,[e-mail],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: [e-mail],aes128-ctr,aes192-ctr,aes256-ctr,[e-mail],[e-mail]
debug2: ciphers stoc: [e-mail],aes128-ctr,aes192-ctr,aes256-ctr,[e-mail],[e-mail]
debug2: MACs ctos: [e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: [e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],[e-mail],hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: none,[e-mail]
debug2: compression stoc: none,[e-mail]
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: [e-mail] MAC: <implicit> compression: none
debug1: kex: client->server cipher: [e-mail] 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,[e-mail],ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,[e-mail]>
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 [e-mail]
debug3: send packet: type 80
debug1: Entering interactive session.
debug1: pledge: network
debug3: receive packet: type 80
debug1: client_input_global_request: rtype [e-mail] 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-server 显然正在运行：

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

标签： phpmysqllaravelubuntussh

Answer 1

看起来 ssh 隧道没有指向REMOTEmysql 服务器，而是指向 mysql 服务器，而是LOCAL在本地机器上侦听端口 3306。

所有关于 mysql 配置、授权和调试下一个故障点的建议（如果有）可能会变得很方便，但首先，从客户端到服务器的套接字连接应该指向正确的服务器。

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

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