MySQL

MySQL Start Guide

1. Connections

Connection example:

{
  "name": "MySQL",
  "server": "localhost",
  "driver": "MySQL",
  "port": 3306,
  "database": "test_db",
  "username": "root",
  "askForPassword": false,
  "password": "root",
  "connectionTimeout": 15
}

Using a socket file example:

{
  "name": "MySQL",
  "driver": "MySQL",
  "database": "test_db",
  "username": "root",
  "askForPassword": false,
  "password": "root",
  "connectionTimeout": 15,
  "socketPath": "/path/to/mysqld.sock"
}

1.1 Specific Options

MySQL driver specific options can be passed using mysqlOptions settings.

{
  "name": "MySQL",
  "server": "localhost",
  "driver": "MySQL",
  "port": 5433,
  "database": "test_db",
  "username": "root",
  "askForPassword": false,
  "password": "root",
  "connectionTimeout": 15,
  "mysqlOptions": {
    ... // options See section 2. mysqlOptions
  }
}

2. mysqlOptions

We have two options of connectors for MySQL. You can choose which one to use by change the setting mysqlOptions.authProtocol. The allowed values are default and xprotocol, we use default by default since is the most often used.

Extra options can be used as defined in the connectors documentation.

• For MySQL default protocol
  • See https://github.com/mysqljs/mysql#connection-options and https://github.com/mysqljs/mysql#pool-options
• For MySQL xprotocol
  • See https://dev.mysql.com/doc/dev/connector-nodejs/8.0/

They will be passed to the pool constructor directly. See https://github.com/mtxr/vscode-sqltools/blob/master/packages/core/driver/mysql/index.ts .

Know Errors and How to Fix

MySQL 8 and ER_NOT_SUPPORTED_AUTH_MODE

Potential solutions:

• Ensure you’ve set authProtocol to xprotocol
• Add a trailing 0 to the port. See the MySQL Guide to Ports

Example configuration:

{
  "sqltools.connections": [
    {
      "askForPassword": true,
      "connectionTimeout": 30,
      "driver": "MySQL",
      "name": "LocalHost",
      "port": 33060,
      "server": "127.0.0.1",
      "username": "root",
      "mysqlOptions": {
        "authProtocol": "xprotocol"
      }
    }
  ]
}

3. IAM database authentication (Amazon RDS / Aurora)

For MySQL on Amazon RDS or Aurora, you can authenticate using IAM database authentication instead of a password. SQLTools signs a short-lived (15 min) auth token with the AWS SDK’s RDS Signer when opening the pool.

1. In the connection form, set Password mode to IAM database authentication.
2. Provide the AWS Region the RDS / Aurora instance is in (for example us-east-1).
3. Optionally provide an AWS Profile name from your shared AWS config (~/.aws/credentials or ~/.aws/config). Leave empty to use the default credential provider chain (environment variables, default profile, IMDS, SSO, etc.).
4. Enable SSL under mysqlOptions > SSL and supply the RDS CA bundle (downloadable from Using SSL/TLS to encrypt a connection to a DB instance). IAM database authentication requires SSL and will refuse to connect otherwise.
5. Configure the database user for IAM auth: CREATE USER <user> IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';.

Because the mysql2 library does not support an async password callback, the pool uses a single token for its lifetime. Tokens expire after 15 minutes; reconnect to refresh.

Example stored connection:

{
  "name": "Aurora IAM",
  "driver": "MySQL",
  "server": "my-cluster.cluster-xyz.us-east-1.rds.amazonaws.com",
  "port": 3306,
  "database": "app",
  "username": "dbuser",
  "useAwsIamAuth": true,
  "awsIamOptions": {
    "region": "us-east-1",
    "profile": "dev"
  },
  "mysqlOptions": {
    "ssl": {
      "ca": "/path/to/rds-global-bundle.pem"
    }
  }
}