Can't access MySQL through MySQL Workbench or HeidiSQL, but can access through shell

Question

I don't know if this is a user error on my end, or if the remote server is configured incorrectly.

I was given some connection info for a server as follows:

• Server IP
• server username
• keyfile
• mysql user
• mysql password

and I can shell in and run the "mysql" command with the mysql user and password I was given, but I can't setup a connection in either MySQL Workbench or in HeidiSQL.

In MySQL Workbench, the 'Standard TCP/IP over SSH' seemed to best fit the info I was given, but when I try to connect I get "Authentication error. Please check that your username and password are correct and try again." or "Authentication error opening SSH tunnel: Authentication error. Please check that your username and password are correct and try again." in the logfile.

In HeidiSQL the "MySQL (SSH tunnel)" seemed to best fit the info I was given, but when I try to connect there, I get "Lost connection to MySQL server at 'reading initial communication packet', system error:0"

I looked up both these errors and couldn't find anything for the first one, and the second one seemed to be more about server configuration. I'm not too savvy with server config, so I'm a bit stuck evaluating whether or not I'm doing something wrong on my end, or if something needs to be changed on the server. I'd appreciate some advice. Thanks!

Neibart

You are getting blocked in SSH before you get a chance to connect to mysql. Can you SSH into the server via terminal on your local pc ? – Has

Did you or anyone else change the my.cnf to enable remote connections? – Runnymede

I can SSH just fine via terminal, and my user can perform mysql operations in terminal, but I cannot get it to work over a MySQL tool like MySQL Workbench. I have no idea about the .cnf file. I was given these credentials and told they worked for the person who gave them to me. – Neibart

I went through Germán Robles troubleshooting steps (except for the .cnf file updates) and everything looked fine. – Neibart

I tried again in HeidiSQL and got it to work (so I guess that one was user error!). However I still can't get MySQL workbench to work. I've checked everything over and over and everything seems to be correct. I even had a coworker double-check my settings. I don't know what's going wrong in MySQL Workbench, but I'll just switch to HeidiSQL instead. – Neibart

Answer 1

You have to check a couple configuration steps before you can perform a remote mysql connection:

1. First of all you must check that mysql default port(3306) is listening on the correct interface. You can check this out using netstat command in the remote server:

   # netstat -tulnp tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1111/mysqld

   http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

2. Then you must check that the remote host is allowed to perform querys into mysql engine (you must check user and host permissions over your database).

   Connect into terminal and run the following command in mysql prompt:

   mysql> select user,host from mysql.user;

   Check the following link to deep insight mysql user and hosts system: https://dev.mysql.com/doc/refman/5.0/en/adding-users.html

   Check user,host permissions over your database:

   mysql> show grants for root@localhost;

   https://dev.mysql.com/doc/refman/5.1/en/grant.html

3. You must check that remote host (client) exist for example: [email protected]

4. Finally if you are using putty to encrypt over ssh you connection please check that you rsa key fingerprint is added you can perform this just logging ssh using you putty client.

Procurable

Answer 2

You must be more specific about the credentials you got. Is "server IP" the IP address of the MySQL server or an SSH server? Is the "server username" the SSH user or something else (e.g. a regular user of the target machine)? In my tutorial video on Youtube I explained what you have to use where to create a connection, including SSH connections: https://www.youtube.com/watch?v=DCgRF4KOYIY

Adnate