MySQL Tunnel, HASSSIO, and startup

Configuration
1

Hello, I am having trouble getting a MySQL tunnel for recorder to start automatically on hassio at startup. I have tried running the shell script (below) from within hassio on a startup event and as a shell script that runs every minute but recorder still fails to connect to the db.

Here is the bash script:

#!/bin/bash

exec >> $0.log 2>&1

dt=$(date '+%d/%m/%Y %H:%M:%S');

createTunnel() {
  /usr/bin/ssh -i /config/ssh/id_rsa -4 -N -L 3307:localhost:3306 [email protected] &
  if [[ $? -eq 0 ]]; then
    echo $dt tunnel created successfully
  else
    echo $dt an error occurred creating a tunnel. ReturnCode is $?
  fi
}

/bin/pidof ssh
if [[ $? -ne 0 ]]; then
  echo $dt creating new tunnel ...
  [ ! -f /root/.ssh/known_hosts ] && cp /config/ssh/known_hosts /root/.ssh/known_hosts
  createTunnel
else
  echo $dt tunnel up
fi

Please forgive me for not giving credit to the person who created the base of this script. I forget where I got it from.

My current success after a power cycle or reboot is that I have to ssh in, run the script, then restart hassio. That is the only way recorder reconnects to mysql correctly.

Can anyone help me run this script before HA starts so recorder does the right thing? Ideally, I would like to run the script continuously so that if the MySQL host goes down then the tunnel continues to try to connect.

Thanks!

2

Hi,

did you get this to work?
I’m about to start on the “same journey”, i.e. to move the recorder to an external MySQL Server. But to access it I require an SSh Tunnel.

What are the steps to setup the tunnel, BEFORE the recorder starts up?

BR
Yonz

3

Hey! I ended up just opening the 3306 port on the server and creating a looong password. I got it to work but not reliably. I had to use shell commands.

My solution looked something like this:

#!/bin/bash

exec >> $0.log 2>&1

dt=$(date '+%d/%m/%Y %H:%M:%S');

createTunnel() {
  /usr/bin/ssh -i /config/ssh/id_rsa -4 -N -L 3307:localhost:3306 [email protected] &
  if [[ $? -eq 0 ]]; then
    echo $dt tunnel created successfully
  else
    echo $dt an error occurred creating a tunnel. ReturnCode is $?
  fi
}

/bin/pidof ssh
if [[ $? -ne 0 ]]; then
  echo $dt creating new tunnel ...
  [ ! -f /root/.ssh/known_hosts ] && cp /config/ssh/known_hosts /root/.ssh/known_hosts
  createTunnel
else
  echo $dt tunnel up
fi