Question Cannot connect to MySQL Workbench

[PeterK900]

This forum got me connecting to the MySQL server via the command line. (Plesk, Linux ) with

mysql -uadmin -p'cat /etc/psa/.psa.shadow.'

However when I use the same credentials to create a workbench connection I get:

Your connection attempt failed for user 'admin' to the MySQL server at 199.199.99.0. Access denied for user 'admin'@'host-81-86-71-70.static.as9105.net (using password: YES)

I don't understand the host-81-86-71-70 - is this an IP address set somewhere in the configuration. I suspect it's got something to do with the use of the credential string I use in the command line login.

I tried posting this to the MySQL - Workbench forum but so far nobody has offered any help.

What am I doing wrong? Thanks.

 

[Mike99]

Hi, try this, it is working for me just fine, you are probably doing something wrong.

In connection settings in Workbench, change 123.123.123.123 with your server's public IP:

--------------------------------------------------------------------------------------

Connection method: Standard TCP/IP over SSH
SSH Hostname: 123.123.123.123:22
SSH Username: root
SSH Key File: /home/username/.ssh/youserverpublickey.pem
MySQL Hostname: 127.0.0.1
MySQL Port: 3306
Username: admin
Password: $AES-128-............................................MGUIP90lQ==

--------------------------------------------------------------------------------------

The last Password part is the content of your # cat /etc/psa/.psa.shadow, just paste it there when asked for a password.

 

[PeterK900]

Hi, try this, it is working for me just fine, you are probably doing something wrong.

In connection settings in Workbench, change 123.123.123.123 with your server's public IP:

--------------------------------------------------------------------------------------

Connection method: Standard TCP/IP over SSH
SSH Hostname: 123.123.123.123:22
SSH Username: root
SSH Key File: /home/username/.ssh/youserverpublickey.pem
MySQL Hostname: 127.0.0.1
MySQL Port: 3306
Username: admin
Password: $AES-128-............................................MGUIP90lQ==

--------------------------------------------------------------------------------------

The last Password part is the content of your # cat /etc/psa/.psa.shadow, just paste it there when asked for a password.

Hi Mike, Thanks for this and for testing it out. I've tried to follow your instructions, but I'm still failing to login - although the message in my first post has gone.

I presume the connection settings you refer to are entered via the UI, and not by editing a file.
The connection to the server is not via SSH - i.e I have no SSH Key File
When I paste the .psa.shadow key into the password box, it disappears, a popup flashes and then the login form re-displays.

Please see the screenshots on the attached file. I'm obviously still missing the point.

Thanks again for your help so far.

 

[Mike99]

Hi Peter,

make sure you enabled remote connections in Plesk How to enable remote access to MySQL server on Plesk? there is a video in the support article with instructions. You need to allow remote access, change the binding address in my.cnf and also make sure your firewall is not blocking port 3306.

If you don't want to do that, just generate SSH key so you can use TCP IP over SSH, it is more secure and you don't have to expose your server's database at all. The SSH key file is not for MySQL, is the file you use to connect to your Linux server over ssh, you should have one, if not find instructions on the web to generate one and use ssh-copy-id (Easiest way to copy ssh keys to another machine?) to copy it to your server.

 

[PeterK900]

Thanks again Mike. I don't need SSH on this server as it is only for development/experimentation.

I have looked at the video and all the requirements are in place - Bind address 0.0.0.0 and Allow Server to accept Remote External Connections is checked. And the fact that this is in place correctly is supported by the fact that I can access MySQL databases via ODBC and also via the command line with mysql -uadmin -p'cat /etc/psa/.psa.shadow.' from the /etc folder.

I don't think the problem is remote connection acceptance because this Plesk Forum post says you'd get a Connection Refused error message. Which I don't get. Your point about inserting the psa.shaddow as the password moved things forward. I'm wondering if the issue is simply inconsistency between the User Name and Password. The user is admin and the password string is psa.shadow. I wish I could read the message that is flashed up quickly before the password entry form is re-displayed!

I sense I must be doing something really stupid since you have got this to work. But I've looked hard at what I'm doing and checked all the points and it still doesn't work. Thanks for your on-going support with this.

 

[PeterK900]

Hi Mike,

I created a new database user and gave them access to all databases and using these credentials Workbench starts - i.e no error message. BUT... I now get a Workbench error saying it has "stopped working". But this isn't a Plesk issue, I'm sure. I'll re-install or try an earlier release, or a different version of Windows.

PS.. After all this... it looks as though MySQL Workbench does not work reliably with MariaDb See this post.