TCP 포트를 사용하여 원격 컴퓨터의 MySQL Unix 도메인 소켓에 연결하려고 합니다. 강화하기 위해 mySQL 서버에서 네트워크를 제거했습니다.
SSH 터널은 다음과 같습니다.
MyPC <---------------> 리눅스 서버 1 <------> 리눅스 서버 2(MySQL DB)
1단계) TCP 포트 3088을 로컬 및 원격 포트로 사용하여 MyPC에서 Linux Server 1로 SSH를 통해 연결합니다.
2단계) 로컬 TCP 포트 3088 및 대상 소켓 /var/lib/mysql/mysql.sock을 사용하여 Linux Server 1에서 Linux Server 2로 SSH
예제 명령:
- ssh -L localhost:3088:/var/lib/mysql/mysql.sock -vv [이메일 보호됨]
이 문제는 MyPC의 클라이언트를 통해 MySQL 데이터베이스에 액세스하려고 할 때 발생합니다. 클라이언트가 데이터베이스에 연결할 수 없으며 터미널 세션에서 다음 오류 메시지가 나타납니다.
debug1: /var/lib/mysql/mysql.sock 포트-2로 전달된 포트 3088에 대한 연결 요청입니다.
debug2: fd 11 O_NONBLOCK 설정 debug1: 채널 3: 신규 [[이메일 보호됨]]
채널 3: 오픈 실패: 관리 금지: 오픈 실패
debug2: 채널 3: 좀비
debug2: 통로 3: 가비지 수집
디버그 1: 채널 3: 유휴:[이메일 보호됨]: /var/lib/mysql/mysql.sock 포트-2의 포트 3088에서 수신하고, 127.0.0.1 포트 60358에서 127.0.0.1 포트 3088, nchannels 4로 연결합니다.
소켓이 올바른지 확인했으며 소켓 파일을 사용하여 Linux Server 2에서 로컬로 데이터베이스에 액세스할 수 있습니다.
MySQL(my.cnf:skip-networking)에서 네트워킹을 비활성화하기 전에 Linux Server 1에서 다음 예제 명령을 사용하는 대신 포트 3306을 사용하여 서버에 연결할 수 있었습니다.
- ssh -L 3088:localhost:3306 [이메일 보호됨]
Linux 2의 sshd_config 파일은 다음과 같습니다.
# $OpenBSD: sshd_config,v 1.80 2008/07/02 02:24:18 djm Exp $
# This is the sshd server system-wide configuration file. See
# sshd_config(5) for more information.
# This sshd was compiled with PATH=/usr/local/bin:/bin:/usr/bin
# The strategy used for options in the default sshd_config shipped with
# OpenSSH is to specify options with their default value where
# possible, but leave them commented. Uncommented options change a
# default value.
Ciphers aes128-ctr,aes192-ctr,aes256-ctr KexAlgorithms diffie-hellman-group-exchange-sha256,diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha1 MACs hmac-sha2-256,hmac-sha2-512,hmac-sha1
#Port 22
#AddressFamily any AddressFamily inet
#ListenAddress 0.0.0.0
#ListenAddress ::
# Disable legacy (protocol version 1) support in the server for new
# installations. In future the default will change to require explicit
# activation of protocol 1 Protocol 2
# HostKey for protocol version 1
#HostKey /etc/ssh/ssh_host_key
# HostKeys for protocol version 2
#HostKey /etc/ssh/ssh_host_rsa_key
#HostKey /etc/ssh/ssh_host_dsa_key
# Lifetime and size of ephemeral version 1 server key
#KeyRegenerationInterval 1h
#ServerKeyBits 1024
# Logging
# obsoletes QuietMode and FascistLogging
#SyslogFacility AUTH SyslogFacility AUTHPRIV
#LogLevel INFO LogLevel VERBOSE
# Authentication:
#LoginGraceTime 2m
#PermitRootLogin yes PermitRootLogin no
#StrictModes yes
#MaxAuthTries 6
#MaxSessions 10
#RSAAuthentication yes
#PubkeyAuthentication yes
#AuthorizedKeysFile .ssh/authorized_keys
#AuthorizedKeysCommand none
#AuthorizedKeysCommandRunAs nobody
# For this to work you will also need host keys in /etc/ssh/ssh_known_hosts
#RhostsRSAAuthentication no
# similar for protocol version 2
#HostbasedAuthentication no
# Change to yes if you don't trust ~/.ssh/known_hosts for
# RhostsRSAAuthentication and HostbasedAuthentication
#IgnoreUserKnownHosts no
# Don't read the user's ~/.rhosts and ~/.shosts files
#IgnoreRhosts yes
# To disable tunneled clear text passwords, change to no here!
#PasswordAuthentication yes
#PermitEmptyPasswords no PasswordAuthentication yes
# Change to no to disable s/key passwords
#ChallengeResponseAuthentication yes ChallengeResponseAuthentication no
# Kerberos options
#KerberosAuthentication no
#KerberosOrLocalPasswd yes
#KerberosTicketCleanup yes
#KerberosGetAFSToken no
#KerberosUseKuserok yes
# GSSAPI options
#GSSAPIAuthentication no GSSAPIAuthentication yes
#GSSAPICleanupCredentials yes GSSAPICleanupCredentials yes
#GSSAPIStrictAcceptorCheck yes
#GSSAPIKeyExchange no
# Set this to 'yes' to enable PAM authentication, account processing,
# and session processing. If this is enabled, PAM authentication will
# be allowed through the ChallengeResponseAuthentication and
# PasswordAuthentication. Depending on your PAM configuration,
# PAM authentication via ChallengeResponseAuthentication may bypass
# the setting of "PermitRootLogin without-password".
# If you just want the PAM account and session checks to run without
# PAM authentication, then enable this but set PasswordAuthentication
# and ChallengeResponseAuthentication to 'no'.
#UsePAM no UsePAM yes
# Accept locale-related environment variables AcceptEnv LANG LC_CTYPE LC_NUMERIC LC_TIME LC_COLLATE LC_MONETARY LC_MESSAGES AcceptEnv LC_PAPER LC_NAME LC_ADDRESS LC_TELEPHONE LC_MEASUREMENT AcceptEnv LC_IDENTIFICATION LC_ALL LANGUAGE AcceptEnv XMODIFIERS
#AllowAgentForwarding yes
#AllowTcpForwarding yes
#AllowStreamLocalForwarding yes
#GatewayPorts no
#X11Forwarding no X11Forwarding yes
#X11DisplayOffset 10
#X11UseLocalhost yes X11UseLocalhost no
#PrintMotd yes
#PrintLastLog yes
#TCPKeepAlive yes
#UseLogin no
#UsePrivilegeSeparation yes
#PermitUserEnvironment no
#Compression delayed
#ClientAliveInterval 0 ClientAliveInterval 300
#ClientAliveCountMax 3
#ShowPatchLevel no
#UseDNS yes UseDNS no
#PidFile /var/run/sshd.pid
#MaxStartups 10:30:100
#PermitTunnel no
#ChrootDirectory none
# no default banner path Banner /etc/issue
Ciphers aes128-ctr,aes192-ctr,aes256-ctr KexAlgorithms diffie-hellman-group-exchange-sha256,diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha1 MACs hmac-sha2-256,hmac-sha2-512,hmac-sha1
# override default of no subsystems Subsystem sftp /usr/libexec/openssh/sftp-server
# Example of overriding settings on a per-user basis
#Match User anoncvs
# X11Forwarding no
# AllowTcpForwarding no
# ForceCommand cvs server
AllowTcpForwarding yes/any를 활성화하고 sshd를 다시 시작해 보았지만 동작이 변경되지 않았습니다. 마찬가지로 MySQL(my.cnf:skip-networking)에서 네트워킹을 비활성화하기 전에 Linux Server 1에서 다음 예제 명령을 사용하는 대신 포트 3306을 사용하여 서버에 연결할 수 있었습니다.
- ssh -L 3088:localhost:3306 [이메일 보호됨]
성공적인 인증의 일부 디버그 출력을 Linux Server 2에 추가하면 MySQL 클라이언트가 터널을 통해 MySql 서버에 연결을 시도할 때 오류 메시지가 표시됩니다.
ssh -L localhost:3088:/var/lib/mysql/mysql.sock -vv [email protected]
OpenSSH_7.4p1, OpenSSL 1.0.2k-fips 26 Jan 2017
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 58: Applying options for *
debug2: resolving "10.10.10.134" port 22
debug2: ssh_connect_direct: needpriv 0
debug1: Connecting to 10.10.10.134 [10.10.10.134] port 22.
debug1: Connection established.
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_rsa type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_rsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_dsa type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_dsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_ecdsa type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_ecdsa-cert type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_ed25519 type -1
debug1: key_load_public: No such file or directory
debug1: identity file /home/userName/.ssh/id_ed25519-cert type -1
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_7.4
debug1: Remote protocol version 2.0, remote software version OpenSSH_5.3
debug1: match: OpenSSH_5.3 pat OpenSSH_5* compat 0x0c000000
debug2: fd 4 setting O_NONBLOCK
debug1: Authenticating to 10.10.10.134:22 as 'userName'
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug2: local client KEXINIT proposal
debug2: KEX algorithms: curve25519-sha256,[email protected],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,diffie-hellman-group1-sha1,ext-info-c
debug2: host key algorithms: [email protected],rsa-sha2-512,rsa-sha2-256,ssh-rsa,[email protected],[email protected],[email protected],[email protected],[email protected],ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,ssh-ed25519,ssh-dss
debug2: ciphers ctos: [email protected],aes128-ctr,aes192-ctr,aes256-ctr,[email protected],[email protected],aes128-cbc,aes192-cbc,aes256-cbc
debug2: ciphers stoc: [email protected],aes128-ctr,aes192-ctr,aes256-ctr,[email protected],[email protected],aes128-cbc,aes192-cbc,aes256-cbc
debug2: MACs ctos: [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: none,[email protected],zlib
debug2: compression stoc: none,[email protected],zlib
debug2: languages ctos:
debug2: languages stoc:
debug2: first_kex_follows 0
debug2: reserved 0
debug2: peer server KEXINIT proposal
debug2: KEX algorithms: diffie-hellman-group-exchange-sha256,diffie-hellman-group-exchange-sha1,diffie-hellman-group14-sha1
debug2: host key algorithms: ssh-rsa,ssh-dss
debug2: ciphers ctos: aes128-ctr,aes192-ctr,aes256-ctr
debug2: ciphers stoc: aes128-ctr,aes192-ctr,aes256-ctr
debug2: MACs ctos: hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: MACs stoc: hmac-sha2-256,hmac-sha2-512,hmac-sha1
debug2: compression ctos: none,[email protected]
debug2: compression stoc: none,[email protected]
debug2: languages ctos:
debug2: languages stoc:
debug2: first_kex_follows 0
debug2: reserved 0
debug1: kex: algorithm: diffie-hellman-group-exchange-sha256
debug1: kex: host key algorithm: ssh-rsa
debug1: kex: server->client cipher: aes128-ctr MAC: hmac-sha2-256 compression: none
debug1: kex: client->server cipher: aes128-ctr MAC: hmac-sha2-256 compression: none
debug1: kex: diffie-hellman-group-exchange-sha256 need=32 dh_need=32
debug1: kex: diffie-hellman-group-exchange-sha256 need=32 dh_need=32
debug1: SSH2_MSG_KEX_DH_GEX_REQUEST(1024<8192<8192) sent
debug1: got SSH2_MSG_KEX_DH_GEX_GROUP
debug2: bits set: 4090/8192
debug1: SSH2_MSG_KEX_DH_GEX_INIT sent
debug1: got SSH2_MSG_KEX_DH_GEX_REPLY
debug1: Host '10.10.10.134' is known and matches the RSA host key.
debug1: Found key in /home/userName/.ssh/known_hosts:1
debug2: bits set: 4061/8192
debug2: set_newkeys: mode 1
debug1: rekey after 4294967296 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug2: set_newkeys: mode 0
debug1: rekey after 4294967296 blocks
debug2: key: /home/userName/.ssh/id_rsa ((nil))
debug2: key: /home/userName/.ssh/id_dsa ((nil))
debug2: key: /home/userName/.ssh/id_ecdsa ((nil))
debug2: key: /home/userName/.ssh/id_ed25519 ((nil))
debug2: service_accept: ssh-userauth
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic,password
debug1: Next authentication method: gssapi-keyex
debug1: No valid Key exchange context
debug2: we did not send a packet, disable method
debug1: Next authentication method: gssapi-with-mic
debug1: Unspecified GSS failure. Minor code may provide more information
No Kerberos credentials available (default cache: KEYRING:persistent:2085)
debug1: Unspecified GSS failure. Minor code may provide more information
No Kerberos credentials available (default cache: KEYRING:persistent:2085)
debug2: we did not send a packet, disable method
debug1: Next authentication method: publickey
debug1: Trying private key: /home/userName/.ssh/id_rsa
debug1: Trying private key: /home/userName/.ssh/id_dsa
debug1: Trying private key: /home/userName/.ssh/id_ecdsa
debug1: Trying private key: /home/userName/.ssh/id_ed25519
debug2: we did not send a packet, disable method
debug1: Next authentication method: password
[email protected]'s password:
debug2: we sent a password packet, wait for reply
debug1: Authentication succeeded (password).
Authenticated to 10.10.10.134 ([10.10.10.134]:22).
debug1: Local connections to localhost:3088 forwarded to remote address /var/lib/mysql/mysql.sock:-2
debug1: Local forwarding listening on ::1 port 3088.
debug2: fd 5 setting O_NONBLOCK
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 3088.
debug2: fd 6 setting O_NONBLOCK
debug1: channel 1: new [port listener]
debug1: channel 2: new [client-session]
debug2: channel 2: send open
debug1: Requesting [email protected]
debug1: Entering interactive session.
debug1: pledge: network
debug2: callback start
debug2: fd 4 setting TCP_NODELAY
debug2: client_session2_setup: id 2
debug2: channel 2: request pty-req confirm 1
debug1: Sending environment.
debug1: Sending env LANG = en_CA.UTF-8
debug2: channel 2: request env confirm 0
debug2: channel 2: request shell confirm 1
debug2: callback done
debug2: channel 2: open confirm rwindow 0 rmax 32768
debug2: channel_input_status_confirm: type 99 id 2
debug2: PTY allocation request accepted on channel 2
debug2: channel 2: rcvd adjust 2097152
debug2: channel_input_status_confirm: type 99 id 2
debug2: shell request accepted on channel 2
MySQL 클라이언트 연결(예: MySQL Workbench)
debug1: Connection to port 3088 forwarding to /var/lib/mysql/mysql.sock port -2 requested.
debug2: fd 10 setting O_NONBLOCK
debug1: channel 3: new [[email protected]]
channel 3: open failed: administratively prohibited: open failed
debug2: channel 3: zombie
debug2: channel 3: garbage collecting
debug1: channel 3: free: [email protected]: listening port 3088 for /var/lib/mysql/mysql.sock port -2, connect from 127.0.0.1 port 33034 to 127.0.0.1 port 3088, nchannels 4
누구든지 제공할 수 있는 도움에 감사드립니다.