Connecting Visual Studio Code to remote Mysql database

Question

I know this should be more focused but I lost whole day on this and I cant figure it out.

I am trying to connect to remote MySQL database using any of available VS Code extensions.

I tried to use SQLTools with MySQL/MariaDB plugin, ApexSQL Database Power Tools for VS Code, vscode-database.

I have read all documentations and searched here and else-ware for examples. All I am finding is connecting to local-host databases.

I just wont list all combinations I tried, because I feel I tried them all. Using all possible log in methods.

Biggest problem I have come across using few methods is when i enter remote server address and username. That username always transfers to: username@'93-138-183-84.adsl.net.t-com.hr' where 93-138-183-84.adsl.net.t-com.hr' of coarse is my current IP address.

And I get errors like:

Error connecting to server: Access denied for user 'xxx'@'93-138-183-84.adsl.net.t-com.hr' (using password: YES)

I tried using strings and example settings like:

Server=xxx;Port=3306;Database=xxx;Uid=xxx;Pwd=xxx;


{
  "name": "MySQL",
  "server": "my server adress",
  "driver": "MySQL",
  "port": 3306,
  "database": "xxx",
  "username": "xxx",
  "askForPassword": false,
  "password": "xxx",
  "connectionTimeout": 15,
  "mysqlOptions": {
    ... // options See section 2. mysqlOptions
  }
}

Nothing worked for me

PS; I checked for server address, user and port inside my database, so I don't think my credentials are wrong. I tried using database specific, and root user for whole server.

Is it even possible to connect to remote MySQL database from VS code to run query's, if it is, using what and how?

Answer 1

So finally after whole day:

using macOS:

1. I already had SSH keys added to system so I just need to open tunneling, its easy just one line, explanation is here: How to connect to a MySQL database using an SSH Tunnel

   ssh -p 722 -N -L 3306:localhost:3306 user@servername

• use your ports and C-panel logins.

Next in VS code (tested with SQLTools with MySQL/MariaDB plugin):

use 127.0.0.1 as server address and enter your DB credentials.

And that is it.

Now you have autocomplete SQL methods, SQL litter and autocomplete all tables and fields from remote DB in VS-Code.

Answer 2

I had the same issues and finally figured it out. Set up your mysql user to have a wildcard in the host field. By default when you set up a new user in mysql that users "host" field is set to 'localhhost'. When you try to connect to a remote mysql server from VSC it sends the string 'username'@'host' where 'host' is the ip address of the computer you are using to connect. I set up a mysql user as follows and then the connection worked fine:

CREATE USER 'sammy'@'192.168.0.%' IDENTIFIED BY 'password';

This will allow user sammy to access mysql from anywhere inside the network designated by the ip address, in this case it's my internal network. You can just use '%' and leave out the ip so the user can access from anywhere.

It's all right here: https://dev.mysql.com/doc/refman/5.7/en/create-user.html