0

Everything started when I was asked to set up a syncing method for our Mysql server. Considering that I am a developer I got into deep waters first with DBA and now I found myself into SysAdmin.

So I need to connect from a remote server (slave, using ) to an other remote server (master) in order to achive master to slave replication of a database. One of my current problem is how to pipe thrugh to my master that sits behind a secure connection listening to a specific port. I am kind lost with all the ports that I see around the web. I have the local port on my slave, that should connect to the masters port and then connect to 3306 port of mysql.

I have set up new users on both the machines (just to keep them isolated from existing users) and so far I have created an ssh connection from the client/slave to the server/master using this
ssh -pMasterPort user_on_master@master.Ip
But I can not set up the port forwarding in order to connect to mySql using a local (slave) port. I used this
ssh -R 3336:127.0.01:3306 user_on_master@master.Ip -P MasterPort
but when I try to connect using
mysql -h 127.0.0.1 -P 3306
I am getting error ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

any help would be appreciated

EDIT
ok, I am not sure what I wrote wrong the first time around, but now I am getting a different error. when I run

mysql -h 127.0.0.1 -P 3336 -ugemh -pgemh@69

I get

channel 2: open failed: connect failed: Connection refused  
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I have no idea what I am doing wrong. The port is there open and waiting, when I run sudo lsof -i -n among others I get

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
....
ssh 31338 skaros 4u IPv6 501647763 0t0 TCP [::1]:3336 (LISTEN)
ssh 31338 skaros 5u IPv4 501647764 0t0 TCP 127.0.0.1:3336 (LISTEN)

On a side not, it seems that the port forwarding is closing at some point. Now (few hours later) the above command doesn’t produce the same result. The lines involving port 3336 don’t exist any more. how can I make them to stick in order to keep the tunnel for future use?

Skaros Ilias
  • 131
  • 5
  • Why are you trying to set up an ssh tunnel instead of just connecting directly? – Michael Hampton Mar 31 '18 at 16:37
  • @MichaelHampton The master is behind an ssh server, and from what I understand I need ssh tunnel for the mysql connection to work. Even through the MySql Workbench I connect via ssh tunnel – Skaros Ilias Apr 01 '18 at 06:19
  • I think some further clarification on the issue is required. Is there no access aside from ssh or is this over security concerns. – Timothy Frew Apr 27 '18 at 23:45
  • @TimothyFrew I am not sure why such a clarification is needed. The machine is on server room, so technically there is physical access to it, but its not easy for me to get there, I am at remote location. So practically there is no other way to connect to it, just ssh – Skaros Ilias Apr 30 '18 at 06:47
  • The clarification wasn’t in terms of physical access. I was more wondering why you were doing the replication over ssh tunnel? Why not just MySQL—>MySQL – Timothy Frew Apr 30 '18 at 09:14
  • @TimothyFrew there is no direct access to the machine. it is behind ssh with port tunnelling. – Skaros Ilias May 02 '18 at 11:16
  • Can you not poke a whole in the firewall to allow 3306 between the two machines I can’t see doing it over ssh as a reliable nor stable solution – Timothy Frew May 02 '18 at 11:19
  • @TimothyFrew I am not sure how, and even if i did, i dont think I am allowed to. I have seen some articles that people made this to work though. I believe there should be a way. – Skaros Ilias May 02 '18 at 11:25

1 Answers1

-1

Use rsync -avHPe "ssh -pPORTNUMBER" user@server:/path/to/file /home/user/path/to/file to securely download from a server using alternate ssh port. To securely upload to a server try rsync -avHPe "ssh -pPORTNUMBER" /home/user/path/to/file -e ssh user@server:/path/to/file .

TurpakpbIc
  • 31
  • 1