Connect a Self-Managed MySQL Data Source to NineData via SSH Tunnel (Linux)

NineData supports multiple data source connection methods. This guide shows how to add a self-managed MySQL data source through an SSH tunnel on Linux.

Before you begin

• The SSH service is installed and running on the server that hosts the target data source.

• The SSH configuration file (sshd_config) on the data source server contains the following settings:

  • AllowTcpForwarding yes

  • AuthorizedKeysFile .ssh/authorized_keys

    tip

    sshd_config: by default, the file resides in the /etc/ssh directory.

Environment

This example uses the following environment to connect a self-managed data source through an SSH tunnel:

• Data source version: Linux version 4.4180+
• SSH Version: OpenSSH_8.2p1
• Data source: MySQL 8.0

tip

For Windows, see Connect a self-managed MySQL data source through an SSH tunnel on Windows.

Procedure

1. Sign in to the NineData Console.

2. In the left navigation pane, choose Datasource > Datasource.

3. Click Create Datasource. On the Create Datasource page, configure these parameters.

   Parameters	Description
   Name	Enter a meaningful data source name so the team can find and manage it later.
   Data source Location	Select On-Premise/Other Cloud
   Environment	PROD (Production) and DEV (Development) environments are provided by default, and you can also use customized environments. Note: In organizational mode, the database environment can also be used for permission policy management. For example, the production environment administrator role can access only data sources in the production environment and cannot access data sources in other environments. For more information, see Managing Roles.
   DB Type	Select MySQL.
   Connection	Select SSH Tunnel.
   SSH Host	Enter the IP address or domain name of the server that hosts the target data source, plus the SSH port. The default SSH port is 22.
   SSH Authentication Method	Password: Connect with SSH Username and SSH Password. Key (recommended): Connect with SSH Username and Key File. This option requires generated key files. For instructions, see Appendix.
   SSH Username	Enter the login username of the server where the target data source is located.
   SSH Password	Displayed when SSH Authentication Method is Password. Enter the SSH password for the server that hosts the target data source. Note: Click Connection Test. If Connection Succeeded appears, the SSH tunnel is reachable. If an error appears, check the username and password.
   Key File	Displayed when SSH Authentication Method is Key. Click Upload and upload the private key file, which is the generated key file without the .pub suffix. For instructions, see Appendix.
   Password	Displayed when SSH Authentication Method is Key. Enter the passphrase that was set when the key file was generated. If no passphrase was set in step 1, leave this field empty. Note: Click Connection Test. If Connection Succeeded appears, the SSH tunnel is reachable. If an error appears, troubleshoot based on the message and try again.
   Host	Enter the data source host and port. Note: Because NineData connects to the data source through the SSH tunnel, you can usually enter localhost or 127.0.0.1 and the target data source port.
   DB Account	Username for the database account.
   DB Password	Enter the password for the database account. Note: Click Connection Test. If Connection Succeeded appears, the data source connection is available. If an error appears, check the database account and password.
   Access Region	Select the region/location closer to data source for better network performance.
   Encryption	Enable SSL encryption when the data source requires encrypted connections. Turn the switch on or off as needed. Click > beside Encryption to expand advanced settings. Note: Options differ by data source type. Use the console as the source of truth. For MySQL SSL configuration, see Configure MySQL encrypted connections.

4. Click Create Datasource to complete adding the data source.

Result

NineData creates the MySQL data source and stores the SSH tunnel configuration. After Connection Test succeeds, NineData can reach the target database through the SSH tunnel.

Next steps

• Use the data source in SQL Console, data replication, backup, comparison, or other supported NineData features.
• If the connection test fails, check the SSH service, key or password authentication, authorized_keys permissions, and the database host and port.

Appendix: Generate SSH Tunnel Key Files

Note: Run these commands in the terminal on the server where the data source is located.

1. Run `ssh-keygen -f your_key_name` to generate a key file. When prompted for a passphrase, either enter one and press Enter or press Enter without entering a value.
   tip

   The command generates the following two files:

   • <key file name>: Private key used for SSH authentication.
   • <key file name>.pub: Public key stored on the server and matched with the private key during login.

2. Enter the cat <key file name>.pub >> ~/.ssh/authorized_keys command to add the public key content to the end of the authorized_keys file.

   tip

   The authorized_keys file must be 600 or stricter, which means only the file owner can read and write it. Looser permissions may cause the connection to fail. Run ll or ls -l in the ~/.ssh/ directory to check the permission. If needed, run chmod 600 authorized_keys to update it.