General pointers would be appreciated — it's likely I'm misunderstanding the mechanisms involved in a remote connection through ssh. I can use ssh to connect remotely, and then, through the CLI, connect to MySQL. I'd like to do that with MySQL Workbench.
I don't understand the parameters to send:
From the CLI, ssh thufir@192.168.1.2 works without a password because the key authenticates. Once I'm logged in, I can then access the database as so:
thufir@doge:~$
thufir@doge:~$ ssh thufir@192.168.1.2
Last login: Mon Feb 23 06:52:53 2015 from 192.168.1.3
Thank you for installing ViciBox Server v.6.0!
This software is available for free download at
http://www.vicibox.com. If you paid for this
software you have been ripped off. Please report
any fraud or abuses of this software to
abuse@vicidial.com. Please report any bugs on
the forum at http://www.vicidial.org
To configure the LAN settings type:
yast lan
To change the server IP in the database type:
/usr/share/astguiclient/ADMIN_update_server_ip.pl
Official paid-for ViciDial support is available at
http://www.vicidial.com
Free community-based ViciDial Support is available
at http://www.vicidial.org/VICIDIALforum
- ViciBox Redux v.6.0.3-141118
thufir@tleilax:~>
thufir@tleilax:~> mysql -u root asterisk
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1349
Server version: 5.5.33-MariaDB-log openSUSE package
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [asterisk]>
MariaDB [asterisk]>
(ViciDial redux ships without password the the MySQL root user; for the time being I'll leave that as is.)
I've tried a few different users; I've enabled and disabled "use old authentication protocol" without result.
To go through the specifics:
MySQL WB Setup
For Connection Method, select Standard TCP/IP over SSH
Set the name to whatever you want, preferably an easily recognizable label.
Under the Parameters Tab, enter the following information:
SSH Hostname: Your server IP address or URL
SSH Username: mysql-tunnel
SSH Password: leave blank/click “Clear” to be safe
SSH Keyfile: ~/.ssh/id_rsa (or if using Windows the keyfile generated by PuTTy)
MySQL Hostname: 127.0.0.1
MySQL Port: 3306
Username: (We will provide this – your database username)
Password: (We will provide this – your database password)
Click the Advanced tab and make sure Use the old authentication protocol is checked.
http://www.sononaco.com/faqs/connecting-to-databases-using-mysql-workbench/
old authentication method? Sounds suspect..
hostname: 192.168.1.2
ssh username: the name I login with over ssh, "thufir" as in "thufir@192.168.1.2"
ssh password: there's no password during ssh, it uses a key…
ssh key file: well, I just typed in the path manually
the other connection parameters are those MySQL connection paramters which I use once I'm logged in through ssh?
The full error from Workbench is:
Cannot Connect to Database Server
Your connection attempt failed for user 'root' from your host to server at tleilax:3306:
Tunnel error: Remote connection to tleilax:3306 failed: AttributeError("'Transport' object has no attribute 'window_size'",)
Please:
1 Check that mysql is running on server tleilax
2 Check that mysql is running on port 3306 (note: 3306 is the default, but this can be changed)
3 Check the root has rights to connect to tleilax from your address (mysql rights define what clients can connect to the server and from which machines)
4 Make sure you are both providing a password if needed and using the correct password for tleilax connecting from the host address you're connecting from
Starting with #3, I don't want (MySQL) root to connect from any address except localhost…isn't the point of using ssh that I first login as a user, and then workbench, only after logging in to the system, then initiates a database connection?
Troubleshooting steps:
thufir@doge:~$
thufir@doge:~$ telnet 192.168.1.2 3306
Trying 192.168.1.2...
telnet: Unable to connect to remote host: Connection timed out
thufir@doge:~$
so…port 3306 is closed. But that's ok, because ssh uses port 22..
and:
thufir@tleilax:~>
thufir@tleilax:~> mysql -u root -h 127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1529
Server version: 5.5.33-MariaDB-log openSUSE package
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
can connect once connected via ssh…
Finally:
ssh -L 33000:192.168.1.2:3306 thufir@192.168.1.2
connects me fine to tleilax..admittedly, I'm not quite sure what those parameters mean, I'll have to look them up.
See also this excellent question:
Best Answer
No you can't.
SSH is an operating system for remote administration, what is happening when you do ssh and directly enter to the database is because of a script or default shell changed in /etc/passwd for the specific user.
But for mariadb(mysql) (including workbench) this means nothing, cause in practice is very likely to do ssh root@yourserver and then mysql -pyourpass.
So ssh is not the protocol you are looking for.