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 [email protected]
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 [email protected] -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?

8
  • Why are you trying to set up an ssh tunnel instead of just connecting directly? Mar 31, 2018 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 Apr 1, 2018 at 6:19
  • I think some further clarification on the issue is required. Is there no access aside from ssh or is this over security concerns. Apr 27, 2018 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 Apr 30, 2018 at 6: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 Apr 30, 2018 at 9:14

1 Answer 1

-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 .

1
  • I am not trying to sync files. I need to connect sync/replicate two mysql databases Apr 1, 2018 at 6:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .