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 firstname.lastname@example.org 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 email@example.com 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 firstname.lastname@example.org. 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.
old authentication method? Sounds suspect..
ssh username: the name I login with over ssh, "thufir" as in "email@example.com"
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?
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..
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...
ssh -L 33000:192.168.1.2:3306 firstname.lastname@example.org
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: