Can I tunnel from public server to private server?

Question

Originally, my question read "Replication possible with mysql master on private net and slave on Internet?" but then realized the problem is with the tunnel, not the replication.

A little history: We host our websites and email on the private intranet at the office and want to replicate mysql on the internet in case our network goes down. This is primarily for email.

I have tried and searched (this offered no help and I have all static IP's) and found nothing to help me with this problem.

I have a master mysql server on the private intranet, one slave on the intranet and one slave in the internet. Port forwarding at the router to the master, so essentially, the master IS internet facing.

I have tried tunneling with

autossh -M 0 -q -f -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -L 4444:localhost:22 user@public_ip_for_office

but get the resulting SYN_SENT with

lsof -i -n | grep ssh
ssh 7616  root 3u IPv4 147357 0t0  TCP Remote_ip:42602->Public_IP_for_office:ssh (SYN_SENT)

and it never gets ESTABLISHED. So far replication is not possible, because I have no active tunnel. (Local slave is setup and replicating properly.)

Can anyone shed some light having done this very thing before?

per @Aroly7:

sshd_config

PermitRootLogin no
PasswordAuthentication no
ChallengeResponseAuthentication no
UsePAM yes
X11Forwarding yes
PrintMotd no
AcceptEnv LANG LC_*
Subsystem   sftp    /usr/lib/openssh/sftp-server

output of "ssh -vvv -L 40648:127.0.0.1:3306 admin@98.x.x.x"

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 "98.x.x.x" port 22
debug2: ssh_connect_direct: needpriv 0
debug1: Connecting to 98.x.x.x [98.x.x.x] port 22.

output of ssh admin@98.x.x.x -p 40648 -v -L 40648:127.0.0.1:3306

debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 19: Applying options for *
debug1: Connecting to 98.x.x.x [98.x.x.x] port 40648.
debug1: Connection established.
debug1: permanently_set_uid: 0/0

debug1: Local version string SSH-2.0-OpenSSH_7.6p1 Ubuntu-4ubuntu0.3
debug1: Remote protocol version 2.0, remote software version OpenSSH_7.6p1 Ubuntu-4ubuntu0.3
debug1: match: OpenSSH_7.6p1 Ubuntu-4ubuntu0.3 pat OpenSSH* compat 0x04000000
debug1: Authenticating to 98.x.x.x:40648 as 'ktc_admin'
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
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
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: Server host key: ecdsa-sha2-nistp256 SHA256:nFnH4JaVGkI5z9a7X6cLHpvXxC2Lq9d5/jV+9y8L07U
debug1: Host '[98.x.x.x]:40648' is known and matches the ECDSA host key.
debug1: Found key in /root/.ssh/known_hosts:3
debug1: rekey after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: SSH2_MSG_NEWKEYS received
debug1: rekey after 134217728 blocks
debug1: SSH2_MSG_EXT_INFO received
debug1: kex_input_ext_info: server-sig-algs=<ssh-ed25519,ssh-rsa,rsa-sha2-256,rsa-sha2-512,ssh-dss,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521>
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,password
debug1: Next authentication method: publickey
debug1: Offering public key: RSA SHA256:YVTsXDSIQbkoTlTBWDX3Ar3oD8ct8HNHamEolnQko4Y /root/.ssh/id_rsa
debug1: Server accepts key: pkalg rsa-sha2-512 blen 279
debug1: Authentication succeeded (publickey).
duthenticated to 98.x.x.x ([98.x.x.x]:40648).
debug1: Local connections to LOCALHOST:40648 forwarded to remote address 127.0.0.1:3306
debug1: Local forwarding listening on ::1 port 40648.
debug1: channel 0: new [port listener]
debug1: Local forwarding listening on 127.0.0.1 port 40648.
debug1: channel 1: new [port listener]
debug1: channel 2: new [client-session]
debug1: Requesting no-more-sessions@openssh.com
debug1: Entering interactive session.
debug1: pledge: network
debug1: client_input_global_request: rtype hostkeys-00@openssh.com want_reply 0
debug1: Sending environment.
debug1: Sending env LANG = C.UTF-8

UPDATE

I have the following currently:

user@mail:~# lsof -i -n | grep ssh

sshd        945            user    3u  IPv4  19213      0t0  TCP *:ssh (LISTEN)
sshd        945            user    4u  IPv6  19224      0t0  TCP *:ssh (LISTEN)
sshd       9829            user    3u  IPv4 170078      0t0  TCP 142.x.x.x:ssh->98.x.x.x:46008 (ESTABLISHED)
sshd       9910            user    3u  IPv4 170163      0t0  TCP 142.x.x.x:ssh->98.x.x.x:46018 (ESTABLISHED)
sshd      18444            user    3u  IPv4 259519      0t0  TCP 142.x.x.x:ssh->98.x.x.x:40196 (ESTABLISHED)
ssh       18607            user    3u  IPv4 260946      0t0  TCP 142.x.x.x:37814->98.x.x.x:40648 (ESTABLISHED)
ssh       18607            user    4u  IPv6 260957      0t0  TCP [::1]:40648 (LISTEN)
ssh       18607            user    5u  IPv4 260958      0t0  TCP 127.0.0.1:40648 (LISTEN)
ssh       18751            user    3u  IPv4 262884      0t0  TCP 142.x.x.x:50590->98.x.x.x:ssh (SYN_SENT)
ssh       18756            user    3u  IPv4 262029      0t0  TCP 142.x.x.x:50594->98.x.x.x:ssh (SYN_SENT)
ssh       18758            user    3u  IPv4 262042      0t0  TCP 142.x.x.x:50596->98.x.x.x:ssh (SYN_SENT)
ssh       18763            user    3u  IPv4 262925      0t0  TCP 142.x.x.x:50598->98.x.x.x:ssh (SYN_SENT)

I have successfully created the tunnel, but cannot access mysql on the server on the intranet - I think because I have unix-socket-only logins enabled here?

Aroly7 · Answer 1 · 2019-04-10T22:20:42.140

0

From your question, I think you want to bind port on the master to MySQL port on the slave.

The right syntax would be -L $local_port_to_bind:127.0.0.1:$mysql_port_on_slave

Then try to connect to the slave by mysql --host=127.0.0.1 --port=$local_port_to_bind -u$User -p

If the problem persists please provide sshd_config and output of verbose ssh command.

OK in end the right autossh command will be autossh -M 0 -q -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -L 40648:127.0.0.1:3306 -p 4444 user@public_ip_for_office

edited Apr 10 '19 at 22:20

answered Apr 10 '19 at 19:37

Aroly7

417

added sshd_config to the post – richardwhitney Apr 10 '19 at 20:24
I am missing part where you try negotiate the connection – Aroly7 Apr 10 '19 at 20:32
That's a far as it gets and hangs – richardwhitney Apr 10 '19 at 21:23
@richardwhitney Can you at least connect to that server without port forwarding? – Aroly7 Apr 10 '19 at 21:27
yes @Aroly7 ssh user@98.x.x.x -p 4444 gets me in with a key pair – richardwhitney Apr 10 '19 at 21:54
@richardwhitney then use ssh -p 4444 -v -L 40648:127.0.0.1:3306 user@98.x.x.x without -p 4444 you have tried to connect on port 22 – Aroly7 Apr 10 '19 at 21:58
Got all kinds of output before connecting successfully - pasting into question. – richardwhitney Apr 10 '19 at 22:03
Sorry you cannot to chat so " As long as you have that session open you should be able to access the slave from master on port 40648 "mysql --host=127.0.0.1 --port=40648 -u$User -p"" – Aroly7 Apr 10 '19 at 22:06
@richardwhitney Yeah that could be an issue. – Aroly7 Apr 11 '19 at 16:58

score 0 · Answer 2 · answered Apr 11 '19 at 21:14

Thank you Arol7 for pointing me in the right direction!

I successfully created the tunnel with:

autossh -M 0 -q -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -L 4444:127.0.0.1:3306 -p 40648 root@98.x.x.x -f

note the trailing -f to send it to the background

Once I got the right parameters for the tunnel, setting up the slave was easy

Can I tunnel from public server to private server?

2 Answers2