0

I have installed on my application server mysql mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)). My operating system on my VPS is Ubuntu 20.04.1 LTS.

To manage this database I connect locally via SSH to the server and THEN accessing the database.

However, services such as google data studio need a jdbc url or a database hostname.

enter image description here

I tried my servers IP as hostname and then using the correct port, username, pwd. However, I get:

enter image description here

I am thinking that my hostname is wrong as it is locally on the server localhost and not the server's IP.

Any suggestions how to connect to the database or find the correct hostname?

I appreciate your replies!

Update

Running ss -nlp I get:

root@myVPS:~# sudo ss -nlp | grep mysql u_str LISTEN 0 70 /var/run/mysqld/mysqlx.sock 190906736 * 0 users:(("mysqld",pid=1668517,fd=33)) u_str LISTEN 0 151 /var/run/mysqld/mysqld.sock 190906739 * 0 users:(("mysqld",pid=1668517,fd=36)) u_dgr UNCONN 0 0 * 190906575 * 12685 users:(("mysqld",pid=1668517,fd=3)) tcp LISTEN 0 70 127.0.0.1:33060 0.0.0.0:* users:(("mysqld",pid=1668517,fd=32)) tcp LISTEN 0 151 *:3306 *:* users:(("mysqld",pid=1668517,fd=34))
Improve this question

1 Answer 1

Reset to default
2

(Mysql on Ubuntu? I thought this had been replaced in the repos with Mariadb).

You'll notice that you are being asked for a username and password. Did you create one in MySQL? Did you create a database to use? This can seem like a chicken and egg problem (it is). You should start by creating a database aand a user to access it.

I believe the default build of MySQL currently uses so_peer authentication, so you can connect as root without a password using the mysql cli tool:

user@host ~$ sudo mysql ... mysql > CREATE USER `testuser`@`localhost` IDENTIFIED BY 's3cr3t'; mysql > CREATE USER `testuser`@`%` IDENTIFIED BY 's3cr3t'; mysql > CREATE DATABASE example1; mysql > GRANT ALL ON example1.* TO `testuser`@`localhost`; mysql > GRANT ALL ON example1.* TO `testuser`@`%`; mysql > FLUSH PRIVILEGES; mysql > quit

In the context above '%' means any IP address. MySQL attaches a special meaning to 'localhost'. It DOES NOT describe the loopback network interface - it means the access is via a filesystem socket. This can cause a lot of confusion.

Usually MySQL comes configured to listen on the filesystem socket and sometimes the loopback interface. You can check this (and indeed if the mysql service is running at all with....

user@host ~$ sudo ss-nlp | grep mysql u_str LISTEN 0 128 /var/run/mysqld/mysqld.sock 842576 * 0 users:(("mysqld",pid=16463,fd=20)) tcp LISTEN 0 128 127.0.0.1:3306 *:*

Here I can see that the server is using the filesystem socket at /var/run/mysqld/mysqld.sock and port 3306 on the loopback interface.

The dialog you've shown us above does not obviously accomodate a filesystem socket - so you'll need to use the loopback interface. In order to connect to the network socket, you MUST tell the mysql client the host is at "127.0.0.1", not localhost.

Improve this answer
2
  • Thx for your reply! However, google data studio needs the hostname to the mysql server. if I try 127.0.0.1:33060 I cannot connect as this in on another server and not on a google server. Is there a way to "enhance" my current mysql installation with server capabilities? Commented Dec 28, 2021 at 20:32
  • 1
    Yes - but you're going beyond the scope of your question here. I would find it very exceptional that Google Data Studio will not accept 127.0.0.1 as the hostname (the port should be entered in a separate field) but it would be trivial to fudge a different alias in the hosts file (since this is a Linux, not a MS-Windows host). Once you've got that sorted, have a google for "mysql bind_address" to find out how to expose the service across the network. Commented Dec 29, 2021 at 10:50

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.