MySQL over SSH

DEPRECATED

The humanitec/mysql-ssh Driver is deprecated. Existing users are encouraged to migrate to using the Humanitec Agent with the humanitec/mysql Driver instead.

This provisions a new MySQL database in an existing MySQL instance. The Driver will first connect to an SSH server acting as a bastion host and then port forward to the MySQL instance. The SSH server must be accessible from Humanitec IPs and the MySQL instance must be directly accessible from the SSH server.

Property	Description
Resource type	`mysql`
Account type	None

Inputs

Values

Name	Type	Description
`host`	string	The IP Address or hostname that the instance is available on.
`port`	integer	The port the instance is listening on.
`append_host_to_user`	boolean	[Optional] Azure Databases for Postgres and MySQL require usernames to have `@servername` appended to them. Set this to `true` for the Driver to append this automatically. (See: Azure Database connection strings )
`copy_from_name`	string	[Optional] If provided, specifies the database in the same instance to copy data from.
`ssh_host`	string	Hostname or IP address of the SSH server.
`ssh_port`	integer	The port to connect to on the SSH server.
`ssh_user`	string	The username to use to connect to the SSH server.

Secrets

Name	Type	Description
`dbcredentials`	object	An object holding `username` and `password` properties for the MySQL superuser.

Notes

Configuring the SSH server

In order for the Driver to use the SSH server as a bastion host, the following needs to be configured:

The Humanitec Database Server Public Key must be added to the authorized_keys file on the server.
The user that is used for connecting on must have permissions to perform a port forward.
The SSH server must be configured to allow port forwarding - typically via an entry in the configuration file similar to AllowTcpForwarding yes

The Humanitec Database Server Public Key is:

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQC81rM8WC50rYoQvn0brj+iONhgs8GyAospZofqN+rPOu9dJTN6movAqmCRYbfZxaDpE86I5+HzAMW206znhxzLw/7ips709U4jkUzpTTpRm+6Q3draO6Kj6tRhhvGyUCCcBeG2E1ZpL+5CrYOQe6R2BTgrRS/3JhKA6A4VkN/HycrwQORJ/AtMWoNlh5hn0IOB5H9BMg7sBLYyX26tFQNdubJFIPDkMDzXbyS6SSVzqQMUZCR7ksCaPj/9eZMBWtNvklrkAsI9pgvFawT+yTOxIKzmJ4j1JTd7y+HjTxO1AGaddhkrxXcyVTt9Rz/8J+/BlvrTlSp4gOyKudIhAvk7HHr+nBPdBOM9pc1zOIfB8pcj6+tNm+Rwae/i9uK6PiTIefRkIxX3yVprwQawjg0FbNOAd4wtsMZU3XtKc3ScXtFhqPUkyKLefTJjRWwvS86NrKTO0fsewZnrvNuH94rXvBFPBACnfImtN6rC3oeqXmbZshq32z07LAdR5SLU+iU= [email protected]

Automatic population of database

MySQL does not provide any standard way of duplicating databases. Instead, the suggested approach is to “dump and restore” a database using tools such as mysqldump .

This Driver emulates a dump and restore of the database: for example, it serially copies data from the source database to the target database. It therefore suffers from limitations of dumping and restoring a database. The main issue is that if the source database is being written to, there are no guarantees about the data integrity in the resulting database. Consider a source database with 2 tables A and B:

Table A is copied to the target database.
New data is then written to Table A and Table B in the source database.
Table B is copied from the source database to the target database.

The target database will now have the updated to table B but not table A.

This capability should not be used for production databases or where data integrity must be guaranteed.

Example

Set the following environment variables for the CLI and API commands:

Variable	Example	Description
`HUMANITEC_TOKEN`	`my-token`	The authentication token for accessing the Humanitec API.
`HUMANITEC_ORG`	`my-org-id`	The unique identifier for the organization in Humanitec.

Use the command below for the interface of your choice to create a fresh MySQL database in an instance available at dev-mysql.internal.example.com accessed via a bastion host on bastion.example.com as the user example-ssh-user.

Create a file defining the Resource Definition you want to create:

cat << EOF > dev-mysql-ssh.yaml
apiVersion: entity.humanitec.io/v1b1
kind: Definition
metadata:
  id: dev-mysql-ssh
entity:
  driver_type: humanitec/mysql-ssh
  name: Dev MySQL SSH
  type: mysql
  driver_inputs:
    secrets:
      dbcredentials:
        username: root
        password: 53cr3t-P455w0rd
    values:
      host: dev-mysql.example.com
      port: 3306
      ssh_host: bastion.example.com
      ssh_port: 22
      ssh_user: example-ssh-user
  criteria:
  - env_type: development
EOF

Use the humctl create command to create the Resource Definition in the Organization defined by your configured context:

humctl create -f dev-mysql-ssh.yaml
rm dev-mysql-ssh.yaml

curl https://api.humanitec.io/orgs/${HUMANITEC_ORG}/resources/defs \
  -X POST \
  -H "Authorization: Bearer ${HUMANITEC_TOKEN}" \
  -H "Content-Type: application/json" \
  --data-binary '
{
  "id": "dev-mysql-ssh",
  "name": "Dev MySQL SSH",
  "type": "mysql",
  "criteria": [
    {
      "env_type": "development"
    }
  ],
  "driver_type": "humanitec/mysql-ssh",
  "driver_inputs": {
    "values": {
      "host": "dev-mysql.example.com",
      "port": 3306,
      "ssh_host": "bastion.example.com",
      "ssh_port": 22,
      "ssh_user": "example-ssh-user"
    },
    "secrets": {
      "dbcredentials": {
        "username": "root",
        "password": "53cr3t-P455w0rd"
      }
    }
  }
}'

Most MySQL implementations use a single shared Driver. For Google MySQL implementations, see the Google MySQL Driver .

Prerequisites

You must have a database instance/server running.
You must have a user defined on the instance for Workloads to use when connecting to the database.

Add a Resource Definition

From the Resource Management screen, click Add resource definition.
In the modal dialog click MySQL.
Next, select the mysql-ssh Driver.
Finally, provide the following information, then click Add MySQL.
1. In the ID field provide a unique ID for the Resource.
2. Provide the user or role of the database.
3. Provide the password for the database.
4. Provide a privilege list to be applied to the user.
5. Choose whether to have @servername appended to the username.
6. Provide the database hostname and port.
7. Enter the SSH hostname, port, and username.

Resource Matching

Now that the Resource is defined you will need to add matching criteria.

Click on the relevant row in the Resource Definition table.
Then switch to the Matching Criteria tab.
Click + Add new Criteria.
Configure the matching rules as needed.
Click Save.

MySQL over SSH

Inputs #

Values #

Secrets #

Notes #

Configuring the SSH server #

Automatic population of database #

Example #

Prerequisites #

Add a Resource Definition #

Resource Matching #