RichardU's picture

Just created Turnkeylinux MySQL in Virtualbox.

Trying to connect to it with MySQL Workbench. Entering 192.168.1.100 into hostname doesn't work.

What do I enter for hostname? Anything else I need to configure?

Thanks, Richard

Forum: 
Jeremy Davis's picture

So first it'd be good to know if you can connect ok to the server. So try that in your web browser (if you haven't already). Just type in the IP address. You noted 192.168.1.100, so I'm guessing that's what's showing in Virtualbox, assuming so, use that.

And if that works, please check the "Quick start" tab for further info.

For what it's worth, I'll repeat it here:


Connection Hints

As per all previous releases, by default TurnKey MySQL (MariaDB) appliance listens on all interfaces via (default MySQL/MariaDB) port 3306.

However, since v16.0 there have been some changes...

New remote user username

As of v16.0+ the default "root-like" user is now named "remote".

SSL now enabled (and required) for the "remote" user

SSL is now enabled and required for remote TCP connections to the MySQL/MariaDB server. If desired it can be disabled (and re-enabled) via the Confconsole plugin (Advanced >> System Settings >> MySQL remote SSL) and/or the 'turnkey-mysql-ssl' commandline tool.

SSL details

Self-signed certificates, signed by a custom CA cert are all generated on firstboot and stored in '/etc/mysql/certificates'. To connect remotely via SSL, you will need to download the relevant files and configure your client to use these, or reconfigure it to your desires. The required files are:

/etc/mysql/certificates/ca.pem # The CA certifcate
/etc/mysql/certificates/cert.pem # The certificate file
/etc/mysql/certificates/cert.key # The key file

For example, to use the commandline MySQL/MariaDB client from another TurnKey instance, assuming that the files have been downloaded to the same local locations, the following lines are required in the MySQL/MariaDB client config ('/etc/mysql/mariadb.conf.d/50-client.cnf'):

ssl_ca = /etc/mysql/certificates/ca.pem
ssl-cert = /etc/mysql/certificates/cert.pem
ssl-key = /etc/mysql/certificates/cert.key

Note that the user who is launching the client must have read permission for these files.

Once configured, then connection should work as per usual remote MySQL/MariaDB connection. E.g.:

root@core ~# mysql -h remote-mysql.example.com -u remote -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 41
Server version: 10.3.22-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Then to demonstrate that the connection is encrypted, you can use the '\s' command. I.e.:

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:		41
Current database:
Current user:		remote@remote-mysql.example.com
SSL:			Cipher in use is DHE-RSA-AES256-SHA
Current pager:		less -X -R -F
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.3.22-MariaDB-0+deb10u1 Debian 10
Protocol version:	10
Connection:		192.168.1.74 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		3306
Uptime:			34 min 12 sec

Threads: 7  Questions: 77  Slow queries: 0  Opens: 32  Flush tables: 1  Open tables: 26  Queries per second avg: 0.037
--------------

Note the ciper noted against "SSL:"! :)

Alternate configurations

There are a number of alternate configrations possible (including using "proper" CA signed certs) but you are on your own with those for now. Please see the MariaDB "Securing Connections" KB page for further ideas.

If you do configure this appliance to connect via SSL in alternate way and would like to share your config (please do!), and/or have any questions please feel free to post in the TurnKey forums.

For the most up to date details, please check the MySQL appliance page and/or the docs.

RichardU's picture

Thanks, Jeremy

I can connect, and I believe what you're calling the "Quick start" tab is now called "Connection Docs"

I don't need SSL, so I set about disabling it using Confconsole. Took a while to figure out where that is, but I found the answer here

I saw you are planning to update the Docs. You should know there's a dead link to the Confconsole documentation source on this page

Also, when I toggle Advanced >> System Settings >> MySQL remote SSL, it says: Automatic certificate renewal is currently disabled/enabled

To me, saying that certificate renewal is disabled is not the same as saying you no longer need a certificate for access. Based on what you wrote, having toggled that off, I'm assuming I no longer need a certificate, yet I am still unable to connect from Knime and I get this error

Execute failed: Access denied for user 'remote'@'Richard.localdomain' (using password: YES)

How would I track this down? 

Thanks.

Jeremy Davis's picture

Ok, so first things first, thanks for the detailed response. I really appreciate the feedback and it sounds like there are quite a few rough edges here that could be improved.

So first up, you are correct, that is the "Connection Docs" (I was thinking "Quick start" because that's what other appliances have). Perhaps we could change that and/or make that documentation stand out more? Perhaps that may have made getting started easier? (Hence perhaps it should be "Quick start" or "Getting started"?

It sounds like it would have been useful to guide you to Confconsole better? Perhaps there should be a link directly to the Confconsole doc page there?

Thanks for the heads up on the dead link, but FWIW that is the source code of the documentation on the website. Ironically the broken link in the source should be self-referencing (it's the link back to the doc source code for the published page on the website).

Thanks for the heads up on the confusing message when toggling the MySQL SSL certs. That's a bug. It should say, something like "enable/disable SSL remote connections" or something better. (That was an oversight; much of he script was borrowed from elsewhere and I clearly didn't update all the text).

So after look at the code, nothing is jumping out at me. It appears to enable/disable the SSL config and restart MySQL to apply the changed config. So to assist to understand the state of your machine, please run the following. Please run them as root on a shell on your appliance (e.g. Webshell or SSH client).

First check what the commandline 'turnkey-mysql-ssl' script tells us:

turnkey-mysql-ssl status

If that reports that SSL is enabled, please (re)run it to disable:

turnkey-mysql-ssl disable

Once you've done that, please test loggin in via the commandline:

mysql -u remote -p

It will interactively ask you for your password, then should drop you to a MySQL (actually a MariaDB) prompt that looks like this:

MariaDB [(none)]>

Run the command '\s' to gather info. It should look something like this (note this is with SSL enabled, your SSL line should say something else!).

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:		41
Current database
Current user:		remote@localhost
SSL:			Cipher in use is DHE-RSA-AES256-SHA
Current pager:		less -X -R -F
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.3.22-MariaDB-0+deb10u1 Debian 10
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		3306
Uptime:			34 min 12 sec

Threads: 7  Questions: 77  Slow queries: 0  Opens: 32  Flush tables: 1  Open tables: 26  Queries per second avg: 0.037
--------------

Assuming that SSL is disabled and login via CLI worked, then I'm not sure what else might be going wrong? You should be able to connect with it set up like that...

Perhaps you need to check the logs for details? Maybe that will give a hint of what is going wrong? Perhaps try this (if for some reason that doesn't work, please try a '-t' swithc instead):

journalctl -u mariadb

If there is still nothing obvious, then perhaps there is something else going on on your network? Let me know how you go and I'll think what other issues might be at play here...

RichardU's picture

I fired up a brand new instance. Logged in with root

turnkey-mysql-ssl status

Returns: True

turnkey-mysql-ssl disable

Returns: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'REQUIRE NONE' at line 1

Hope that's useful information.

 

Jeremy Davis's picture

Awesome, thanks for that. Yes, that is very useful info!

It turns out that my code needed a tweak. I'm not sure how this slipped through testing?! Anyway, I have opened an issue regarding this and have a bugfixed script for you to download. I have noted how to download it on the issue, but for convenience, I'll repeat it here:

url=https://raw.githubusercontent.com
repo=JedMeister/mysql/16.2-update
file=usr/local/bin/turnkey-mysql-ssl
full_url=$url/$repo/overlay/$file

wget -O /$file $full_url

(As noted on the issue, once I merge the code, the issue will be closed and that line will fail. You will then need to download from the main repo.)

RichardU's picture

FYI, this link in the issue self-references:

The fixed script have been pushed (but not yet merged as of this post) and can currently be found here.

I'll wait a while and start from scratch. Cheers.

Jeremy Davis's picture

Thanks. I've fixed it now (I had neglected to include the link targets).

Add new comment