{"id":66,"date":"2012-08-10T14:34:04","date_gmt":"2012-08-10T10:34:04","guid":{"rendered":"http:\/\/xxxl.co.za\/?p=66"},"modified":"2023-03-14T19:04:21","modified_gmt":"2023-03-14T15:04:21","slug":"safe-ssh-tunnel-based-mysql-updates-well-i-think","status":"publish","type":"post","link":"https:\/\/xxxl.co.za\/?p=66","title":{"rendered":"Safe SSH tunnel based Mysql updates (Well i think?)"},"content":{"rendered":"

Server:
\nSetup user with key based authentication:
\n<\/p>\n

useradd -s \/bin\/false myuser\r\nmkdir \/home\/myuser\/.ssh\r\ntouch \/home\/myuser\/.ssh\/authorized_keys\r\nchown -R myuser:myuser \/home\/myuser\/.ssh\r\nchmod 755 \/home\/myuser\/.ssh\r\nchmod 600 \/home\/myuser\/.ssh\/authorized_keys<\/pre>\n
<\/code><\/p>\n
Client side:
\nInstall rpmforge repo and autossh.
\n<\/p>\n
rpm --import http:\/\/apt.sw.be\/RPM-GPG-KEY.dag.txt\r\nwget http:\/\/packages.sw.be\/rpmforge-release\/rpmforge-release-0.5.2-2.el5.rf.x86_64.rpm\r\nor\r\nwget http:\/\/packages.sw.be\/rpmforge-release\/rpmforge-release-0.5.2-2.el5.rf.i386.rpm\r\n\r\nrpm -K rpmforge-release-0.5.2-2.el5.rf.*.rpm\r\nrpm -i rpmforge-release-0.5.2-2.el5.rf.*.rpm\r\nsed -i "s\/enabled = 1\/enabled = 0\/" \/etc\/yum.repos.d\/rpmforge.repo\r\nyum -y install --enablerepo=rpmforge autossh\r\nssh-keygen -t rsa<\/pre>\n
<\/code>
\nSet up an RSA key pair as root on each client, leaving all questions blank:
\n<\/p>\n
ssh-keygen -t rsa\r\nroot@local# ssh-keygen -t rsa\r\nGenerating public\/private rsa key pair.\r\nEnter file in which to save the key (\/var\/root\/.ssh\/id_rsa):\r\nEnter passphrase (empty for no passphrase):\r\nEnter same passphrase again:\r\nYour identification has been saved in \/var\/root\/.ssh\/id_rsa.\r\nYour public key has been saved in \/var\/root\/.ssh\/id_rsa.pub.\r\nThe key fingerprint is:\r\nXX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX root@myhost.local<\/pre>\n
<\/code>
\nNow scp this to your server:
\n<\/p>\n
scp -P 22 \/root\/.ssh\/id_rsa.pub root@myserver.com:\/tmp\/myuser.local_rsa.pub<\/pre>\n
<\/code>
\nOn Server: Add to your authorized_keys.
\n<\/p>\n
cat \/tmp\/myuser.local_rsa.pub >> \/home\/myuser\/.ssh\/authorized_keys<\/pre>\n
<\/code>
\nClient side:
\ncreate \/etc\/init.d\/startautossh on client with contents below. \/\/This example nables connection to server MySQL port 3306 on localhost port 3307.
\n<\/p>\n
# pidfile: \/var\/run\/autossh.pid\r\n# @since 2012-02-22 15:31:47\r\n# @author Roderick Derks\r\n# Source function library\r\n. \/etc\/init.d\/functions\r\n\r\nprog="autossh"\r\nautossh="\/usr\/bin\/autossh"\r\nRETVAL=0\r\nAUTOSSH_PIDFILE=\/var\/run\/autossh.pid\r\n\r\n# Tunnel configuration\r\nLOCAL_PORT_LISTEN=3307\r\nREMOTE_DESTINATION_PORT=3306\r\nUSER=myuser\r\nREMOTE_DESTINATION_IP=yourserver.com\r\nREMOTE_SSH_SERVER_PORT=22\r\n\r\nstart() {\r\necho -n $"Starting $prog: "\r\nif [ ! -e $AUTOSSH_PIDFILE ]; then\r\nAUTOSSH_PIDFILE=$AUTOSSH_PIDFILE;export AUTOSSH_PIDFILE\r\nautossh -M 0 -q -f -N -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" -L $LOCAL_PORT_LISTEN:localhost:$REMOTE_DESTINATION_PORT -p $REMOTE_SSH_SERVER_PORT\u00a0 $USER@$REMOTE_DESTINATION_IP\r\n\r\nRETVAL=$?\r\nelse\r\nRETVAL=1\r\necho_failure\r\necho pid file still exists $AUTOSSH_PIDFILE\r\nfi\r\necho\r\n[ $RETVAL -eq 0 ] touch \/var\/lock\/subsys\/$prog\r\nreturn $RETVAL\r\n}\r\n\r\nstop() {\r\necho -n $"Stopping $prog: "\r\nkillproc $autossh\r\nRETVAL=$?\r\necho\r\n[ $RETVAL -eq 0 ] rm -f \/var\/lock\/subsys\/$prog rm -f $AUTOSSH_PIDFILE\r\nreturn $RETVAL\r\n}\r\n\r\ncase "$1" in\r\nstart)\r\nstart\r\n;;\r\nstop)\r\nstop\r\n;;\r\nrestart)\r\nstop\r\nstart\r\n;;\r\nstatus)\r\nstatus $autossh\r\nRETVAL=$?\r\n;;\r\n*)\r\n\r\necho $"Usage: $0 {start|stop|restart|status}"\r\nesac\r\nRETVAL=1\r\nClient side: (make script executeable):\r\nchmod +x \/etc\/init.d\/startautossh<\/pre>\n
<\/code>
\nReferences:
\nhttp:\/\/www.r71.nl\/kb\/technical\/348-autossh-init-script
\nhttp:\/\/tychoish.com\/rhizome\/persistent-ssh-tunels-with-autossh\/<\/p>\n
AutoSSH on CentOS<\/a><\/p><\/blockquote>\n