Fucking remote db doesn't want to work with me and workbench. DB is on an empty test server, no firewall issues on the network, powershell on my pc says ping ok, tcp failing though, server firewall not running, server up and running.
Tried to modify network access on db configs like bind-adress, set my db user "host" value to wildcard. Now I can log in on workbench with my user, yet root somehow fails, wtf?
And of course once the connection is live, no db us visible, accessible, nothing works. I'm so frustrated. About to nuke it and restart ... again!

Have you checked the authentication plugins of the user?

If it's socket, it doesn't matter what you set in host.

And.... For the love of god.

Don't use root. Create a separate super admin user with a different name and leave root as a fallback when shits going down and you have to unwank the fuckery you or someone else might have done via SSH and need a working user.

@IntrusionCM root is set to auth-socket, my own user isn't set to auth-socket.

@cb219

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

This is super unhelpful but i remember encountering exactly this while working with a remote azure sql db. I don’t remember if i ever got it to work.

Sorry!

I've solved it 😪
- root was on auth_socket which I replaced with mysql_native_password which for some reason caused problems
- had to comment out the "bind-address" in the config to get all remote connections in
- host needed to be set to 'localhost' -> '%' for root

Did the same now for a user I've added. It works, finally....

Did you open a port in services under /etc ?

Can you connect from local host ?

Root access is usually blocked remotely you need to specify specific user privileges

@YouAllSuck already commented my solution. Wouldn't have thought that it was such a pain to get it running the way I wanted. Took me almost a week to set up. Beforehand I couldn't get any connection, so that's a win.

@cb219

You have created a security nightmare btw.

It's a real bad way of setting up a database. Even if it is just for yourself, you shouldn't even think about doing it in this way. Do things right and proper. Always.

Last but not least.... If it took you a week - you should really learn to read documentation.

I'm not saying this to offend you, but it seems like you don't even know the basic stuff which is - given the complexity a database can be - a bad omen for the future. Don't know what you want to do or what you try to achieve - but learn the basics before you try to develop with databases... It will not only be easier, but spare you a lot of pain.

@IntrusionCM the server is just for practice, no sensitive data. This is the first time I've set up a db on a remote server, that db should be reachable. Later it shall talk to a backend on that same server and be reachable only for "maintenance". So gotta do everything wrong before learning how to make it right, correct? 😅
As I've only set up in-memory db's on my machine with localhost before or worked with already pre-configured setups of hosting providers, I couldn't imagine the things that would await me. "Almost a week" was a bit exaggerated, maybe 2-2½ days to figure out using stackoverflow and mysql documentation (at least trying to understand it), but with little practical knowledge on this setup.
I agree that it "might be" (or in fact is) a security nightmare, opening the connection to anyone. Any sources on how to improve in the future? Maybe I haven't found the right doc pages of MySQL yet?

Guess the basic gist is:

- socket auth means that the user is "fully" local - there is no auth except checking the username exists. Hence my hint that this is meant for recovery - root is an account that shall never be used for normal purposes

- any db should be treated like a multi user os - a user can have limits (e.g. number of connections to prevent ddos) and permissions. It's really equivalent to a multi user os where you don't want your mom to find you "private files" nor your brother to steal all INet bandwidth.

- if the MySQL server and backend application is on the same server, don't do remote auth at all.

Use SSH - create a SSH tunnel to the remote server, then connect to localhost.

Any database is non TLS by default. Your password and all data gets transmitted in clear text...

SSH was made for this. And any good IDE supports this by default.

(And yes. You should never use root for SSH too. Same principles apply).

That's the reason socket auth exists - too. Simply put, it's the acceptance that too many people shat on security, hence it's default for root.

And security wise, as there is no remote connection possible at all, it's the best.

An SSH tunnel doesn't require a bind to all IP addresses, too.

You can use an SSH tunnel and just connect to the socket.

:)

The MySQL documentation is stiff - yes.

But with basics I mean the stuff before you get down with databases - e.g. user permissions / multi user, networking knowledge, basic security stuff like what TLS is and how it works and then it kinda starts getting together.

If you don't know e.g. a multi user OS or TLS then the privilege system and pluggable auth of databases will be a nightmare.

With secure auth via SSH tunnel and socket auth, a good bunch of possible attack vectors is gone. You can then test around what / how e.g. the pluggable auth works, GRANT statements and so on.

@IntrusionCM oh wow, that's a bunch of points to reply to 😅
Regarding socket_auth for root. It feels ironic for it to not require a password, hence I changed it to mysql_native_password. Might have been wrong? I've created an additional user with password and some permissions, not a super user.
The db would be accessed by backend on the same server, as my created user, so the user host value could be "localhost"? Any other maintenance would be via ssh to server and using the db as root or my user? What about using workbench for maintenance on my machine?
I've learned a few basics about networks, the idea behind TLS/SSL, some light sprinkles about its usage inside the HTTP protocol and also generally about db's but lack foremost the practical knowledge except some personal little projects.
Hence my statement: doing everything wrong before learning to do it right.
I like how this might become like a student/teacher conversation. 😄