Connect Visual Studio Code to a remote MySQL database: Simplify the connection process

Question

I know this should be more focused, but I wasted an entire day doing this and I can't figure it out.

I'm trying to connect to a remote MySQL database using any available VS Code extension.

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

I've read all the documentation and searched for examples here and in else-ware. All I found was to connect to the localhost database.

I'm just not going to list every combination I've tried because I feel like I've tried them all. Use all possible login methods.

The biggest problem I have with several methods is when I enter the remote server address and username. This username is always transferred to: username@'93-138-183-84.adsl.net.t-com.hr' The rough 93-138-183-84.adsl.net.t-com.hr is my current IP address.

I receive the following error:

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 sample 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 works for me

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

Is it possible to connect to a remote MySQL database from VS code to run a query (if so, what and how to run it?)

Answer 1

I also encountered the same problem and finally solved it. Set your mysql user to use wildcards in the host field. By default, when you set up a new user in mysql, the user's "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 machine you are connecting from. I set up the mysql user as follows and the connection works fine:

Create user "sammy"@"192.168.0.%" identified by "password";

This will allow user sammy to access mysql from anywhere within the network specified by the IP address, which in this case is my internal network. You can just use "%" and omit the IP so users can access from anywhere.

Everything is here: https://dev.mysql.com/doc/refman/5.7/en/create-user.html

Answer 2

Finally after a whole day:

Using macOS:

1. I already added the SSH keys to the system, so I just need to open the tunnel, it's easy, just one line, explained below: How to connect to MySQL database using SSH tunnel

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

• Log in using your port and C-panel.

Next step in VS Code (using SQLTools and MySQL/MariaDB plugin):

Use 127.0.0.1 as the server address and enter your database credentials.

That's it.

Now you can autocomplete SQL methods, SQL garbage and autocomplete all tables and fields in the remote database in VS-Code.