layth
Posts: 7
Joined: Sat Feb 08, 2020 8:07 am

ssh tunnel to access mysql instance on the pi

Sat Feb 08, 2020 9:40 am

I am running a mysql instance on the pi which I would like to access via ssh tunnel.

Code: Select all

 ssh -L 9000:localhost:9000 [email protected] 

This does not seem to work for me. Any suggestions ?

User avatar
DougieLawson
Posts: 37111
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: ssh tunnel to access mysql instance on the pi

Sun Feb 09, 2020 12:23 am

Is your MySQL server binding to the INADDR_ANY address (0.0.0.0)?

Code: Select all

[email protected]:~$ sudo netstat -tlnpu | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1043/mysqld
[email protected]:~$
I'd also suspect that 1.1.1.1 is NOT the address of your RPi (unless you're in Thailand).
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

layth
Posts: 7
Joined: Sat Feb 08, 2020 8:07 am

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 5:51 am

Here is the output:


Code: Select all

[email protected] ~> sudo netstat -tlnpu | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      522/mysqld    

tpyo kingg
Posts: 743
Joined: Mon Apr 09, 2018 5:26 pm
Location: N. Finland

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 5:59 am

The one end of the tunnel will have to be port 3306 if that is where you have the database listening. The other end can be as well, but that is not as important as long as you know where to point the client.

Code: Select all

ssh -L 9000:localhost:3306 [email protected]

# or 

ssh -L 3306:localhost:3306 [email protected]

layth
Posts: 7
Joined: Sat Feb 08, 2020 8:07 am

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 6:21 am

Thanks for the help. I've tried this but to no avail. The strange part is that I made a sample index.php file running on port 3000 and I was able to access that in the ssh tunnel!

User avatar
DougieLawson
Posts: 37111
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 10:45 am

Something has replaced my mysql config files

Change /etc/mysql/mariadb.conf.d/50-server.cnf

Code: Select all

bind-address            = 127.0.0.1
to

Code: Select all

bind-address            = 0.0.0.0
then restart mysql.

Edit: that's now working with ssh -N -L 3306:127.0.0.1:3306 [email protected] to get the tunnel running. On the remote system I had to use mysql -u tester -h 127.0.0.1 -p to get the connection running.
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

tpyo kingg
Posts: 743
Joined: Mon Apr 09, 2018 5:26 pm
Location: N. Finland

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 11:08 am

Changing the bind address should not help because the tunnel goes to 127.0.0.1 anyway. So if changing the bind address does anything it would just open the SQL daemon up to the LAN or Internet, depending on what connections the Rasperry Pi has.

The default settings for MariaDB will allow tunneling as described in the earlier post. So the question is what has been changed from the default and it would help to know more details about how it is not connecting.

Also, over on the Raspberry Pi, one can also look at the logs while a connection is attempted.

Code: Select all

tail -f /var/log/mysql/error.log 

User avatar
DougieLawson
Posts: 37111
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 11:14 am

tpyo kingg wrote:
Mon Feb 10, 2020 11:08 am
Changing the bind address should not help because the tunnel goes to 127.0.0.1 anyway. So if changing the bind address does anything it would just open the SQL daemon up to the LAN or Internet, depending on what connections the Rasperry Pi has.
Except that was the difference between my experiment failing and my second experiment working.
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

tpyo kingg
Posts: 743
Joined: Mon Apr 09, 2018 5:26 pm
Location: N. Finland

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 11:18 am

Interesting. Mine work out of the box with tunneling while bound to 127.0.0.1, but I notice you've used an IPv4 address explicitly for your tunnel. layth has 'localhost' which would also map to IPv6.

User avatar
DougieLawson
Posts: 37111
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: ssh tunnel to access mysql instance on the pi

Mon Feb 10, 2020 12:28 pm

tpyo kingg wrote:
Mon Feb 10, 2020 11:18 am
Interesting. Mine work out of the box with tunneling while bound to 127.0.0.1, but I notice you've used an IPv4 address explicitly for your tunnel. layth has 'localhost' which would also map to IPv6.
I'm using explicit IPv4 addresses because my IPv6 network was getting in the way. My router gives me a 6to4 tunnel and every device gets a globally unique IPv6 address.
Note: Having anything humorous in your signature is completely banned on this forum. Wear a tin-foil hat and you'll get a ban.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

layth
Posts: 7
Joined: Sat Feb 08, 2020 8:07 am

Re: ssh tunnel to access mysql instance on the pi

Thu Feb 13, 2020 9:12 am

After removing password from root user, it is now working. I am not really sure why this has happened, but I am glad it is working. I am going to attempt to change the password back and see what gives.

Here is the log:

Code: Select all

2020-02-13  1:07:36 0 [Note] InnoDB: Buffer pool(s) load completed at 200213  1:07:36
2020-02-13  1:07:36 0 [Note] Server socket created on IP: '127.0.0.1'.
2020-02-13  1:07:36 0 [Note] Reading of all Master_info entries succeeded
2020-02-13  1:07:36 0 [Note] Added new Master_info '' to hash table
2020-02-13  1:07:36 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.17-MariaDB-0+deb10u1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Raspbian 10

tpyo kingg
Posts: 743
Joined: Mon Apr 09, 2018 5:26 pm
Location: N. Finland

Re: ssh tunnel to access mysql instance on the pi

Fri Feb 14, 2020 1:14 pm

It would be good to have a password for the account.

What is the exact error message get when you try to connect via the tunnel? You do need to tell the client to use TCP instead of UNIX sockets:

Code: Select all

mysql --protocol=TCP -P 3306 -h localhost -u root -p

Return to “Networking and servers”